Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ParamArray debug anomaly
I have the following, with the first row of the range containing the
values 33,34,35,36. When I run the sub procedure the Locals window shows in part as below, indicating that x(0)(1) is a Variant array, but I get a "Subscript out of range" error message at the line of foo10, z = UBound(x(0)(1). Can someone explain the apparent anomaly? Thanks, Alan Beban Sub abtest5() x = range("a51:d54") y = foo10(x) End Sub Function foo10(ParamArray x()) z = UBound(x(0)(1)) End Function x Variant(0 to 0) x(0) Variant/Variant/Variant(1 to 4,1 to 4) x(0)(1) Variant(1 to 4) x(0)(1,1) 33 Variant/Double x(0)(1,2) 34 Variant/Double x(0)(1,3) 35 Variant/Double x(0)(1,4) 36 Variant/Double |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ParamArray debug anomaly
The array is x(0) not x(0)(1)
Sub abtest5() Dim x, y x = Range("a51:d54") y = foo10(x) End Sub Function foo10(ParamArray x()) Dim z z = UBound(x(0)) End Function -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Alan Beban" wrote in message ... I have the following, with the first row of the range containing the values 33,34,35,36. When I run the sub procedure the Locals window shows in part as below, indicating that x(0)(1) is a Variant array, but I get a "Subscript out of range" error message at the line of foo10, z = UBound(x(0)(1). Can someone explain the apparent anomaly? Thanks, Alan Beban Sub abtest5() x = range("a51:d54") y = foo10(x) End Sub Function foo10(ParamArray x()) z = UBound(x(0)(1)) End Function x Variant(0 to 0) x(0) Variant/Variant/Variant(1 to 4,1 to 4) x(0)(1) Variant(1 to 4) x(0)(1,1) 33 Variant/Double x(0)(1,2) 34 Variant/Double x(0)(1,3) 35 Variant/Double x(0)(1,4) 36 Variant/Double |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ParamArray debug anomaly
Bob Phillips wrote:
The array is x(0) not x(0)(1) Sub abtest5() Dim x, y x = Range("a51:d54") y = foo10(x) End Sub Function foo10(ParamArray x()) Dim z z = UBound(x(0)) End Function Thanks for responding. Yes, one would think. But what is the meaning then, in the Locals window, of the line questioned below? x Variant(0 to 0) x(0) Variant/Variant/Variant(1 to 4,1 to 4) x(0)(1) Variant(1 to 4) '<---------------------?? x(0)(1,1) 33 Variant/Double x(0)(1,2) 34 Variant/Double x(0)(1,3) 35 Variant/Double x(0)(1,4) 36 Variant/Double Alan Beban |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ParamArray debug anomaly
I think what you are trying to do with this -
Function foo10(ParamArray x()) z = UBound(x(0)(1)) End Function is really - Function foo10(ParamArray x()) z = UBound(Application.Index(x(0), 1)) End Function or depending which dimension - z = UBound(Application.Index(x(0), 2)) Regards, Peter T "Alan Beban" wrote in message ... I have the following, with the first row of the range containing the values 33,34,35,36. When I run the sub procedure the Locals window shows in part as below, indicating that x(0)(1) is a Variant array, but I get a "Subscript out of range" error message at the line of foo10, z = UBound(x(0)(1). Can someone explain the apparent anomaly? Thanks, Alan Beban Sub abtest5() x = range("a51:d54") y = foo10(x) End Sub Function foo10(ParamArray x()) z = UBound(x(0)(1)) End Function x Variant(0 to 0) x(0) Variant/Variant/Variant(1 to 4,1 to 4) x(0)(1) Variant(1 to 4) x(0)(1,1) 33 Variant/Double x(0)(1,2) 34 Variant/Double x(0)(1,3) 35 Variant/Double x(0)(1,4) 36 Variant/Double |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ParamArray debug anomaly
Peter T wrote:
I think what you are trying to do with this - Function foo10(ParamArray x()) z = UBound(x(0)(1)) End Function is really - Function foo10(ParamArray x()) z = UBound(Application.Index(x(0), 1)) End Function or depending which dimension - z = UBound(Application.Index(x(0), 2)) Regards, Peter T Thanks for responding. What I'm really trying to do is understand the following line in the Locals window when z = UBound(x(0)(1)) gives a "Subscript out of range" error message: x(0)(1) Variant(1 to 4) Thanks, Alan Beban |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ParamArray debug anomaly
"Alan Beban" wrote in message
... Peter T wrote: I think what you are trying to do with this - Function foo10(ParamArray x()) z = UBound(x(0)(1)) End Function is really - Function foo10(ParamArray x()) z = UBound(Application.Index(x(0), 1)) End Function or depending which dimension - z = UBound(Application.Index(x(0), 2)) Regards, Peter T Thanks for responding. What I'm really trying to do is understand the following line in the Locals window when z = UBound(x(0)(1)) gives a "Subscript out of range" error message: x(0)(1) Variant(1 to 4) Thanks, Alan Beban Perhaps I'm missing something but I don't see any anomaly. I would expect this line to fail (where x is the ParamArray) z = UBound(x(0)(1)) x(0) is the first and, in your example, only element in the paramArray. Also in the example, this sole 'element' in the paramArray is a 2d array x(0)(1) in effect refers to the top row of the 2d array, NOT an entire array, hence my Index example. arr = x(0) arr is now a 2d array, same as the array (also named x in the calling procedure) assigned to the range.value look at arr(1) in locals and it's type description is the same as x(0)(1), as expected z = Ubound(arr(1)) Above fails as expected, as does z = UBound(x(0)(1)) which is effectively same. To get the bounds of any arrays in a ParamArray - Sub test1() a = Range("A1:C4") ' 4x3 b = 123 c = Range("A1:E7") ' 7x5 res = foo1(a, b, c) End Sub Function foo1(ParamArray pa()) e = 0 For Each v In pa If IsArray(v) Then Debug.Print e; "is array", UBound(v); UBound(v, 2) Else Debug.Print e; "not array" End If e = e + 1 Next ' or For e = 0 To UBound(pa) If IsArray(pa(e)) Then Debug.Print e; "is array", UBound(pa(e)); UBound(pa(e), 2) Else Debug.Print e; "not array" End If Next End Function Regards, Peter T |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ParamArray debug anomaly
Peter T wrote:
Perhaps I'm missing something but I don't see any anomaly. I would expect this line to fail (where x is the ParamArray) z = UBound(x(0)(1)) x(0) is the first and, in your example, only element in the paramArray. Also in the example, this sole 'element' in the paramArray is a 2d array x(0)(1) in effect refers to the top row of the 2d array, NOT an entire array, hence my Index example. What it comes down to is my failure to observe that the line in the Locals window x(0)(1) Variant(1 to 4) did not indicate that x(0)(1) was a Variant() array. This is the first time in the dozen or so years that I have been fooling with this stuff that I have had occasion to recognize that a Variant variable can refer to not only a value or an array, but a set of multiple values that do not constitute an array. Thanks very much for your thorough response. Alan Beban |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ParamArray debug anomaly
Peter T wrote: Perhaps I'm missing something but I don't see any anomaly. I would expect this line to fail (where x is the ParamArray) z = UBound(x(0)(1)) On reflection, I guess there does remain one slight anomaly. The Locals window suggests that x(0)(1) is of type Variant, whereas Typename(x(0)(1)) will throw an error. Thanks again, Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date anomaly | Excel Discussion (Misc queries) | |||
Sort anomaly | Excel Worksheet Functions | |||
ParamArray bugs? | Excel Programming | |||
Help with paramarray | Excel Programming | |||
ParamArray | Excel Programming |