Working with References

One of the major enhancements in recent versions of VBA is the ability to declare a reference to an external object library (using the Tools O References dialog), and then use the objects defined in that library as if they were built into Excel. In this chapter, for example, you have been using the objects defined in the VBA Extensibility library without thinking about where they came from.

The term for this is early binding, so named because you are binding the external object library to your application at design time. Using early binding gives the following benefits:

□ The code is much faster, because all the links between the libraries have been checked and compiled.

□ The New operator can be used to create instances of the external objects.

□ All of the constants defined in the object library can be utilized, thus avoiding numerous "magic numbers" throughout the code.

Excel displays the Auto List Members, Auto Quick Info, and Auto Data Tips information for the objects while the application is being developed.

This is explained in more detail in Chapter 19.

There is, however, one major disadvantage. If you try to run your application on a computer that does not have the external object library installed, you will get a compile-time error that cannot be trapped using standard error-handling techniques — usually showing a perfectly valid line of code as being the culprit. Excel will display the error when it runs some code in a module, which contains:

□ An undeclared variable in a procedure — and you didn't use Option Explicit

□ A declaration of a type defined in the missing object library

□ A constant defined in the missing object library

□ A call to a routine, object, method, or property defined in the missing object library

The VBIDE References collection provides a method of checking that all the application's references are functioning correctly, and that all the required external object libraries are installed and are the correct versions. The code to check this should be put in your Auto_Open (or Workbook_Open) routine, and the module that contains the Auto_Open must not contain any code that uses the external object libraries. If there is a broken reference, it is unlikely that any other code will run, so the routine simply stops after displaying which references are missing. Typical Auto_Open code is:

Sub Auto_Open()

Dim oRef As Object, bBroken As Boolean, sDescn As String

For Each oRef In ThisWorkbook.VBProject.References 'Is the link broken? If oRef.IsBroken Then

'Some broken links don't have descriptions, so ignore the error

On Error Resume Next sDescn = "<Not known>"

sDescn = oRef.Description

On Error GoTo 0

'Display a message, asking the user to install the missing item MsgBox "Missing reference to:" & vbCrLf & _ " Name: " & sDescn & vbCrLf & _ " Path: " & oRef.FullPath & vbCrLf & _ "Please reinstall this file."

bBroken = True End If Next

'If everything present and correct, carry on with the initializing code If Not bBroken Then

'...Continue to open End If End Sub

0 0

Post a comment