[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

arrays from Excel VBA

Kelly

12/19/2006 12:15:00 PM

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
3 Answers

RB Smissaert

12/19/2006 12:43:00 PM

0

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" <mhodkin@comcast.net> wrote in message
news:b8OdnRtcj79dShrYnZ2dnUVZ_uuqnZ2d@comcast.com...
>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
>
>

PapaDos

12/19/2006 4:58:00 PM

0

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

RB Smissaert

12/19/2006 6:43:00 PM

0

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" <PapaDos@discussions.microsoft.com> wrote in message
news:14C3BC8A-0366-4934-B576-1BE06B05422F@microsoft.com...
> 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
>>
>>
>>