Excel VBA On Error handling with User-Defined Type

Question

this is an example sub to programatically install a type library for API. Why is the error handling routine failing? I attempted to follow the try...except...finally strategy I am familiar with from Python.

Sub CopyViewLayout():

'TRY:
On Error GoTo addReference
    Dim App As femap.model
    'COMPILE ERROR: USER TYPE NOT DEFINED

ResumeSub:
    Dim App As femap.model
    Set App = GetObject(, "femap.model")
    Dim rc As Variant
    Dim feView As femap.View
    Set feView = App.feView
    rc = feView.Get(0)

Exit Sub

'EXCEPT:
addReference:
    Dim vbaEditor As VBIDE.VBE
    Dim vbProj As VBIDE.VBProject
    Dim checkRef As VBIDE.Reference
    Dim filepath As String

    Set vbaEditor = Application.VBE
    Set vbProj = ActiveWorkbook.VBProject

    filepath = "C:\apps\FEMAPv11\"

    On Error GoTo Failure
    vbProj.References.AddFromFile (filepath & "femap.tlb")
    Set vbProj = Nothing
    Set vbaEditor = Nothing
    GoTo ResumeSub

'FINALLY
Failure:
    MsgBox ("couldn't find type library, exiting sub")

End Sub

EDIT

I broke out this section from main because Error handling is just ridiculous in VBA... A better approach for me was to implement a finite-state-machine using Booleans.

answer

Sub refcheck()
Dim i As Long
Dim FEMAP_GUID As String
FEMAP_GUID = "{08F336B3-E668-11D4-9441-001083FFF11C}"
With ActiveWorkbook.VBProject.references
    For i = 1 To .Count
        If .Item(i).GUID = FEMAP_GUID Then
            Exit For
        Else
            'note: filepath is determined using Dir() elsewhere...
            .AddFromFile (filepath & "femap.tlb")
            Exit For
        End If
    Next
End With
End Sub

Answer 1

Like Dick mentioned, use Late Binding but that alone is not enough. You will have to use it with proper Error Handling.

For example

Dim App As Object

On Error Resume Next
Set App = GetObject(, "femap.model")
On Error GoTo 0

If App Is Nothing Then
    MsgBox "Please check if femap is installed"
    Exit Sub
End If

'
'~~> Rest of the code
'

If you are sure that it is installed then you are getting the error because the relevant library is not referenced. For that I would recommend having a look at How to add a reference programmatically

I would however still suggest that you take the Late Binding route.

Answer 2

Error handling only handles runtime errors; not compile time errors. Use

Dim App as Object

And make sure you only Dim App once in your code.

By using As Object, you can late bind any object to it. You lose Intellisense while youre coding thought.