I am a supporter ofSt. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation toSt. Joseph's, please. Information onSt. Joseph's
 How do you determine the number of dimensions of an array? Function ArrayDim(v As Variant) As Long'Returns number of dimensions of an array or 0 for'an undimensioned array or -1 if no array at all.'Reverse("moc.LiborPlus.www") V0.1 PB 10-May-2010Dim i As LongArrayDim = -1If Not IsArray(v) Then Exit FunctionOn Error Resume Next 'Please read http://sulprobil.com/html/error_trapping.htmlErr.ClearDo While IsNumeric(UBound(v, i + 1))    If Err.Number <> 0 Then Exit Do    i = i + 1LoopArrayDim = iEnd Function Another version, inspired by Bob Phillips and by Rick Rothstein:Function DimCount(v As Variant) As Long'Returns number of dimensions of an array or 0 for'an undimensioned array or -1 if no array at all.'Reverse("moc.LiborPlus.www") V0.1 PB 11-May-2010    On Error Resume Next 'Please read http://sulprobil.com/html/error_trapping.html    Do        DimCount = DimCount - IsNumeric(UBound(v, DimCount + 1))    Loop Until Err.Number    DimCount = DimCount + Not IsArray(v)End Function This robust and fast code is from R. B. Smissaert: Declare Sub CopyMemory Lib "kernel32" Alias _                       "RtlMoveMemory" (pDst As Any, _                                        pSrc As Any, _                                        ByVal ByteLen As Long)Function ArrayTester(arr As Variant) As Integer  '-----------------------------------------------------------------  ' will return:  ' -1 if not an array  ' 0  if an un-dimmed array  ' 1  or more indicating the number of dimensions of a dimmed array  '-----------------------------------------------------------------  Dim ptr As Long  Dim VType As Integer  Const VT_BYREF = &H4000&  'get the real VarType of the argument  'this is similar to VarType(), but returns also the VT_BYREF bit  CopyMemory VType, arr, 2  'exit if not an array  If (VType And vbArray) = 0 Then    ArrayTester = -1    Exit Function  End If  'get the address of the SAFEARRAY descriptor  'this is stored in the second half of the  'Variant parameter that has received the array  CopyMemory ptr, ByVal VarPtr(arr) + 8, 4  'see whether the routine was passed a Variant  'that contains an array, rather than directly an array  'in the former case ptr already points to the SA structure.  'Thanks to Monte Hansen for this fix  If (VType And VT_BYREF) Then    ' ptr is a pointer to a pointer    CopyMemory ptr, ByVal ptr, 4  End If  'get the address of the SAFEARRAY structure  'this is stored in the descriptor  'get the first word of the SAFEARRAY structure  'which holds the number of dimensions  '...but first check that saAddr is non-zero, otherwise  'this routine bombs when the array is uninitialized  '(Thanks to VB2TheMax aficionado Thomas Eyde for  ' suggesting this edit to the original routine.)  If ptr Then    CopyMemory ArrayTester, ByVal ptr, 2  End IfEnd Function