Under what circumstances, if any, would one want to code

Dim arr(1 to 20, 1 to 10) instead of

Dim arr
Redim arr(1 to 20, 1 to 10)?

Alan Beban

When one is not sure how many elements can be expected in the array,
when he wants to populate the elements during run time.
Just a crude example:- I want to build an arry based on two columns in a
worksheet. I do not know how may of the rows in these column will
actually have data in it. So I first find out how may rows are actually
used during run time and then will Redim the array, then popuate the
array.
Compared to defining an Array with Dim (1000, 1000) so that may array
will never be short of elements,
I will be saving memory space used by the array, number of calculation
which I will be performing on the array elements, by using dymanic
array.

Sharad



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sharad wrote:
When one is not sure how many elements can be expected in the array,
when he wants to populate the elements during run time.
Just a crude example:- I want to build an arry based on two columns in a
worksheet. I do not know how may of the rows in these column will
actually have data in it. So I first find out how may rows are actually
used during run time and then will Redim the array, then popuate the
array.
Compared to defining an Array with Dim (1000, 1000) so that may array
will never be short of elements,
I will be saving memory space used by the array, number of calculation
which I will be performing on the array elements, by using dymanic
array.

Sharad



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks for the response, Sharad. I get from the above why one would use

Dim arr
Redim arr(20,10)

My question is when, if ever, would one want to use

Dim arr(20,10) instead.

Thanks again,
Alan Beban

When one knows number of elements before hand and need not change number
of elements it during the run time and also not want to clear all
elements during run time.

Please note apart from changing the number of elements, Redim can be
used to clear all the elements of the array, by single, Redim command
without using Preserve.

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

There's probably some performance benefit because the compiler/OS
doesn't have to worry about potentially having to resize the array. In
the old mainframe days, there was a way to convince the compiler to
stick the entire array into the stack. Helped speed up a program at
the cost of locking down that much memory.

With modern computers, it probably saves a whole microsecond or two
over the lifetime of a program. {grin}

And, it requires fewer keystrokes, something else that seems to impress
many people.

Finally, an oddity. The Dim part is not necessary. While the
documentation says it is, it is not. Until someone pointed it out,
I've been using Redim without a preceeding Dim blissfully unaware of
the requirement.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Under what circumstances, if any, would one want to code

Dim arr(1 to 20, 1 to 10) instead of

Dim arr
Redim arr(1 to 20, 1 to 10)?

Alan Beban

Tushar Mehta wrote:
There's probably some performance benefit because the compiler/OS
doesn't have to worry about potentially having to resize the array. In
the old mainframe days, there was a way to convince the compiler to
stick the entire array into the stack. Helped speed up a program at
the cost of locking down that much memory.

With modern computers, it probably saves a whole microsecond or two
over the lifetime of a program. {grin}

And, it requires fewer keystrokes, something else that seems to impress
many people.

Finally, an oddity. The Dim part is not necessary. While the
documentation says it is, it is not. Until someone pointed it out,
I've been using Redim without a preceeding Dim blissfully unaware of
the requirement.

Thanks, Tushar.

I have a dim recollection (no pun intended) that I had the same view
about no Dim being required in xl5, but that something changed in xl97;
that under some circumstances a ReDim would be rejected without a Dim.
I'll try to recreate the problem, and if I do I'll report back.

Thanks again,
Alan Beban

Alan Beban wrote:
Tushar Mehta wrote:

There's probably some performance benefit because the compiler/OS
doesn't have to worry about potentially having to resize the array.
In the old mainframe days, there was a way to convince the compiler to
stick the entire array into the stack. Helped speed up a program at
the cost of locking down that much memory.

With modern computers, it probably saves a whole microsecond or two
over the lifetime of a program. {grin}

And, it requires fewer keystrokes, something else that seems to
impress many people.

Finally, an oddity. The Dim part is not necessary. While the
documentation says it is, it is not. Until someone pointed it out,
I've been using Redim without a preceeding Dim blissfully unaware of
the requirement.

Thanks, Tushar.

I have a dim recollection (no pun intended) that I had the same view
about no Dim being required in xl5, but that something changed in xl97;
that under some circumstances a ReDim would be rejected without a Dim.
I'll try to recreate the problem, and if I do I'll report back.

Thanks again,
Alan Beban

Aha!

I have a 1993-94 Microsoft Excel Visual Basic User's Guide. It states
"When you create a dynamic array that is local to a procedure, declaring
the array with a *Dim* or *Static* statement is recommended but not
required."

In xl2000 the following works; but with the Dim statement commented out,
it throws a compile error "Invalid ReDim".

