Why does this produce an error?

[zaphod2003]

Public Sub initialize()
Dim NumberOfColumns As Integer
NumberOfColumns = 13
Dim p(NumberOfColumns) As Integer


end Sub


Seems that Excel doesn't like using Variables to intialize arrays. How should this be done?

[[email protected]]

Hi
You need

Public Sub initialize()
Dim NumberOfColumns As Integer
Dim p( ) as integer
NumberOfColumns = 13
ReDim p(1 to NumberOfColumns)


end Sub

So you decare the array first with p( ) if you don't yet know the index
range. Then you must ReDim it once the index range is known.

regards
Paul

[Andy Pope]

Hi,

And as the warning message says you can use a Const.

Public Sub initialize()
Const NumberOfColumns = 13
Dim p(NumberOfColumns) As Integer
End Sub

Or use the ReDim

Public Sub initialize()
Dim NumberOfColumns As Integer
NumberOfColumns = 13
ReDim p(NumberOfColumns) As Integer
End Sub

Cheers
Andy

zaphod2003 wrote:
> Public Sub initialize()
> Dim NumberOfColumns As Integer
> NumberOfColumns = 13
> Dim p(NumberOfColumns) As Integer
>
>
> end Sub
>
>
> Seems that Excel doesn't like using Variables to intialize arrays. How
> should this be done?
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

[zaphod2003]

Thanks for your help much appreciated