Performance benefit of variable declerations in VBA?

Question

Is there a performance benefit of having variables dimensioned in the beginning of a function verses having them declared just before they are used?

I am using VBA in MS Access 2003.

Example,

 Function f(y As Long) As Long
      Dim x As Long
      If y <> 0 Then
           x = 1000000
      End If
 End Function

Verses

 Function f(y As Long) As Long
      If y <> 0 Then
           Dim x As Long
           x = 1000000
      End If
 End Function

Answer 1

Absolutely no difference for VBA. Declaring the variables will only affect the design-time debugging (the IDE will know what auto-complete (intellisense) to show). It doesn't affect performance at all, in this case. I've done a lot of VBA macros as well, and that's one thing I've noticed.

As a demonstration, just try to set a breakpoint on a Dim statement, and you'll see that it doesn't allow you. It's because this instruction is never executed, and is only used to guide the just-in-time debugging engine.

Hope it helps

Answer 2

To add to Wadih M's answer, all declared variables are initialised prior to the procedure being compiled, regardless of their position in the procedure.

To test this, try to use a variable without declaring it (you do use Option Explicit, right?):

Option Explicit

Private Sub notDeclared()
    Dim x As Long

    Debug.Print x
    Debug.Print y
End Sub

This returns a "Variable not defined error" with the breakpoint being the procedure declaration, i.e. before any of the code is executed, and the undeclared variable y highlighted.

Answer 3

Not in performance of the code itself as has been answered by Wadih.

But performance in maintaining the code, sure. I'd say most programmers expect the variables to be defined at the top of the function. A simple example like you provided and it doesn't matter. But a complex function with variable declarations interspersed in the code makes it feel cluttered. Thus slowing down the time to read and grok the code. Thus increasing the time it takes to maintain.

There is also the possible problem with declaring a variable in a loop and not intending to. Thus the variable gets reset with each loop iteration.

And performance of the code itself is often not as important as performance in maintaining the code.