VBA – Ubound of Multidimensional Array or How to Get What We Need?
Transforming Range to Array in VBA is actually a one liner – we take the range and we assign it to a variant:
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:
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:
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:
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
