arrays from Excel VBA

M

MJH

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
 
R

RB Smissaert

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
 
R

RB Smissaert

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top