Ask Your Question

Calc Macros: Multiple Optional Parameters

asked 2018-01-17 14:34:05 +0100

Guilty_Spark_343 gravatar image


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"
    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"


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?

edit retag flag offensive close merge delete


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

Lupp gravatar imageLupp ( 2018-01-17 18:03:32 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-01-17 15:06:10 +0100

JohnSUN gravatar image

updated 2018-01-17 15:11:48 +0100

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

edit flag offensive delete link more



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.

Guilty_Spark_343 gravatar imageGuilty_Spark_343 ( 2018-01-17 15:42:39 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-01-17 14:34:05 +0100

Seen: 707 times

Last updated: Jan 17 '18