Option Explicit and Arrays

  • Thread starter Thread starter Harlan Grove
  • Start date Start date
H

Harlan Grove

Just caught a typo.

In Excel 97 VBE, the following general module code doesn't generate a compile
error.

'---- begin VBA ----
Option Explicit

Sub foo()
Dim n As Long
n = 5
ReDim y(1 To 5) '<- !!
MsgBox n
End Sub
'---- end VBA ----

Is the ReDim line an array variable declaration similar to Dim, or a statement,
or a hybrid? Does this work the same in 2K and XP?
 
Harlan said:
Just caught a typo.

In Excel 97 VBE, the following general module code doesn't generate a compile
error.

'---- begin VBA ----
Option Explicit

Sub foo()
Dim n As Long
n = 5
ReDim y(1 To 5) '<- !!
MsgBox n
End Sub
'---- end VBA ----

Is the ReDim line an array variable declaration similar to Dim, or a statement,
or a hybrid? Does this work the same in 2K and XP?
Click to expand...
Works as is in 2002 (XP).

It causes an error if it is changed to:
ReDim Preserve y(1 To 5)

Apparently this is a feature - I found in the help the following:
"Caution: The ReDim statement acts as a declarative statement if the
variable it declares doesn't exist at module level or procedure level.
If another variable with the same name is created later, even in a
wider scope, ReDim will refer to the later variable and won't
necessarily cause a compilation error, even if Option Explicit is in
effect. To avoid such conflicts, ReDim should not be used as a
declarative statement, but simply for redimensioning arrays."


Regards,

Matthew
 
Harlan,

The Help file indicates that this is the expected behavior.
Caution The ReDim statement acts as a declarative statement if
the variable it declares doesn't exist at module level or
procedure level.
<<<

It is the same in 2000 and 2002.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)


Harlan Grove said:
Just caught a typo.

In Excel 97 VBE, the following general module code doesn't generate a compile
error.

'---- begin VBA ----
Option Explicit

Sub foo()
Dim n As Long
n = 5
ReDim y(1 To 5) '<- !!
MsgBox n
End Sub
'---- end VBA ----

Is the ReDim line an array variable declaration similar to Dim, or a statement,
or a hybrid? Does this work the same in 2K and XP?

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup
Click to expand...
archives.
 
There are two types of arrays(i.e. static and dynamic). A
static array is dim myarray(1 to 5). A dynamic array will
appear as dim myarray(). And then the code further down,
will show the redim statement specifying the parameters,
such as redim myarray (1 to foundfiles.count). The error
in your code below is that the redim refers to the wrong
letter (s/b n, not y) or that redim Y was not initalized
with dim y.

Robertw
 
Back
Top