Alzheimer's hasn't caught me yet :-)

Sub foo()
Dim rng As Range
Dim arr
Set rng = Range("A1:C3")
arr = rng
ReDim arr(4, 4)
Debug.Print UBound(arr); UBound(arr, 2)
End Sub

Alan Beban

In article ,
says...
Alan Beban wrote:
Tushar Mehta wrote:

There's probably some performance benefit because the compiler/OS
doesn't have to worry about potentially having to resize the array.
In the old mainframe days, there was a way to convince the compiler to
stick the entire array into the stack. Helped speed up a program at
the cost of locking down that much memory.

With modern computers, it probably saves a whole microsecond or two
over the lifetime of a program. {grin}

And, it requires fewer keystrokes, something else that seems to
impress many people.

Finally, an oddity. The Dim part is not necessary. While the
documentation says it is, it is not. Until someone pointed it out,
I've been using Redim without a preceeding Dim blissfully unaware of
the requirement.

Thanks, Tushar.

I have a dim recollection (no pun intended) that I had the same view
about no Dim being required in xl5, but that something changed in xl97;
that under some circumstances a ReDim would be rejected without a Dim.
I'll try to recreate the problem, and if I do I'll report back.

Thanks again,
Alan Beban

Aha!

I have a 1993-94 Microsoft Excel Visual Basic User's Guide. It states
"When you create a dynamic array that is local to a procedure, declaring
the array with a *Dim* or *Static* statement is recommended but not
required."

In xl2000 the following works; but with the Dim statement commented out,
it throws a compile error "Invalid ReDim".

Alzheimer's hasn't caught me yet :-)

Sub foo()
Dim rng As Range
Dim arr
Set rng = Range("A1:C3")
arr = rng
ReDim arr(4, 4)
Debug.Print UBound(arr); UBound(arr, 2)
End Sub

Alan Beban

Hi Alan,

I guess I haven't run into that problem because I never program without
Option Explicit.

The following works just fine:

Option Explicit
Sub testIt()
ReDim arr(1 To 5)
MsgBox TypeName(arr) & ", " & nbrDim(arr) _
& ", " & LBound(arr) & ", " & UBound(arr)
arr = Range("A1:A3")
MsgBox TypeName(arr) & ", " & nbrDim(arr) _
& ", " & LBound(arr) & ", " & UBound(arr)
arr = Application.WorksheetFunction.Transpose(Range("A1: A3"))
MsgBox TypeName(arr) & ", " & nbrDim(arr) _
& ", " & LBound(arr) & ", " & UBound(arr)
End Sub


nbrDim is a function that returns the number of dimensions in an array:

Function nbrDim(x) As Integer
Dim i As Integer, rslt As Integer
i = 1
On Error GoTo XIT
Do While True
rslt = LBound(x, i)
i = i + 1
Loop
XIT:
nbrDim = i - 1
End Function


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Hi Alan,

I guess I haven't run into that problem because I never program without
Option Explicit.

The following works just fine:

Option Explicit
Sub testIt()
ReDim arr(1 To 5)
MsgBox TypeName(arr) & ", " & nbrDim(arr) _
& ", " & LBound(arr) & ", " & UBound(arr)
arr = Range("A1:A3")
MsgBox TypeName(arr) & ", " & nbrDim(arr) _
& ", " & LBound(arr) & ", " & UBound(arr)
arr = Application.WorksheetFunction.Transpose(Range("A1: A3"))
MsgBox TypeName(arr) & ", " & nbrDim(arr) _
& ", " & LBound(arr) & ", " & UBound(arr)
End Sub


nbrDim is a function that returns the number of dimensions in an array:

Function nbrDim(x) As Integer
Dim i As Integer, rslt As Integer
i = 1
On Error GoTo XIT
Do While True
rslt = LBound(x, i)
i = i + 1
Loop
XIT:
nbrDim = i - 1
End Function


--
Regards,

Tushar Mehta

Tushar,

Hope it's not picky to point out your example would fail in XL97

ReDim arr(1 To 5)
arr = Range("A1:A3")
'compile error, can't assign to array (xl97)

but this is OK in xl97
Dim arr
arr = Range("A1:A3")

recently http://tinyurl.com/4ydfd

Alan,

Another highly contrived scenario when it is necessary to declare before
Redim

Sub test()
Dim x, i
Dim arr() As Long 'fails if commented
x = 20
For i = 1 To x
If i = 5 Then Exit For
ReDim Preserve arr(1 To i)
arr(i) = i
Next
MsgBox UBound(arr)
End Sub

Regards,
Peter