Add-Ins
Blogs I Read
ContactNovember 17, 2008 – 9:51 am by JP
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.
. So you (or your end users) can get an immediate visual clue as to the success or failure of their formula. It's low-tech but very effective.
