Why am I getting this error "Can't Assign To Array" for this code?

Dim myArray(0 To 5) As Single
Dim Var as Single

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

but if I code it this way everything works:

Dim myArray As Variant
Dim Var as Variant

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

Any help would be great.
--
Cheers,
Ryan

You can't assign a Variant created with the Array function to a statically
declared array. You can, however, assign it to a dynamic array. For example,

Dim Arr() As Variant
Arr = Array(1, 2, 3, 4)

You can initialize the size of the array with a ReDim, but that has no
effect. The final result will be sized to the number of elements in the
Array statement. E.g

Dim Arr() As Variant
ReDim Arr(0 To 2) '<<<< THIS LINE IS IRRELEVANT
Arr = Array(1, 2, 3, 4)

Note that you need to declare the Arr variable () As Variant rather than,
for example, () As Integer.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





"RyanH" wrote in message
...
Why am I getting this error "Can't Assign To Array" for this code?

Dim myArray(0 To 5) As Single
Dim Var as Single

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

but if I code it this way everything works:

Dim myArray As Variant
Dim Var as Variant

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

Any help would be great.
--
Cheers,
Ryan

From the VBA help:

Array Function


Returns a Variant containing an array.

Syntax

Array(arglist)

The required arglist argument is a comma-delimited list of values that are
assigned to the elements of the array contained within the Variant. If no
arguments are specified, an array of zero length is created.



RBS



"RyanH" wrote in message
...
Why am I getting this error "Can't Assign To Array" for this code?

Dim myArray(0 To 5) As Single
Dim Var as Single

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

but if I code it this way everything works:

Dim myArray As Variant
Dim Var as Variant

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

Any help would be great.
--
Cheers,
Ryan

First off, the Array function returns an array and, in VB, arrays can only
be assigned to other arrays if those other arrays were dynamically declared.
You declared your array as having a fixed number of elements, so the array
assignment failed. On top of that, the Array function requires its target to
be a Variant (a variant array is also possible target) where as you tried
specifying it as a Single. The reason a Variant target is required is
because you can put almost anything in the argument list to the Array
function (as long as you remember what is what, of course<g). For
example...

Dim V As Variant
V = Array(123, "Text String", Range("A1"))
MsgBox V(0) & vbCrLf & V(1) & vbCrLf & V(2).Address

Notice that the 3rd element, V(2), is a Range object, so to MessageBox out
something from it, you need to reference one of its properties. True, I
could have let it use its default Value property, but I wanted to positively
demonstrate that it was an actual object being stored in the third element.

Rick


"RyanH" wrote in message
...
Why am I getting this error "Can't Assign To Array" for this code?

Dim myArray(0 To 5) As Single
Dim Var as Single

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

but if I code it this way everything works:

Dim myArray As Variant
Dim Var as Variant

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

Any help would be great.
--
Cheers,
Ryan

You have to declare the array as variant, even though all the arguments in
the array have the same data type ( in my case Single)?
--
Cheers,
Ryan


"Rick Rothstein (MVP - VB)" wrote:

First off, the Array function returns an array and, in VB, arrays can only
be assigned to other arrays if those other arrays were dynamically declared.
You declared your array as having a fixed number of elements, so the array
assignment failed. On top of that, the Array function requires its target to
be a Variant (a variant array is also possible target) where as you tried
specifying it as a Single. The reason a Variant target is required is
because you can put almost anything in the argument list to the Array
function (as long as you remember what is what, of course<g). For
example...

Dim V As Variant
V = Array(123, "Text String", Range("A1"))
MsgBox V(0) & vbCrLf & V(1) & vbCrLf & V(2).Address

Notice that the 3rd element, V(2), is a Range object, so to MessageBox out
something from it, you need to reference one of its properties. True, I
could have let it use its default Value property, but I wanted to positively
demonstrate that it was an actual object being stored in the third element.

Rick


"RyanH" wrote in message
...
Why am I getting this error "Can't Assign To Array" for this code?

Dim myArray(0 To 5) As Single
Dim Var as Single

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

but if I code it this way everything works:

Dim myArray As Variant
Dim Var as Variant

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

Any help would be great.
--
Cheers,
Ryan

Since myArray contains arguments with the same data type (Single) I figured
you could declare the array like so:

Dim myArray(0 To 5) as Single

but you are saying you have to declare it as a Variant like so:

Dim myArray as Variant

Right?


--
Cheers,
Ryan


