VBA check if array is one dimensional

Question

I have an array (that comes from SQL) and can potentially have one or more rows.

I want to be able to figure out if the array has just one row.

UBound doesn't seem to be helpful. For 2-dimensional arrays UBound(A,1) and UBound(A,2) returns the number of rows and columns respectively, but when the array has only one row, UBound(A,1) returns the number of columns and UBound(A,2) returns a <Subscript out of range>.

I have also seen this Microsoft help page for determining the number of dimensions in an array. It is a very horrifying solution that involves using the error handler.

How can I determine whether the array has just one row (hopefully without using the error handler)?

Sumy

haha, the error handler is probably the best way to do it. programming with microsoft is special – Lionel

I agree with @serakfalcon: I would take what that KB article you posted says to be true, specifically "There is no built-in function to return the number of dimensions in an array variable in Microsoft Visual Basic for Applications. The easiest way to do this is by looping through the dimensions and using an error handler to return the number of dimensions." – Arda

@roryap, I'm hopeful that there is another solution because I have the additional constraint that I'm only testing whether there is 1 dimension, whereas the Microsoft solution is broad for any number of dimensions. Also, the Microsoft solution is for Excel 98 and they've had 16 years to think of something. – Sumy

Here is a version that tests just for 1-d arrays: https://mcmap.net/q/243203/-how-to-return-the-number-of-dimensions-of-a-variant-variable-passed-to-it-in-vba-duplicate. Why the "without using the error handler" restriction though? – Igloo

Question is a duplicate as per jtolle's post. Also not overly useful as appropriate use of OERN is absolutely fine. – Damselfly

@Damselfly The error handler is fine but I was hoping there was a cleaner and simpler way to do it. I can't think of another language where resizing an array is such a complicated issue that you need to whip out error handling. – Sumy

@alec Yes, VBA has its limitations. But error handling - used properly - is still a valid approach – Damselfly

Answer 1

If you REALLY want to avoid using On Error, you can use knowledge of the SAFEARRAY and VARIANT structures used to store arrays under the covers to extract the dimension information from where it's actually stored in memory. Place the following in a module called mdlSAFEARRAY

Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (ByVal Destination As Long, ByVal Source As Long, ByVal Length As Integer)

Private Type SAFEARRAY
    cDims As Integer
    fFeatures As Integer
    cbElements As Long
    cLocks As Long
    pvData As Long
End Type

Private Type ARRAY_VARIANT
    vt As Integer
    wReserved1 As Integer
    wReserved2 As Integer
    wReserved3 As Integer
    lpSAFEARRAY As Long
    data(4) As Byte
End Type

Private Enum tagVARENUM
    VT_EMPTY = &H0
    VT_NULL
    VT_I2
    VT_I4
    VT_R4
    VT_R8
    VT_CY
    VT_DATE
    VT_BSTR
    VT_DISPATCH
    VT_ERROR
    VT_BOOL
    VT_VARIANT
    VT_UNKNOWN
    VT_DECIMAL
    VT_I1 = &H10
    VT_UI1
    VT_UI2
    VT_I8
    VT_UI8
    VT_INT
    VT_VOID
    VT_HRESULT
    VT_PTR
    VT_SAFEARRAY
    VT_CARRAY
    VT_USERDEFINED
    VT_LPSTR
    VT_LPWSTR
    VT_RECORD = &H24
    VT_INT_PTR
    VT_UINT_PTR
    VT_ARRAY = &H2000
    VT_BYREF = &H4000
End Enum

Public Function GetDims(VarSafeArray As Variant) As Integer
    Dim varArray As ARRAY_VARIANT
    Dim lpSAFEARRAY As Long
    Dim sArr As SAFEARRAY
    CopyMemory VarPtr(varArray.vt), VarPtr(VarSafeArray), 16&
    If varArray.vt And (tagVARENUM.VT_ARRAY Or tagVARENUM.VT_BYREF) Then
        CopyMemory VarPtr(lpSAFEARRAY), varArray.lpSAFEARRAY, 4&
        If Not lpSAFEARRAY = 0 Then
            CopyMemory VarPtr(sArr), lpSAFEARRAY, LenB(sArr)
            GetDims = sArr.cDims
        Else
            GetDims = 0  'The array is uninitialized
        End If
    Else
        GetDims = 0  'Not an array - might want an error instead
    End If
