Error handling with VBA compiling?

Question

How easy would it be for someone with only decent programming experience to add error handling for compiling? I have an Excel-VBA program that on some computers fails to locate the references, so I made a common folder and placed the .dll files there. Is there a way I can automate this error handling process? It is a compile error for "cannot find library" so I imagine I cannot do this with VBA. Is this easily doable? Thoughts?

I did not attach code because there is nothing relevant to show. I get a compile error on some computers because there is a missing reference that I would like to have a program locate on error, rather than a human.

I don't know what compiler directives are lol...I found this. https://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically?rq=1 and it seems I can program it in so I am going to try that but I do not see how that would avoid a compile error. I guess we will see — someone serious, Jul 09 '18 at 16:00
If you use late binding you may be able to use run-time error handling. — Tim Williams, Jul 09 '18 at 16:02

score 3 · Accepted Answer · answered Jul 09 '18 at 16:15

Error handling refers to run-time errors. You can't handle compile-time errors with error handling, since executing the error handling requires compiling and running the code in the first place.

What you can do, is change how you're compiling your code. For example, this code requires a compile-time reference to MyLibrary:

Dim foo As MyLibrary.Something

This code doesn't:

Dim foo As Object

The difference is that member calls against foo are no longer resolved at compile-time - which means you can't get intellisense when coding against it; VBA will happily compile a typo and run-time will blow up with error 438 saying it can't find the specified member.

foo.DoSomething 'works if foo has a DoSomething member.
foo.IDontExist  'compiles but throws error 438 at run-time.

When your project has a reference to MyLibrary, you're early-binding - meaning code will refuse to compile if, say, the DLL can't be found.

When your project doesn't have a reference to MyLibrary, and your code doesn't include any identifier that's defined in that library (Option Explicit becomes utterly important then), you're late-binding - meaning the compiler will not care to validate the member calls, and the linking happens at run-time.

Converting your early-bound code to late-bound code will require extensive overhaul of every single declaration, replacing every single class with Object, then removing the references to the DLL's.

Then you can't do this anymore:

Set foo = New MyLibrary.Something

Instead, you use the class' registered ProgId (a GUID works too) to locate the DLL at run-time by hitting the Windows Registry entry for it, using the CreateObject function:

Set foo = CreateObject("MyLibrary.Something")

If the MyLibrary.Something ProgID isn't registered on the machine the code is running on, a run-time error will occur, that you can handle at run-time with classic On Error statements. The correct ProgID values to use depend on how the DLLs were registered; they may or may not match the "Library.TypeName" scheme.

I have a similar error that I'd like to handle. It pertains to one line, used twice in the whole project: `Dim dict As New Scripting.Dictionary`. Would converting this to late-binding require "extensive overhaul"? — Shawn V. Wilson, Oct 22 '19 at 16:12
@MathieuGuindon I'm sorry, are you telling me what I should do? If so, what are the steps? (My goal is to have a pop-up telling the user that he has to check the reference for "Microsoft Scripting Runtime") — Shawn V. Wilson, Oct 22 '19 at 16:57
@ShawnV.Wilson *My goal is to have a pop-up telling the user that he has to check the reference for "Microsoft Scripting Runtime"* ...the reference should be checked already. The missing reference will be causing compiler errors, you can't have a missing reference *and* hope to be able to run code to fix it. Sounds like you want late binding then. Consider encapsulating the late binding into its own class, so that you can early-bind the rest of the code instead of having `As Object` all over the place. — Mathieu Guindon, Oct 22 '19 at 16:59
Okay, so if the user doesn't have the reference checked, what should I change to do it for him, or how do I tell him how to do it himself? — Shawn V. Wilson, Oct 22 '19 at 17:04
You could have a separate host document with code that loads a given document, inspects its VBA project references, and attempts to reload them - it just can't be in the same project, because if the project has missing references you're asking for compile errors. I'm not going to write up a tutorial on this in a comment though. — Mathieu Guindon, Oct 22 '19 at 17:06

score 0 · Answer 2 · answered Jul 19 '19 at 02:18

You can trap some compile errors. In this example, I was able to trap whether the Microsoft Word 16.0 Object Library was missing (which was required later in the code), and instruct the user what to do.

In the Workbook_BeforeSave event, record the version of Office that last saved the Macro workbook; then, when it's opened, test whether the current version is older than the version it was saved under. Similar code could be used to test whether a specific .dll exists in the filesystem, and then instruct the user how to correct the problem. It's not a perfect solution, but better than nothing

This code goes in ThisWorkbook.:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   Me.Sheets("Macros").Activate
   Sheets("Macros").Shapes(1).TextFrame.Characters.Text = "Show Tools" & Chr(10) & "Application Version: " & Application.Version
End Sub

Sub Workbook_Open()
   Dim sAppVersion As String
   Application.EnableEvents = True
   Me.Windows(1).ActivateNext
   sAppVersion = Split(Split(Sheets("Macros").Shapes(1).TextFrame.Characters.Text, "Application Version: ")(1), vbCr)(0)
   If sAppVersion > Application.Version Then
       MsgBox ("You are running these Macros in an older version than it was created in.  If you get an error, end the macro (Run -> Reset) and update the References in the VBA." _
           & vbCr _
           & "To do this, press Alt+F11 and click Tools -> References....  Then uncheck the Missing Reference, scroll down and check " _
           & "Microsoft Word " & Application.Version & " Object Library")
   End If

End Sub

Error handling with VBA compiling?

2 Answers2