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