background preloader

VBA

Facebook Twitter

VBA Check if date is valid in a certain format DD.MM.YYYY. Quickly read and write a binary file to and from an array. Read file in VBA (txt, xml, csv, binary) - The Analyst Cave. Today shortly on how to read files using VBA (VBA read file).

Read file in VBA (txt, xml, csv, binary) - The Analyst Cave

Text/binary files are common ways of storing data as opposed to databases or regular Excel files. Looking at various resources I missed a single resource which would demonstrate the various methods for PROPERLY reading files in VBA. Writing an array to a worksheet range – correction. The Microsoft Excel blog has some tips for speeding up VBA performance in Excel 2007, which apply equally to earlier versions.

Writing an array to a worksheet range – correction

The number 2 tip (which in my opinion should have been the number 1 tip, and probably the number 3 to 10 tip as well) was “Read/Write Large Blocks of Cells in a Single Operation”. This could be expressed more generally as “minimise the number of data transfer operations between the worksheet and VBA”, which then also covers the benefits of writing VBA code rather than using a .worksheetfunction call for functions that are found in Excel but not VBA. I covered this subject in one my first posts to this blog Ranges and Arrays-2, but looking back at this post I see that the code did not work as intended. What I wrote was: Excel VBA: Sort/Sorting an Array of Text. Sort a 1 Dimensional Array. Sort an Array.

Excel VBA: Sort/Sorting an Array of Text. Sort a 1 Dimensional Array

See Also: Sort Sheets/WorksheetsThe Excel VBA macro code below can be used in any Excel Workbook. When run, it will sort the 1 dimensional array of strings, MyArray. It will output the unsorted array of values to A1:A10, then sort the array, then output the new sorted array of values to B1:B10. It should be noted that one could also output the array to an Excel Worksheet, sort using Excel's built in sort, then fill the array again with the sorted values. In this example, we use an array of text strings, but it could be an array of numeric values. VBA-Excel: Arrays – Two Dimension, Dynamic Array. For cre­at­ing two dimen­sional dynamic array in excel, fol­low the steps below: Declare the two dimen­sional ArrayResize the arrayStore val­ues in arrayRetrieve val­ues from array Declare the two dimen­sional Array Dim arrT­woD() Resize the array.

VBA-Excel: Arrays – Two Dimension, Dynamic Array

Dynamic Arrays. This documentation is archived and is not being maintained.

Dynamic Arrays

Sometimes you may not know exactly how large to make an array. You may want to have the capability of changing the size of the array at run time. A dynamic array can be resized at any time. Arrays in Visual Basic. Updated: July 20, 2015 For the latest documentation on Visual Studio 2017 RC, see Visual Studio 2017 RC Documentation.

Arrays in Visual Basic

By using an array, you can refer to these related values by the same name, and use a number that’s called an index or subscript to tell them apart. The individual values are called the elements of the array. They’re contiguous from index 0 through the highest index value. Assign range to an array : Array « Data Type « VBA / Excel / Access / Word. 5 Different Ways to Find The Last Row or Last Column Using VBA — The Spreadsheet Guru. Excel VBA Programming - Multidimensional Arrays. The arrays in our last lesson were all one dimensional arrays.

Excel VBA Programming - Multidimensional Arrays

It's one dimensional because there's only one column of items. But you can have arrays with more than one dimension. In theory you could have an array with up to 60 dimensions, in Excel VBA. However, you'll be glad to know that it's a rare programme that uses more than 3 dimensions, or even 2 dimensions. Using a named range in Data Validation list. How to invoke a SOAP Web Service from custom VBA Code. Recently I have been involved with several integration projects between different EPM products.

How to invoke a SOAP Web Service from custom VBA Code

Initially it was quite challenging because as with any integration project the devil is always in the detail. The great thing with SAP EPM products is that there are web services for major processes and functions that need to be performed. I needed to invoke a web service for an EPM product but had to do it in SAP BPC NW. Now there is several methods to invoke a web service from BPC NW, but it wouldnt be transportable to the MS product. So the solution was to invoke it from the client machine using VBA because on the client machine it can invoke any web service. How to invoke a SOAP Web Service from custom VBA Code. SOAP Request via VBA in Excel.

Hoping someone can help with this.

SOAP Request via VBA in Excel

I'm trying to test sending a request.xml file using VBA in Excel and can't quite figure out how to do it. I'd like to have the response be sent to the MessageBox before I can go any further. Slicing an Array Without Loop – Application.Index. Posted: June 12, 2013 by Transformer in Excel, VBA Tags: Advanced, Array, Range, Trick of the Week Sometimes we need to slice an array i.e. fetch a row/column from a multidimensional array.

Slicing an Array Without Loop – Application.Index

There is no inbuilt function in VBA to do the same and the most common way to do so is using a loop. However it can be done using a worksheet function named Index. Syntax : Application.Index(Array, Row_Number, Column_Number) OR Application.WorksheetFunction.Index(Array, Row_Number, Column_Number)