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.