In standard code, we make use of #LateBind conditional compilation constant, which is typically defined via project’s Conditional Compilation Argument in the dialog:
NOTE: The dialog can be found via Tools → <project name> Properties, which is located below the Options menu item. The menu item caption will be different as it includes the project name. In this example, because the project name is YourApp, the menu will show YourApp Properties…:
Why have #LateBind constant?
The primary objective for the constant is to enable easy switch between early and late binding while minimizing the changes to the code. Contrast the difference between a typical early bound code to a typical late bound code:
Early-bound code
|
1 2 3 4 5 6 7 8 9 10 11 |
Dim app As Excel.Application Dim wbk As Excel.Workbook Set app = New Excel.Application Set wbk = app.Workbooks.Add If wbk.FileFormat = xlOpenXMLWorkbook Then Debug.Print "It's a xslx format" Else Debug.Print "It's not a xlsx format" End If |
Late-bound code
|
1 2 3 4 5 6 7 8 9 10 11 |
Dim app As Object Dim wbk As Object Set app = CreateObject("Excel.Application") Set wbk = app.Workbooks.Add If wbk.FileFormat = 51 Then Debug.Print "It's a xslx format" Else Debug.Print "It's not a xlsx format" End If |
Note the follow differences:
- Data types are Object rather than strong-typed versions
- Constants/Enums are no longer available
- New cannot be used; CreateObject or GetObject must be used instead.
- The early-bound code requires a reference (via Tools → References) to Excel object library. Late-bound code doesn’t but it does need Excel installed to run.
The major problem is that for a code that does a lot of automation, there can be several changes necessary to make a code late-bound. As a company rule, we prefer to ship code using late-binding rather than early-binding which avoid several problems associated with versioning and resolving the references. A broken reference can cause application to malfunction. For that reason, we prefer to minimize the number of references we must ship in our application.
OK, but how does #LateBind help us?
Instead of changing several pieces and risking introducing bugs into your code, we should write the code once, and make it compatible for both modes using conditional compilation. Here’s a better version that avoids the unnecessary changes:
Code capable of both early/late binding
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
#If Latebind Then Dim app As Object Dim wbk As Object Const xlOpenXMLWorkbook As Long = 51 #Else Dim app As Excel.Application Dim wbk As Excel.Workbook #End If Set app = CreateObject("Excel.Application") Set wbk = app.Workbooks.Add If wbk.FileFormat = xlOpenXMLWorkbook Then Debug.Print "It's an xslx format" Else Debug.Print "It's not an xlsx format" End If |
Note the following difference:
- We declare both versions of variables as either Object or its strong-typed version
- We define the constants/enums within the LateBind branch
- We always use CreateObject outside the conditional blocks
- The rest of code remains unchanged.
This means that simply by changing LateBind from 1 to 0, we can be start using early-bound code and thus leverage Intellisense support while we develop and debug against external references. When we are done developing & debugging and ready to ship the code, we can change Latebind back to 1, remove the references and ship the application.
How do I change the mode?
- To switch from late-bound to early-bound change the Latebind constant to 0.
- To switch from early-bound to late-bound change the Latebind constant to 1.