End Function

Here is a quick test function to show usage:

Public Sub testdims()
    Dim anotherarr(1, 2, 3) As Byte
    Dim myarr() As Long
    Dim strArr() As String
    ReDim myarr(9)
    ReDim strArr(12)
    Debug.Print GetDims(myarr)
    Debug.Print GetDims(anotherarr)
    Debug.Print GetDims(strArr)
End Sub

Conventioner

+1, this is the direct way of accessing it, better than the MSFT solution – Affright

++ even though many will consider this an overkill I like it! – Bambibambie

+1 also, but this does bear a lot of extra overhead vs. the "error handling" option, no? The call to the CopyMemory method, especially when the number of dimensions of the array climbs... – Arda

@roryap Actually, CopyMemory is not being used to copy the data in the array, just the structures that describe it. In total there are three copies totaling 36 bytes, which is presumably the same thing VBA would have to do to find out the same information. – Conventioner

@Chiffonier While I agree with you in theory, the Automation library that the two structures used are a part of is specifically intended to be a common interface (Component Object Model or COM) through which programs can interact with eachother. As such, it specifically relies on having a consistent, backwards compatible interface to allow that interoperation. The structures are not "internal" in this case but more like a data interchange format. That being said, it's probably still overkill for most purposes. – Conventioner

@Chiffonier from here "COM is a platform-independent, distributed, object-oriented system for creating binary software components that can interact. COM is the foundation technology for Microsoft's OLE (compound documents) and ActiveX (Internet-enabled components) technologies." – Conventioner

While this solution is way more complicated and has prohibitively too much overhead, it's the only one without using the error handler, so I'm going to put it as my accepted answer. – Sumy

@Conventioner Thanks, I thought these structures were the internal ones, not those exposed through COM, I just didn't realize they were the same. I deleted my comment. However I agree this is overkill, and even though I don't like using the error handler this way, I thinks it's cleaner than this solution ;) – Breechblock

Embarrassingly, I just realized I had already posted this answer to a similar question, except with better comments: https://mcmap.net/q/243203/-how-to-return-the-number-of-dimensions-of-a-variant-variable-passed-to-it-in-vba-duplicate – Conventioner

Answer 2

I know you want to avoid using the error handler, but if it's good enough for Chip Pearson, it's good enough for me. This code (as well as a number of other very helpful array functions) can be found on his site:

http://www.cpearson.com/excel/vbaarrays.htm

Create a custom function:

Function IsArrayOneDimensional(arr as Variant) As Boolean
    IsArrayOneDimensional = (NumberOfArrayDimensions(arr) = 1)
End Function

Which calls Chip's function:

Public Function NumberOfArrayDimensions(arr As Variant) As Integer
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' NumberOfArrayDimensions
' This function returns the number of dimensions of an array. An unallocated dynamic array
' has 0 dimensions. This condition can also be tested with IsArrayEmpty.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Ndx As Integer
Dim Res As Integer
On Error Resume Next
' Loop, increasing the dimension index Ndx, until an error occurs.
' An error will occur when Ndx exceeds the number of dimension
' in the array. Return Ndx - 1.
Do
    Ndx = Ndx + 1
    Res = UBound(arr, Ndx)
Loop Until Err.Number <> 0

Err.Clear

NumberOfArrayDimensions = Ndx - 1

End Function

Rolling

Answer 3

I realized that my original answer can be simplified - rather than having the VARIANT and SAFEARRAY structures defined as VBA Types, all that is needed is a few CopyMemorys to get the pointers and finally the Integer result.

