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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:14C3BC8A-0366-4934-B576-(E-Mail Removed)...
> 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
>>
>>
>>