Set a formula from macro basic using countif and regular expression

[Waiwurrie]

Hi. If I run the following sub against a blank sheet I get Err 508 in cell A4. Clicking on the cell gives the text of the formula, clicking the tick or hitting enter the error remains. Type a additional space at the end of the formula and hit enter the formula works (giving a result of zero!)

Using the same code to populate the cell with a more basic formula (=A1 as in the second line) works first time without the need to add an additional space. What am I doing wrong?

Subroutine

Sub test3
ThisComponent.Sheets(1).getCellByPosition(0,3).setFormula("=COUNTIF(A1:A2, "".*X.*"")")
ThisComponent.Sheets(1).getCellByPosition(0,4).setFormula("=A1")
end Sub

(Edited a bit for better readability by @Lupp)

[Lupp]

I would not recommend wiki questions.

[prabhatbolia]

This is not the problem actually you edit anything in formuale then you will get value. May be ) or ; or = sign

[Lupp]

There was a very bad (imo) design decision to replace the parameter delimiter, mandatorily a semicolon in old days, for locales where the comma not is used as the decimal separator by the comma by default.
You supposed you can delimit parameters by commas now, too - and everywhere.

In fact still the semicolon is used as parameter delimiter in Calc. Only for the localized version shown in the ‘FormulaBar’ and for the diplay in cells it is replaced by the comma under certain conditions.
If you insist to use the comma in your BASIC-hosted formulas you need to assign them to the .FormulaLocal property of the target cell. Better get back to the semicolon as the parameter delimiter. There may be a case one day for applying the .SetFormulaArray(FA) e.g. and there is no FormulaLocalArray.

Down with that silly localizeritis! There may also occur problems with localized function names and with add-in functions.

I suppose you either
-1- have set a locale where the comma is the decimal separator or
-2- have wisely set the semicolon as your parameter (“function”) delimiter in ‘Options’ > ‘LibreOffice Calc’ > ‘Formula’ > ‘Separators’ to avoid useless problems across locales or
-3- are using a rather old version of LibO where the semicolon is still mandatory in the role.

If writing a Calc formula in BASIC, it is a string which must regard exactly the syntax for the formula.

(Your Sub works for me with the semicolon instead of the comma.)

After some

[Waiwurrie]

Using the “;” works for me too and I have subsequently changed the separator from “,” to “;” as per 2) above so I am continually reminded!

Thank you for ending an evening of pain:-)