Take advantage of Intellisense when writing late bound code

Many people are familiar with the page on binding found on Dick Kusleika's website "Dick's Clicks" which advises the following:

To get the most out of the VBA development environment and still write robust code, you should write the code early bound, but change it to late bound before distributing it. Even if you write it for personal use only, it makes sense to convert it to late bound. Someday you will have a different computer or send it to your brother and it won't work because they will have an earlier version. If you're a die-hard procrastinator like me, you will be cursing yourself for not converting to late bound sooner.

The page also provides a convenient checklist for getting this done:

Late Bound Conversion Checklist

• Change all declarations from Outlook objects to the generic Object data type
• Change Set statements to GetObject or CreateObject
• Change any built-in constants to their intrinsic values
• Add optional arguments that have a default value

In this post I am going to go through these steps and show you exactly how to convert early bound to late bound code, taking advantage of Intellisense along the way, to get the best of both worlds, if you will.

First, here's a small sub that lists all of the files in a folder in column A. Before I wrote the code, I set a reference to the Microsoft Scripting Runtime. That way, we can take advantage of Intellisense to write our code.

Sub ListFiles_1()

Dim fso As Scripting.FileSystemObject
Dim filen As Scripting.File
Dim NextRow As Long

On Error Resume Next
Set fso = New Scripting.FileSystemObject
On Error GoTo 0

If fso Is Nothing Then GoTo ExitProc

Dim strFolder As String

strFolder = "F:\MyFolder\"

NextRow = 1

For Each filen In fso.GetFolder(strFolder).Files
  Cells(NextRow, 1).Value = filen.Name
  NextRow = NextRow + 1
Next filen

ExitProc:
Set fso = Nothing
End Sub

You'll notice that Intellisense tries to help you when you enter the following statements relating to the Scripting Runtime:

Dim fso As Scripting.FileSystemObject
Dim filen As Scripting.File
Set fso = New Scripting.FileSystemObject
For Each filen In fso.GetFolder(strFolder).Files
  Cells(NextRow, 1).Value = filen.Name

If you started out with the intention of writing late-bound code, and immediately started declaring your variables as Object, you would need to be very familiar with your objects to make sure you access their properties correctly. When its early bound, the computer (mostly) does that for you.

Now that we've got our early bound code written, and Intellisense has helped us all it can, we start changing the code so it's late bound.

As you recall from above, the first step is: Change all declarations from Outlook objects to the generic Object data type. So we'll change the declarations to Object for all of the Scripting objects.

Sub ListFiles_2()

Dim fso As Object
Dim filen As Object
Dim NextRow As Long

On Error Resume Next
Set fso = New Scripting.FileSystemObject
On Error GoTo 0

If fso Is Nothing Then GoTo ExitProc

Dim strFolder As String

strFolder = "F:\MyFolder\"

NextRow = 1

For Each filen In fso.GetFolder(strFolder).Files
  Cells(NextRow, 1).Value = filen.Name
  NextRow = NextRow + 1
Next filen

ExitProc:
Set fso = Nothing
End Sub

Even though we haven't fully converted the code yet, it will still work because we still have that reference to Microsoft Scripting Runtime. We want it to be fully late-bound, so we'll go to step 2: Change Set statements to GetObject or CreateObject. In addition, we'll remove that reference to Microsoft Scripting Runtime.

Sub ListFiles_Final()

Dim fso As Object
Dim filen As Object
Dim NextRow As Long

On Error Resume Next
Set fso = CreateObject("Scripting.FileSystemObject")
On Error GoTo 0

If fso Is Nothing Then GoTo ExitProc

Dim strFolder As String

strFolder = "F:\MyFolder\"

NextRow = 1

For Each filen In fso.GetFolder(strFolder).Files
  Cells(NextRow, 1).Value = filen.Name
  NextRow = NextRow + 1
Next filen

ExitProc:
Set fso = Nothing
End Sub

Steps 3 and 4 don't really apply to this example, but here's a small example if you were writing late-bound code for Outlook to create a MailItem.

Change:

Dim olApp As Outlook.Application
Set olApp = Outlook.Application
olApp.CreateItem(olMailItem)

to:

Dim olApp As Object
Set olApp = CreateObject("Outlook.Application")
olApp.CreateItem(0)

According to the list of Outlook 2003 Constants, the value of olMailItem is 0 (zero), so the late-bound version uses that value instead of the constant.

Related Articles:

• March 18, 2009 -- List matching folders in a directory
• March 6, 2009 -- Why I prefer late bound code