[Solved] Formula with commas instead of semicolons

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Alt1234
Posts: 2
Joined: Mon Nov 11, 2019 8:51 pm

[Solved] Formula with commas instead of semicolons

Post by Alt1234 »

I am currently using getFormulaArray to retrieve a cell's formula. Is there any way to get the formula with comma separated arguments rather than semicolon separated?
Thanks
Last edited by Hagar Delest on Tue Nov 12, 2019 4:04 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 6.0.7.3 on Ubuntu 18.04
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Retrieving formula with commas instead of semicolons

Post by Zizi64 »

Always use the semicolon separator in the Calc cells. The AOO Calc and LibreOffice Calc are not MS Excel. The comma is the localized decimal separator in many languages.

otherwise you can substitute any character to an another one programatically in a string.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Retrieving formula with commas instead of semicolons

Post by JeJe »

You can use the SUBSTITUTE function to do the replacement in code

Code: Select all

	FuncAcc = createunoservice("com.sun.star.sheet.FunctionAccess")

	dim arr
	redim arr(2)
	
	arr(0)=";aaa;;;;aa;a;"
	arr(1)=";"
	arr(2) =","
	
	res = FuncAcc.callFunction("SUBSTITUTE",arr())
   	
   	msgbox res


Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Retrieving formula with commas instead of semicolons

Post by Villeroy »

print ThisComponent.CurrentSelection.FormulaLocal

The FormulaLocal property of a cell displays the formula as shown in the formula bar. It can not have comma separated arguments when the locale uses the comma as decimal separator.
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
Alt1234
Posts: 2
Joined: Mon Nov 11, 2019 8:51 pm

Re: Retrieving formula with commas instead of semicolons

Post by Alt1234 »

Thanks for the help everyone!
OpenOffice 6.0.7.3 on Ubuntu 18.04
Post Reply