Function Overloading and UDF in Excel VBA

Question

I'm using Excel VBA to a write a UDF. I would like to overload my own UDF with a couple of different versions so that different arguments will call different functions.

As VBA doesn't seem to support this, could anyone suggest a good, non-messy way of achieving the same goal? Should I be using Optional arguments or is there a better way?

Answer 1

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").