Page 1 of 1

[Solved] Formula with commas instead of semicolons

PostPosted: Mon Nov 11, 2019 8:57 pm
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

Re: Retrieving formula with commas instead of semicolons

PostPosted: Mon Nov 11, 2019 9:17 pm
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.

Re: Retrieving formula with commas instead of semicolons

PostPosted: Mon Nov 11, 2019 9:49 pm
by JeJe
You can use the SUBSTITUTE function to do the replacement in code

Code: Select all   Expand viewCollapse view
   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



Re: Retrieving formula with commas instead of semicolons

PostPosted: Mon Nov 11, 2019 10:16 pm
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.

Re: Retrieving formula with commas instead of semicolons

PostPosted: Tue Nov 12, 2019 1:07 pm
by Alt1234
Thanks for the help everyone!