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
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
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.
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.
I think you are wrong when you say "Absolutely no difference for VBA". For example if you run this code
Private Sub test2()
zz = Timer
For i = 1 To 100000000
x = x + 1
Next
Debug.Print "No décalartion : " & Timer - zz
yy = Timer
Dim y As Long
For i = 1 To 100000000
y = y + 1
Next
Debug.Print "No décalartion : " & Timer - yy
End Sub
you will get this result
No décalartion : 3,746094 With décalartion : 1,855469
Consequently variable declaration improves performances
regards
