Function Overloading 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?

Thanks, Patrick

Answer 1

VBA is messy. I'm not sure there is an easy way to do fake overloads:

In the past I've either used lots of Optionals, or used varied functions. For instance

Foo_DescriptiveName1()

Foo_DescriptiveName2()

I'd say go with Optional arguments that have sensible defaults unless the argument list is going to get stupid, then create separate functions to call for your cases.

Answer 2

You mighta also want to consider using a variant data type for your arguments list and then figure out what's what type using the TypeOf statement, and then call the appropriate functions when you figure out what's what...

Answer 3

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

Answer 4

If you can distinguish by parameter count, then something like this would work:

Public Function Morph(ParamArray Args())

    Select Case UBound(Args)
    Case -1 '' nothing supplied
        Morph = Morph_NoParams()
    Case 0
        Morph = Morph_One_Param(Args(0))
    Case 1
        Morph = Two_Param_Morph(Args(0), Args(1))
    Case Else
        Morph = CVErr(xlErrRef)
    End Select

End Function

Private Function Morph_NoParams()
    Morph_NoParams = "I'm parameterless"
End Function

Private Function Morph_One_Param(arg)
    Morph_One_Param = "I has a parameter, it's " & arg
End Function

Private Function Two_Param_Morph(arg0, arg1)
    Two_Param_Morph = "I is in 2-params and they is " & arg0 & "," & arg1
End Function

If the only way to distinguish the function is by types, then you're effectively gonig to have to do what C++ and other languages with overridden functions do, which is to call by signature. I'd suggest making the call look something like this:

Public Function MorphBySig(ParamArray args())

Dim sig As String
Dim idx As Long
Dim MorphInstance As MorphClass

    For idx = LBound(args) To UBound(args)
        sig = sig & TypeName(args(idx))
    Next

    Set MorphInstance = New MorphClass

    MorphBySig = CallByName(MorphInstance, "Morph_" & sig, VbMethod, args)

End Function

and creating class with a number of methods that match the signatures you expect. You'll probably need some error-handling though, and be warned that the types that are recognisable are limited: dates are TypeName Double, for example.