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

This isn't a particularly good solution. Generally the use of SendKeys is discouraged and this solution only works if you have disabled your MoveAfterReturn property (i.e. unchecked in the image below)

Function test(rng As Range)
    'check condition
    If rng.Count <> 2 Then
        MsgBox "Incorrect..."
        SendKeys "{F2}", True
        Exit Function
    End If

    'code here to be run if parameters are valid
    test = "Success"
End Function