Macro calling a function
Macro calling a function
Hi all,
Need some help with :
svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
svc.callFunction("MATCH",Array(A1;A2:A9))
The error I get is "Parentheses do not match" ? I'm wanting to do this with a macro; take the string that's in A1 and find a match for it in the rest of the column?
I know I can put the formula in a cell, but I would like to use a macro for this project.
Need some help with :
svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
svc.callFunction("MATCH",Array(A1;A2:A9))
The error I get is "Parentheses do not match" ? I'm wanting to do this with a macro; take the string that's in A1 and find a match for it in the rest of the column?
I know I can put the formula in a cell, but I would like to use a macro for this project.
OpenOffice 4.01 on Windows7
Re: Macro calling a function
A cellrange as an element of an array?Array(A1;A2:A9)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: Macro calling a function
Hoy you passed the values of the referenced cells for your custon function (or sub)?
You can not reference to the content of a cell or cellrage by a pure name of the cell or the cellrange in a basic macro. You must get theirs contents by API functions directly from the worksheet or by other method.
You can not reference to the content of a cell or cellrage by a pure name of the cell or the cellrange in a basic macro. You must get theirs contents by API functions directly from the worksheet or by other method.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: Macro calling a function
Zizi6.4,
Thanks for the response. If I add this function to a cell in my worksheet, =MATCH(A1;A2:A6), it works just fine. But, it won't work in the macro because I'm passing a reference
to a range of cells that the macro knows nothing about?
Thanks for the response. If I add this function to a cell in my worksheet, =MATCH(A1;A2:A6), it works just fine. But, it won't work in the macro because I'm passing a reference
to a range of cells that the macro knows nothing about?
OpenOffice 4.01 on Windows7
Re: Macro calling a function
Can you upload the full code of the macro sub or function and an example ods. file here?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: Macro calling a function
OK,
Here's the file; it's not much because it's just a test bed for testing & learning.
Here's the file; it's not much because it's just a test bed for testing & learning.
OpenOffice 4.01 on Windows7
Re: Macro calling a function
Code: Select all
Sub Main
srv = createUnoService("com.sun.star.sheet.FunctionAccess")
sh = ThisComponent.Sheets.getByIndex(0)
x = sh.getCellByPosition(0,0).getValue()
v = sh.getCellRangeByPosition(0,1,0,8).getDataArray()
m = srv.callFunction("MATCH", Array(x,v))
print typename(m), m
End Sub
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Macro calling a function
Gentlemen,
Thank you. Your code is working fine with the spreadsheet I provided you to test with; however, it doesn't
seem to be working with a different spreadsheet, a .xls.
And, that has been driving me crazy, the 'EXACT' function seems to work, but the 'MATCH' function is flakey.
The 'EXACT" function returns a hit, but the 'Match' doesn't, using the same test string.
I think it has something to do with the spreadsheet being a .xls.?
Thank you. Your code is working fine with the spreadsheet I provided you to test with; however, it doesn't
seem to be working with a different spreadsheet, a .xls.
And, that has been driving me crazy, the 'EXACT' function seems to work, but the 'MATCH' function is flakey.
The 'EXACT" function returns a hit, but the 'Match' doesn't, using the same test string.
I think it has something to do with the spreadsheet being a .xls.?
- Attachments
-
- TestMATCH.ods
- (10.89 KiB) Downloaded 218 times
OpenOffice 4.01 on Windows7
Re: Macro calling a function
There is not (THERE IS NOT!) 100% compatibility between the native international Standard ODF fileformat and the foreign, never standardized fileformats. And there is much less compatibility between the AOO/LO macros and the Excel macros...however, it doesn't
seem to be working with a different spreadsheet, a .xls.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: Macro calling a function
Once the document is loaded, it makes no difference if it is an xls or ods document. The same MATCH function works in the same way with any loaded spreadsheet but possibly slightly different than in Excel.
You do not know the proper use of spreadsheet functions MATCH and EXACT. Exact compares 2 values, MATCH needs to be called in unordered mode.
You do not know the proper use of spreadsheet functions MATCH and EXACT. Exact compares 2 values, MATCH needs to be called in unordered mode.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice