blocks|key|2963204|text|VBA是凌乱的。我不确定有没有一种简单的方法来做假重载：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2963205|在过去，我要么使用了很多可选的，要么使用了不同的函数。例如|2963206|Foo_DescriptiveName1()

Foo_DescriptiveName2()|code-block|syntax|javascript|2963207|我建议使用具有合理默认值的可选参数，除非参数列表将变得愚蠢，然后创建单独的函数来调用您的案例。|2963208|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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 

<pre><code>Foo_DescriptiveName1()

Foo_DescriptiveName2()
</code></pre>

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.

blocks|key|3808570|text|您可能还希望考虑对参数列表使用变体数据类型，然后使用TypeOf语句找出什么是什么类型，然后在找出什么是什么时调用适当的函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3808571|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|5218832|text|将参数声明为Optional+Variants，然后可以使用IsMissing()测试它们是否缺失，或者使用TypeName()检查它们的类型，如下面的示例所示：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|5218833|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+%2B+1
++++End+If

End+Function|code-block|syntax|javascript|5218834|可以在工作表中以=FOO(number)，或=FOO("string").的形式调用=FOO()|BOLD|5218835|entityMap^0|6|H|U|B|1I|A|0|0|8|C|M|E|16|6|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]|$9|S|A|T|B|C]|$9|U|A|V|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|W|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|X|8|@$9|Y|A|Z|B|M]|$9|10|A|11|B|M]|$9|12|A|13|B|M]]|D|@]|E|$]]|$1|N|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|O|$]]

Declare your arguments as <code>Optional Variants</code>, then you can test to see if they're missing using <code>IsMissing()</code> or check their type using <code>TypeName()</code>, as shown in the following example:

<pre><code>Public Function Foo(Optional v As Variant) As Variant

 If IsMissing(v) Then
 Foo = "Missing argument"
 ElseIf TypeName(v) = "String" Then
 Foo = v &amp; " plus one"
 Else
 Foo = v + 1
 End If

End Function
</code></pre>

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

blocks|key|2963368|text|如果您可以通过参数计数来区分，那么下面这样的代码将会起作用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2963369|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|code-block|syntax|javascript|2963370|如果区分函数的唯一方法是通过类型，那么您实际上将不得不做C%2B%2B和其他具有覆盖函数的语言所做的事情，即通过签名调用。我建议让调用看起来像这样：|2963371|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|2963372|并创建一个具有多个方法的类，这些方法与您期望的签名匹配。不过，您可能需要一些错误处理，并且要注意可识别的类型是有限的:例如，日期是TypeName+Double。|2963373|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

If you can distinguish by parameter count, then something like this would work:

<pre><code>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 " &amp; arg
End Function

Private Function Two_Param_Morph(arg0, arg1)
 Two_Param_Morph = "I is in 2-params and they is " &amp; arg0 &amp; "," &amp; arg1
End Function
</code></pre>

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:

<pre><code>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 &amp; TypeName(args(idx))
 Next

 Set MorphInstance = New MorphClass

 MorphBySig = CallByName(MorphInstance, "Morph_" &amp; sig, VbMethod, args)

End Function
</code></pre>

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.

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?

Function Overloading and UDF in Excel VBA

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我正在使用Excel VBA来编写一个UDF。我想用几个不同的版本重载我自己的UDF，这样不同的参数将调用不同的函数。由于VBA似乎不支持这一点，有没有人能建议一个好的，不混乱的方法来实现同样的目标呢？我应该使用可选参数，还是有更好的方法？

问Excel VBA中的函数重载和UDF
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel VBA中的函数重载和UDFEN