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

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.

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

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.