Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 06 Dec 2017 03:40:38 +0100Set a formula from macro basic using countif and regular expressionhttps://ask.libreoffice.org/en/question/139946/set-a-formula-from-macro-basic-using-countif-and-regular-expression/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)Tue, 05 Dec 2017 23:36:46 +0100https://ask.libreoffice.org/en/question/139946/set-a-formula-from-macro-basic-using-countif-and-regular-expression/Comment by prabhat bolia for <p>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!)</p>
<p>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?</p>
<p>Subroutine</p>
<pre><code>Sub test3
ThisComponent.Sheets(1).getCellByPosition(0,3).setFormula("=COUNTIF(A1:A2, "".*X.*"")")
ThisComponent.Sheets(1).getCellByPosition(0,4).setFormula("=A1")
end Sub
</code></pre>
<p>(Edited a bit for better readability by @Lupp)</p>
https://ask.libreoffice.org/en/question/139946/set-a-formula-from-macro-basic-using-countif-and-regular-expression/?comment=139970#post-id-139970This is not the problem actually you edit anything in formuale then you will get value. May be ) or ; or = signWed, 06 Dec 2017 03:40:38 +0100https://ask.libreoffice.org/en/question/139946/set-a-formula-from-macro-basic-using-countif-and-regular-expression/?comment=139970#post-id-139970Comment by Lupp for <p>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!)</p>
<p>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?</p>
<p>Subroutine</p>
<pre><code>Sub test3
ThisComponent.Sheets(1).getCellByPosition(0,3).setFormula("=COUNTIF(A1:A2, "".*X.*"")")
ThisComponent.Sheets(1).getCellByPosition(0,4).setFormula("=A1")
end Sub
</code></pre>
<p>(Edited a bit for better readability by @Lupp)</p>
https://ask.libreoffice.org/en/question/139946/set-a-formula-from-macro-basic-using-countif-and-regular-expression/?comment=139948#post-id-139948I would not recommend wiki questions.Tue, 05 Dec 2017 23:50:00 +0100https://ask.libreoffice.org/en/question/139946/set-a-formula-from-macro-basic-using-countif-and-regular-expression/?comment=139948#post-id-139948Answer by Lupp for <p>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!)</p>
<p>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?</p>
<p>Subroutine</p>
<pre><code>Sub test3
ThisComponent.Sheets(1).getCellByPosition(0,3).setFormula("=COUNTIF(A1:A2, "".*X.*"")")
ThisComponent.Sheets(1).getCellByPosition(0,4).setFormula("=A1")
end Sub
</code></pre>
<p>(Edited a bit for better readability by @Lupp)</p>
https://ask.libreoffice.org/en/question/139946/set-a-formula-from-macro-basic-using-countif-and-regular-expression/?answer=139950#post-id-139950There 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.
<s>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.
</s>
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 someWed, 06 Dec 2017 00:02:13 +0100https://ask.libreoffice.org/en/question/139946/set-a-formula-from-macro-basic-using-countif-and-regular-expression/?answer=139950#post-id-139950Comment by Waiwurrie for <p>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. <br>
You supposed you can delimit parameters by commas now, too - and everywhere. </p>
<p>In fact still the semicolon is used as parameter delimiter in Calc. <strong>Only for the localized version shown in the 'FormulaBar' and for the diplay in cells it is replaced by the comma under certain conditions.</strong> <br>
If you insist to use the comma in your BASIC-hosted formulas you need to assign them to the <code>.FormulaLocal</code> property of the target cell. <strong>Better</strong> get back to the semicolon as the parameter delimiter. There may be a case one day for applying the <code>.SetFormulaArray(FA)</code> e.g. and there is no FormulaLocalArray. </p>
<p>Down with that silly localizeritis! There may also occur problems with localized function names and with add-in functions.</p>
<p><s>I suppose you either <br>
-1- have set a locale where the comma is the decimal separator or <br>
-2- have wisely set the semicolon as your parameter ("function") delimiter in 'Options' > 'LibreOffice Calc' > 'Formula' > 'Separators' to avoid useless problems across locales or <br>
-3- are using a rather old version of LibO where the semicolon is still mandatory in the role.
</s>
If writing a Calc formula in BASIC, it is a string which must regard exactly the syntax for the formula. </p>
<p>(Your Sub works for me with the semicolon instead of the comma.) </p>
<p>After some</p>
https://ask.libreoffice.org/en/question/139946/set-a-formula-from-macro-basic-using-countif-and-regular-expression/?comment=139951#post-id-139951Using 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:-)Wed, 06 Dec 2017 00:23:07 +0100https://ask.libreoffice.org/en/question/139946/set-a-formula-from-macro-basic-using-countif-and-regular-expression/?comment=139951#post-id-139951