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]

Personally I use UBound() to find the end of the array. 2 reasons:

1. I didn't know .Count existed
2. If you have a strangely sized array, i.e. an array with elements from 10 to 100, UBound will return 100 whereas .Count will return 91 (I think). Therefore .Count doesn't return the end of the array in my opinion.

 

[Norie]

Are you sure arrayname.Count works?

 

[mikerickson]

In code like

Dim rng1 as Range, myArray as Varint

Set rng1 = Range("A1:A10")

myArray = rng1.Value

For i = 1 to ???

I prefer to use UBound(myArray, 1) rather than rng1.Count. On the logic that if I'm working on myArray, I should refer to myArray.

For example, if I later find that I need to Redim Preserve myArray before looping, then I don't need to edit the loop code.

 

[Mr930]

.Count seems to work, but I see UBound used a lot so I wanted to check.

 

[Mr930]

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....

 

[Rick Rothstein]

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....

Are you calling your Interp function from other VB code or from within a worksheet formula? Also, show us all of your Interp function's code so we can see what is going on.

 

[Norie]

What are x and y arrays of?

 

[Norie]

Rick

Interop? Is this VB.Net or something?

 

[Rick Rothstein]

Rick

Interop? Is this VB.Net or something?

It does sound like that, doesn't it; but I said Interp, not Interop. Here is the section I quoted from the OP in my message that you are referring to (note the part highlighed in red)...

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....