Force UDF in VBA to display a MsgBox when the user enters more than expected arguments?

Question

When the user enters too many arguments for the COUNTBLANK function,the function displays this error message, and returns to edit mode:

You've entered too many arguments for this function.

How to make any UDF work like that?

For example:

Function COUNT2 (c As Range)
    COUNT2 = c.Count
End Function

By default this UDF returns #VALUE! error when the user enters more than one argument.

How to make it work like the COUNTBLANK function?

Answer 1

We can only simulate the behavior to some extent, but it will never be exactly similar to the behavior of the built-in functions of Excel.

We can add a mechanism so that when the UDF detects some syntax error, it initiates some error data (msg to display, cell to activate) that the workbook will manipulate once calculation is over. Of course this should be displayed only once, not for each cell holding an error formula, because it would be extremely stressing for the user.

' Code Module ThisWorkbook
Option Explicit
Private errorCell As Range
Private errorMsg As String

Public Sub setErrorToshow(cel As Range, msg As String)
  If Not errorCell Is Nothing Then Exit Sub ' To display a msg only once, not per cell
  Set errorCell = cel
  errorMsg = msg
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
  Application.EnableEvents = False
  On Error GoTo Cleanup
  If Not errorCell Is Nothing Then
    Application.Goto errorCell
    MsgBox errorMsg
    SendKeys "{F2}"
  End If

Cleanup:
    Application.EnableEvents = True
    Set errorCell = Nothing
    errorMsg = ""
End Sub

The UDF checks the syntax and sets appropriate error info:

' Code module Module1
Option Explicit
Function COUNT2(ParamArray args())
  Application.Volatile
  If UBound(args) > 0 Then
    ThisWorkbook.setErrorToshow Application.caller, "too many args for function COUNT2"
  ElseIf Not TypeOf args(0) Is Range Then
    ThisWorkbook.setErrorToshow Application.caller, "wrong argument type for COUNT2"
  Else
    COUNT2 = args(0).count
  End If
End Function

---

To test this, try entering wrong formulas like =COUNT2(A1:A20, B1:B20) or =COUNT2(12345), the error message will show and the cell will be again in edit mode, unless you press ESCape. When there are many erroneous cells (copy the error cell and paste in many others), then you press F9, the message will appear once only and one of those cells will be in edit mode.