vba 如何在函数中指定多个可选参数而不考虑它们的顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18454165/
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
How to specify multiple optional arguments in a function without respecting the order they are given
提问by W A Carnegie
The below example the optional arguments must be given in order; hence ShowHeaders Must Precede ValueAdd and so on. If I want to specify ValueAdd, I*must* specify ShowHeaders:
下面的示例必须按顺序给出可选参数；因此 ShowHeaders 必须先于 ValueAdd 等等。如果我想指定 ValueAdd，我*必须*指定 ShowHeaders：
Function Example(Value1, Optional ShowHeaders = "Headers=N", Optional ValueAdd = "Sprd=0")
I want to be able to specify one or more of a (relatively) large list of optional arguments: 1) but not in order, and 2) not necessarily all of them.
我希望能够指定一个或多个（相对）大的可选参数列表：1）但不按顺序，以及 2）不一定是全部。
For 1) I was thinking, perhaps make a list of arguments generic, eg rather than the above do:
对于 1) 我在想，也许可以列出一个通用的参数列表，例如而不是上面做的：
Function Example(Value1, Optional Arg1, Optional Arg2)
Then subsequently check if the leftmost section of Arg1 = "Headers=" or "Sprd=" and so on, and then do the same for Arg2. This is finebut doesn't seem terribly efficient and I'd be planning on creating UDFs with > 10 optional arguments. The above solution would also address 2) but I just don't feel its very good coding.
然后随后检查 Arg1 的最左边部分是否 = "Headers=" 或 "Sprd=" 等，然后对 Arg2 执行相同操作。这很好，但似乎效率不高，我计划创建具有 > 10 个可选参数的 UDF。上述解决方案也将解决 2) 但我只是觉得它的编码不是很好。
For 2) I know we can use
对于 2) 我知道我们可以使用
If IsMissing(Arg) Then
but this doesn't really address the order we specify functions.
但这并没有真正解决我们指定函数的顺序。
采纳答案by Graham Anderson
You can use the := operator along with the name of the variable. This way you only need to send the optional values that are specific to that call. Using your above example you could use:
您可以将 := 运算符与变量名称一起使用。这样您只需要发送特定于该调用的可选值。使用上面的示例，您可以使用：
Call Example(Value1, ValueAdd := "Sprd=0")
And this way you don't have to enter anything about showheaders etc.
这样你就不必输入任何关于节目头等的信息。
Edit:
编辑：
I've modified your example to handle the missing arguments so that they can be used for maths, hope this helps.
我已经修改了你的例子来处理缺失的参数，以便它们可以用于数学，希望这会有所帮助。
Function Example(Value1, Optional ValueA, Optional ValueB)
If IsMissing(ValueB) Then ValueB = 0
If IsMissing(ValueA) Then ValueA = 0
Example = (Value1 + ValueA) * ValueB)
MsgBox (Example)
End Function
Sub TestExample()
Call Example(2, ValueB:=1)
Call Example(2, ValueB:=1, ValueA:=6)
End Sub
