Ask Your Question
0

Calc Macros: Multiple Optional Parameters

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

Guilty_Spark_343 gravatar image

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?

edit retag flag offensive close merge delete

Comments

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

1 Answer

Sort by » oldest newest most voted
1

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

Comments

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.

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

Question Tools

1 follower

Stats

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

Seen: 279 times

Last updated: Jan 17 '18