How to find if any compile errors exists due to missing DLL reference?

Question

We have developed a program in Excel VBA.

Say for instance we are having a compiler problem in one of the functions (Debug->Compile VBAProject gives some type mismatch error).

Without clicking that option, is there any way after the program load, we can intimate the user by showing a message like "You have compiler error in your test program, please click Debug->Compile VBAProject and fix the compiler errors"?

Actual problem is, a program (Excel VBA) is having compiler error, but the user doesn't know it. He just started running the program which results in failure.
So after the program load, without manually clicking Debug->Compile, after the excel is launched, it should throw a pop-up saying "you are having compiler errors, Do Debug->Compile VBAProject and fix it".

The actual error is "Compiler error: User-defined type not defined".
As part of our software installation, we are installing certain DLLs in the customer machine. The customer can add those DLLs as reference and edit their program.
As the Excel program is having a compiler error, "OnProgramLoaded interpose" function is not called. So the code inside that function is not executed. The user has no clue that this code is not called.
Additional information: Earlier we have the "Compile On Demand" checkbox as checked. So even though there is a compiler error in the program, the "OnProgramLoaded interpose" function is getting called. Now for different reason, we have removed the check box from "Compile On Demand", so what happens now is, since there is compiler error, it is not executing the other functions too.

So what we are trying to do is, as soon as the program is launched, we need to give the message to user that the program is having compiler errors. Fix it and restart the program.

A type mismatch error is not a compile error, but a run-time error. Code that doesn't compile won't run. What would the user change in the program you developed that would cause a compile error? — BigBen, Jul 18 '19 at 06:09
The actual error is Compiler error: User-defined type not defined. As part of our software installation, we are installing certain dll in the customer machine. The customer can add those dll's as reference and edit thier program. — StackUser, Jul 18 '19 at 06:34
Please show the code that throws the compile error. We cannot help without seeing the code (see [ask]). — Pᴇʜ, Jul 18 '19 at 06:37
In the current case, the error is happening in below line, Private Function SelectBpmuFVRange(Voltage As Double) As BpmuVRange where BpmuVRange is not added in reference. — StackUser, Jul 18 '19 at 06:43
If the user click Debug->Compile and see the compiler error, he knows how to fix it. But now, because of these compiler error, OnProgramLoaded is not called and the user is not aware that this function is not called. So if we provide a message to him after program load, it will be easy for them to identify and fix the problem. — StackUser, Jul 18 '19 at 06:50
What you are trying to do is have code running in an environment where there are errors to say there are errors. If you think about it this could not work. It would be the equivalent of having a television which was not plugged in showing a programme telling the user they cannot watch a programme without plugging in the television. What you can do is properly trap the errors. And telling a non-technical person they have a "compiler error" is not going to make any sense to them. — , Jul 18 '19 at 07:13

score 0 · Answer 1 · answered Jul 18 '19 at 08:21

You can check, if your desired VBA references are already enabled:

Private Sub CheckVBAReferences()
    Dim dict As Object
    Dim oRef As Object
    Dim i As Long

    Set dict = CreateObject("Scripting.Dictionary")
    For Each oRef In ThisWorkbook.vbProject.References
        dict.Add oRef.Description & ", " & oRef.fullpath, oRef.GUID
        Debug.Print oRef.Description & ", " & oRef.fullpath
    Next oRef

    If Not dict.Exists("Microsoft Excel 16.0 Object Library, " & _
                    "C:\Program Files\Microsoft Office\Root\Office16\EXCEL.EXE") Then
        MsgBox "Please add a VBA reference to ..."
        ' or set it according to following link
    End If

    dict.RemoveAll
    Set dict = Nothing
End Sub

And then you may set a missing reference according to this answer.

How to find if any compile errors exists due to missing DLL reference?

1 Answers1