Missing parameter problem & its evaluation

Creating a macro - Writing a Script - Using the API

Missing parameter problem & its evaluation

Postby eeigor » Sat Apr 24, 2021 10:33 am

Please tell me how to use missing parameters in StarBasic correctly.

My code is using option VBASupport:
Option VBASupport 1

1. Function UDF(Optional bStatus As Boolean = True)
The following statement works without failures in VBA, but here I had to drop it.
<Optional bStatus As Boolean = True> works unstable, crashes (of course, in my context)
2. Function UDF(Optional bStatus As Boolean)
If IsMissing(bStatus) Then: End If
And bStatus will automatically be set to False after calling the IsMissing function. Why?
If IsMissing(bStatus) Then bStatus = True
Alas, the missing parameter after the call to the IsMissing function is set to False and is no longer considered missing and will not be assigned a new value of True. Is this a bug?
3. Function UDF(Optional bStatus)
If IsMissing(bStatus) Then bStatus = True
This is how it works. Variant/Boolean type. But then the "b" prefix is ​​not entirely correct.
Last edited by eeigor on Sat Apr 24, 2021 12:19 pm, edited 1 time in total.
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Missing parameter problem

Postby Zizi64 » Sat Apr 24, 2021 10:59 am

Try to use two variables to handle the optional parameter.

Code: Select all   Expand viewCollapse view
Function UDF(Optional bPassedStatus As Boolean)

dim bInnerStatus as boolean

   If IsMissing(bPassedStatus) then bInnerStatus = True

   REM Use the bInnerStatus in the next parts of the function...


The conventional "b" prefix never will determine the type of the variable. It is only a helper mark for the programmer.
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9882
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Missing parameter problem

Postby eeigor » Sat Apr 24, 2021 11:13 am

Thanks.
Yes, I've seen this practice. This was not the case in VBA. However, I'm not entirely sure if it really should have been done exactly as you advise. It just leads to an increase in the variables. My UDF has 7 parameters, of which only 2 are required.
Just in case, I will ask if there are other opinions?

UPD
Why doesn't the code below work?
If you try to assign a string to an optional parameter of type Variant, an error will occur. The reason is that the parameter will initially be evaluated as an Object because there is a call to the object method .CellRange in the code. Is this a bug?
E.g. Pseudocode
Call a UDF from a sheet cell by passing a cell or range reference and the name (address) should be output.
Call the TestIt procedure and there will be a parameter type error.
Code: Select all   Expand viewCollapse view
Option VBASupport 1
Sub TestIt()
    Call UDF("TestIt")
End Sub
Function UDF(Optional Caller)
    Dim sCallerName$

    If Not IsMissing(Caller) Then
        If TypeName(Caller) = "Range" Then  'Excel VBA
            ' Retrieve the fully qualified reference to the Caller.
            sCallerName = Caller.CellRange.AbsoluteName
        ElseIf TypeName(Caller) = "String" Then
            sCallerName = Caller
        End If
        MsgBox sCallerName
    End If
End Function


In VBA, there is no such connection between an input parameter and a procedure code. And moreover, with code that will not be executed in a particular case. This is where the second variable is really needed, but of the Variant type. Some kind of nonsense...
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Missing parameter problem & its evaluation

Postby Zizi64 » Sat Apr 24, 2021 1:07 pm

It is not a good idea to use the VBA procedures in the LibreOffice, in the Apache OpenOffice. Use the API functions and procedures. The MS VBA and the AOO/LO never will be fully compatible.
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9882
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Missing parameter problem & its evaluation

Postby eeigor » Sat Apr 24, 2021 1:35 pm

No, no, I only use VBA for the sole purpose of passing a range reference to the UDF, but the question was different. Understand how variables are handled.
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Missing parameter problem & its evaluation

Postby JeJe » Sat Apr 24, 2021 2:10 pm

Same solution to avoid an error.

Code: Select all   Expand viewCollapse view
Option VBASupport 1
Sub TestIt()
    Call UDF("TestIt")
End Sub
Function UDF(Optional Caller)
    Dim sCallerName$,t

    If Not IsMissing(Caller) Then
        If TypeName(Caller) = "Range" Then  'Excel VBA
            ' Retrieve the fully qualified reference to the Caller.
           
            t = caller
            sCallerName =t.CellRange.AbsoluteName
        ElseIf TypeName(Caller) = "String" Then
            sCallerName = Caller
        End If
        MsgBox sCallerName
    End If
End Function


In Basic you can't set a variable to a value on declaration so you can't put "optional varname =" or "varname ="

Edit: and you'll need to check to see if "Range" is implemented as a typename.
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1561
Joined: Wed Mar 09, 2016 2:40 pm

Re: Missing parameter problem & its evaluation

Postby eeigor » Sat Apr 24, 2021 3:41 pm

@JeJe, thank you. Now a complete understanding has been reached. You enter a variable t of type Variant. What is the reason for this approach? In VBA, everything is simpler and, if you want, cleaner. Give your opinion on the reasons for all that I have touched upon here. Is StarBasic not finalized yet?
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Missing parameter problem & its evaluation

Postby eeigor » Sat Apr 24, 2021 3:44 pm

JeJe wrote:In Basic you can't set a variable to a value on declaration so you can't put "optional varname =" or "varname ="

I'm sorry, I didn't read your post to the end :)

Really, I can, if VBA Support mode is enabled.
Function UDF(Optional bStatus As Boolean = True)

This is not the first time I have encountered this feature. For example, why is the obj variable introduced in the code below? Try this: oDoc.SupportsService(), and get an error.
Code: Select all   Expand viewCollapse view
Sub Test_IsCalcDocument()
   Print IsCalcDocument("Hello"); Chr$(9); IsCalcDocument(ThisComponent)
End Sub

Function IsCalcDocument(oDoc) As Boolean
   On Error GoTo Failed
   Dim obj: obj = oDoc  'avoids the error

'   IsCalcDocument = False
   If obj.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then
      IsCalcDocument = True
   End If

Failed:
End Function


And why does a parameter of the Variant type get the Object subtype even before assigning a value just because there is a method call in the procedure body? That's the question.
In short, I don't understand something here:
Dim obj: obj = oDoc 'avoids the error
Attachments
Снимок экрана от 2021-04-24 17-07-55.png
Снимок экрана от 2021-04-24 17-07-55.png (9.23 KiB) Viewed 4851 times
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Missing parameter problem & its evaluation

Postby JeJe » Sat Apr 24, 2021 4:19 pm

Really, I can, if VBA Support mode is enabled.
Function UDF(Optional bStatus As Boolean = True)


You're right! Didn't know that... that could be useful!

*

Wild guess... maybe the compiler checks through the sub/function to make sure any variables there and in the sub declaration match for type?

I think you just have to accept how OOBasic does things and work with it... Office API aside its almost a clone of the VB family of languages. There are quirks like with the split function:

viewtopic.php?f=20&t=95717

You just have to live with them and work round it.
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1561
Joined: Wed Mar 09, 2016 2:40 pm

Re: Missing parameter problem & its evaluation

Postby eeigor » Sat Apr 24, 2021 4:26 pm

Yes, of course. It's just that I'm still at the stage of mastering these compiler features. In the Pitonyak manual, these details are not reflected in the details. Although I tend to consider these features a disadvantage.
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Missing parameter problem & its evaluation

Postby Lupp » Wed Apr 28, 2021 2:03 pm

To be honest: I only had a fly over this thread. Too much VBA.

However, it might be releated somehow to this bug: https://bugs.documentfoundation.org/sho ... ?id=102381.
Concerning optional parameters which actually were omitted in the call, the behaviour gravely depends on whether the call came from the Calc evaluator or from a different Basic routine. Calc wrongly passes 0 (Double) for a missing optional parameter to a user function in Basic.

(In addition there is a fundamental problem with trying to omit a parameter when calling a standard function via the FunctionAccess service. The implications of omitting parameters are "a bit" messed up.)
On Windows 10: LibreOffice 7.1 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3071
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Missing parameter problem & its evaluation

Postby Villeroy » Wed Apr 28, 2021 2:44 pm

You can dump layers and layers and more layers on this API until you end up with dependency hell, security issues, performance issues in very poor and unmaintained amateur code. Look at website development and all the layers of silly JavaScript libraries for the stupid.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29883
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Missing parameter problem & its evaluation

Postby eeigor » Wed Apr 28, 2021 3:49 pm

@Lupp thanks for the valuable observations. And the bug you mentioned is still present.
My procedure just assumes two calls: from the sheet and from the code.
I plan to take the time and test everything as it should. I do not have complete clarity today. This makes the UDF look unfinished.

Everything works if you declare the parameters as Variant and assign the passed values to internal variables. But I want to get rid of the redundancy of the code.
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Missing parameter problem & its evaluation

Postby eeigor » Wed Apr 28, 2021 9:04 pm

Compare two calls.
Run PassParamsByType func from a sheet cell (screenshot 1), and then run Test_PassParamsByTypeFromModule (screenshot 2). Complete confusion... And @Lupp was right.

A1: =PASSPARAMSBYTYPE(1;;;;;)

Code: Select all   Expand viewCollapse view
Function PassParamsByType(pRequired _
, Optional pVariant, Optional pLong&, Optional pDouble#, Optional pString$, Optional pBoolean As Boolean)

   If IsMissing(pBoolean) Then
      pBoolean = True
   End If
   MsgBox "Parameters Passed" _
    & Chr(10) & "TypeName(pRequired):=" & TypeName(pRequired) _
    & Chr(10) & "pVariant:=" & pVariant _
    & Chr(10) & "pLong:=" & pLong _
    & Chr(10) & "pDouble:=" & pDouble _
    & Chr(10) & "pString:=" & pString _
    & Chr(10) & "pBoolean:=" & pBoolean _
    , , "macro:PassParamsByType"
End Function

Sub Test_PassParamsByTypeFromModule()
   Call PassParamsByType(1 _
    , , , , ,)
End Sub

Pay attention to the boolean parameter handling and look at my remarks in the start post. And the string parameter is set to zero. :(
Zizi64 wrote:Try to use two variables to handle the optional parameter.

It seems that yes.

Screenshots 1 & 2:
Attachments
Снимок экрана от 2021-04-28 22-01-33.png
Снимок экрана от 2021-04-28 22-01-44.png
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Missing parameter problem & its evaluation

Postby Zizi64 » Thu Apr 29, 2021 7:03 am

When you call the function in this way from the cell

Code: Select all   Expand viewCollapse view
= PASSPARAMSBYTYPE(1;;;;;)


then the parameters are NOT missing, but they have empty (zero) values.


Try it from the cell:

Code: Select all   Expand viewCollapse view
=PASSPARAMSBYTYPE(1)


Code: Select all   Expand viewCollapse view



In this case the params are missing really.

You can check irf a parameter is missing or not - really:

Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****

Function PassParamsByType(pRequired, Optional pVariant, Optional pLong&, Optional pDouble#, Optional pString$, Optional pBoolean As Boolean)

   If IsMissing(pBoolean) Then
      pBoolean = True
      print "is missing" REM ------------------------- inserted by Zizi64
   End If
   MsgBox "Parameters Passed" _
    & Chr(10) & "TypeName(pRequired):=" & TypeName(pRequired) _
    & Chr(10) & "pVariant:=" & pVariant _
    & Chr(10) & "pLong:=" & pLong _
    & Chr(10) & "pDouble:=" & pDouble _
    & Chr(10) & "pString:=" & pString _
    & Chr(10) & "pBoolean:=" & pBoolean _
    , , "macro:PassParamsByType"
End Function

Sub Test_PassParamsByTypeFromModule()
   Call PassParamsByType(1, , , , ,)
End Sub


I checked it WITHOUT the VBA compatibility option!!!!%!!!!!
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9882
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Missing parameter problem & its evaluation

Postby eeigor » Thu Apr 29, 2021 7:54 am

Zizi64 wrote:…then the parameters are NOT missing, but they have empty (zero) values.

Yes, they have. But aren’t the params missing? That is the question.
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Missing parameter problem & its evaluation

Postby Zizi64 » Thu Apr 29, 2021 8:28 am

It seems there is such logic in the Calc: If you marked the place of the passed variables (by the semicolons), then they will be recognized as existing but empty passed parameters in a formula.
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9882
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Missing parameter problem & its evaluation

Postby eeigor » Thu Apr 29, 2021 8:53 am

We'll have to give up. Such bugs cannot live so long... This is a feature
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests