如何在 VBA 过程中使用可选的数组参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2265349/
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 can I use an optional array argument in a VBA procedure?
提问by Sebastian
I've got a private procedure in a VBA script for MS Access:
我在 MS Access 的 VBA 脚本中有一个私有过程：
Private Sub drawLineDiagram(chartSpace As Variant, title As String, caption As String, x_val() As Variant, y_val() As Variant, Optional y_val2() As Variant = ????)
Private Sub drawLineDiagram(chartSpace As Variant, title As String, caption As String, x_val() As Variant, y_val() As Variant, Optional y_val2() As Variant = ????)
As you see, I want to have an optional last parameter for an array of values.
如您所见，我想为一组值设置一个可选的最后一个参数。
What kind of default parameter must I assign? If I do it with an optional integer value and assign it e.g. 0 it's all fine.
我必须分配什么样的默认参数？如果我用一个可选的整数值来做它并分配它，例如 0，那就没问题了。
If I do it with an array as shown above and assign an array, the line is marked red => as an error (and it won't compile).
如果我使用如上所示的数组执行此操作并分配一个数组，则该行标记为红色 => 作为错误（并且不会编译）。
回答by GSerg
If you need an optional array in VBA, declare it as Variantwithout array specificator, but access it as an array anyway. This way you get a Variant(single variable) that holds an array of Variants, as opposed to just array of Variants. No default value is required:
如果您在 VBA 中需要一个可选数组，请将其声明为Variant没有数组说明符，但无论如何都将其作为数组访问。通过这种方式，您将获得一个Variant（单个变量），其中包含一个Variants数组，而不仅仅是Variants数组。不需要默认值：
Private Sub drawLineDiagram(chartSpace As Variant, title As String, caption As String, x_val As Variant, y_val As Variant, Optional y_val2 As Variant)
For consistency, also declare as plain Variants the other two parameters.
为了保持一致性，还将Variant其他两个参数声明为 plain s。
If you hate the IDE, do not use it. Use notepad. Then paste written code.
如果您讨厌 IDE，请不要使用它。使用记事本。然后粘贴写好的代码。
回答by Fionnuala
Perhaps you want a Parameter Array:
也许你想要一个参数数组：
In the procedure declaration, define the parameter list in the normal way. All parameters except the last one must be required (not Optional (Visual Basic)).
Precede the last parameter name with the keywords ByVal ParamArray. This parameter is automatically optional. Do not include the Optional keyword.
在过程声明中，按正常方式定义参数列表。除了最后一个参数之外的所有参数都必须是必需的（不是可选的 (Visual Basic)）。
在最后一个参数名称之前使用关键字 ByVal ParamArray。此参数是自动可选的。不要包含 Optional 关键字。
-- How to: Overload a Procedure that Takes an Indefinite Number of Parameters (Visual Basic)
--如何：重载采用不确定数量参数的过程 (Visual Basic)
Reference for VBA: Understanding parameter arrays
VBA 参考：了解参数数组
回答by sdanse
There is a simpler but not necessarily better answer to this question. Sebastian said, "If I do it with an array as shown above and assign an array, the line is marked red => as an error (and it won't compile)."
这个问题有一个更简单但不一定更好的答案。塞巴斯蒂安说，“如果我使用如上所示的数组执行此操作并分配一个数组，该行将标记为红色 => 作为错误（并且它不会编译）。”
Your code includes "Optional y_val2() As Variant = ????". You don't need the "()" there for it to take a Variant array as a parameter. So if you really want to do it that way, you can, for instance with something like "Optional y_val2 = FALSE".
您的代码包括“可选的 y_val2() As Variant = ????”。您不需要那里的“()”来将 Variant 数组作为参数。所以如果你真的想这样做，你可以，例如“可选的 y_val2 = FALSE”。
When passing the argument initially, if you want to pass an array, then just make sure that thatis a Variant array.
最初传递参数时，如果你想传递一个数组，那么只需确保它是一个 Variant 数组。
I do think that it's more elegant not to use a default there, so I agree with GSerg's answer in general (and upvoted both that and the original question).
我确实认为在那里不使用默认值更优雅，所以我总体上同意 GSerg 的回答（并且对这个问题和原始问题都投了赞成票）。
However to GSerg and spinjector, yes, you can check the optional parameter with "If IsArray(YourOptionalVariantParameter) Then", but if you're using a Variant, "IsMissing(YourOptionalVariantParameter)" is handy and elegant, may be a smidge faster, and can be used when (and only when) a Variant is passed as an argument, to check to see whether or not it exists.
然而对于GSerg和spinjector，是的，您可以使用“If IsArray(YourOptionalVariantParameter) Then”检查可选参数，但如果您使用的是变体，则“ IsMissing(YourOptionalVariantParameter)”既方便又优雅，可能会更快一点，并且可以在（且仅当）将 Variant 作为参数传递时使用，以检查它是否存在。
If you do "IsArray(YourOptionalVariantParameter)" and no such parameter exists, then all we're doing is checking whether a nonexistent variable is an array. If you use a default parameter value like FALSE (as in my first example), then it does make sense to first check whether the variable is an array or not.
如果你执行“IsArray(YourOptionalVariantParameter)”并且没有这样的参数存在，那么我们所做的就是检查一个不存在的变量是否是一个数组。如果您使用 FALSE 这样的默认参数值（如在我的第一个示例中），那么首先检查变量是否为数组是有意义的。
By the way, I don't agree that you need to declare all the parameters as Variants for consistency. Variants are less efficient than other types, and so should be used only when necessary, I think.
顺便说一句，我不同意您需要将所有参数声明为 Variants 以保持一致性。变体的效率低于其他类型，因此我认为应该仅在必要时使用。
回答by Patrick Honorez
The IDE might not be of great use, but the help (for once) contains the answer:
ParamArray
Optional. Used only as the last argument in arglist to indicate that the final argument is an Optional array of Variant elements. The ParamArray keyword allows you to provide an arbitrary number of arguments. ParamArray can't be used with ByVal, ByRef, or Optional.
IDE 可能没有多大用处，但帮助（一次）包含答案：
ParamArray
Optional。仅用作 arglist 中的最后一个参数，以指示最后一个参数是 Variant 元素的 Optional 数组。ParamArray 关键字允许您提供任意数量的参数。ParamArray 不能与 ByVal、ByRef 或 Optional 一起使用。
