How to use an optional array argument in a VBA procedure?

Question

I have 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 can see, I want to have an optional last parameter for an array of values.

Which default parameter should be assigned? If I do this with an optional integer value and assign it, for example. 0 everything is fine.

If I do this using an array, as shown above, and assign an array, the line will be marked in red => as an error (and it will not compile).

Answer 1

If you need an additional array in VBA, declare it as Variant without an array specifier, but in any case, access it as an array. This way you get Variant (the only variable) that contains the Variant s array, not just the Variant s array. The default value is not 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 two other parameters as a simple Variant .

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

Answer 2

Perhaps you need an array of parameters:

In the procedure declaration, define a list of parameters as usual. All parameters except the last one (optional) (Visual Basic)).

The previous name of the last parameter is the ByVal ParamArray keywords. This parameter is automatically optional. Do not include the Optional keyword.

- How to define a procedure with an indefinite number of parameters

Answer 3

The IDE may not be useful, but the help (at a time) contains the answer:
ParamArray
Optional. Used only as the last argument in arglist to indicate that the last argument is an optional array of Variant elements. The ParamArray keyword allows you to specify an arbitrary number of arguments. ParamArray cannot be used with ByVal, ByRef, or optionally.