Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ubound matrix
Hi! I have a two dimensional matrix. I want to use Ubound to check one of the
dimensions. How shall I write this and how do I know which dimension I am checking? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ubound matrix
LBound(arrayName,1)
UBound(arrayName,1) LBound(arrayName,2) UBound(arrayName,2) etc Arne Hegefors wrote: Hi! I have a two dimensional matrix. I want to use Ubound to check one of the dimensions. How shall I write this and how do I know which dimension I am checking? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ubound matrix
The following code should give you what you need.
and how do I know which dimension I am checking? It depends on which dimension you specify. Either Dim UB As Long UB = UBound(Arr,1) ' or UB = UBound(Arr,2) Sub AAA() Dim Arr() As Long Dim NumDims As Long ReDim Arr(1 To 3, 1 To 5, 1 To 6) NumDims = NumberOfDimensions(Arr) Debug.Print "Number Of Dimensions: " & NumDims Debug.Print "UBound of last: " & UBound(Arr, NumDims) End Sub Function NumberOfDimensions(Arr As Variant) As Long Dim N As Long Dim UB As Long If IsArray(Arr) = False Then NumberOfDimensions = 0 Exit Function End If If IsArrayAllocated(Arr) = False Then NumberOfDimensions = 0 Exit Function End If On Error Resume Next Err.Clear N = 1 UB = UBound(Arr, N) Do Until Err.Number < 0 Err.Clear UB = UBound(Arr, N) N = N + 1 Loop NumberOfDimensions = N - 2 End Function Function IsArrayAllocated(Arr As Variant) As Boolean On Error Resume Next IsArrayAllocated = (Not IsError(LBound(Arr))) And (LBound(Arr) <= UBound(Arr)) End Function -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Arne Hegefors" wrote in message ... Hi! I have a two dimensional matrix. I want to use Ubound to check one of the dimensions. How shall I write this and how do I know which dimension I am checking? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ubound matrix
FWIW, the one thing the functions don't do is distinguish between
unallocated arrays and ranges or other non-array input. Alan Beban Chip Pearson wrote: The following code should give you what you need. and how do I know which dimension I am checking? It depends on which dimension you specify. Either Dim UB As Long UB = UBound(Arr,1) ' or UB = UBound(Arr,2) Sub AAA() Dim Arr() As Long Dim NumDims As Long ReDim Arr(1 To 3, 1 To 5, 1 To 6) NumDims = NumberOfDimensions(Arr) Debug.Print "Number Of Dimensions: " & NumDims Debug.Print "UBound of last: " & UBound(Arr, NumDims) End Sub Function NumberOfDimensions(Arr As Variant) As Long Dim N As Long Dim UB As Long If IsArray(Arr) = False Then NumberOfDimensions = 0 Exit Function End If If IsArrayAllocated(Arr) = False Then NumberOfDimensions = 0 Exit Function End If On Error Resume Next Err.Clear N = 1 UB = UBound(Arr, N) Do Until Err.Number < 0 Err.Clear UB = UBound(Arr, N) N = N + 1 Loop NumberOfDimensions = N - 2 End Function Function IsArrayAllocated(Arr As Variant) As Boolean On Error Resume Next IsArrayAllocated = (Not IsError(LBound(Arr))) And (LBound(Arr) <= UBound(Arr)) End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ubound matrix
FWIW, the one thing the functions don't do is distinguish between unallocated arrays and ranges or other non-array input. I don't think it is a good idea to think of a Range object as an Array, even though IsArray sometimes says it is so. I base this judgment on the following problems that occur when pretending that an Range is an Array. - You can't use LBound or UBound to get the bounds. This doesn't work: Dim R As Range Set R = Range("A1,C3") Debug.Print LBound(R, 1) - IsArray doesn't return a reliable True or False value. For example, R1 and R2 below are perfectly valid Ranges but are not treated as arrays by IsArray. Only for R3 will IsArray return True. Dim R1 As Range Dim R2 As Range Dim R3 As Range Set R1 = Range("A1") Set R2 = Range("A1,C3") Set R3 = Range("A1:C3") Debug.Print "IsArray: R1: " & IsArray(R1) & " R2: " & IsArray(R2) & " R3: " & IsArray(R3) - Indexing does not work as one would expect if a Range were really an array: Dim R1 As Range Set R1 = Range("A1,D4") Debug.Print R1(2).Address This displays $A$2, which is not part of the range R1. If a Range worked like an Array, the code should display $D$4. The code I posted works fine with real arrays, not these pseudo-array things like a Range. If one is going to start extending the definition of an array, would you consider the Worksheets object as an array? What about Collections? Conversely, I don't like the idea of using a For Next on an array. For example, I do not like the following code: Dim Arr(1 To 3) Dim V As Variant Arr(1) = 11 Arr(2) = 22 Arr(3) = 33 For Each V In Arr Debug.Print V Next V -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Alan Beban" wrote in message ... FWIW, the one thing the functions don't do is distinguish between unallocated arrays and ranges or other non-array input. Alan Beban Chip Pearson wrote: The following code should give you what you need. and how do I know which dimension I am checking? It depends on which dimension you specify. Either Dim UB As Long UB = UBound(Arr,1) ' or UB = UBound(Arr,2) Sub AAA() Dim Arr() As Long Dim NumDims As Long ReDim Arr(1 To 3, 1 To 5, 1 To 6) NumDims = NumberOfDimensions(Arr) Debug.Print "Number Of Dimensions: " & NumDims Debug.Print "UBound of last: " & UBound(Arr, NumDims) End Sub Function NumberOfDimensions(Arr As Variant) As Long Dim N As Long Dim UB As Long If IsArray(Arr) = False Then NumberOfDimensions = 0 Exit Function End If If IsArrayAllocated(Arr) = False Then NumberOfDimensions = 0 Exit Function End If On Error Resume Next Err.Clear N = 1 UB = UBound(Arr, N) Do Until Err.Number < 0 Err.Clear UB = UBound(Arr, N) N = N + 1 Loop NumberOfDimensions = N - 2 End Function Function IsArrayAllocated(Arr As Variant) As Boolean On Error Resume Next IsArrayAllocated = (Not IsError(LBound(Arr))) And (LBound(Arr) <= UBound(Arr)) End Function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ubound matrix
Hi Chip,
Chip Pearson wrote: FWIW, the one thing the functions don't do is distinguish between unallocated arrays and ranges or other non-array input. I don't think it is a good idea to think of a Range object as an Array, even though IsArray sometimes says it is so. I base this judgment on the following problems that occur when pretending that an Range is an Array. . . . No,no; I agree. I recall many years ago being "upset" to learn that IsArray returned true for multi-celled Ranges. My post wasn't clear; I should have said that the functions don't distinguish between unallocated Arrays, on the one hand, and Ranges or other non-Array input, on the other. I just meant that if the functions return 0, that could be because the input was an unallocated Array, or because it was a multi-celled Range, or because it was a single-celled Range, or because it was any other non-Array input. Returning 0 for all of them leaves one unable to distinguish. In fact, one might say that returning 0 as the number of dimensions if the input is a Range is pretending that a Range is an unallocated Array, i.e., an Array with 0 dimensions. Conversely, I don't like the idea of using a For Next on an array. For example, I do not like the following code: Dim Arr(1 To 3) Dim V As Variant Arr(1) = 11 Arr(2) = 22 Arr(3) = 33 For Each V In Arr Debug.Print V Next V . . . What are the objections to it? Alan Beban |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ubound matrix
"Alan Beban" wrote in message ... Hi Chip, Chip Pearson wrote: Conversely, I don't like the idea of using a For Next on an array. For example, I do not like the following code: Dim Arr(1 To 3) Dim V As Variant Arr(1) = 11 Arr(2) = 22 Arr(3) = 33 For Each V In Arr Debug.Print V Next V . . . What are the objections to it? Alan Beban Not sure if this amounts to an objection but there's something inconsistent between For Each V In Arr & For each cell in rng namely, the for each loop of the variant goes down rows then across columns, in contrast to a for each loop of the range area which goes across columns then down rows. Regards, Peter T |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ubound matrix
Peter T wrote:
Not sure if this amounts to an objection but there's something inconsistent between For Each V In Arr & For each cell in rng namely, the for each loop of the variant goes down rows then across columns, in contrast to a for each loop of the range area which goes across columns then down rows. Regards, Peter T True; though this is controllable, if inconvenient, with For Each V in Application.Transpose(Arr), if it were to make a difference in the application. Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why is this returning a Ubound value of zero | Excel Discussion (Misc queries) | |||
UBound | Excel Programming | |||
resize(Ubound, Lbound) | Excel Programming | |||
Array Ubound | Excel Programming | |||
Ubound & Lbound | Excel Programming |