VBA Calling/Application.run with dynamic inputs + error handling

[Patrickll]

Hi, the following code contain a summary of the important elements with my issue. Before looking at it, here is the situation:

The main macro called MacroZero has to be a fulltime running macro. Thus errors must not prevent him from running. An error handler is added to the main macro (MacroZero) to deal with errors. Within MacroZero, there is a call procedure and there are 2 different ways that I know of to call macros:

1- Using Call
2- Using Application.Run

Here are my 2 constraints:

Application.run resets my error handler. The MacroZero error handler is needed, and writting an error handling line in any of the called macros is not an option.

Using Call however does not reset my error handling process. BUT, The name of my macros to call is also dynamic. Call does not seem to allow me to run Call *Variable containing the dynamic macro name*. While this is not possible with Call, I can run it just fine with Application.run.

Both options contradicts each other. Is there an inbetween that would allow me to run both at the same time?

Thank you very much!


'Main Macro
Sub MacroZero()


On Error GoTo ErrHandler


'Define MacroOneValue in cells(1,1)
MacroOneValue = Cells(1, 1).Value


'Start Important Code here ...


'Using the Call function to call the macro named: MacroOne
Call MacroOneValue


'OR


'Using the Application.run function to call the macro named: MacroOne
Application.Run MacroOneValue


'End Important Code here ...


Exit Sub


ErrHandler:
'...


End Sub

[mikerickson]

Something like this is one work-around.

Select Case Cells(1, 1).Value
Case "Macro1"
Call Macro1
Case "Macro2"
Call Macro2
Case "Macro3"
Call Macro3
End Select

Another option might be to
Bullet-proof all the macros being called by writing in their own error handling.
and also re-instate the Error Handler after invoking Application.Run


On Error Goto HandleError

' code

Application.Run "Macro1"
On Error Goto HandleError

[Patrickll]

Hi, thanks for the quick reply.

For the second suggestion, that is what I want to avoid. I have a main Excel file running 24/7 and launching many different individual files. Those individual files all contain the 1 Main Macro (Called MacroZero in the example above) and they have their own procedures (1-2-3-4.5...). The idea is not not go in each of the procedures one by one to add on error resume next. Not because of current concerns, but the day that someone adds a module, let's say 6 and forgets to add on error resume next, the Main Macro will bug out and stopping the 24/7 process at the same time. Which is not an option.

As for the 1st suggestion, bulding a select case would mean having to go in the Main MacroZero code manually and add each macro name. At that point, using Call with the macro names would be the exact same. However this could be an option if the following is possible:

Is it possible to add this to my MacroZero:

'Start of Select case process

Select Case process here....

'End of Select case process

So that when my MacroZero starts running, it picks up the macro names to run in the excel worksheet and saves the list as an array temporarily. Then the macro goes and search within its own procedure for the 'Start of select process and 'End of select process, delete the code within those lines, creates and pastes the new version containing the Select case + the macro names stored in the array? In other words, it searches within its code for specific lines delete and replace them with the new info. The when the code runs that procedure, the Call process would work, including my global error trapping? This might sound complex, but if it's possible, it is a step forward.

[mikerickson]

It sounds like you have problem.

On Error Resume Next is not the solution. Normally, one wants unhandled errors to stop everything, because after they happen you are getting garbage results. If you can't stop, at least there should be handling to report the error to a common Report workbook.

Your environment is ultra-hazerdous, in that you are allowing people to add code to a "Must Be 24/7 reliable" situation and, yet can't trust those people to insure that they are writing bullet-proof code (i.e. code that won't crash).

Could I suggest that you restrict the number of people who are allowed to add/modify code.
AND that you require that all added macros be vetted by your "checking program" Which would read their macro, make sure that the first line is some kind of error handler and no line "On Error Goto 0" is in the code.

I'd suggest that you require that all added macros use this format



Sub NewSub()
On Error Goto ErrorReporter

' code that does not include the line On Error Goto 0

Exit Sub
ErrorReporter:
With Workbooks("Reporting").Sheets(1).Range("A65536").End(xlup).Offset(1,0)
.Cells(1,1).Value = ThisWorkbook.Name
.Cells(1,2).Value = ActiveSheet.Name
.Cells(1,3).Value = Now
.Cells(1,4).Value = Err
.Cells(1,5).Value = Error
End With
Err.Clear
Resume Next
End Sub

Then you could write an Sub that will check any (proposed) added macros for that format i.e. the first line must be On Error Goto ErrorReporting, the last bit of code must be the ErrorReporting routine and that no line "On Error Goto 0" be in the sub.

Only allow users who pass their sub through your checking routine to modify the VBA in your meta-project.

[Patrickll]

Hi,

thanks for the reply and the idea. The error resume next process here is not to correct the error, but simply to not crash the main macro. An error in the process will lead to a fail execution process and it will be handled as such. There is already a handler to produce a complete report of the fails. But by running Application.run it disables it, which is the other side of my problem.

As for the code added, it is not on the 24/7 file. Nobody is touching that file, however in time files are added to its duty list. Those files are managed individualy by other people.

I don't dislike your idea of adding a macro checker though. As a matter of fact, when the 24/7 file opens up a sub file and launches the macro: MacroZero, its first task could be to scan the new macros for those lines you spoke of. Now to think of it, I like that idea. Do you have any templates/sites/resource to point me to? I would definitely like to analyse one of those macros.

Thanks

[mikerickson]

You could use something like this


Sub test()
Dim i As Long, ProcEnd As Long, j As Long
Dim strReport As String
Dim CurrentProc As String
With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
For i = 1 To .CountOfLines
If .ProcOfLine(i, vbext_pk_Proc) <> CurrentProc Then
CurrentProc = .ProcOfLine(i, vbext_pk_Proc)
ProcEnd = i + .ProcBodyLine(CurrentProc, vbext_pk_Proc)
strReport = CurrentProc & " lines: " & i & " to " & ProcEnd
j = i
If .Find("On Error", j, 1, i + .ProcBodyLine(CurrentProc, vbext_pk_Proc), 0) Then
j = i
If .Find("On Error Goto 0", j, 1, i + .ProcBodyLine(CurrentProc, vbext_pk_Proc), 0) Then
strReport = strReport & vbCr & """On Error Goto 0"" on " & j
Else
strReport = strReport & vbCr & "Error handling, but no bad line"
End If
Else
strReport = strReport & vbCr & "contains no error handling"
End If
MsgBox strReport
End If
Next i
End With
End Sub
Note that the ThisWorkbook and the name of the code module can be controlled.
Note also that the .Find function of a .CodeModule object changes the arguments, so they need to be reset immediately before the .Find is exicuted. (Hence the use of the variable j in the above)
The Visual Basic For Application Extensibiltiy library should be open when writing or running code like this.