How can I pass optional arguments on to another function which has optional arguments

0
votes

I have a function called myfunction. it has 10 optional arguments. This calls another function called myotherfunction which has the same 10 optional arguments.

So let's say I call myfunction with only argument one. How do I call myotherfunction using only argument one when I don't know which of the 10 optional arguments have been passed?

I can't say ret_value=myotherfunction(arg1:=argumentone) because there could be between 1 and 10 arguments that I need to pass. If I used 2 arguments then it would have to be ret_value=myotherfunction(arg1:=argumentone, arg2:=argumenttwo) but again at run time I don't know which arguments have been passed to myfunction. Is there a way to parse a function call?

2
vbafunctionexcelargument-passing

2 Answers

2
votes

Just pass the arguments along the chain. As a very simplistic example:

Sub foo()
    Call bar("test")
End Sub
Sub bar(sOne As String, Optional sTwo)
    Call foobar(sOne, sTwo)
End Sub
Sub foobar(strOne As String, Optional strTwo)
    If IsMissing(strTwo) Then
        MsgBox strOne
    Else
        MsgBox strOne & "- " & strTwo
    End If
End Sub

Note: the IsMissing in Foobar is only to indicate that each routine needs to test the optional arguments before doing anything with them. Perhaps a clearer example of just passing them on is:

Sub foo()
   ' note only one parameter provided
    Call bar("test")
End Sub
Sub bar(sOne As String, Optional sTwo, Optional sThree, Optional sFour)
     ' still pass all parameters even if we only got one
    Call foobar(sOne, sTwo, sThree, sFour)
End Sub
Sub foobar(strOne As String, Optional strTwo, Optional strThree, Optional strFour)
   ' some code here
End Sub
1
votes

Use IsMissing.

If IsMissing(arg1) Then
    'do something
End If

It works only for Variant type.