UPDATE: This version should work on both 32 bit and 64 bit systems (original preserved below the break for posterity):

Option Explicit

Private Declare PtrSafe Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (ByVal Destination As LongPtr, ByVal Source As LongPtr, ByVal Length As Integer)

Public Function GetDims(VarSafeArray As Variant) As Integer
    Dim variantType As Integer
    Dim pointer As LongPtr
    Dim arrayDims As Integer

    CopyMemory VarPtr(variantType), VarPtr(VarSafeArray), 2& 'the first 2 bytes of the VARIANT structure contain the type

    If (variantType And &H2000) > 0 Then 'Array (&H2000)
        'If the Variant contains an array or ByRef array, a pointer for the SAFEARRAY or array ByRef variant is located at VarPtr(VarSafeArray) + 8
        CopyMemory VarPtr(pointer), VarPtr(VarSafeArray) + 8, LenB(pointer)

        'If the array is ByRef, there is an additional layer of indirection through another Variant (this is what allows ByRef calls to modify the calling scope).
        'Thus it must be dereferenced to get the SAFEARRAY structure
        If (variantType And &H4000) > 0 Then 'ByRef (&H4000)
            'dereference the pointer to pointer to get the actual pointer to the SAFEARRAY
            CopyMemory VarPtr(pointer), pointer, LenB(pointer)
        End If
        'The pointer will be 0 if the array hasn't been initialized
        If Not pointer = 0 Then
            'If it HAS been initialized, we can pull the number of dimensions directly from the pointer, since it's the first member in the SAFEARRAY struct
            CopyMemory VarPtr(arrayDims), pointer, 2&
            GetDims = arrayDims
        Else
            GetDims = 0 'Array not initialized
        End If
    Else
        GetDims = 0 'It's not an array... Type mismatch maybe?
    End If
End Function

---

Here is the simplest complete GetDims that checks the dimensions directly through the variables in memory:

Option Explicit

Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (ByVal Destination As Long, ByVal Source As Long, ByVal Length As Integer)

Public Function GetDims(VarSafeArray As Variant) As Integer
    Dim variantType As Integer
    Dim pointer As Long
    Dim arrayDims As Integer
    
    CopyMemory VarPtr(variantType), VarPtr(VarSafeArray), 2& 'the first 2 bytes of the VARIANT structure contain the type
    
    If (variantType And &H2000) > 0 Then 'Array (&H2000)
        'If the Variant contains an array or ByRef array, a pointer for the SAFEARRAY or array ByRef variant is located at VarPtr(VarSafeArray) + 8
        CopyMemory VarPtr(pointer), VarPtr(VarSafeArray) + 8, 4&
        
        'If the array is ByRef, there is an additional layer of indirection through another Variant (this is what allows ByRef calls to modify the calling scope).
        'Thus it must be dereferenced to get the SAFEARRAY structure
        If (variantType And &H4000) > 0 Then 'ByRef (&H4000)
            'dereference the pointer to pointer to get the actual pointer to the SAFEARRAY
            CopyMemory VarPtr(pointer), pointer, 4&
        End If
        'The pointer will be 0 if the array hasn't been initialized
        If Not pointer = 0 Then
            'If it HAS been initialized, we can pull the number of dimensions directly from the pointer, since it's the first member in the SAFEARRAY struct
            CopyMemory VarPtr(arrayDims), pointer, 2&
            GetDims = arrayDims
        Else
            GetDims = 0 'Array not initialized
        End If
    Else
        GetDims = 0 'It's not an array... Type mismatch maybe?
    End If
End Function

Conventioner

Definitely throw a type mismatch, in a guard clause; this would remove a whole nesting level. – Lasandralasater

I remember this code from a book by Francesca Balena (or it may have been Mathew Curland) but your comments are very readable. So this is my favourite. – Allegiance

