How can I use an optional array argument in a VBA

Question

I've got a private procedure in a VBA script for MS Access:

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.

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

Answer 1

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

When passing the argument initially, if you want to pass an array, then just make sure that that is a Variant array.

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

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.

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.

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.

Answer 2

If you need an optional array in VBA, declare it as Variant without 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:

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.

If you hate the IDE, do not use it. Use notepad. Then paste written code.

Answer 3

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.

Answer 4

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.

-- How to: Overload a Procedure that Takes an Indefinite Number of Parameters (Visual Basic)

Reference for VBA: Understanding parameter arrays