VBA user-defined function not run because of invalid argument input

0
votes

I have an Excel 2016 VBA UDF (user-defined function) that looks this;

Public Function AddUDF(Param1 As Single, Param2 As Single)
    'Code implementation
End Function

Contents in Cell A1, A2 are inputs to the arguments Param1, Param2.

When cells A1 and A2 contains numbers, the UDF runs normally as expected. When one of the cells contains a string such as "N.A", this UDF is not run and the value #VALUE! is returned. I know the UDF is not run because I put a breakpoint into the UDF and the breakpoint is not reached at all.

How do I get the UDF to run?

1
vbaexceluser-defined-functionsexcel-2016
Change you params to variants. - Scott Craner
@ Scott Craner, that's the answer! Please put it down as answer and I will mark it as the answer. You deserve the extra points! - user3848207
It depends what you're doing with the arguments. If you're performing a sum on them you'll always get an error if they're not numbers. - Darren Bartrup-Cook
If your inputs will always be cell references, then change input type to Range and handle Range.Value exceptions accordingly, but if you would use it like =AddUDF($A$1, 16.5) then you'll need Variant params. - David Zemens
Yep, go with Scotts answer then. You may want to look at Chip Pearsons site on returning errors from UDFs - cpearson.com/excel/ReturningErrors.aspx - Darren Bartrup-Cook

1 Answers

3
votes

With your Parameter set to Single it is telling Excel not accept anything but numbers.

Change the Parameters to Variant then Test to ensure the type you want:

Public Function AddUDF(Param1 As Variant, Param2 As Variant)
    If Not (IsNumeric(Param1) And IsNumeric(Param2)) Then
        AddUDF = "not numbers"
        Exit Function
    End If
    'the rest of your code.
End Function