Menu
Does anyone know how to return the number of dimensions of a (Variant) variable passed to it in VBA?
917
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.
Dim in the VBA language is short for Dimension and it is used to declare variables. The Dim statement is put at the start of a VBA module after the Sub statement (short for Subroutine). It is a way to refer to the declared term rather than the same object over and over again.
A Variant is a special data type that can contain any kind of data except fixed-length String data. (Variant types now support user-defined types.) A Variant can also contain the special values Empty, Error, Nothing, and Null.
Variables declared as the Variant data type can contain string, date, time, Boolean, or numeric values, and can convert the values that they contain automatically.
VBA Variant is easy as using Integer or Long or String data types for declaring variables. This saves time in thinking which type of data type we need to choose for variable declaration. For each different data, we will get the same output by using a Variant data type as we could get using traditional variables.
Pros & Cons of Excel VBA Variant 1 We can replace most of the data types with a single data type Variant. 2 VBA Variant is easy as using Integer or Long or String data types for declaring variables. 3 This saves time in thinking which type of data type we need to choose for variable declaration. More items...
Variant arrays can hold any type of values: text, numbers, dates, time, or objects. To assign values to a variant array, you can use the Array Function: 'populate the array varNames () = Array ("Fred", "Wilma", "Barney", "Betty")
You can determine how the data in a Variant is treated by using the VarType function or TypeName function. Numeric data can be any integer or real number value ranging from -1.797693134862315E308 to -4.94066E-324 for negative values and from 4.94066E-324 to 1.797693134862315E308 for positive values.
Function getDimension(var As Variant) As Long On Error GoTo Err Dim i As Long Dim tmp As Long i = 0 Do While True i = i + 1 tmp = UBound(var, i) Loop Err: getDimension = i - 1 End Function That's the only way I could come up with. Not pretty….
Looking at MSDN, they basically did the same.
82
To return the number of dimensions without swallowing errors:
#If VBA7 Then Private Type Pointer: Value As LongPtr: End Type Private Declare PtrSafe Sub RtlMoveMemory Lib "kernel32" (ByRef dest As Any, ByRef src As Any, ByVal Size As LongPtr) #Else Private Type Pointer: Value As Long: End Type Private Declare Sub RtlMoveMemory Lib "kernel32.dll" (ByRef dest As Any, ByRef src As Any, ByVal Size As Long) #End If Private Type TtagVARIANT vt As Integer r1 As Integer r2 As Integer r3 As Integer sa As Pointer End Type Public Function GetDims(source As Variant) As Integer Dim va As TtagVARIANT RtlMoveMemory va, source, LenB(va) ' read tagVARIANT ' If va.vt And &H2000 Then Else Exit Function ' exit if not an array ' If va.vt And &H4000 Then RtlMoveMemory va.sa, ByVal va.sa.Value, LenB(va.sa) ' read by reference ' If va.sa.Value Then RtlMoveMemory GetDims, ByVal va.sa.Value, 2 ' read cDims from tagSAFEARRAY ' End Function Usage:
Sub Examples() Dim list1 Debug.Print GetDims(list1) ' >> 0 ' list1 = Array(1, 2, 3, 4) Debug.Print GetDims(list1) ' >> 1 ' Dim list2() Debug.Print GetDims(list2) ' >> 0 ' ReDim list2(2) Debug.Print GetDims(list2) ' >> 1 ' ReDim list2(2, 2) Debug.Print GetDims(list2) ' >> 2 ' Dim list3(0 To 0, 0 To 0, 0 To 0) Debug.Print GetDims(list3) ' >> 3 ' 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
