Early vs Late Binding
Contents:
- Early vs. Late Binding
- More about CreateObject and GetObject
- So Which One Is Better?
- Converting code from early binding to late binding
Early vs. Late Binding
The code examples above all make use of what is known as "early binding". Early binding generally involves two steps:
- adding a reference to the Microsoft Excel Object Library in your VB6 app
- using code like the following to declare and create your Excel objects:
Dim oExcel As Excel.Application Dim oWorkbook As Excel.Workbook Set oExcel = New Excel.Application Set oWorkbook = oExcel.Workbooks.Add
This is called "early binding" because the compiler knows up front what kind of object oExcel will be (namely, an Excel application object). The alternative to early binding is called "late binding". If you use late binding:
- do not create a reference to the Excel object library
- use code like the following to declare and create the Excel.Application reference:
Dim oExcel As Object Dim oWorkbook As Object Set oExcel = CreateObject("Excel.Application") Set oWorkbook = oExcel.Workbooks.Add
This is called "late binding" because VB will only find out what kind of object oExcel will be when you create it.
If you use late binding, VB won't be able to draw on the Excel object library. That will require you to make some changes to your code, compared to early binding. More on that later.
More about CreateObject and GetObject
Note that the following is an example of early binding, despite the use of CreateObject:
Dim oExcel As Excel.Application Set oExcel = CreateObject("Excel.Application")
A close relative to CreateObject() is the GetObject() function. It can be used to attain a reference to a currently running instance of Excel, or to create a new one, or even to open an Excel workbook directly. GetObject can be used with both early and late binding.
Here's an example with early binding. The following snippet will grab the existing Excel instance if Excel is already running; otherwise it will start up a new instance of Excel:
On Error Resume Next Set oExcel = GetObject(,"Excel.Application") On Error Goto 0 If oExcel Is Nothing Then 'Excel wasn't Open - Open a New one Set oExcel = GetObject("","Excel.Application") End If
So Which One Is Better?
Let me say this straightaway: I would use early binding whenever possible. And it is possible in the majority of cases.
The advantages of early binding include:
- Your app will run faster
- It is easier to write the code, because you can make use of IntelliSense/AutoComplete, as well as the Object browser
- You'll get fewer runtime errors, since syntax errors will be picked up at compile time
- You can use Excel's built-in constants, like xlCellTypeVisible, xlContinuous, xlNone etc. With late binding, you would have to use the equivalent integer values instead, or declare the constants yourself (more on that later).
The main argument you'll generally hear for late binding is that your app is then more likely to run with all versions of the application you're automating, whereas with early binding you essentially tie yourself to a single version. And if the user doesn't have that version installed on their PC, your app would fail. However, that argument doesn't really hold for Excel. All the newer versions of Excel include the lower-version object libraries. So in that case I'd still use early binding, and use the lowest available version of the Excel object library - and your code will work with all versions of Excel.
Note that this is not true for all Office apps - for Access, for example, MS says:
"During development of Access 2000, the Access object model was accidentally modified in a way that breaks both binary (v-table) and dispid compatibility with Access 97"
- so late binding would be recommended for automating Access.Late binding would also be useful if you're not sure whether the user has any version of Excel installed at all. With late binding, that'll cause a trappable runtime error, whereas with early binding, your code wouldn't run at all. You would also use late binding if you're writing a VBScript rather than a VB6 app.
Converting code from early binding to late binding
If you're going to use late binding, I would recommend you to use early binding while developing your code, and when you're finished, convert it to late binding. This way you get all the advantages of early binding during coding (AutoComplete, the object browser etc). I've already mentioned the changes needed, but here they are again:
- Remove the reference to the Excel object library
- Change the declaration of all Excel objects to the generic Object type
- Use CreateObject to create the Excel object, rather than Set and New
- Actually this one should be done first and not last, but it takes longer to explain...) Replace Excel's built-in constants with integer equivalents, or declare them yourself - I prefer the latter, because it makes your code much easier to read. Example:
'early binding: With xlWB.Charts.Add .SetSourceData Source:=xlWB.Worksheets("Sheet1").Range("A1:A10") .ChartType = xlLine .Location Where:=xlLocationAsObject, Name:="Sheet1" End With 'late binding: Const xlLine As Integer = 4 Const xlLocationAsObject As Integer = 2 With xlWB.Charts.Add .SetSourceData Source:=xlWB.Worksheets("Sheet1").Range("A1:A10") .ChartType = xlLine .Location Where:=xlLocationAsObject, Name:="Sheet1" End With
You can find the integer equivalents through the object browser - just search for the constant name. Another easy way to find the integer values of Excel's constants is to write your app using early binding, and then in break mode use either the Immediate Window to print the values of the constants - or simply hover your mouse over the constant in your code. Note that you need to do this before you remove your reference to the Excel object library.
