Application.Run with Error Trapping

Question

In Access, using VBA, I want to use Application.Run and trap any errors. Unfortunately, Application.Run seems to hijack error trapping. Is there a way to fix this?

On Error Resume Next
Application.Run ...

I never get past Application.Run on an error, even if I specify On Error Resume Next or On Error GoTo ErrCatch. My error trapping setting is ignored.

That's because `Application.Run` is basically the same thing as an external call. What are you using it *for*? It's possible there's a work-around. — Comintern, Feb 08 '17 at 21:55
If it is a relatively small list of subs which might be run, you could use a select-case construction where the various cases call the subs directly — John Coleman, Feb 08 '17 at 22:02
Thank you for your responses. It needs to be more flexible than a select-case construction. — someprogrammer, Feb 08 '17 at 22:27

ThunderFrame · Answer 1 · 2017-02-08T22:43:27.447

If the procedure you're calling is inside your VBA project, then you can just call the procedure directly with:

Sub Foo()
   'Application.Run "SomeProc"
   SomeProc
End Sub

If you need to be able to call things dynamically by name, you could explore using classes and CallByName:

'In a standard module
Sub Foo()
   Dim o as New ProcRunner
   CallByName o, "SomeProc", VbMethod, args
End Sub

'In a class module called ProcRunner
Sub SomeProc()
   DoSomethingHere
   'Or, do something in a standard module
   Module1.SomeOtherProc
End Sub

Or, you could write your own dynamic handler, along the lines of:

Sub AppRun(ProcName As String, ParamArray Args)
  Select Case ProcName
    Case "SomeProc"
        SomeProc
    Case "SomeFunc"
        SomeFunc
  End Select
End Sub

If you're calling procedures in another VBA project, you may need to add a reference to that project, depending upon the VBA host.

However, if you're using Application.Run because you're calling functions registered by a DLL or XLL, then you don't have any option other than to use Application.Run

The CallByName looks interesting, but I don't have ProcRunner available as a Type. Would you have any idea what the Reference for that is? — someprogrammer, Feb 08 '17 at 22:29
@someprogrammer You'd have to add a class of your own, called `ProcRunner`, an example of which I've added to my answer. — ThunderFrame, Feb 08 '17 at 22:36

score 0 · Answer 2 · answered Jan 11 '19 at 18:25

There is a way to do this. We have a test harness in VBA and need a way of testing whether a procedure throws an error or not, giving a true/false result. We want True to indicate that an error was thrown, and False to indicate that no error was thrown. This only works for public procedures, but you can pass in a variable amount of arguments.

mIsErrorThrownDuringRunProcedure allows you to pass in a proc name and a varags list of arguments. It creates a new module, then writes another procedure to a the new module, calls that other procedure, and returns the result. The other procedure checks whether running the proc with the given args had any errors. When the dynamically created procedure is finished running, the new module is deleted.

An auxiliary function called mCreateCodeToExecute creates the code that is run from the new module to actually get the true/false result.

Public Function mIsErrorThrownDuringRunProcedure(pProcName As String, ParamArray pArgs() As Variant) As Boolean
    Dim lVbComp As Object
    Set lVbComp = ThisWorkbook.VBProject.VBComponents.Add(1)
    Dim lProcNameToExecute As String
    lProcNameToExecute = "mIsErrroRunDuringProcedure" & pProcName
    Dim lCodeToExecute As String
    Dim lNumArgs As Integer: lNumArgs = 0
    Dim lArg As Variant
    For Each lArg In pArgs
        lNumArgs = lNumArgs + 1
    Next
    lCodeToExecute = mCreateCodeToExecute(pProcName, lProcNameToExecute, lNumArgs)
    lVbComp.CodeModule.AddFromString lCodeToExecute
    mIsErrorThrownDuringRunProcedure = Application.Run(lProcNameToExecute, pArgs)
    ThisWorkbook.VBProject.VBComponents.Remove lVbComp
End Function

Private Function mCreateCodeToExecute(pProcName As String, lProcNameToExecute As String, numArgs As Integer)
    Dim lCodeToExecute As String
    lCodeToExecute = "Function " & lProcNameToExecute & "("
    lCodeToExecute = lCodeToExecute & "ParamArray pArgs() As Variant) As Boolean" & vbCrLf
    Dim lGoToLabel As String: lGoToLabel = "gtCodeHadError"
    lCodeToExecute = lCodeToExecute & "  On Error GoTo " & lGoToLabel & vbCrLf
    lCodeToExecute = lCodeToExecute & "  Call " & pProcName & "("
    Dim lIndex As Integer
    lIndex = 0
    For lIndex = 0 To numArgs - 1
        lCodeToExecute = lCodeToExecute & "pArgs(" & lIndex & "), "
        lIndex = lIndex + 1
    Next
    Dim lCutOff As Integer: lCutOff = 2
    If lIndex = 0 Then lCutOff = 1
    lCodeToExecute = left(lCodeToExecute, Len(lCodeToExecute) - lCutOff)
    If lCutOff = 2 Then lCodeToExecute = lCodeToExecute & ")"
    lCodeToExecute = lCodeToExecute & vbCrLf & "  " & lProcNameToExecute & "= False" & vbCrLf & "  Exit Function"
    lCodeToExecute = lCodeToExecute & vbCrLf & lGoToLabel & ":" & vbCrLf
    lCodeToExecute = lCodeToExecute & "  " & lProcNameToExecute & "= True"
    lCodeToExecute = lCodeToExecute & vbCrLf & "End Function"
    mCreateCodeToExecute = lCodeToExecute
End Function

References

How to run a string as a command in VBA

Please leave a comment if this code doesn't work. I don't know if there are any dependencies to anything else that I've left out. You must trust access to the VBA object module for this to work. See link in References. — Colm Bhandal, Jan 11 '19 at 18:29

Application.Run with Error Trapping

2 Answers2

References