5

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.

W A Carnegie
  • 2,195
  • 2
  • 14
  • 8

1 Answers1

3

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
Graham Anderson
  • 1,209
  • 10
  • 17
  • 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. – W A Carnegie Aug 27 '13 at 08:15