Arrays in Sub procedures

Working With Arrays in Sub Procedures Passing Values From a VBA Module TO A Worksheet... 

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. 

A RANGE SPECIFIED IN A SUB PROCEDURE CAN BE SET EQUAL TO AN ARRAY VARIABLE... 

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 ... 

Equating a variable in VBA to a worksheet range automatically creates an array in VBA in which each array element contains the value stored in the cell. Using the Set command to equate an object variable in VBA to a worksheet range automatically creates an array of "cell" objects in VBA. If you change the value of one of the array elements by means of your VBA code, the value in that cell in the worksheet will change, as illustrated by the Sub procedure in Figure 15-3. 

VBA considers a one-dimensional array to have the elements of the array in a row. This can cause problems when you select a range of cells in a column and assign an array to it, as in the Sub procedure shown in Figure 15-6. 

A second way is to specify both the row and the column dimensions of the array, so as to make it an array in a column, as illustrated in the Sub procedure shown in Figure 15-8. 

A second approach is to use the Array keyword. The Array function returns a variant that contains an array. In the Sub procedure shown in Figure 15-12, the variable MyArray contains an array of text values, but an array can contain text, number values, the value of a VBA variable, even a Range reference. 

ArrayDemos.xls contains 14 Sub or Function procedures that illustrate various features of using arrays in VBA. 

The thickness of the low permeability fault rocks is an important variable in evaluation of cross-fault flow behaviour. Because fault zones are usually composed of complex arrays of intersecting sub-seismic faults and fractures and because the permeability reductions associated with faulting develop after low amounts ( cm) of displacement incorporation of the impact of complex fault zones (as opposed to single faults ) is not a simple procedure. 

---