Ubound matrix

[Guest]

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!

 

[JW]

LBound(arrayName,1)
UBound(arrayName,1)
LBound(arrayName,2)
UBound(arrayName,2)
etc

 

[Chip Pearson]

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)

 

[Alan Beban]

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]

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]

Hi Chip,

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

 

[Peter T]

Hi Chip,


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

 

[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

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