I wrote a sub in VBA for Excel that accepts either 1) a selected range
directly from the active worksheet, or 2) from an created array (have used
both "as Double" and "as Variant") by another routine, which in turn
uses the same selected range. In my sub, there is an "if" statement
(=) which compares every value of the array of numbers to a set
(const) value. I get the same result, no matter where the array comes
from (created or selected) *except* when one of the const values happen
to *equal* one of the array numbers. For some reason, if the const
value equals one of the created (Double or Varient) array values, it
continues to loop one more time, as if the numbers were not equal?!

I guess the question boils down to this: when VBA uses a selected
range as an array, what type of variable is it? If the selection is a
range of numbers, why would they not equal their equivalent in Double
or Variant form? What changes when I convert a spreadsheet range to an array
of values in VBA?




thanks

Not sure it explains your problem, but and array based
on a worksheet range is always a Variant array.

So you do:

Dim arr

arr = Range(cells(1), cells(20,20))

and you will get a Variant array.

RBS


"MJH" wrote in message
. ..
I wrote a sub in VBA for Excel that accepts either 1) a selected range
directly from the active worksheet, or 2) from an created array (have used
both "as Double" and "as Variant") by another routine, which in turn
uses the same selected range. In my sub, there is an "if" statement
(=) which compares every value of the array of numbers to a set
(const) value. I get the same result, no matter where the array comes
from (created or selected) *except* when one of the const values happen
to *equal* one of the array numbers. For some reason, if the const
value equals one of the created (Double or Varient) array values, it
continues to loop one more time, as if the numbers were not equal?!

I guess the question boils down to this: when VBA uses a selected
range as an array, what type of variable is it? If the selection is a
range of numbers, why would they not equal their equivalent in Double
or Variant form? What changes when I convert a spreadsheet range to an
array of values in VBA?




thanks

But when the array comes from a sheet range like this:

Dim arr
arr = range(cells(1), cells(20,20))

then it will always be a 1-based, 2-D (even when there is only one column in
the range) variant array.

RBS

"PapaDos" wrote in message
...
Hard to guess what you talk about without seeing your code and variables
declarations.

But I guess you have a problem with indexing, by default VBA arrays start
at
0 and ranges start at 1. So using my_variable(1) is referencing the first
item in a range but the second item in an array...

Look at the "Option Base" statement or change the way you declare your
arrays, forcing them to start at 1...

--
Regards,
Luc.

"Festina Lente"


"MJH" wrote:

I wrote a sub in VBA for Excel that accepts either 1) a selected range
directly from the active worksheet, or 2) from an created array (have
used
both "as Double" and "as Variant") by another routine, which in turn
uses the same selected range. In my sub, there is an "if" statement
(=) which compares every value of the array of numbers to a set
(const) value. I get the same result, no matter where the array comes
from (created or selected) *except* when one of the const values happen
to *equal* one of the array numbers. For some reason, if the const
value equals one of the created (Double or Varient) array values, it
continues to loop one more time, as if the numbers were not equal?!

I guess the question boils down to this: when VBA uses a selected
range as an array, what type of variable is it? If the selection is a
range of numbers, why would they not equal their equivalent in Double
or Variant form? What changes when I convert a spreadsheet range to an
array
of values in VBA?




thanks