[Solved] Calc Built-In Functions
Posted: Thu Nov 05, 2009 12:35 pm
In VBA one can use the Excel built-in functions in your code. Is this a possibility in Calc ?
User community support forum for Apache OpenOffice, LibreOffice and all the OpenOffice.org derivatives
https://forum.openoffice.org/en/forum/
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)
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