CODEWITHSUNDEEP
excelvba
Robert Lewkovich
Robert Lewkovich

Reputation: 223

Compile error during workbook open

I have a workbook which uses a certain type, let's call it T, that's in a module (DLL) defined in my References -- all good.

I created some code that's called in Workbook_Open() that will add a reference to the DLL if it's not already in the list of references. This is so I can give the workbook to someone and they won't have to deal with creating the reference by hand.

My problem is that when I open the workbook (double-click), before Workbook_Open() gets executed (and the Refence can be set) I get thrown into the debugger which points to and complains that type T, defined in the not-yet-referenced DLL, is not defined. Well no kidding it's not.

This seems a little chick and egg. Anyone seen this before? How did you fix it?

Upvotes: 3

Views: 2687

Answers (1)

mischab1
mischab1

Reputation: 1601

Like other people have said, if you are not going to add the reference to the DLL until Workbook_Open, you have to use late binding to connect to the stuff in the DLL.

However, I think you are going about this the wrong way. Instead of adding a reference to the DLL in Workbook_Open, have it already referenced and check .IsBroken instead. Then you can have code to do what ever is needed to fix the broken reference.

This keeps the benefit of early binding and you won't get the same kind of compile errors if the reference to the DLL is broken.

NOTE: You can still get compile errors, but they won't be for the missing DLL. While you have a broken reference, any use of functions from the VBA Strings module will trigger a compile error if the function isn't prefaced with Strings. For example, the following code will cause a compiler error before Workbook_Open runs if you have any broken references.

Private Sub Workbook_Open()
   Dim r As Reference
   For Each r In ThisWorkbook.VBProject.References
      If r.IsBroken Then
         MsgBox "Found broken reference." & vbCrLf _
              & Mid(r.FullPath, InStrRev(r.FullPath, "\") + 1)
      End If
   Next r
End Sub

To fix, prefix Mid and InStrRev with Strings.. 

MsgBox "Found broken reference." & vbCrLf _
     & Strings.Mid(r.FullPath, Strings.InStrRev(r.FullPath, "\") + 1)

Upvotes: 1

Related Questions