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