Is it possible to declare a reference as an object, this way we could avoid our issues with broken references? I would just try it, but it seems like there is a quite a lot of testing it would require, and maybe someone here already knows the answer.
Okay. Not a big deal, I just saw a post on a blog about getting information about broken references. It was a function that gives you the name and expected location of the broken reference, so that got me thinking.
Could you give an example of late binding a reference?
I have references but use code to check they still exist and repair the reference if it is broken. I never really considered late binding the reference as I always got side tracked and it ended up being a case of 'i will do it eventually'!
One example is if you are using code to work with Excel. You don't need to set a reference to Excel (which will then simplify matters as it won't matter which version you have - for the most part, unless you use newer features which don't exist in other versions that may be using this code).
Instead of early binding (setting a reference to Microsoft Excel x.0 Object Library and using
Code:
Dim objXL As Excel.Application
Dim xlWB As Excel.Workbook
Set objXL = New Excel.Application
you would use
Code:
Dim objXL As Object
Dim xlWB As Object
Set objXL = CreateObject("Excel.Application")
Late binding is not as simple as that. In addition to losing intellisense, you also loose the ability to use enums. You will need to replace all arguments with their numeric values.
That's not all that hard, if you use a separate module and add them as your own constants as you need them and then just export that module out as a bas file and import it when needed to other databases.
And as for intellisense, I work with early binding when developing and then switch to late binding when moving to production. No biggie.
I get around the Intellisense and Enums during developement by including the reference and declaring the object type but use the Late Binding command to instantiate them. This "Mixed Binding" (for want of a better name) works fine.
Later I simply change the declarations to Object and remove the reference.
Then I leave the Enum argument names in the code and declare them as Global Constants. The values can easily be determined by using the Immediate Window while the reference is still included.
eg ? acCmdSaveRecord
(This reveals it as 97.)
I have an exported module that includes the values I use as constants so they are easily imported into any project that requires them.
Another alternative is to use Conditional Compilation.
Here is an example instantiating an Excel application.
Code:
#Const LateBinding = True
Private Sub ConditionalBindingDemo()
#If LateBinding Then
Dim obj As Object
Set obj = CreateObject("Excel.Application")
#Else
Dim obj As Excel.Application
Set obj = New Excel.Application
#End If
End Sub
If you put this code into a Standard Module and run the Sub you will see it happily runs without the Excel reference so long as the LateBinding constant is True.
Note that this does not slow down the application at runtime. It is a compliation directive so is only executed during compilation.
This can also be done with the Mixed Binding which would only require the Dim to be conditional.
Unlike an ordinary If, the Conditional Compilation If block can be used in the Declarations section of the module for module wide and global variables.
Unfortunately AFAIK in VBA the #Const is only able to be declared at a module level so needs to be done separately in each module where Conditional Compilation is used.
