Vertex42 The Excel Nexus
If one-dimensional arrays are analogous to a single column in a spreadsheet, then two-dimensional arrays are analogous to multiple columns in a spreadsheet. Three-dimensional arrays are analogous to using multiple worksheets and higher dimensions than three are a bit difficult to imagine, but nevertheless are available. You can declare multi-dimensional arrays in VBA with up to 60 dimensions. Unless you're comfortable imagining multi-dimensional spaces greater than dimension three, I suggest keeping the number of dimensions in an array to three or less.
Dim myArray(10, 2) As Integer
The above declaration creates a two-dimensional integer array with 11 rows and 3 columns (remember the lower-bound is 0). Access the individual elements of the array using the row and column indices.
This example assigns the value of the spreadsheet cell B6 to the sixth row and second column in the array myArray.
As with one-dimensional arrays, multi-dimensional arrays are typically accessed within loops; however, you need to use nested loops in order to access both indices in a multidimensional array.
The sub procedure below transposes the values of a group of cells in a worksheet. This sub procedure takes input from the first ten rows and three columns in a worksheet and transposes the values to the first three rows and ten columns in the same worksheet. See Figure 4.4 and Figure 4.5 for depictions of the initial spreadsheet and the spreadsheet resulting from running the Transpose() sub procedure.
After variable declarations, the values in the spreadsheet are loaded into the two-dimensional array named transArray.
An Excel spreadsheet prior to running the
Transpose() sub procedure.
An Excel spreadsheet prior to running the
Transpose() sub procedure.
Q Microsoft Excel - Trans pose 2 E.xts
An Excel spreadsheet after running the
Transpose() sub procedure.
Q Microsoft Excel - Trans pose 2 E.xts
Type a question for help -.fix
J J j J ' J J. ¿S.: ■ -I -la^-ilit & -1; LMT. . g! - ^ Security... % fr hj, g
¡¿J File Edt yaw [nsert Famiat IodIs Did Window Hs^ . Gnok Antiqua - - B/U ~ ~ ~ J-: > %
ft 61
21 43
76 91
93 |
63 |
94 |
93 |
65 |
54! |
61 |
93 |
33 |
75! |
77 |
26 |
61 |
24; |
31 |
32 |
50 |
26 |
39 |
50 |
59 |
5 |
17 |
57 |
A three-dimensional array is declared with three values within the parentheses of its declaration (for example, Dim myArray(9, 2, 2)). You could use a three-dimensional array to keep track of rows and columns from multiple worksheets, whereas a two-dimensional array would keep track of rows and columns from a single worksheet.
The looping variables in the nested For/Next loops are used to access the row and column indices of the array transArray. The looping variables I and J are used as the column and row indices, respectively, in both the array and worksheet. Next, the contents of the worksheet are cleared using the ClearContents method of the Range object. (The Range object will be covered in detail in Chapter 5.)
To transpose the values, the looping variables I and J are now used to access the opposite index (i.e., I is used for the row index; J is used for the column index) in the Cells property; however, the array transArray uses the indices as in the previous For/Next loop. These nested For/Next loops effectively transpose the values, as shown in Figure 4.5.
Public Sub Transpose()
'Transposes first 10 rows and first 3 columns of worksheet 'to first 3 rows and first 10 columns. Dim I As Integer Dim J As Integer Dim transArray(9, 2) As Integer For I = 1 To 3
transArray(J - 1, I - 1) = Cells(J, I).Value Next J Next I
Range("A1:C10").ClearContents For I = 1 To 3
Cells(I, J).Value = transArray(J - 1, I - 1) Next J Next I End Sub
Was this article helpful?
Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.