Menu
Is This code correct for determining the number of elements in a single dimension variant array in Excel VBA. Supposing I have a variant array named Array1 with k elements.
Dim n as Integer
n = UBound(Array1)
348
Each element in an array contains one value. The following statement declares the array variable with 365 elements. By default, an array is indexed beginning with zero, so the upper bound of the array is 364 rather than 365.
VBA Code to get the length of Array (one-dimensional array):Press Alt+F8 to popup macro window. Select ” oneDimArrayLength” and Click Run button. Compute Number of Rows, Number of Columns using UBound and LBound function. Multiply by noOfRow and noOfCol variable to get Number of elements in multi-dimensional array.
In VBA, to get the length of an array means to count the number of elements you have in that array. For this, you need to know the lowest element and the highest element. So, to get this you can use the UBOUND and LBOUND functions that return the upper bound and lower bound, respectively.
To get an accurate count, you need to do UBound - LBound + 1. This is because arrays don't have to go from index 1 to n, they can start at basically any index you want. Here's an example where it goes from 3 to 7, which is a total of 5 elements (3, 4, 5, 6, and 7):
Sub tgr()
Dim Array1(3 To 7) As Variant
Dim lNumElements As Long
lNumElements = UBound(Array1) - LBound(Array1) + 1
MsgBox lNumElements
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
