Assign a single variable to an Array filled with same data types

[RyanH]

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.

 

[Chip Pearson]

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)

 

[RB Smissaert]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

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]

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

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)

 

[Rick Rothstein \(MVP - VB\)]

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

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

 

[RB Smissaert]

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

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