vba Excel VBA中的函数重载和UDF
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/64436/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Function Overloading and UDF in Excel VBA
提问by Patrick
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.
我正在使用 Excel VBA 编写 UDF。我想用几个不同的版本重载我自己的 UDF，以便不同的参数调用不同的函数。
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?
由于 VBA 似乎不支持这一点，有人可以提出一种实现相同目标的好方法吗？我应该使用可选参数还是有更好的方法？
回答by Joel Spolsky
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:
将您的参数声明为Optional Variants，然后您可以使用 来测试它们是否丢失IsMissing()或使用 来检查它们的类型TypeName()，如以下示例所示：
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").
这可以从工作表中调用为=FOO()、=FOO( number)或=FOO(" string")。
回答by Mike Woodhouse
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:
如果区分函数的唯一方法是通过类型，那么您实际上将不得不执行 C++ 和其他具有重写函数的语言所做的事情，即通过签名调用。我建议让电话看起来像这样：
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.
并创建一个具有许多与您期望的签名相匹配的方法的类。不过，您可能需要一些错误处理，并注意可识别的类型是有限的：例如，日期是 TypeName Double。
回答by theo
VBA is messy. I'm not sure there is an easy way to do fake overloads:
VBA 很乱。我不确定是否有一种简单的方法可以进行假重载：
In the past I've either used lots of Optionals, or used varied functions. For instance
在过去，我要么使用了很多 Optionals，要么使用了各种函数。例如
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.
我会说使用具有合理默认值的可选参数，除非参数列表会变得愚蠢，然后创建单独的函数来调用您的案例。
回答by Jon Fournier
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...
您可能还想考虑为参数列表使用变体数据类型，然后使用 TypeOf 语句确定什么是什么类型，然后在确定什么是什么时调用适当的函数......
