VBA array

The Dim statement is used to declare the size of an array. Unless specified otherwise, VBA arrays begin with an index of 0. The statement... 

There are at least two ways to get values from a worksheet into a VBA array. You can either set up a loop to read the value of each worksheet cell and write the value to the appropriate element of an array, or you can assign the VBA array to a worksheet range. These two methods are described next. There is a definite difference with respect to speed. (See "Speed Differences in Reading or Writing Arrays Created by Two Different Methods" later in this chapter.)... 

USING A LOOP TO TRANSFER VALUES FROM A WORKSHEET TO A VBA ARRAY... 

The Sub procedure shown in Figure 15-1 reads values from worksheet cells and writes them to elements of a VBA array by means of a loop. Either the Cells method or the Range method can be used in the example that follows, the Range method is used, with the concatenation operator, to access the appropriate cell in the range. In this example the values to be copied are in column A, beginning in row 2. To illustrate an additional point, the values extend down to a row that must be determined by the VBA code. 

If a variable in a VBA Sub procedure is set equal to a range of cells in a worksheet, that variable becomes an array. No Dim statement is necessary. Thus the following expression in Figure 15-2 creates a VBA array called TestArray ... 

The worksheet array can be a range reference or a name that refers to a reference. Thus, if the name XRange had been assigned to the range "A2 A1 0", then the following expression would also create a VBA array called TestArray ... 

Note that the values in the range of cells have not been transferred to a VBA array the VBA variable simply "points" to the range on the worksheet. 

Figure 15-4. Passing values from a VBA array to a worksheet.

Illustrates that 1-D VBA arrays have elements in a row, not a column. 

Reads values from column A, beginning in row 2, into a VBA array. 

---