@Conventioner sorry for reviving this old thread. When I run the code and get to the line CopyMemory VarPtr(arrayDims), lpSAFEARRAY, 2&, it makes Excel 2007 crash. Is it something wrong with my version of excel? I am on Windows 7 btw! – Alic

@OscarAnthony Make sure you don't have a different or competing definition of CopyMemory than I provided - sometimes people use "ByRef dest as Any", which would cause the pointer math to go very wrong. That's about the only thing I can think of. I just tested the function on a similar system and it seems to be working ok. – Conventioner

@OscarAnthony out of curiosity, what is the value of lpSAFEARRAY right before the call? – Conventioner

Doesn't seem to work if Variant is declared without parentheses: Dim a, b : a = [{1,2,3}] : b = [{1,2,3;4,5,6}] : Debug.Print GetDims(a) ' -4981 Debug.Print GetDims(b) ' -31764 – Volume

@Volume - The guard clause is incorrect - [{1,2,3}] returns a fixed array, not a SAFEARRAY. It should also be testing whether the VT_BY_REF flag is set. – Narcotize

@Volume I can't seem to get this to compile in VBA - is this VB syntax? What I would do is scroll up to my first answer and use the types/enums to inspect the Variants a and b to find out what types they actually are (see tagVarEnum). Perhaps you can use that to identify these cases and handle them appropriately. – Conventioner

@Volume AHA! So, the [{}] notation is apparently not valid in Access VBA, but it is in Excel! Examining the data structures, it turns out that if you define your array as Dim a() As Variant, it is passed with a type of &H600C which is a ByRef (&H4000) Variant (&HC) Array (&H2000), but if you define it as Dim a As Variant or just Dim a, then its type is &H400C which is a Variant (&HC) Array (&H2000). So the problem with my code is I was always assuming arrays defined with "()", causing ByRef, meaning a pointer to a pointer. But your case is just a pointer. I'll fix it :D – Conventioner

@Volume Try the new version - it correctly handles ByRef, vs no ByRef. – Conventioner

@Narcotize see the update re ThunderFrame. Apparently if your array variable is just Variant, VBA passes it directly to the function. However, if your array variable is Double(), Variant(), Byte(), etc, these are all considered value types and a new Variant is built ByRef as a layer of indirection pointing back to the actual variable in the calling scope. – Conventioner

@Conventioner - Yep, I've used that specific boxing behavior before to get ahold of other pointers. And I apparently forgot to upvote before... :Thumbsup: – Narcotize

Very instructive, thanks for this. I wrote a test sub to work with this code and posted as an answer. – Girder

@Conventioner How to update the above code for Office 365? The above code was working perfectly for me in 2016 but creates a compile error in the latest office versions. – Neilneila

@Neilneila I don't have a copy handy, so I can't say for sure, but it's possible that if Office 365 is 64 bit, you'd need to make the pointer a LongLong instead of a Long. Although it's also possible that they've completely changed the memory model (this code is a nifty hack, but it definitely depends on "implementation-specific behavior") See learn.microsoft.com/en-us/office/vba/language/concepts/… – Conventioner

@Neilneila in fact, reading the link (learn.microsoft.com/en-us/office/vba/language/concepts/…) it definitely seems like this is the case. Try dim pointer as LongPtr - this should be backwards compatible and work in both versions – Conventioner

@Neilneila See my edit - the new version should work on both 32-bit and 64-bit Office – Conventioner

Answer 4

For a 2D array (or more dimensions), use this function:

Function is2d(a As Variant) As Boolean
    Dim l As Long
    On Error Resume Next
    l = LBound(a, 2)
    is2d = Err = 0
End Function

which gives :

Sub test()
    Dim d1(2) As Integer, d2(2, 2) As Integer,d3(2, 2, 2) As Integer
    Dim b1, b2, b3 As Boolean

    b1 = is2d(d1) ' False
    b2 = is2d(d2) ' True
    b3 = is2d(d3) ' True

    Stop
End Sub

Chiffonier

still using an OERN but ++ for the effort :) – Bambibambie