"Chip Pearson" wrote:

You can't assign a Variant created with the Array function to a statically
declared array. You can, however, assign it to a dynamic array. For example,

Dim Arr() As Variant
Arr = Array(1, 2, 3, 4)

You can initialize the size of the array with a ReDim, but that has no
effect. The final result will be sized to the number of elements in the
Array statement. E.g

Dim Arr() As Variant
ReDim Arr(0 To 2) '<<<< THIS LINE IS IRRELEVANT
Arr = Array(1, 2, 3, 4)

Note that you need to declare the Arr variable () As Variant rather than,
for example, () As Integer.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





"RyanH" wrote in message
...
Why am I getting this error "Can't Assign To Array" for this code?

Dim myArray(0 To 5) As Single
Dim Var as Single

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

but if I code it this way everything works:

Dim myArray As Variant
Dim Var as Variant

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

Any help would be great.
--
Cheers,
Ryan

Yes!

Either of these will work..

Dim myArray As Variant

or

Dim myArray() As Variant

but the data type of myArray must be a Variant in order for you to be able
to assign the output of the Array function to it.

Rick


"RyanH" wrote in message
...
You have to declare the array as variant, even though all the arguments in
the array have the same data type ( in my case Single)?
--
Cheers,
Ryan


"Rick Rothstein (MVP - VB)" wrote:

First off, the Array function returns an array and, in VB, arrays can
only
be assigned to other arrays if those other arrays were dynamically
declared.
You declared your array as having a fixed number of elements, so the
array
assignment failed. On top of that, the Array function requires its target
to
be a Variant (a variant array is also possible target) where as you tried
specifying it as a Single. The reason a Variant target is required is
because you can put almost anything in the argument list to the Array
function (as long as you remember what is what, of course<g). For
example...

Dim V As Variant
V = Array(123, "Text String", Range("A1"))
MsgBox V(0) & vbCrLf & V(1) & vbCrLf & V(2).Address

Notice that the 3rd element, V(2), is a Range object, so to MessageBox
out
something from it, you need to reference one of its properties. True, I
could have let it use its default Value property, but I wanted to
positively
demonstrate that it was an actual object being stored in the third
element.

Rick


"RyanH" wrote in message
...
Why am I getting this error "Can't Assign To Array" for this code?

Dim myArray(0 To 5) As Single
Dim Var as Single

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

but if I code it this way everything works:

Dim myArray As Variant
Dim Var as Variant

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

Any help would be great.
--
Cheers,
Ryan

You could always do something like this:


Sub test()

Dim i As Long
Dim arrVariant
Dim arrSingles(0 To 3) As Single

arrVariant = Array(1.1, 2.2, 3.3, 4.4)

For i = 0 To 3
arrSingles(i) = arrVariant(i)
Next i

'just to show you still have a Single data type
MsgBox VarType(arrSingles(1)), , "VarType 4 = vbSingle"

End Sub


RBS


"RyanH" wrote in message
...
You have to declare the array as variant, even though all the arguments in
the array have the same data type ( in my case Single)?
--
Cheers,
Ryan


"Rick Rothstein (MVP - VB)" wrote:

First off, the Array function returns an array and, in VB, arrays can
only
be assigned to other arrays if those other arrays were dynamically
declared.
You declared your array as having a fixed number of elements, so the
array
assignment failed. On top of that, the Array function requires its target
to
be a Variant (a variant array is also possible target) where as you tried
specifying it as a Single. The reason a Variant target is required is
because you can put almost anything in the argument list to the Array
function (as long as you remember what is what, of course<g). For
example...

Dim V As Variant
V = Array(123, "Text String", Range("A1"))
MsgBox V(0) & vbCrLf & V(1) & vbCrLf & V(2).Address

Notice that the 3rd element, V(2), is a Range object, so to MessageBox
out
something from it, you need to reference one of its properties. True, I
could have let it use its default Value property, but I wanted to
positively
demonstrate that it was an actual object being stored in the third
element.

Rick


"RyanH" wrote in message
...
Why am I getting this error "Can't Assign To Array" for this code?

Dim myArray(0 To 5) As Single
Dim Var as Single

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

but if I code it this way everything works:

Dim myArray As Variant
Dim Var as Variant

ERROR = myArray = Array(var1, var2, var3, var4, var5, var6)

For Each Var In myArray
Var = 0
Next Var

Any help would be great.
--
Cheers,
Ryan