Sub Macro1() Dim pets(1 to 2, 1 to 2, 1 to 2) pets(1,1,1) = "dog" pets(1,2,1) = 5 pets(2,1,1) = "cat" pets(2,2,1) = 9 pets(1,1,2) = "snake" pets(1,2,2) = 3 pets(2,1,2) = "fish" pets(2,2,2) = 4 End Sub Each element or value has a index number indicating its position in the array.Arrays always have a lower and upper limit, the Lbound statement returns the lower limit and the Ubound statement returns the upper limit. If the lower limit is 1 and the upper limit is 5, the array contains 5 values.The performance improvements can be dramatic - something that takes hours can be done in minutes.
The good news is that VBA provides the tools and objects to massively speed things up.
The key to this is to use variant arrays to grab all the data from a woksheet or range and then to work on this array.
This post will teach you how to work with excel arrays in visual basic for applications (VBA). Excel is much quicker executing tasks with arrays than working with values on a worksheet.
Arrays exists in your internal computer RAM memory while workbooks exists on your hard drive.
It is possible to return values vertically also, se Transpose later in this post.
User defined function The following user defined function (udf) returns array values but you need to enter it as an array formula.
Working with data from your internal computer RAM memory is much faster.
Note that this clears all previous saved array values.
Other attributes of the cells, font information, comment text etc, is dropped.
If you need to operate on cells based on font colour, content of comments, style or other attribute then using a variant array doesn't gain you much.