MsgBox for error handling in a VBA UDF called from cells in Excel?

Question

Is it possible to allow Excel users to stop MsgBox messages from VBA error handlers popping up and also re-allowing MsgBox messages? Or is it possible to programmatically control their frequency, for instance suppressing new MsgBox events for 1 minute after the last MsgBox event?

I created a UDF to be called from a cell in a worksheet like so in cell H12:

=CashFlowByPhaseWeek($A$4;H$5;$B12;"P7")

The end user would be populating the function call to a larger range (as we always do by dragging the lower right corner across multiple columns or rows and then the resulting column range or row range in the other direction).

The functioning of that UDF depends on several things on another worksheet in the same workbook. For instance, the other worksheet must contain certain Excel tables and the names of the worksheet and tables need to follow a certain naming convention. "P7" would contain tables "P7Phases", "P7UnitCost", etc.

As the end user is not Excel savvy, I thought it to be wise to create an error handler which would provide specific MsgBox hints to the end user for anticipated issues, for instance:

For a new project the end-user is supposed to copy the sheet with the tables, for instance copying "P6" and renaming the copy to "P7", renaming the tables in the sheet to "P7Phases" which Excel renamed by adding a suffix, for instance "P6Phases5", then marking the remaining group of sheet tabs belonging to "P6" and copying the group of tabs (which is possible because they don't contain tables), renaming these sheets to "P7...", and finally marking the entire tab group and replacing all occurrences of P6 by P7 looking into formulas. He would then enter some data for the new project, including change of table sizes. He could easily forget renaming some tables to adhere to the naming convention.

The MsgBox messaging would work well if we wouldn't be calling the UDF from multiple cells. But in fact, each cell containing the formula calling the UDF would trigger a separate call and each call would display the same error message. 5 or 10 minutes of constantly clicking OK would result before the user could fix the issue on sheet "P7".

I know I could solve the issue by dropping the UDF and letting all updates on the target sheet been done by a single sub-procedure call. That would have certain pros and cons. A pro would be that the sub-procedure could easily send message boxes as planned. It would also run faster all in all. But cons would be, that I would need to teach the end-user to use macros, or I would need to develop UI control(s) on the cash flow sheet which would trigger the sub-procedure(s), and the sub-procedure(s) would also need to take control of proper initialization of the sheet, for instance clear data cells which were earlier used and left over from the copying, but might not be overwritten in the new project.

Setting calculations to manual would prevent the individual triggers whilst you make a change. Are they manually changing all P6 to P7? If that is done via form/inputbox then that code could utilize application.calculation = xlManual. Once all changes are made, application.calculation = xlAutomatic. — Cyril
– Cyril, Commented Oct 25, 2024 at 20:16
Excel VBA Custom Function Error Handling - only display error message when editing cell — rotabor
– rotabor, Commented Oct 25, 2024 at 20:33

Tim Williams · Accepted Answer · 2024-10-25 23:15:13Z

2

Here's a couple of ways to limit MsgBox occurences

Limit the frequency to (eg) 1 per minute
Use cell comments to flag up errors and suggest fixes

Function CashFlowByPhaseWeek()   'parameters omitted for simplification...
    
    Const MSG_INTERVAL As Double = 1 / 1440 '1 minute
    Static lastmsg As Double
    Dim ws As Worksheet, c As Range
    
    Set c = Application.Caller 'the cell where the function is being called from
                               '(this will error if not called as a UDF)
    
    'check for required worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Test")
    On Error Goto 0
    
    If ws Is Nothing Then 'something missing?
        If Now - lastmsg > MSG_INTERVAL Then 'last message more than 1 minute ago?
            MsgBox "Missing worksheet 'Test'"
            lastmsg = Now 'set/reset timestamp
        End If
        
        'something else to consider?
        c.AddComment "Missing worksheet 'Test'" 'works from a UDF....
        
        CashFlowByPhaseWeek = "#Error"
        Exit Function
    End If
    
    'clear any "error" comment if present
    If Not c.Comment Is Nothing Then c.Comment.Delete
    
    CashFlowByPhaseWeek = "OK" 'do whatever...
    
End Function

edited Oct 25, 2024 at 23:15

answered Oct 25, 2024 at 21:22

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

taller Over a year ago

@TimWillams Do you think the 2nd On Error Resume Next should be replaced with On Error Goto 0?

Tim Williams Over a year ago

Yes - thanks @taller. Fixed above.

Collectives™ on Stack Overflow

MsgBox for error handling in a VBA UDF called from cells in Excel?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related