Excel VBA Custom Function Error Handling - only display error message when editing cell

Question

I have a custom function which I've written in VBA.

At the start of the code, the function checks that the input data is in the right format and displays an error message if not.

The problem I have is that if I break the input data (e.g. put some text in a cell which the function requires to be numeric), then every cell which contains my function and references that data will display an error message. So I get loads of error messages all at once.

I'd like to be able to change my function so it only displays the error message if the user is actively editing cell (similar to usual excel function error messages).

I thought I'd be able to use something like Worksheet_Change but I don't see how I could embed that in my function.

Another option would be to only allow 1 error message at a time but no idea how to set that up either.

Any ideas would be appreciated!

Here's the start of my code with the first input data check and error message:

Public Function OnLevelFactor(effective_dates As Range, rate_changes As Range, OnLevelYear As 
Range) As Double

'do they only contain numbers (/dates)?
If (WorksheetFunction.Count(effective_dates) + WorksheetFunction.CountBlank(effective_dates)) <> effective_dates.Count() Then
    Msg = "Dates " & effective_dates.Address & " must be numeric."
    MsgBox Msg, , "Error: OnLevelFactor", Err.HelpFile, Err.HelpContext
    Exit Function
End If

'rest of code

End Function

EDIT

I found this code which works pretty well:

Public Function OnLevelFactor(effective_dates As Range, rate_changes As Range, OnLevelYear As 
    Range) As Double

'Check if cell is being actively edited before triggering error-handling
Dim caller As Range
Set caller = Application.caller
MsgBoxTrigger = Not Intersect(caller, Application.ActiveCell) Is Nothing

And then each error check begins with "If MsgBoxTrigger Then". Any thoughts on this approach would be appreciated.

Don't use MsgBox in your UDF but return error text to the calling cell? — Tim Williams
– Tim Williams, Commented Oct 14, 2024 at 20:41

rotabor · Accepted Answer · 2024-10-14 21:02:39Z

2

The correct way of error handling in Excel user defined functions (UDF) is presented below:

Public Function OnLevelFactor(effective_dates As Range, rate_changes As Range, _
  OnLevelYear As Range) As Variant
  On Error GoTo UnknownErrorHandler
  If (WorksheetFunction.Count(effective_dates) + WorksheetFunction.CountBlank(effective_dates)) <> effective_dates.Count() Then
      OnLevelFactor = CVErr(xlErrValue) ' #VALUE!
      Exit Function
  End If

  'rest of code
  
ExitFunction:
  Exit Function
UnknownErrorHandler:
  OnLevelFactor = CVErr(xlErrUnknown)
  Resume ExitFunction
End Function

The function return type should be Variant. The error constants are defined in the XlCVError enum.

Following this way UDF works the same as standard Excel functions.

If you need to validate user input, you can use Excel's Data Validation feature or handle the Workbook_Change event.

edited Oct 14, 2024 at 21:02

answered Oct 14, 2024 at 20:52

rotabor

5,6112 gold badges5 silver badges23 bronze badges

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

2 Comments

Adam Dougall Over a year ago

Thank you, I'll try CVErr. I'd also like to be able to replicate Excel's "There's a problem with this formula." msgbox though

rotabor Over a year ago

"I'd also like to be able to replicate..." - don't waste your time, it's not possible to replicate. The only way here is the Data Validation feature.

MGonet · Accepted Answer · 2024-10-14 22:02:55Z

1

You can also send this message to a cell comment. You can choose a cell with a comment to read and don't have to enter information in MsgBox from which cell the message comes.

Public Function OnLevelFactor(effective_dates As Range, rate_changes As Range, OnLevelYear As Range) As Variant
With Application.ThisCell
    'do they only contain numbers (/dates)?
    If WorksheetFunction.Count(effective_dates) + WorksheetFunction.CountBlank(effective_dates) <> effective_dates.Count Then
    
        If Not .Comment Is Nothing Then .Comment.Delete
        .AddComment "Dates " & effective_dates.Address & " must be numeric."
        ' MsgBox Msg, , "Error: OnLevelFactor", Err.HelpFile, Err.HelpContext
        OnLevelFactor = CVErr(xlErrValue)
        Exit Function
    Else
        If Not .Comment Is Nothing Then .Comment.Delete
    End If
End With

'rest of code
    OnLevelFactor = 1
End Function

answered Oct 14, 2024 at 22:02

MGonet

2,6102 gold badges3 silver badges9 bronze badges

1 Comment

Tim Williams Over a year ago

Nice approach !

Collectives™ on Stack Overflow

Excel VBA Custom Function Error Handling - only display error message when editing cell

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related