How to force an argument in an Excel UDF

Question

I've created an excel UDF that accepts an array input. I want it only to allow an even number of items in the array. Here's the code: (it's only short so I'll post it all and that way you can have some context)

Function SUBSTITUTEMULTI(inputString As String, ParamArray criteria() As Variant) as String

Dim subWhat As String
Dim forWhat As String
Dim x As Single


If UBound(criteria()) Mod 2 = 0 Then
'check whether an even number of arguments is input
MsgBox "You've entered too few arguments for this function", vbExclamation

Else
    x = 0
    For Each element In criteria
        If x = 0 Then
            subWhat = element
        Else
            forWhat = element
            inputString = WorksheetFunction.Substitute(inputString, subWhat, forWhat)
        End If
        x = 1 - x
    Next element
SUBSTITUTEMULTI = inputString
End If
End Function

Currently I return a message box that looks like Excel's own one that appears when you enter SUBSTITUTE() with the third argument missing. However when you do that with SUBSTITUTE() or any similar function, Excel prevents you from entering the formula, instead it clicks you back into it so you can fix the faulty function. I would like this, as otherwise my function can be pasted in its broken state (odd number of arguments) into several cells, meaning the message box appears several times when recalculating!

How can I fix the code so that if incorrect arguments are detected (an odd number of items in the array) ,then the user is automatically returned to the editing formula step?

Answer 1

Have the function return an error when an incorrect number of arguments are entered.

The return will have to be a Variant rather than String.

Substitute your msgbox with SUBSTITUTEMULTI=CVErr(xlErrValue).

A list of errors:

• xlErrDiv0 for a #DIV/0 error
• xlErrNA for a #N/A error
• xlErrName for a #NAME? error
• xlErrNull for a #NULL error
• xlErrNum for a #NUM error
• xlErrRef for a #REF error
• xlErrValue for a #VALUE error

(http://www.cpearson.com/excel/writingfunctionsinvba.aspx)