Dim and Redim

[Guest]

Hi All,

I am interested in knowing why this is not working ....

dim arr() as integer

some code .....

redim preserve arr(20) as integer

If I use redim in the first line -> redim arr() as integer then it works.

So in short my question is why redim doesn't work after dim on the same
array? It says it is supposed to work according to the excel help files.

thanx

 

[Guest]

You do not want to specify "As Integer" on the redim statement...

dim arr() as integer

redim preserve arr(20)

 

[Guest]

Hi Jim,
I tried that and it still won't work.

this is the code...
Sub try()

Dim arr() As Integer
Dim first As Integer

Range("A1").Select
first = 1

Do While ActiveCell.Offset(first, 0).Value <> ""
arr(first) = ActiveCell.Offset(first, 0).Value
first = first + 1
Loop

ReDim Preserve arr(100)
'Call sort(arr)
first = 1

Do While ActiveCell.Offset(first, 0).Value <> ""
ActiveCell.Offset(first, 1).Value = arr(first)
first = first + 1
Loop

End Sub

It says application-defined or object-defined error.
thanx

 

[Guest]

You can not use the array until you have initailized it to some number of
items. Also note that arrays start at zero (unless you change the base)

Sub try()

Dim arr() As Integer
Dim first As Integer

Range("A1").Select
first = 1

Do While ActiveCell.Offset(first, 0).Value <> ""
ReDim Preserve arr(first - 1)
arr(first - 1) = ActiveCell.Offset(first, 0).Value
first = first + 1
Loop

'Call sort(arr)
first = 1

Do While ActiveCell.Offset(first, 0).Value <> ""
ActiveCell.Offset(first, 1).Value = arr(first - 1)
first = first + 1
Loop

End Sub

 

[Guest]

I see... thatx a lot.

You can not use the array until you have initailized it to some number of
items. Also note that arrays start at zero (unless you change the base)

Sub try()

Dim arr() As Integer
Dim first As Integer

Range("A1").Select
first = 1

Do While ActiveCell.Offset(first, 0).Value <> ""
ReDim Preserve arr(first - 1)
arr(first - 1) = ActiveCell.Offset(first, 0).Value
first = first + 1
Loop

'Call sort(arr)
first = 1

Do While ActiveCell.Offset(first, 0).Value <> ""
ActiveCell.Offset(first, 1).Value = arr(first - 1)
first = first + 1
Loop

End Sub