How to find the number of dimensions that an array has?

Question

Below is a piece of code where I need to store some info about a warning message by going through messages passed. The parameter passed itself is a variant which is set by an API call to SAPListOfMessages which returns an array of String. What I've noticed however is that whenever there is more than 1 warning, the list is 2D and messageList(x-1) obviously leads to an error because it's not a proper index. What's also strange is that the for each loop seems to ignore dimensions and somehow just flatten the array and loop through it as if it were 1D. The only way around this I see is checking how many dimensions the array has before doing anything else and hence my question. I wasn't able to find any info on getting the number of dimensions - I only found info about their bounds. Is it possible to find the number of dimensions of an array in VBA? If not, how would you suggest I tackle this problem?

Sub getOverlapWarnings(ByRef messageList As Variant, ByRef warnings As Dictionary)

  Dim msg As Variant
  Dim x As Integer
  x = 1
 'look for an overlap warning message in the list of messages
  For Each msg In messageList
    'look for the keyword 'overlap' in the list of messages
    
    If InStr(1, msg, "overlap") <> 0 Then
       warnings.Add messageList(x - 1), msg
    End If
   x = x + 1
  Next msg
End Sub

Possible duplicate of [VBA using ubound on a multidimensional array](https://stackoverflow.com/questions/26644231/vba-using-ubound-on-a-multidimensional-array) — Victor K, Jun 09 '18 at 17:53
Does this answer your question? [How to return the number of dimensions of a (Variant) variable passed to it in VBA](https://stackoverflow.com/questions/6901991/how-to-return-the-number-of-dimensions-of-a-variant-variable-passed-to-it-in-v) — Luuklag, Sep 16 '20 at 20:37

T.M. · Accepted Answer · 2020-09-17T18:14:46.027

9

Is it possible to find the number of dimensions of an array in VBA?

This approach increments the possible dimensions count, 60 being the built in maximum (c.f. comment):

Private Function nDim(ByVal vArray As Variant) As Long
' Purpose: get array dimension (MS)
Dim dimnum     As Long
Dim ErrorCheck As Long    ' OP: As Variant
On Error GoTo FinalDimension

For dimnum = 1 To 60        ' 60 being the absolute dimensions limitation 
    ErrorCheck = LBound(vArray, dimnum)
Next
' It's good use to formally exit a procedure before error handling
' (though theoretically this wouldn't needed in this special case - see comment) 
Exit Function

FinalDimension:
nDim = dimnum - 1

End Function

Further links (thx @ChrisNeilson)

MS Using arrays

Big Array

edited Sep 17 '20 at 18:14

answered Jun 09 '18 at 17:25

T.M.

8,281
3
29
55

1

There is no needs to keep in mind dims limitations. Just enough `Do ... Loop` instead of hardcoding of `60000` or some else... And, bth, what for `ErrorCheck As Variant` if `L-UBound` definitely returns `Long` as it is documented? – user6698332 Jun 09 '18 at 18:07
@user6698332 - I preferred shortness here and this way is no need of an `Exit Function` because a wanted error will occur in *any* case; so the function's return value is coded in FinalDimension. – T.M. Jun 09 '18 at 18:35
1

**Re:** _"I preferred shortness.. "_ Personally, by yourself, you can even prefer coffee with salt. But you advise to others wrong, bad approach on programming. Moreover, you propagandize this to an unlimited number of persons... **Re:** _"..here and this way is no need of an.. "_ Yeah. Tomorrow you decline `Option Explicit` and variables types assigning in favor of _"here"_. **Re:** _"error will occur in any case"_ - it is such implicity, so this case is NEED to be programmed on the best practice. But, you skimp of 1 (ONE!) exit(!) code line at the expense of clarity and good style. – user6698332 Jun 09 '18 at 19:25
1

@user6698332 - okay and Yes, we are leading programmers to best practice - I edited OP – T.M. Jun 09 '18 at 19:47
1

Many thanks for editing and for the right understanding! :) – user6698332 Jun 09 '18 at 20:21
1

A nasty hack. But seems the only way. Beware that Application.Evaluate can return a scalar, a two dim array, or, sometimes, a one dim array! – Tuntable May 19 '20 at 08:21
Note: the max dimensions of an array is 60, not 60,000. [Reference](https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/using-arrays) – chris neilsen Sep 16 '20 at 22:56
Thank you, of course you are true (possible confusion with limitations concerning `Application.Index` *et al.* of roughly 65k). @chrisneilsen – T.M. Sep 17 '20 at 17:49

score 2 · Answer 2 · answered Jun 09 '18 at 17:27

An array has 2 bounds: Upper and Lower.

I think you're asking where the lower bound begins.

By default, the lower bound is zero. For example:

Sub test()
    Dim arr
    arr = Array("a", "b", "c")
    Debug.Print "Lower: " & LBound(arr), "Upper: " & UBound(arr)
End Sub

returns: Lower: 0 Upper: 2 because the 3 elements have indices of 0, 1, and 2.

Some functionality may begin at 1 by default but it's rare. One example is filling an array with a range:

Sub test()
    Dim arr
    arr = Range("A2:A4")
    Debug.Print "Lower: " & LBound(arr), "Upper: " & UBound(arr)
End Sub

...returns: Lower: 1 Upper: 3

If you fully declare the array, you can make the upper and lower bound whatever you want:

Sub test()
    Dim arr(99 To 101) As String
    arr(100) = "blah"
    Debug.Print "Lower: " & LBound(arr), "Upper: " & UBound(arr)
End Sub

...returns: Lower: 99 Upper: 101, but an array with declared bounds won't work with many functions (like the previous examples.

You can also set the default lower bound with an statement at the very top of each module:

Option Base 1

...but there are so many places it doens't apply it's kind of useless. (More here.)

How to find the number of dimensions that an array has?

2 Answers2

See also:

Linked

Related