When is a Dim statement processed when vba code is compiled?

Question

When is a Dim statement processed in the compiling of VBA code? Is there any efficiency gain if I do this?:

Dim oFileDiag As FileDialog

Set oFileDiag = Application.FileDialog(msoFileDialogFilePicker)                
If oFileDiag.Show = -1 Then

    '// Dim statement further down in the code...
    Dim ofdSelected As FileDialogSelectedItems
    Set ofdSelected = .SelectedItems

End If

As opposed to this?:

'// Dim statement at the beginning of the code...
Dim oFileDiag As FileDialog
Dim ofdSelected As FileDialogSelectedItems

Set oFileDiag = Application.FileDialog(msoFileDialogFilePicker)                
If oFileDiag.Show = -1 Then

    Set ofdSelected = .SelectedItems

End If

score 3 · Accepted Answer · answered May 26 '11 at 18:04

As I understand it (I've been wrong before), there is no gain in efficiency in VB(A). Declaring any variable anywhere in the routine will use the same resources (though your variable won't be available until below its declaration).

The declaration of an object variable (Dim) only creates the reference, it does not instantiate the object until the Set. However, watch out for the opposite problem in this sort of construct:

Dim rsFoo As New ADODB.Recordset

'other statements

With rsFoo
    .LockType = adBatchOptimistic
    'other statements
End With

This seems like it might be convenient, since you don't have to do an explicit Set... = New.... But the gotcha is that every time you use rsFoo at runtime the code has to check whether or not it's been instantiated. Much better this way:

Dim rsFoo As ADODB.Recordset

'other statements

Set rsFoo = New ADODB.Recordset
With rsFoo
    .LockType = adBatchOptimistic
    'other statements
End With

This is, to some extent, a "religious" difference, at least in languages where there isn't any real difference in overhead. The most common practice in VB(A) is to declare all variables at the beginning of the routine, though there are some who argue that keeping the declaration as close as possible to the first use is "clearer" (not to those of us who are conditioned to expect them all at the beginning, it's not...).

@Hari, here is more to add to @Roland's answer: http://stackoverflow.com/questions/2478097/vba-difference-in-two-ways-of-declaring-a-new-object-trying-to-understand-why/2480559#2480559 — jtolle, May 26 '11 at 23:52

score 0 · Answer 2 · answered May 26 '11 at 14:03

0

Educated guess: that won't make any difference, however, prgrammers that come from C kept the habit to declare all their variable in the beginning of their code/function.

answered May 26 '11 at 14:03

Clement Herreman

10,274
4
35
57

Dave Rager · Answer 3 · 2011-05-26T14:13:43.060

0

I would say slight though since it's part of an interactive user interface you would never notice.

This is actually a tip in one of the Scott Meyers' Effective C++ books to hold off declaration of variables until the last moment to avoid unnecessary constructor overhead if (as in this case) you never actually need the object based on a failed condition.

I assume this would be similar in VB. Does Dim ofdSelected As FileDialogSelectedItems instantiate an object or just declare a reference?

edited May 26 '11 at 14:13

answered May 26 '11 at 14:07

Dave Rager

8,002
3
33
52

I wasnt sure though since if vba being a scripting language if it set the constructor overhead anyway, regardless of where it was. To answer your question, the ofdSelected declares a reference to an object. It is not instantiated (I dont think... ) until the Set statement. Thanks for the info... – Hari Seldon May 26 '11 at 14:48

When is a Dim statement processed when vba code is compiled?

3 Answers3