People obsess about different variable types. They use singles instead of
doubles, and integers instead of longs, and the joke's on them, because VB
converts singles to doubles and integers to longs, does the math, then
converts back. Some people refuse to use variants, but for interacting with
a worksheet, and for some other tasks, there isn't any better way. I've seen
people use a variant to contain the values from a worksheet range, convert
this to an array of integers or doubles or whatever, then proceed, but it
seems to me that once you have the variant array in the first place, the
damage is done and it's not likely to get perceptibly worse.

I dunno, maybe I'm dumb, but it seems to me much more time is spent by the
user scratching his head wondering what he wants to type into that textbox,
than by my use of Variant instead of Integer(). Not that performance isn't
important, it is. But I'll Pareto it, take my 80%, and move on.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"MichaelDavid" wrote in message
...
Hi Jon:
Thanks again for your kind help. I followed your suggestions, and have
modified my code accordingly. My program is now working great, but I am
always looking for ways to cut down on processing time. Therefore, I was
just
hoping against hope that there was someway of setting an array of integers
to
a range:

Dim MyArray() As Integer
Range("A1") = 1
Range("A2") = 2
Range("A3") = 3
MyArray = Range("A1:A3").Value

which, if it, or something similar, could be done, would be much faster
than:

For i = 1 To 3
MyArray(i, 1) = Range("A" & i).Value
Next i

I am always looking for ways of minimizing processing time. Hopefully,
Microsoft will allow MyArray = Range("A1:A3").Value someday.

--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick


"Jon Peltier" wrote:

Why don't you just use the approaches which have been suggested, based on
the well documented behavior of variants and arrays in Excel VBA?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"MichaelDavid" wrote in message
...
Greetings All!
Further research shows that:
1) Type 8204 is the Array of Variants data type
2) Type 8194 is the Array of Integers data type
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick


"MichaelDavid" wrote:

Greetings! Thanks for your kind help. I did some further research and
here is
what I found:

I used VarType to determine the underlying types of each of the
following
variables:

Dim MyArray1() ' Type 8204
Dim MyArray2() As Integer ' Type 8194
Dim MyArray3 ' Type Variant but no initial value
assigned
(vbEmpty)
Dim MyArray4 As Integer ' Type Integer (vbInteger)
Dim MyArray5 As Variant ' Type Variant but no initial value
assigned
(vbEmpty)
Dim MyArray6() As Variant ' Type 8204

Do you (or anyone else in the Excel VBA Discussion group) know what
types
are indicated by types 8204 and 8194?
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick


"Jon Peltier" wrote:

No. It's not unadvertised. This is well documented as the way that a
variant
is populated by a worksheet range.

Any dimensions you declare your variable in are wiped out, and the
dimensions of the range are used. When I told you how to declare
your
variable as a variant, I next told you not to redim it. Then I
showed a
few
examples of how array dimensions work in VBA.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"MichaelDavid" wrote in
message
...
Hi Jon!

I tried your notes and could not get the Procedure ArrayStudies14
to
work
with ReDim, so I commented out the ReDim statement, and ran the
procedure
to
see what would happen:

Sub ArrayStudies14()

' with Jon's suggestions from Excel VBA Group

Dim MyArray As Variant ' Declare nonarray variant.

Range("A1") = 1
Range("A2") = 2
Range("A3") = 3

MyArray = Range("A1:A3").Value

' ReDim MyArray(3) ' Allocate 3 elements. COMMENTED OUT.

MsgBox "MyArray(3) = " & MyArray(3, 1)

End Sub

Would you believe that it works perfectly without the ReDim? Could
an
unadvertised Excel VBA feature be that sometimes ReDim is not
needed?
This
is
strange, and should be brought to the attention of Microsoft
Programming.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick


"Jon Peltier" wrote:

A few notes.

The variable that accepts the values in a range should be
declared
as a
non
array variant.

Dim MyArray As Variant

Don't redim it prior to populating it, because VBA does that
anyway,
using
the correct dimensions. When you redim the variable using:

ReDim MyArray(3)

that is the same as redimming it like this:

ReDim MyArray(0 to 3)

because VBA assumes a lower bound of 0 (0-base) unless told
otherwise.

When you then assign the values in A1:A3

MyArray = Range("A1:A3").Value

(use the .Value property of the range, even though it's the
default)
the
array comes out dimensioned as

MyArray(1 to 3, 1 to 1)

because the worksheet range is a 2D array, and it is treated as a
1-base
array (lower bound is 1 not 0).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"MichaelDavid" wrote in
message
...
Hi Ariel!
Thanks for your speedy reply. The original reason for my post
is
that
the
code in question typifies a lot of the code in a hugh macro
which
takes
hours
to run. Consequently, I have performed timing studies on the
various
possible
ways to code certain tasks. For example, the instruction of the
third
subroutine (ArrayStudies3()) which does:
MyArray = Range("A1:A3") ' Initialize array.
runs much quicker than the code which does the For Next Loop of
example
2
as
you modified it:

For i = 1 To UBound(MyArray)
MyArray(i, 1) = Range("A" & i).Value ' Initialize array
Next i

Therefore I am looking for a way to dimension MyArray as an
Integer
like
so:
Dim MyArray() As Integer
rather than as the Variant, Dim MyArray() (which results in
execution
without error), for even greater speed. Hopefully some "super
expert"
in
this discussion group will come up with a way of dimensioning
MyArray()
As
Integer such that MyArray can be set to Range("A1:A3") or an
equivalent
without a Type Mismatch upon execution. Again, thanks for your
response.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick


"Ariel Dugan" wrote:

Hi,

In your 3rd example the variant array has the flexibility to
allow a
range
to be assigned to it.

I don't think you can do that with a explicitely "typed" array
like in
your
second example.

Alternatively you could use this modified version. Its better
this
way
as
you have to explicitely size both dimensions of your array, so
it
is
more
clear what you are actually doing. When assigning a range to
an
array,
even
if its only one row (or column) it is still a 2 dimensional
array
(or
matrix
for visualization purposes).

Also, instead of passing the entire range in one call, you
have
to do
it
one
element at a time. I did this with a small loop, which isn't
really
necessary in this case, but would be if you were populating
the
array
with a
large number of values. I used the UBound of the array to
return the
upper
limit of the array. This is a common approach to identify the
upper
limit
of the array, for purposes like this.



Sub ArrayStudies2()

Dim MyArray() As Integer ' Declare dynamic array of type
Integer.
Dim i As Integer

ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements.

Range("A1") = 1
Range("A2") = 2
Range("A3") = 3


For i = 1 To UBound(MyArray)
MyArray(i, 1) = Range("A" & i).Value ' Initialize
array
Next i


' The follwg instr gives: "Run-time error '13': Type
mismatch"
'MyArray = Range("A1:A3")

MsgBox "MyArray(1, 1) = " & MyArray(1, 1)
MsgBox "MyArray(2, 1) = " & MyArray(2, 1)
MsgBox "MyArray(3, 1) = " & MyArray(3, 1)

End Sub



Thanks
Ariel

"MichaelDavid" wrote
in
message
...