CODEWITHSUNDEEP
vbaexceluser-defined-functionsexcel-2016
user3848207
user3848207

Reputation: 4987

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

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?

Upvotes: 0

Views: 1080

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

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

Upvotes: 3

Related Questions