We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Calc Macros: Multiple Optional Parameters [closed]

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

Guilty_Spark_343 gravatar image

updated 2020-08-31 21:47:29 +0200

Alex Kemp 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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-31 21:47:39.545179


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 +0200 )edit

2 Answers

Sort by » oldest newest most voted

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

JohnSUN gravatar image

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

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 +0200 )edit

answered 2020-05-29 23:07:32 +0200

libre officer gravatar image

I copied and tested you original code and the call to the function "verbatim" in LibreOffice 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
edit flag offensive delete link more

Question Tools

1 follower


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

Seen: 1,521 times

Last updated: May 29 '20