CODEWITHSUNDEEP
arraysexcelvba
Lindsay
Lindsay

Reputation: 11

How do I find the upper bound of a nested array?

I'm fairly new to VBA, so bear with me.

I have a dynamic array I'm trying to find the upper bound of. I understand how to use UBound to find the upper bound of a 1-d array (i.e. UBound(TestArray)) and to find the upper bound of a 2-d array (i.e. UBound(TestArray,2). What I need to know is if there is a way to find the upper bound of a nested dynamic array.

Basically I have a dynamic array - TestArray(0,j) - where j is a variable and where 0 references a nested dynamic array. I need to know the upper bound for the nested array. Is that possible?

I've tried UBound(TestArray(0,j)()) and UBound(TestArray,1) and they do not work. So what I need is for someone to tell me that one of those should work and it's the rest of my code that's screwed up, that it's not actually possible and I need to start thinking of a work around, or that somewhere, buried fifteen pages into Google search results, someone actually knows how to do this.

BTW, I tried iterating through the array until I reached the end, but obviously after the end the elements don't exist anymore so then I got a "Subscript Out Of Range" error. I'm currently trying to work out the Error Handling on that, but I'm hoping that someone just has a magical solution to my problem that would ideally be about one line of code.

Upvotes: 1

Views: 1586

Answers (2)

Mark Moore
Mark Moore

Reputation: 510

Another option is: You mentioned you had tried looping around the array, but you got the error at the end. This method WILL work if you turn error handling off and then when the error occurs, subtract 1 from your loop counter. The below code is not mine, it is from Chip Pearson and I hope I am allowed to refer to his site. This function is taken from his Array handling module

Public Function NumberOfArrayDimensions(Arr As Variant) As Integer
'http://www.cpearson.com/excel/vbaarrays.htm
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 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

NumberOfArrayDimensions = Ndx - 1

End Function

Upvotes: 0

Mark Moore
Mark Moore

Reputation: 510

I haven't tried this, but have you tried something like:

Dim Innerarray as variant

Innerarray = TestArray(0,j)

and then query ubound(innerarray,1)

Upvotes: 0

Related Questions