Checking existence of array dimensions

[Guest]

Is there any way to check for how many dimensions an array have? For
example, I can use ubound(TempArray,1), ubound(TempArray,2), etc. But what
if there is no 2nd dimension. My code would fail. I would not want to loop
through the column dimension if there isn't any so I need some way to check
for it.

Many thanks.

 

[Jake Marx]

Hi kdw,

Is there any way to check for how many dimensions an array have? For
example, I can use ubound(TempArray,1), ubound(TempArray,2), etc.
But what if there is no 2nd dimension. My code would fail. I would
not want to loop through the column dimension if there isn't any so I
need some way to check for it.

I don't know of any built-in way to get the dimension count, but here's a
quick function that should do the job:

Public Function gnCountDimensions(rvArray As Variant) As Integer
Dim n As Integer
Dim lTemp As Long
Dim bFoundEnd As Boolean

Do While Not bFoundEnd
On Error Resume Next
lTemp = UBound(rvArray, n + 1)
If Err.Number Then
bFoundEnd = True
Else
n = n + 1
End If
On Error GoTo 0
Loop

gnCountDimensions = n
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[SixSigmaGuy]

There's a KB article (213273) on this that says that Excel has no way to
tell you the number of dimensions and recommends that you use error handling
to do it. The following function will tell you how many dimensions and
array has:

Sub TestDimensions()
Dim a(2, 5, 7) As Integer

Debug.Print cArrayDimensions(a)
End Sub

Function cArrayDimensions(a) As Integer
Dim cDimensions As Integer
Dim iDimension As Integer
Dim x

cDimensions = 0

On Error GoTo GotDimensions
Do
x = UBound(a, cDimensions + 1)
cDimensions = cDimensions + 1
Loop

GotDimensions:

On Error GoTo 0
cArrayDimensions = cDimensions

End Function