To Determine the 2D array

[Danny]

Hi,

known that we can use Ubound to determine the array size. However, if
the array is Array(2,5), Ubound just able to determine the "2". May I
know how to find out the "5"?

Thanks.
Danny

 

[Scossa]

known that we can use Ubound to determine the array size. However, if
the array is Array(2,5), Ubound just able to determine the "2". May I
know how to find out the "5"?

Hi Danny,

Try this UDF:

'in standard module
'-------------------
Option Explicit

Public Function ABound(ByVal Arr As Variant) As Long
' by Scossa
'return the dimension of an array
' v(9) -> 1
' v(9,3) -> 2
' v(9,4,5) -> 3

Dim i As Long, j As Long

i = 0
j = 0
On Error Resume Next
While Err.number = 0
j = i
i = i + 1
j = UBound(Arr, i)
Wend
On Error GoTo 0
ABound = j

End Function

'code to test ABound() udf:

Sub m()

Dim a(3, 5) As Variant
Dim k As Integer
Dim j As Integer

k = ABound(a)
For j = 1 To k
Debug.Print UBound(a(), j)
Next
End Sub


Tnks for your feedback.

Bye!
Scossa

 

[Scossa]

Public Function ABound(ByVal Arr As Variant) As Long

corrige:

Public Function ABound(ByVal Arr As Variant) As integer

Bye!
Scossa

 

[Mike S]

Hi,
known that we can use Ubound to determine the array size. However, if
the array is Array(2,5), Ubound just able to determine the "2". May I
know how to find out the "5"?
Thanks.
Danny

You can use lbound and ubound too:

http://www.anthony-vba.kefra.com/vba/vbabasic3.htm

Find The Size of an Array

The largest available subscript for the indicated dimension of an array
can be obtained by using the Ubound function. In our one-dimensional
array example, Ubound(arr) is 5.

In our two-dimensional array example above, there are two upper bound
figures - both are 2.
UBound returns the following values for an array with these dimensions*:

Dim A(1 To 100, 0 To 3, -3 To 4)

Statement Return Value
UBound(A, 1) 100
UBound(A, 2) 3
UBound(A, 3) 4

* Example taken from Excel VBA Help section.

The UBound function is used with the LBound function to determine the
size of an array. Use the LBound function to find the lower limit of an
array dimension.

Statement Return Value
LBound(A, 1) 1
LBound(A, 2) 0
LBound(A, 3) -3

To get the size of an array, use the following formula:

UBound(Arr) - LBound(Arr) + 1

For example:

Ubound(A,1) - LBound(A,1) + 1
= 100 - 1 + 1
= 100

Ubound(A,2) - LBound(A,2) + 1
= 3 - 0 + 1
= 4

Ubound(A,3) - LBound(A,3) + 1
= 4 - (-3) + 1
= 8


For more information on arrays check Microsoft Support

 

[Ron Rosenfeld]

Hi,

known that we can use Ubound to determine the array size. However, if
the array is Array(2,5), Ubound just able to determine the "2". May I
know how to find out the "5"?

Thanks.
Danny

Use the optional dimension argument.

i.e: UBound(Array,2)

Check HELP for UBound