Ask Your Question

Set a formula from macro basic using countif and regular expression [closed]

asked 2017-12-05 23:36:46 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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?


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

(Edited a bit for better readability by @Lupp)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-22 01:15:37.571921


I would not recommend wiki questions.

Lupp gravatar imageLupp ( 2017-12-05 23:50:00 +0100 )edit

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

prabhat bolia gravatar imageprabhat bolia ( 2017-12-06 03:40:38 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-12-06 00:02:13 +0100

Lupp gravatar image

updated 2017-12-06 00:31:21 +0100

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

edit flag offensive delete link more


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:-)

Waiwurrie gravatar imageWaiwurrie ( 2017-12-06 00:23:07 +0100 )edit

Question Tools

1 follower


Asked: 2017-12-05 23:36:46 +0100

Seen: 518 times

Last updated: Dec 06 '17