Function Overloading and UDF in Excel VBA

问题:

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?

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

回答2:

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

回答3:

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.

回答4:

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