Array size question

[Mr930]

I am passing an array into a function, where I do some calculations by looping through it. Is there any speed difference, or preference, between using
arrayname.Count vs. UBound(arrayname) to find the end of the array?

thanks
Fred Emmerich

 

[AD_Taylor]

I am passing in a range to my function:

Function Interp(dTarget As Double, x, y)

I am trying to find the size of the x and y arrays. UBound(x) gives an error. x.Count gives me the size of the array. Can't figure out why UBound is not working....

Well I think you've answered your own question there. You say 'I am passing a range to my function'.

Assuming then that x and y are range type variables (Dim x as Range, for example) you can't use UBound with them. UBound can only find the end point of actual array variables for example:

Dim arr(1 to 5) as Variant

defines a variant array of 5 elements. You can then use UBound(arr) to return 5.

 

[Norie]

So x and y are ranges not arrays?

 

[Mr930]

I call this function from a cell formula and I pass in 2 ranges and a lookup value:
=Interp(.5, $a$5:$a$100, $b$5:$b$100)


Function Interp(dTarget As Double, x, y)
Dim iXMax As Integer
Dim iYMax As Integer

iXMax = x.Count
iYMax = y.Count

If (iYMax = 0 Or iXMax = 0) Then
Interp = "The X or Y range is zero length"
Exit Function
ElseIf (dTarget <= x(1)) Then ' Lower bound check
Interp = y(1)
Exit Function
ElseIf (dTarget >= x(iXMax)) Then ' Upper bound check
Interp = y(iYMax)
Exit Function
End If

Dim iLoc As Integer
iLoc = Application.Match(dTarget, x) ' Locate next lowest position in the X array
Interp = y(iLoc) + (dTarget - x(iLoc)) * (y(iLoc + 1) - y(iLoc)) / (x(iLoc + 1) - x(iLoc)) ' Interpolate

End Function

 

[Mr930]

So x and y are ranges not arrays?

The ranges I pass in are all numbers.

 

[Norie]

But they aren't arrays that's the main thing.

 

[mikerickson]

The confusion comes from the overuse of defaults.

First, the data type of x is not specified, so it is (by default) a Variant data type.

The line

x = Range("A1:A10")

uses two defaults.

1) it uses the default keyword Let
2) it uses the default Range property .Value


so

Let x = Range("A1:A10").Value

makes x an array, and x.Count will error, while UBound(x) won't.

Compare this to the line

Set x = Range("A1:A10")

which makes x a range so x.Count works, while UBound(x) errors.

ASIDE:
The code above uses other defaults. The default ActiveSheet of the unqualified range and the default second argument of UBound, 1 .

 

[Rick Rothstein]

The confusion comes from the overuse of defaults.

Yes, and the differences are lost in the "automatic" way people use them. On the surface, the argument passing of a range to a variant appears to be doing the same thing as an assignment of a range to a Variant variable, but that is not really the case. Running the Test macro (which, in turn, calls the RangeOrArray subroutine) from this code should point out what I am referring to... a reference to the actual range is passed into the subroutine via the Range variable (that is, Rng is Set to the physical range object consisting of the cells in A1:C5 for my example code) whereas the values (not an object) are, by default, assigned to the Arr variable via VB6's ability to assign one array to another (here I am guessing that works because the shape of the memory storage for the values, both text and numeric, in a range is the same way as for the values, both text and numeric, in a two-dimensional Variant array)...

Sub RangeOrArray(Rng As Variant)
  Dim Arr As Variant
  MsgBox Rng.Count
  Arr = Rng
  MsgBox UBound(Arr, 1) & " by " & UBound(Arr, 2)
End Sub

Sub Test()
  RangeOrArray Range("A1:C5")
End Sub