[Solved] Calc Built-In Functions
[Solved] Calc Built-In Functions
In VBA one can use the Excel built-in functions in your code. Is this a possibility in Calc ?
Last edited by SuneOskar on Sun Dec 13, 2009 10:28 am, edited 2 times in total.
LibreOffice 3.45 on MacOs 10.7.3
Re: Calc Built-In Functions
Hello
You can in Openoffice basic not use the spreadsheet functions.
You can use the API, but that is more difficult.
http://wiki.services.openoffice.org/wik ... ASIC_Guide
Romke
You can in Openoffice basic not use the spreadsheet functions.
You can use the API, but that is more difficult.
http://wiki.services.openoffice.org/wik ... ASIC_Guide
Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: Calc Built-In Functions
I'm not sure I'm understanding this correctly, but you can use the Calc spreadsheet functions in a macro. For example,
computes the average of the range A1:D10 on Sheet1 and puts the value in the cell F1. I typed that code up without running it myself, so I bet I made an error. I hope it gives you the idea of how to call Calc functions
Code: Select all
svc = createUnoService( "com.sun.star.sheet.FunctionAccess" ) 'Create a service to use Calc functions
oDoc = ThisComponent
Sheet = oDoc.Sheets.getByName("Sheet1")
oCellRange = Sheet.getCellRangeByName("A1:D10")
arg = Array(oCellRange) 'The arguements of the svc service must be in array form
Cell = Sheet.getCellByPosition(5,0)
Cell.Value = svc.callFunction("Average",arg)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Calc Built-In Functions
Most people forget that FunctionAccess is a service with the same properties as you see in Calc's calculation options (Zero date, regex in formulas, case sensitive, match whole cells, show X decimals, precision as displayed). Even Writer has some of these properties.
The following function is analog to: oDocument.createInstance("com.sun.star.sheet.FunctionAccess") [which is not possible]
The following function is analog to: oDocument.createInstance("com.sun.star.sheet.FunctionAccess") [which is not possible]
Code: Select all
REM return a service for function access with the same properties as given document
REM e.g. MatchWholeCell, Regex, NullDate,...
Function getFunctionAccessByDocument(oDoc)
Dim oSrv, aProps(), i%, sName$, v
oSrv = createUnoService("com.sun.star.sheet.FunctionAccess")
if oDoc.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
aProps() = oSrv.PropertySetInfo.getProperties()
for i = 0 to uBound(aProps())
sName = aProps(i).Name
v = oDoc.getPropertyValue(sName)
oSrv.setPropertyValue(sName, v)
next
endif
getFunctionAccessByDocument = oSrv
End Function
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