15

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

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
Sebastian
  • 151
  • 1
  • 2
  • 6

4 Answers4

18

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.

GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Thanks, I'll try it. I actually do not hate the IDE, I just feel a big usability gap between this IDE and for example netbeans. And the lack of usability, in my opinion, results in less efficiency. – Sebastian Feb 15 '10 at 11:45
  • That is because target audience for VBA IDE is managers and other people that are believed to be smart but might not be programmers. Hence the message boxes on syntax errors and lack of advanced features. Compare to VS.NET IDE that is a definite overkill for a manager. – GSerg Feb 15 '10 at 11:52
  • Ok, that's a good point. Didn't think about that => the typical programmer's view.... ;) VS.NET IDE is a really nice piece of software, but I agree: it would be too much for them. – Sebastian Feb 15 '10 at 12:02
  • Thanks for the solution. I like the idea of passing an array into a variant because it can easily be checked with 'If IsArray(somevar) Then' and off we go. Also tossing my two cents in on the IDE: the VBA IDE is 99% identical to that in our good old friend Visual Studio 6. Because, you see...VB6 never died. It's lived on for the past 10 years in its descendant, VBA -albeit much smaller and non-compiled. It kept going all the way through Office 2010, 2013, and 2016...until the cloud-based Office 365 crap started. – spinjector Jan 08 '18 at 16:26
6

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

aetonsi
  • 208
  • 2
  • 9
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
3

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.

sdanse
  • 151
  • 4
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.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • When a data stream (i.e. chart points) comes as an array, it doesn't make sence to use ParamArray. It's a syntactic sugar for human beings that want to list their values in code as literals and don't want to declare another array variable for that. – GSerg Feb 15 '10 at 11:42