How can I use an optional array argument in a VBA procedure?

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 doesn't run properly). The Visual Basic Editor/IDE is no help at all.

Thanks for help.

Answer 1

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 2

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 3

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: Define a Procedure with an Indefinite Number of Parameters