Dim and Redim

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

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

dim arr() as integer

redim preserve arr(20)

--
HTH...

Jim Thomlinson


"Sean" wrote:

> 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

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

"Jim Thomlinson" wrote:

> You do not want to specify "As Integer" on the redim statement...
>
> dim arr() as integer
>
> redim preserve arr(20)
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Sean" wrote:
>
> > 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

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
--
HTH...

Jim Thomlinson


"Sean" wrote:

> 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
>
> "Jim Thomlinson" wrote:
>
> > You do not want to specify "As Integer" on the redim statement...
> >
> > dim arr() as integer
> >
> > redim preserve arr(20)
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Sean" wrote:
> >
> > > 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

I see... thatx a lot.

"Jim Thomlinson" wrote:

> 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
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Sean" wrote:
>
> > 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
> >
> > "Jim Thomlinson" wrote:
> >
> > > You do not want to specify "As Integer" on the redim statement...
> > >
> > > dim arr() as integer
> > >
> > > redim preserve arr(20)
> > >
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Sean" wrote:
> > >
> > > > 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