Why does this produce an error?

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?

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

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

Thanks for your help much appreciated