Hi,
I hope Dugantrain doesn't mind me chipping in here. I wrote a little tutorial on late binding for my website. My site is currently trashed so here is the gist of what it said. I hope you find it useful
<hr>
You are probably aware that VBA allows you to "bind" to another application and use that application's methods and properties just as though you were programming directly in that application. This is commonly used to automate one application from within another e.g. controlling Microsoft Outlook from within Microsoft Excel. Here is a very simple example demonstrating this technique. You need to have Microsoft Outlook installed for this to work.</p>
- Open Excel and activate the Visual Basic Editor (Alt F11).
- Click Tools, References on the VBE toolbar and look for Microsoft Outlook n.x Object Library where n.x will depend on the version of Outlook available on your machine.
- Insert a standard module and paste the following code:
Code:
Sub EarlyBindingExample()
Dim olApp As Outlook.Application
Dim olContact As Outlook.ContactItem
Set olApp = New Outlook.Application
Set olContact = olApp.CreateItem(olContactItem)
With olContact
.FirstName = "Daniel"
.LastName = "Klann"
.Email1Address = "dan@danielklann.com"
.Save
End With
End Sub
- Run the macro. If you go into Outlook you will see that you have a new contact, me!
Now this code should work perfectly time and time again. Problems may occur however, when you send your workbook off to another colleague, client, friend or whoever and they have a different version of Outlook than you. You may get compile errors - 'Sub or Function not Defined' and the references will be marked MISSING in the References dialog.</p>
Specifically, the problem occurs if the recipient has an older version of the application than the one the project was created in. For example, I develop a project with the above code and I'm using Outlook 2002. I then send the project onto a colleague who uses Outlook 2003. VBA will correctly update the reference to the new library and the code will function without problem. However, if the colleague is using Outlook 2000 for example, then they will develop symptoms similar to those described above and
your code will fail. The goal is to create code that will run as expected regardless of which versions of software are installed.</p>
The most reliable way to get round this problem is to ditch early binding (as demonstrated above) and use late-binding. Late binding is where VBA will create objects "on-the-fly" without knowing in advance what type of object it's going to be creating (including which version). There are distinct disadvantages to late-binding: </p>
<ul>
[*]You will lose the Intellisense drop-down box that lists properties and methods for whatever class you're working with. This could significantly impact development time.
[*]Compile-time errors won't be picked up e.g. missing required arguments.
[*]Your final code will run more slowly than if you'd used early-binding. However, the impact will probably not be too severe.
[/list]
The first two of these can be eliminated by using early-binding whilst developing and then converting to late-binding once you're satisfied that your application functions as expected.</p>
The steps involved: </p>
- Declare any specific classes as the generic Object variable type.
- Replace any usage of the New keyword with CreateObject.
- Convert symbolic constants to their literal equivalents, or define those constants yourself.
- Remove any references from your project relating to the external libraries.
- Compile and test your project.
For this example, I'll use the code from above.</p>
- Once you remove the reference to Outlook, VBA will not know what Outlook is and it will not be able to resolve Outlook.Application and Outlook.ContactItem. They need to be declared as Object.
Code:
Dim oOLApp As Object
Dim oOLContact As Object
Note : I prefer to prefix any variables declared as object with a lower case 'o'. This is not required, it's just my preference.</p>
- The New keyword is used to instantiate an instance of a class i.e. create an object of the specified type. Instead of using New, we need to use CreateObject:
Set oOLApp = CreateObject("Outlook.Application")
- As the Outlook library is not available at compile-time obviously any constants defined in that library will also not be available. In the example the constant olContactItem will be undefined. We need to find out what its value is by either looking for its value in the object browser or by opening the immediate window and typing ?olcontactitem. Either way, its value is 2. My personal preference is to define the constants in my code, rather than using their numeric equivalent. This allows you to maintain readability and will help you understand what the code is doing when you go back to it in a year's time. Here is what the revised code would look like:
Code:
Private Const olContactItem As Long = 2
Sub AutomationExample()
Dim oOLApp As Object
Dim oOLContact As Object
Set oOLApp = CreateObject("Outlook.Application")
Set oOLContact = oOLApp.CreateItem(olContactItem)
With oOLContact
.FirstName = "Daniel"
.LastName = "Klann"
.Email1Address = "dan@danielklann.com"
.Save
End With
End Sub
In essense, that is all you need to do. Obviously for larger projects this may be a time consuming process but if your target users will be using different versions of applications then it is the safest way to develop your projects.</p>
