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

On 26 Ago, 06:29, Danny <dannypct...@gmail.com> wrote:
> 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

On 26 Ago, 08:38, Scossa <scossa...@gmail.com> wrote:

errata:
> Public Function ABound(ByVal Arr As Variant) As Long

corrige:

Public Function ABound(ByVal Arr As Variant) As integer

Bye!
Scossa

On 8/25/2010 9:29 PM, Danny wrote:
> 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