To Determine the 2D array

  • Thread starter Thread starter Danny
  • Start date Start date
D

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
 
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"?
Click to expand...


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
 
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
Click to expand...


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
 
Back
Top