[Solved] Defining Variables for Functionaccess

[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

[Villeroy]

FND is not defined

[sokolowitzky]

It's still the same.

[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

[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

[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

[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.

[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.

[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.

[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
'__________________________________________________________________

[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.

[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)