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" <(E-Mail Removed)> wrote in message
news:7746F0E2-B456-4677-8AC2-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:7746F0E2-B456-4677-8AC2-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:7746F0E2-B456-4677-8AC2-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:7746F0E2-B456-4677-8AC2-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> news:7746F0E2-B456-4677-8AC2-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:08771069-4CE7-48F4-A002-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:7746F0E2-B456-4677-8AC2-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
news:08771069-4CE7-48F4-A002-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:7746F0E2-B456-4677-8AC2-(E-Mail Removed)...
>> > 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
>>
>>