Page 1 of 1

[Solved] Calc Built-In Functions

PostPosted: Thu Nov 05, 2009 12:35 pm
by SuneOskar
In VBA one can use the Excel built-in functions in your code. Is this a possibility in Calc ?

Re: Calc Built-In Functions

PostPosted: Thu Nov 05, 2009 1:09 pm
by RPG
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

Re: Calc Built-In Functions

PostPosted: Thu Nov 05, 2009 3:31 pm
by FJCC
I'm not sure I'm understanding this correctly, but you can use the Calc spreadsheet functions in a macro. For example,
Code: Select all   Expand viewCollapse view
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)


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

Re: Calc Built-In Functions

PostPosted: Thu Nov 05, 2009 4:28 pm
by Villeroy
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]
Code: Select all   Expand viewCollapse view
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

Re: Calc Built-In Functions

PostPosted: Thu Nov 05, 2009 6:52 pm
by SuneOskar
Thanks for your help!!