Calc Macros: Multiple Optional Parameters

[Guilty_Spark_343]

Hi,

i want to use a Sub with multiple optional paramters in LibreOffice Calc like this:

Sub test(_
ByRef mandatory As String, _
Optional ByRef optional1 As String, _
Optional ByRef optional2 As String)

If IsMissing(optional1) Then
	msgbox "Optional Argument 1 Is missing"
Else
	msgbox "Optional Argument 1 is: " & optional1
End If

End Sub

Now i want to call this Sub while not using the first optional paramter “optional1”

test("abc",,"def")

To my suprise IsMissing(optional) does not detect that “optional1” is missing. Instead the macro acts like “optional1” is existing and gives me the message box telling me “Optional Argument 1 is: Error |”.

How do i handle subs with multiple optional paramters?

[Lupp]

Just to understand a bit better: In what way is that a Calc macro? Did you probably pass the parameters via a custom function called from a Calc sheetcell? If so, the issue might be related to bug tdf#102381. (I did not yet test with your code, and don’t know of that problem from my experiences when calling sub purely from within BASIC.).)

[JohnSUN]

Just change parameters type to Variant (or omit it like as here)

Omitted String is “empty string”, it cannot be “null” or “empty” or “missing”

(Also skip ByRef - do you really need it?)

[Guilty_Spark_343]

Hi,

thank you, that solved my issue!

About ByRef: I like to write my code as explicitly as possible. It forces me to actively make decisions instead of relying on defaults without thinking about it.

[libreofficer]

I copied and tested you original code and the call to the function “verbatim” in LibreOffice 7.0.0.0beta1 and it works.

I should add that to make it work for functions you need to declare Option Compatible in the beginning of the module, and use optional default values in the arguments.

This is a fully functional module:

Option Compatible

Function MyFunction2(a As Integer, _
Optional b As integer = 0, _
Optional d As integer = 0, _
Optional c As integer = 0) As Integer
	MyFunction2 = a + b + c + d
End Function

Sub Main

	Print MyFunction2(1)           ' 2nd, 3rd and 4th arguments are missing.
	Print MyFunction2(1,2)        ' 3rd and 4th arguments are missing.
	Print MyFunction2(1,2,3)     ' last argument is missing.
	Print MyFunction2(1,2,3,4)  ' all arguments are passed.
	
	Print MyFunction2(1,,,4)      '  2nd and 3rd arguments are missing.
	Print MyFunction2(1,2,,4)    '  3rd arguments is missing.
	Print MyFunction2(1,,3,4)    '  2nd argument is missing.
	Print MyFunction2(1,,3,)      '  2rd and 4th arguments are missing.

End Sub