How to specify multiple optional arguments in a function without respecting the order they are given

Question

The below example the optional arguments must be given in order; hence ShowHeaders Must Precede ValueAdd and so on. If I want to specify ValueAdd, I*must* specify ShowHeaders:

Function Example(Value1, Optional ShowHeaders = "Headers=N", Optional ValueAdd = "Sprd=0")

I want to be able to specify one or more of a (relatively) large list of optional arguments: 1) but not in order, and 2) not necessarily all of them.

For 1) I was thinking, perhaps make a list of arguments generic, eg rather than the above do:

Function Example(Value1, Optional Arg1, Optional Arg2)

Then subsequently check if the leftmost section of Arg1 = "Headers=" or "Sprd=" and so on, and then do the same for Arg2. This is fine but doesn't seem terribly efficient and I'd be planning on creating UDFs with > 10 optional arguments. The above solution would also address 2) but I just don't feel its very good coding.

For 2) I know we can use

If IsMissing(Arg) Then

but this doesn't really address the order we specify functions.

Tried Example(Value1:="", ValueAdd:="Sprd=0")? Or Example(Value1,Arg2:="A2")? — PatricK
– PatricK, Commented Aug 26, 2013 at 23:09

Graham Anderson · Accepted Answer · 2013-08-27 19:27:38Z

6

You can use the := operator along with the name of the variable. This way you only need to send the optional values that are specific to that call. Using your above example you could use:

Call Example(Value1, ValueAdd := "Sprd=0")

And this way you don't have to enter anything about showheaders etc.

Edit:

I've modified your example to handle the missing arguments so that they can be used for maths, hope this helps.

Function Example(Value1, Optional ValueA, Optional ValueB)

    If IsMissing(ValueB) Then ValueB = 0
    If IsMissing(ValueA) Then ValueA = 0

    Example = (Value1 + ValueA) * ValueB)
    MsgBox (Example)

End Function

Sub TestExample()
    Call Example(2, ValueB:=1)
    Call Example(2, ValueB:=1, ValueA:=6)
End Sub

edited Aug 27, 2013 at 19:27

answered Aug 26, 2013 at 23:09

Graham Anderson

1,25910 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

W A Carnegie Over a year ago

Sorry, not sure I understand how this works within Function (largely due to my relative lack of knowledge of VBA. For instance, how could I employ this with the code: Function Example(Value1, Optional ValueA = "0", Optional ValueB = "1") Example = (Value1 + Value2) * Value3 End Function if I wanted to specify in Excel =Example(2, "ValueB=5") to return 10 (ie (2 + 0) * 5) thus omitting the optional argument ValueA, and recognizing that the argument I specified was for ValueB? Thanks kindly for your response.

Jeremy Caney · Accepted Answer · 2024-06-01 01:42:56Z

0

I have tried this in a recent macro, It is the manner in which such functions are called within VBA code. Your arguments that are optional can be skipped by the use of strategically placed commas (,) in the call.

Case "do not pass ValueA" ...  =Example(2,,5)
Case "do not pass ValueB" ...  =Example(2,7)

Since only two args are present, the function will recognize that ValueB is optional and will 'see' only Value1 and ValueA.

Case "do not pass ValueA or ValueB" ...  =Example(2)

See comment for "do not pass ValueB ... =Example(2,7)"

edited Jun 1, 2024 at 1:42

Jeremy Caney

7,807115 gold badges58 silver badges86 bronze badges

answered May 23, 2024 at 23:31

RonBreen

111 bronze badge

Collectives™ on Stack Overflow

How to specify multiple optional arguments in a function without respecting the order they are given

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related