@mehow I don't think there is any other way – Breechblock

This is the correction solution. Even MSFT's example for the number of array dimensions uses error trapping and admits that there is no direct way of doing so. There is a loophole though. There is no standard way in VBA to do so, but the SAFEARRAY structure has the number of dimensions embedded into it. So if you would like the non On error method, you would need to access the SAFEARRAY memory location with a pointer and then find out the number of dimensions. – Affright

Answer 5

I found Blackhawks's accepted and revised answer very instructive, so I played around with it and learned some useful things from it. Here's a slightly modified version of that code that includes a test sub at the bottom.

Option Explicit

Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" ( _
    ByVal Destination As Long, ByVal Source As Long, ByVal Length As Integer)

Public Function GetDims(VarSafeArray As Variant) As Integer
    Dim variantType As Integer
    Dim pointer As Long
    Dim arrayDims As Integer

    'The first 2 bytes of the VARIANT structure contain the type:
    CopyMemory VarPtr(variantType), VarPtr(VarSafeArray), 2&

    If Not (variantType And &H2000) > 0 Then
    'It's not an array. Raise type mismatch.
        Err.Raise (13)
    End If

    'If the Variant contains an array or ByRef array, a pointer for the _
        SAFEARRAY or array ByRef variant is located at VarPtr(VarSafeArray) + 8:
    CopyMemory VarPtr(pointer), VarPtr(VarSafeArray) + 8, 4&

    'If the array is ByRef, there is an additional layer of indirection through_
    'another Variant (this is what allows ByRef calls to modify the calling scope).
    'Thus it must be dereferenced to get the SAFEARRAY structure:
    If (variantType And &H4000) > 0 Then 'ByRef (&H4000)
        'dereference the pointer to pointer to get actual pointer to the SAFEARRAY
        CopyMemory VarPtr(pointer), pointer, 4&
    End If
    'The pointer will be 0 if the array hasn't been initialized
    If Not pointer = 0 Then
        'If it HAS been initialized, we can pull the number of dimensions directly _
            from the pointer, since it's the first member in the SAFEARRAY struct:
        CopyMemory VarPtr(arrayDims), pointer, 2&
        GetDims = arrayDims
    Else
        GetDims = 0 'Array not initialized
    End If
End Function

Sub TestGetDims()
' Tests GetDims(). Should produce the following output to Immediate Window:
'
' 1             One
' 2             Two
' Number of array dimensions: 2

    Dim myArray(2, 2) As Variant
    Dim iResult As Integer
    myArray(0, 0) = 1
    myArray(1, 0) = "One"
    myArray(0, 1) = 2
    myArray(1, 1) = "Two"

    Debug.Print myArray(0, 0), myArray(1, 0)
    Debug.Print myArray(0, 1), myArray(1, 1)

    iResult = GetDims(myArray)

    Debug.Print "Number of array dimensions: " & iResult
End Sub

Girder

Answer 6

Identify 1-row arrays without Error handling or API functions

"I want to be able to figure out if the array has just one row."

To solve OP's requirement focussing on arrays already dimensioned as 1- and 2-dim arrays, it isn't necessary to determine the array's actual dimension, it suffices to get the number of its "rows". So I came across the following surprisingly simple solution considering the following:

• It's possible to slice 1-dim or 2-dim arrays to isolate their first "column" via Application.Index(arr, 0, 1).
• An eventual UBound now will show the correct number of rows, especially for the asked one-row case.

Function UBndOne(arr) As Long
'Purp: get rows count of (array) input
'Note: returns 1 as the function result for 
'    a) one-dimensional arrays 
'    b) 2-dim arrays with only one row
'      UBound(arr,1) isn't helpful for 1-dim array as it would return the number of elements
    UBndOne = UBound(Application.Index(arr, 0, 1))
End Function

Side note: The combined code UBound(Application.Index(arr, 0, 1)) could be applied even upon other data types than arrays, returning as well 1 as function result.

Forefend