1

I'm creating a two-dimensional dynamic array in Excel VBA. Most of the code examples I can find are like the following:

Dim companyArray() As Variant
Dim height, width As Integer
width = 2
height = WorksheetFunction.CountA(Worksheets(1).Columns(1))
ReDim companyArray(height, width)

Is there a reason I wouldn't want to declare the array after I've gotten a value for the height variable? Would I not want to avoid using ReDim if it's possible?

Community
  • 1
  • 1
cemattis
  • 13
  • 4
  • 1
    Dim and ReDim aren't interchangeable, they do different things. – RBarryYoung Feb 15 '17 at 20:18
  • You "dimmed" `companyArray` already so you have to use `redim`. The alternative is not to `Dim` the variable in the upper part of your code and then immediately use `Dim` where you are currently using `ReDim`. You can `Dim` a variable only once and from there on you have to use `Redim`. (At least that's the theory. In actually its this: http://stackoverflow.com/questions/12907050/redim-without-dim) – Ralph Feb 15 '17 at 20:21

1 Answers1

5

Dim companyArray(1 to 5) is static sizing: needs that the bounds are constants known at the time you write the code.

Redim companyArray(1 to x) is dynamic sizing, required when the bounds are computed while the program is running.

When any of the bounds (on any dimension) is not known at coding time, Redim is the only option.

A word of caution

Redim does not need that the variable be dimmed before, you can just declare and redim a dynamic array. However, There is some risk in doing so. Consider this

' Module
Dim X as variant ' some module-level (or global) variable
''''''''''''''''''
' Some code
'''''''''''''''''
Sub foo()
   Redim X(1 to 3) as long ' <-- Will redim the global X
   ....
End Sub

If the intent of the programmer when writing foo was to declare a local variable and Redim it in one shot, then this will fail, because the Redim statement will operate on the global X because it is within scope.

The bottom line is that Redim declares and redims the variable, but if a variable with the same name exists in its scope, it will redim that variable instead of creating a new variable. For this reason, even though it is not strictly necessary, it is safer to use Dim before Redim, by rules of good practice.

Sub foo()
   Dim X() as Long
   Redim X(1 to 3) as long ' <-- Will now refer to the local variable X
   ....
End Sub
A.S.H
  • 29,101
  • 5
  • 23
  • 50