ReDim not working as expected. Array expert needed.

[MichaelDavid]

Greetings! This one has me totally baffled.

In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer.
MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers,
successive array locations are set to these ranges, and the first array
location is successfully displayed.

Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array
MyArray is set to a 3 cell range having integers. In this case, upon
execution, I get the message " "Run-time error '13': Type mismatch".

Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is
declared as a dynamic array of type Variant rather than as type Integer. And
this worked perfectly with no error messages. Why can't MyArray be declared
as an array of type Integer in Sub ArrayStudies2? (At the top of the module
containing these three subs is: Option Base 1)

Sub ArrayStudies1()

' example patterned after Excel VBA Help on ReDim statement:
' This example uses the ReDim statement to allocate and reallocate storage
space for dynamic-array variables. It assumes the Option Base is 1.

Dim MyArray() As Integer ' Declare dynamic array of type Integer.
ReDim MyArray(3) ' Allocate 3 elements.

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

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

' The following instruction works fine:
MsgBox "MyArray(1) = " & MyArray(1)

End Sub

-------------------------------------------------------------------------------------------------

Sub ArrayStudies2()

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

ReDim MyArray(3) ' Allocate 3 elements.

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

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

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

End Sub

-------------------------------------------------------------------------------------------------

Sub ArrayStudies3()

Dim MyArray() ' Declare dynamic array of type Variant.
ReDim MyArray(3) ' Allocate 3 elements.

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

' The follwg instr works fine
MyArray = Range("A1:A3") ' Initialize array.

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

End Sub

May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[Ariel Dugan]

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]

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

 

[MichaelDavid]

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

 

[Jon Peltier]

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

 

[Ariel Dugan]

Hi Michael,

If you send the the entire application, I can look at possible ways to speed
it up. There may be other issues causing the bog down.

Thanks
Ariel

 

[MichaelDavid]

Hi Ariel:
Greetings! It is very kind of you to look at speeding up my application.
The module has 1714 lines. How should I send it?--it would not look very nice
in this small box I am typing in.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[MichaelDavid]

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

 

[MichaelDavid]

Hi Jon!
I commented out all the ReDims in the hugh procedure which utilizes
arrays. Would you believe that the procedure now runs 1.56% faster on average
with the same correct results as before? I should mention that the procedure
sets arrays to ranges only once in the life of the procedure, so I guess
ReDims are really not needed in this procedure.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[Jon Peltier]

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

 

[MichaelDavid]

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

 

[MichaelDavid]

Hi Jon:
Next I tried to determine these types using TypeName. What I found:

MsgBox TypeName(MyArray1()) ' Type Variant()
MsgBox TypeName(MyArray2()) ' Type Integer
MsgBox TypeName(MyArray6()) ' Type Variant()
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[MichaelDavid]

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

 

[Jon Peltier]

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

 

[MichaelDavid]

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]

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

 

[MichaelDavid]

Hi Jon:
Re your comment: "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", the extensive timing studies I have done of ALL possible
ways of using arrays to store ranges, what you have here suggested is 1.5%
quicker on average than any of the other ways I have tested. So of course,
that is how I have implemented this logic. May you have a blessed day.

--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick