[Solved] Calc Built-In Functions

Keyboard macros or custom scripts

[Solved] Calc Built-In Functions

Postby SuneOskar » 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 ?
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
SuneOskar
 
Posts: 44
Joined: Thu Nov 05, 2009 12:17 pm

Re: Calc Built-In Functions

Postby RPG » Thu Nov 05, 2009 1:09 pm

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
LibreOffice 6.2.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2176
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Calc Built-In Functions

Postby FJCC » Thu Nov 05, 2009 3:31 pm

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
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7385
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Calc Built-In Functions

Postby Villeroy » Thu Nov 05, 2009 4:28 pm

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27382
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc Built-In Functions

Postby SuneOskar » Thu Nov 05, 2009 6:52 pm

Thanks for your help!!
LibreOffice 3.45 on MacOs 10.7.3
SuneOskar
 
Posts: 44
Joined: Thu Nov 05, 2009 12:17 pm


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests