Page 1 of 1

[Solved] Defining Variables for Functionaccess

PostPosted: Sat Jun 29, 2019 5:22 pm
by sokolowitzky
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

Re: Defining Variables for Functionaccess

PostPosted: Sat Jun 29, 2019 5:32 pm
by Villeroy
FND is not defined

Re: Defining Variables for Functionaccess

PostPosted: Sat Jun 29, 2019 5:36 pm
by sokolowitzky
It's still the same.

Re: Defining Variables for Functionaccess

PostPosted: Sat Jun 29, 2019 5:43 pm
by Villeroy
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 

Re: Defining Variables for Functionaccess

PostPosted: Sat Jun 29, 2019 6:52 pm
by sokolowitzky
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

Re: Defining Variables for Functionaccess

PostPosted: Sat Jun 29, 2019 7:02 pm
by Zizi64
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

Re: Defining Variables for Functionaccess

PostPosted: Sat Jun 29, 2019 7:06 pm
by Villeroy
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.

Re: Defining Variables for Functionaccess

PostPosted: Sun Jun 30, 2019 1:09 am
by JeJe
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.

Re: [Solved] Defining Variables for Functionaccess

PostPosted: Sun Jun 30, 2019 5:03 am
by sokolowitzky
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.

Re: [Solved] Defining Variables for Functionaccess

PostPosted: Sun Jun 30, 2019 10:17 am
by Zizi64
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
'__________________________________________________________________

Re: [Solved] Defining Variables for Functionaccess

PostPosted: Sun Jun 30, 2019 12:04 pm
by JeJe
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.

Re: [Solved] Defining Variables for Functionaccess

PostPosted: Sun Jun 30, 2019 5:53 pm
by Villeroy
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)