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