Function Overloading and UDF in Excel VBA Function Overloading and UDF in Excel VBA vba vba

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 IfEnd Function

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


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 SelectEnd FunctionPrivate Function Morph_NoParams()    Morph_NoParams = "I'm parameterless"End FunctionPrivate Function Morph_One_Param(arg)    Morph_One_Param = "I has a parameter, it's " & argEnd FunctionPrivate Function Two_Param_Morph(arg0, arg1)    Two_Param_Morph = "I is in 2-params and they is " & arg0 & "," & arg1End Function

If the only way to distinguish the function is by types, then you're effectively going 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 StringDim idx As LongDim 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 a 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 recognizable are limited: dates are TypeName Double, for example.


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.