Excel VBA与用户定义类型的错误处理

Question

这是一个以编程方式安装API类型库的示例子。为什么错误处理例程会失败？我试图遵循我熟悉的try...except...finally策略。

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

编辑

我从main中删除了这个部分，因为错误处理在VBA中是很荒谬的.对我来说，一个更好的方法是使用Booleans实现一个finite-state-machine。

答案

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

错误处理只处理运行时错误，而不处理编译时错误。使用

Dim App as Object

并确保代码中只包含一次Dim App。

通过使用As Object，可以延迟将任何对象绑定到它。当你编码思想的时候，你失去了智能感知。

Answer 2

正如迪克提到的，使用后期绑定，但这是不够的。您将不得不使用它与正确的错误处理。

例如

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
'

如果您确信它已经安装，那么您将得到错误，因为没有引用相关的库。为此，我建议您看看How to add a reference programmatically

然而，我仍然建议你采取后期绑定的路线。