[Solved] Calc Built-In Functions

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
SuneOskar
Posts: 44
Joined: Thu Nov 05, 2009 12:17 pm

[Solved] Calc Built-In Functions

Post by SuneOskar »

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
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Calc Built-In Functions

Post 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
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Calc Built-In Functions

Post 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

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
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.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc Built-In Functions

Post 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

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

Re: Calc Built-In Functions

Post by SuneOskar »

Thanks for your help!!
LibreOffice 3.45 on MacOs 10.7.3
Post Reply