Transforming Range to Array in VBA is actually a one liner – we take the range and we assign it to a variant:
|
1 2 3 4 5 |
Dim myRange As Range Set myRange = Range("a1").CurrentRegion Dim myArray As Variant myArray = myRange |
The “strange” part is that the array is converted to 2-dimensional array after this:
and working with these is not always fun. Although, if it was a single row or a single column range, it would have been ok. But, let’s say that our range looks like this:
and we want to get the words “ACADEMY” from row 2 and “ICOSE” from column “B” into 2 new arrays, that will look like this: Array("A","C","A","D","E","M","Y") and Array("I","C","O","S","E"). Is it doable? It is. We can even make the task harder, saying that we need the last column and the last row of our range, that is converted now to a 2d array. This is how to get what we need, as working with 2d arrays is a bit tricky:
|
1 2 3 4 5 |
Debug.Print UBound(myArray, 1) 'count of excel cells in a column (5) Debug.Print UBound(myArray, 2) 'count of excel cells in a row (8) Debug.Print LBound(myArray, 1) 'index of first cell in column (1) Debug.Print LBound(myArray, 2) 'index of first cell in row (1) |
Thus, knowing the magic of Ubound(myArray,2) to count the cells in a given row, we may produce two functions, that will transform for us a whole row or column of 2d array to a new 1d array, that we may use further. These are the functions:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
Function GetColumnFromMdArray(myArray As Variant, myCol As Long) As Variant 'returning a column from multidimensional array 'the returned array is 0-based, but the 0th element is Empty. Dim i As Long Dim result As Variant Dim size As Long: size = UBound(myArray, 1) ReDim result(size) For i = LBound(myArray, 1) To UBound(myArray, 1) result(i) = myArray(i, myCol) Next GetColumnFromMdArray = result End Function Function GetRowFromMdArray(myArray As Variant, myRow As Long) As Variant 'returning a row from multidimensional array 'the returned array is 0-based, but the 0th element is Empty. Dim i As Long Dim result As Variant Dim size As Long: size = UBound(myArray, 2) ReDim result(size) For i = LBound(myArray, 2) To UBound(myArray, 2) result(i) = myArray(myRow, i) Next GetRowFromMdArray = result End Function |
Yup, they work even better than imagined – as the 2d array is usually 1-based (as it is from a range), the newly produced array from the function is with 0-th element being Empty (Leer in German is Empty):
This is a bit useful, if you are going to do some manipulations to this array and later write it down to an Excel range. The whole code is here, I have added also PrintArray function, that displays the new arrays:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
Option Explicit Sub PrintMultidimensionalArrayExample() Dim myRange As Range Set myRange = Range("a1").CurrentRegion Dim myArray As Variant myArray = myRange Debug.Print UBound(myArray, 1) 'count of excel cells in a column Debug.Print UBound(myArray, 2) 'count of excel cells in a row Debug.Print LBound(myArray, 1) 'index of first cell in column Debug.Print LBound(myArray, 2) 'index of first cell in row PrintArray GetRowFromMdArray(myArray, 1) PrintArray GetColumnFromMdArray(myArray, 2) PrintArray GetRowFromMdArray(myArray, UBound(myArray, 1)) PrintArray GetColumnFromMdArray(myArray, UBound(myArray, 2)) End Sub Function GetColumnFromMdArray(myArray As Variant, myCol As Long) As Variant 'returning a column from multidimensional array 'the returned array is 0-based, but the 0th element is Empty. Dim i As Long Dim result As Variant Dim size As Long: size = UBound(myArray, 1) ReDim result(size) For i = LBound(myArray, 1) To UBound(myArray, 1) result(i) = myArray(i, myCol) Next GetColumnFromMdArray = result End Function Function GetRowFromMdArray(myArray As Variant, myRow As Long) As Variant 'returning a row from multidimensional array 'the returned array is 0-based, but the 0th element is Empty. Dim i As Long Dim result As Variant Dim size As Long: size = UBound(myArray, 2) ReDim result(size) For i = LBound(myArray, 2) To UBound(myArray, 2) result(i) = myArray(myRow, i) Next GetRowFromMdArray = result End Function Public Sub PrintArray(myArray As Variant) Dim i As Long For i = LBound(myArray) To UBound(myArray) Debug.Print i & " --> " & myArray(i) Next i End Sub |
Tsar’s Palace in Sofia is to the left of my shoe.