Assigning formula does not work from macro

[mikeh369]

Can’t figure out what is wrong with the code, All I get is 0… If I make and undo anything in the formula after it is set, it then works correctly. It should display “Total Cost: …” instead all i get is 0.
I also tried getcellbyposition(1,0).formula = … and copying it from a range with working formulas with same result.

sCurrencyFormat = "[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00"

aNew = oSheet.getCellRangeByPosition(0,0,iCols,iRows).FormulaArray

aNew(0)(1) = "=CONCATENATE(""Total Cost: "";TEXT(SUBTOTAL(9;B2:B65535);""" & sCurrencyFormat & """))" 

oSheet.getCellRangeByPosition(0,0,iCols,iRows).FormulaArray = aNew

[mikekaganski]

Works fine here using Version: 7.5.3.2 (X86_64) / LibreOffice Community
Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3
CPU threads: 12; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: CL threaded

Maybe you have automatic formula calculation disabled?

[mikeh369]

Made some progress, thanks for suggestions. It seems to only work if I manually recalculate each field. Data > Calculate > AutoCalculate was off some how, but only works on one cell. (also ensured Tools > AutoInput was checked), but again one cell at a time (tried on a selected range / entire sheet). Upgraded to 7.5.3.2 from 7.4.6.2… So, I guess my work around is to figure out how to recalculate with a macro. hehe

Version: 7.5.3.2 (X86_64) / LibreOffice Community
Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded

[JohnSUN]

ThisComponent.calculate()
or
ThisComponent.calculateAll()?
By the way, not sure that [RED] will work in TEXT()

[mikeh369]

Used ThisComponent.Calculate() and works fine, wrote this macro 10 years ago and it worked until lately. hehe

Thanks for your help

Yes, RED works

[mikekaganski]

@mikeh369

Yes, RED works

? Do you mean that is does not return errors? Because it definitely cannot make the text red when used in the TEXT function. If you see it red, it would definitely mean that you have some conditional formatting in action, that makes the color formatting, not the cell formula effect.

[mikeh369]

Forced a negative and you are right. Oh well, that formatting works everywhere else.

[erAck]

The TEXT() function returns a text string, no eye candy.