Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
To Determine the 2D array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
To Determine the 2D array
On 26 Ago, 06:29, Danny 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
To Determine the 2D array
On 26 Ago, 08:38, Scossa wrote:
errata: Public Function ABound(ByVal Arr As Variant) As Long corrige: Public Function ABound(ByVal Arr As Variant) As integer Bye! Scossa |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
To Determine the 2D array
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
To Determine the 2D array
On Wed, 25 Aug 2010 21:29:35 -0700 (PDT), 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 Use the optional dimension argument. i.e: UBound(Array,2) Check HELP for UBound |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested Array Formula to Determine Average cost Per Mile in 100 mileincrements | Excel Worksheet Functions | |||
clearing array variables and using an array to determine min value | Excel Programming | |||
Using Multiple Array Criteria - Determine Average Result | Excel Worksheet Functions | |||
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) | Excel Programming | |||
Code to determine if a cell contains an array function? | Excel Discussion (Misc queries) |