How to avoid references in VBA (Early Binding vs. Late Binding)

Solution 1:

Please have a look at "VBA Early Binding" and "VBA Late Binding". for advantages and disadvantages of both methods.

You are using Earling Binding in this line: Set oFS = New FileSystemObject. "New" is a good indicator that Early binding is used. That's why you have to enable a reference on every machine where the macro runs.

If you use Late Binding, you don't have to enable references. The macro would work on every machine.

You can convert Early Binding

Dim oFS  As FileSystemObject 
Set oFS = New FileSystemObject

to Late Binding

Dim oFS  As Object 
Set oFS = CreateObject("Scripting.FileSystemObject")

Attention: If you use Late Binding, underlying numerical values of named variables are unknown to the system. You have to convert those named variables to their corresponding numerical value.

To get the numerical value, temporarily re-enable your library (e.g. Microsoft Scripting Runtime), go to the VBA editor and open the immediate window (CTRL+G). Write for example ?TemporaryFolder into the immediate window and press enter. As you see in the screenshot 2 is the value of our named variable TemporaryFolder

Repeat the steps for all other problematic variables and replace them with their value. Finally untick the library reference and your code is running on every machine whether the library is enabled or not.

oFS.GetSpecialFolder(TemporaryFolder)   'with Early Binding
oFS.GetSpecialFolder(2)                 'with Late Binding

Solution 2:

It is good to know, that

• Early Binding ( Explicitly checked Reference links ) is useful for the development or debugging, because of VBA editor Intellisense, strong object type control and named constants. It is also reportedly faster.
• Late Binding ( independent on Reference links) is useful for inter-machine compatibility.

I use with advantage the fast switching of Early/Late binding.

• I set by the VBA Editor in the VBA Project properties the Conditional compilation arguments to EarlyBinding = 1.

• I use then for the development the conditional compilation directives like below, with active EarlyBinding branch.
• When ready to release, I switch to EarlyBinding = 0 and the project turns From Early to Late Binding for the final Late binding testing.
• Edit: Do not forget to cancel the References previously explicitly used for the Early Binding

For the FileSystemObject TemporaryFolder example, it can be illustrated as:

Sub EarlyVsLateBindingtest()

#If Earlybinding Then
   Dim oFS As Scripting.FileSystemObject
   Set oFS = New Scripting.FileSystemObject
#Else
   Const TemporaryFolder = 2
   Dim oFS As Object
   Set oFS = CreateObject("Scripting.FileSystemObject")
#End If

oFS.GetSpecialFolder (TemporaryFolder)

End Sub