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?

Wouldn't using this function be more intuitive if you passed along a list of originals and a second list of substitutions? Then your function could check that the two ParamArrays were the same size; truncating the second if it was larger than the first and appending the second with the matching value from the original if it was shorter than the first. — , Feb 20 '17 at 15:16
@Jeeped Nice thought. I was trying to emulate the classic `SUBSTITUTE(...,oldText,newText)` formula, i.e. having alternating arguments rather than 2 arrays of arguments was actually what I _wanted_. Also I don't believe you can have 2 ParamArrays as they are undefined length, so how could you determine where one finished and the next started? But your general idea was one that I considered, when I have an odd number of arguments I simply duplicate the last argument in the array, so that it is ignored in the formula altogether - that's one way to deal with the errors, just not the one I'm after — Greedo, Feb 20 '17 at 15:28
Yeah, I should not have used hte term ***paramarray***. They would be simple arrays like `=SUBSTITUTEMULTI(A1, {"a","b", "c"}, {"x", "y", "z"})`. — , Feb 20 '17 at 15:30

score 3 · Answer 1 · edited May 08 '18 at 21:49

3

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)

edited May 08 '18 at 21:49

Community

1
1

answered Feb 20 '17 at 14:56

Darren Bartrup-Cook

18,362
1
23
45

Had to update - forgot to add `CVErr` to return. Only returns the number without it. – Darren Bartrup-Cook Feb 20 '17 at 15:03
nice concept :) – Shai Rado Feb 20 '17 at 15:06
2

This isn't really what OP is asking for. *Returning* an error value is different. For example `SUM(1/0)` returns an error value but `SUM(#)` won't complete. The latter behavior is what OP is after. – CallumDA Feb 20 '17 at 15:14
'tis true @CallumDA. It's an alternative method I guess. – Darren Bartrup-Cook Feb 20 '17 at 15:30

CallumDA · Accepted Answer · 2017-02-20T15:15:31.767

2

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

edited Feb 20 '17 at 15:15

answered Feb 20 '17 at 14:32

CallumDA

12,025
6
30
52

Or just `SendKeys "{up}"` rirst. Is the `True` argument necessary, I removed it and everything still works fine (apparently), what is it there for? – Greedo Mar 09 '17 at 17:10
@Greedo `True` forces Excel to wait for the key to be pressed before returning control to the macro. If you don't wait you might get some unexpected results. For example, if you were using `ActiveCell` in later code and you haven't waited for `SendKeys` to move you to another cell you'd end up with a reference to the wrong cell. *Note, use of `ActiveCell` isn't encouraged* – CallumDA Mar 09 '17 at 17:26
`SendKeys "Up"` would work too - good suggestion! (For those people with a down MoveAfterReturn) – CallumDA Mar 09 '17 at 17:27

How to force an argument in an Excel UDF

2 Answers2