default - 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 won't compile).

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.