Page 1 of 1
[Solved] Defining Variables for Functionaccess
Posted: 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
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
Posted: Sat Jun 29, 2019 5:32 pm
by Villeroy
FND is not defined
Re: Defining Variables for Functionaccess
Posted: Sat Jun 29, 2019 5:36 pm
by sokolowitzky
It's still the same.
Re: Defining Variables for Functionaccess
Posted: 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
Posted: 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
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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
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
Posted: 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
Posted: Sun Jun 30, 2019 5:53 pm
by Villeroy
Code: Select all
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
MatchWholeCell boolean
NullDate .util.Date
RegularExpressions boolean
StandardDecimals short
and Wildcards (boolean, LibreOffice only)