[Solved] Defining Variables for Functionaccess

Creating a macro - Writing a Script - Using the API

[Solved] Defining Variables for Functionaccess

Postby sokolowitzky » Sat Jun 29, 2019 5:22 pm

Here I attach a screen shot of the error I got.
Due to some other functions I use, I must declare "option explicit" in the module.
In this case I must define all variables.
And calc basic does not accept the way I define variables.

I'd be very glad if someone could tell me what is wrong with this code.

Code: Select all   Expand viewCollapse view
option explicit
sub essayer
dim fndtxt as string
dim searchn as string
fndtxt="."
searchn="aaa.1"
msgbox cFND(fndtxt, searchn)
end sub

  function cFND (fndtxt as string, searchn as string)
dim FuncAcc
' The first argument to callFunction() is the Calc Function Name
' The 2nd argument is the parameters of that function as an array
FuncAcc = createunoservice("com.sun.star.sheet.FunctionAccess")
FND = FuncAcc.callFunction( "FIND" , array(fndtxt, searchn))
' msgbox SBST
cFND = FND
End function
Attachments
functionaccess.PNG
Last edited by sokolowitzky on Sun Jun 30, 2019 4:59 am, edited 1 time in total.
Ocasionally; Windows 10 & Open Office 4.1.3//LibreOffice 6.0
sokolowitzky
 
Posts: 62
Joined: Mon Sep 15, 2014 7:34 pm

Re: Defining Variables for Functionaccess

Postby Villeroy » Sat Jun 29, 2019 5:32 pm

FND is not defined
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28647
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Defining Variables for Functionaccess

Postby sokolowitzky » Sat Jun 29, 2019 5:36 pm

It's still the same.
Attachments
functionaccess2.PNG
Ocasionally; Windows 10 & Open Office 4.1.3//LibreOffice 6.0
sokolowitzky
 
Posts: 62
Joined: Mon Sep 15, 2014 7:34 pm

Re: Defining Variables for Functionaccess

Postby Villeroy » Sat Jun 29, 2019 5:43 pm

Remove "as object". StarBasic is somewhat broken when it comes to explicit variable types.

 Edit: "as object" is wrong anyway. The return value of that function is an integer or an error if the search string is not found 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28647
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Defining Variables for Functionaccess

Postby sokolowitzky » Sat Jun 29, 2019 6:52 pm

thank you. I think I can figure out what is wrong now.
But now I can not make the function turn the value.
Mormally I set a function to a variable to use the value that came out of it.
But this time I can not manage it.

This is kind of a different subject. Maybe I should open a newer topic.
Code: Select all   Expand viewCollapse view
option explicit
sub essayer
dim fndtxt as string
dim searchn as string
dim y as integer
fndtxt="."
searchn="aaa.1"
y = cFND(fndtxt, searchn)
'cFND(fndtxt, searchn)
msgbox "but macro can not call the value of function cFND "& chr(13)&"instead it shows """ & y &""
'msgbox asmas.value
end sub

  function cFND (fndtxt as string, searchn as string)
dim oFA
dim FND
  dim cFND
' The first argument to callFunction() is the Calc Function Name
' The 2nd argument is the parameters of that function as an array
oFA = createunoservice("com.sun.star.sheet.FunctionAccess")
FND = oFA.callFunction( "FIND" , array(fndtxt, searchn))
   cFND=FND
msgbox "the function itself can show value of cFND as a variable,"&chr(13)&"which is = "& cFND
End function
Attachments
functionaccess3.PNG
Ocasionally; Windows 10 & Open Office 4.1.3//LibreOffice 6.0
sokolowitzky
 
Posts: 62
Joined: Mon Sep 15, 2014 7:34 pm

Re: Defining Variables for Functionaccess

Postby Zizi64 » Sat Jun 29, 2019 7:02 pm

Why you use the FunctionAccess - what is developed to the Calc Cell functions - for searching a string in an another string? That is developed to the sheets.
In my opinion, it is better to use the function of the StarBasic named InStr():
https://help.libreoffice.org/6.2/en-US/ ... 20401.html
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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: 9587
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Defining Variables for Functionaccess

Postby Villeroy » Sat Jun 29, 2019 7:06 pm

In case of no match, you have to handle the error which can be very tricky in StarBasic.
If this is about the FIND function only, there is Basic InStr which returns 0 in case of no match.
And we can use Python as a true programming language for macros. Python can do anything with strings.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28647
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Defining Variables for Functionaccess

Postby JeJe » Sun Jun 30, 2019 1:09 am

The reason your function returns 0 is because you've declared a variable with the same name as the function. Remove the line,

dim cFND

As suggested above instr would be easier.
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1190
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] Defining Variables for Functionaccess

Postby sokolowitzky » Sun Jun 30, 2019 5:03 am

Thank you all of you.

@Zizi, I use a lot of spreadsheet functions in basic and sometimes I forgot or I don't know at all that there is an equivalent in Starbasic. This is one of them. I was just trying to make some function for trimming texts between dots. Thank you for your message.
Ocasionally; Windows 10 & Open Office 4.1.3//LibreOffice 6.0
sokolowitzky
 
Posts: 62
Joined: Mon Sep 15, 2014 7:34 pm

Re: [Solved] Defining Variables for Functionaccess

Postby Zizi64 » Sun Jun 30, 2019 10:17 am

The InStr() is better for your task, but the next code works for me in my LO 6.1.6 x64.

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

option explicit
'******************************************************************

Sub essayer

dim fndtxt as string
dim searchn as string

   fndtxt="," 'The string has not contain a comma! (For trying the error hander)
   searchn="aaa.1"
   msgbox vFND(fndtxt, searchn)
end Sub
'__________________________________________________________________


Function vFND(sFndTxt as string, sSearchn as string) as variant

dim oFuncAcc as object
   ' The first argument to callFunction() is the Calc Function Name
   ' The 2nd argument is the parameters of that function as an array
   on error goto errorhandler
   oFuncAcc = createunoservice("com.sun.star.sheet.FunctionAccess")

   vFND = oFuncAcc.callFunction( "FIND" , array(sFndTxt, sSearchn))
   Exit function

errorhandler:   
   vFND =  "Error " & error()

end Function
'__________________________________________________________________
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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: 9587
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Defining Variables for Functionaccess

Postby JeJe » Sun Jun 30, 2019 12:04 pm

For a search with more options there is also XTextSearch

https://www.openoffice.org/api/docs/com ... earch.html

See Andrew Pitonyak's OpenOffice.org Macros Explained for a listing on how to use this.
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1190
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] Defining Variables for Functionaccess

Postby Villeroy » Sun Jun 30, 2019 5:53 pm

Code: Select all   Expand viewCollapse view
function cFND (fndtxt as string, searchn as string,optional iStart) AS Integer
dim FuncAcc
If isMissing(iStart) then iStart = 1
On Error Goto exitErr
FuncAcc = createunoservice("com.sun.star.sheet.FunctionAccess")
cFND = FuncAcc.callFunction( "FIND" , array(fndtxt, searchn, iStart))
exit sub
exitErr:
REM exit with Interger 0
End function

And for functions other than FIND (SEARCH for instance) you may want to set some of the properties of your FunctionAccess that are derived from the document settings
Code: Select all   Expand viewCollapse view
MatchWholeCell         boolean             
NullDate               .util.Date               
RegularExpressions     boolean               
StandardDecimals       short                   
and Wildcards (boolean, LibreOffice only)   
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28647
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests