Function Overloading and UDF in Excel VBA

Declare your arguments as Optional Variants, then you can test to see if they’re missing using IsMissing() or check their type using TypeName(), as shown in the following example:

Public Function Foo(Optional v As Variant) As Variant

    If IsMissing(v) Then
        Foo = "Missing argument"
    ElseIf TypeName(v) = "String" Then
        Foo = v & " plus one"
    Else
        Foo = v + 1
    End If

End Function

This can be called from a worksheet as =FOO(), =FOO(number), or =FOO(“string“).