Excel VBA & Arrays
 

I can't get the following procedure to work... according to the help files,
it should work but doesn't. I get a "Compile error: Can't assign to array"
when vColumns = Array("A", "B", ...). Does anyone know why and how to fix?
Thanks! :-)

Public Sub Suffy()

Dim vColumns(6) As Variant
Dim i As Integer
vColumns = Array("A", "B", "C", "D", "E", "F", "G")

For i = 1 To 7 Step 1
Debug.Print sColumns(i)
Next i

End Sub

Excel VBA & Arrays
 

Robert,

The Array funciton returns a single Variant data type variable that contains
an array. Thus, you need to change

Dim vColumns(6) As Variant
to
Dim vColumns As Variant

Also, the lower bound of the array within the Variant is determined by the
"Option Base" statement (if any) at the top of the module. ("Option Base 0"
or "Option Base 1"). Without an Option Base statement, the lower bound of
vColumns will be 0, not 1, as your code expects. When looping through an
array, it is good standard programming practice to use LBound and UBound to
control the values of the index variable rather then hard-coding the values.
E.g.,

For i = LBound(vColumns) To UBound(vColumns)
Debug.Print vColumns(i)
Next i

Finally, you have a typo
Debug.Print sColumns(i)
should be
Debug.Print vColumns(i)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)





"Robert" wrote in message
...
I can't get the following procedure to work... according to the help files,
it should work but doesn't. I get a "Compile error: Can't assign to array"
when vColumns = Array("A", "B", ...). Does anyone know why and how to fix?
Thanks! :-)

Public Sub Suffy()

Dim vColumns(6) As Variant
Dim i As Integer
vColumns = Array("A", "B", "C", "D", "E", "F", "G")

For i = 1 To 7 Step 1
Debug.Print sColumns(i)
Next i

End Sub

Excel VBA & Arrays
 

First, sometimes you use vColumns and sometimes sColumns. That'll always be a
problem.

Second, this worked for me:

Option Explicit
Public Sub Suffy()

Dim vColumns As Variant
Dim i As Long
vColumns = Array("A", "B", "C", "D", "E", "F", "G")

For i = lbound(vColumns) To ubound(vcolumns) Step 1
Debug.Print vColumns(i)
Next i

End Sub


Robert wrote:

I can't get the following procedure to work... according to the help files,
it should work but doesn't. I get a "Compile error: Can't assign to array"
when vColumns = Array("A", "B", ...). Does anyone know why and how to fix?
Thanks! :-)

Public Sub Suffy()

Dim vColumns(6) As Variant
Dim i As Integer
vColumns = Array("A", "B", "C", "D", "E", "F", "G")

For i = 1 To 7 Step 1
Debug.Print sColumns(i)
Next i

End Sub

--

Dave Peterson

Excel VBA & Arrays
 

Just to add...
Using a dynamic array instead of a fixed size array allows one
to assign to the array (provided you are using a later version of Excel)...

Public Sub Suffy()
Dim vColumns() As Variant
Dim i As Integer
ReDim vColumns(0 To 6)

vColumns = Array("A", "B", "C", "D", "E", "F", "G")
For i = 0 To 6
Debug.Print vColumns(i)
Next i
End Sub
-----------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware