REM ***** BASIC *****
Sub Main
ThisComponent.EnableAutomaticCalculation(False)
with ThisComponent.Sheets(0)
.getCellRangeByName("A:D").ClearContents(1023)
msgbox (.getCellRangeByName("A2").NumberFormat)
msgbox (.getCellRangeByName("B2").NumberFormat)
msgbox (.getCellRangeByName("C2").NumberFormat)
msgbox (.getCellRangeByName("D2").NumberFormat)
.getCellRangeByName("B2").setFormula("=IFERROR($A2-$A3;""-"")")
.getCellRangeByName("B3").setFormula("=IFERROR($A3-$A4;""-"")")
.getCellRangeByName("B4").setFormula("=IFERROR($A4-$A5;""-"")")
.getCellRangeByName("B5").setFormula("=IFERROR($A5-$A6;""-"")")
.getCellRangeByName("C2").setFormula("=IFERROR($B2/$A3;""-"")")
.getCellRangeByName("C3").setFormula("=IFERROR($B3/$A4;""-"")")
.getCellRangeByName("C4").setFormula("=IFERROR($B4/$A5;""-"")")
.getCellRangeByName("D2").setFormula("=AVERAGE($C$2:$C$4)")
.getCellRangeByName("A:A").NumberFormat = 138 ' Number: -1,234.57; Format Code: #,##0.00;[RED]-#,##0.00'
.getCellRangeByName("B:B").NumberFormat = 138 ' Number: -1,234.57; Format Code: #,##0.00;[RED]-#,##0.00'
.getCellRangeByName("C:C").NumberFormat = 130 ' Percent: -12.95%; Format Code: #,##0.00%;[RED]-#,##0.00%'
.getCellRangeByName("D2").NumberFormat = 131 ' Percent: -12.950%; Format Code: #,##0.000%;[RED]-#,##0.000%'
msgbox (.getCellRangeByName("A2").NumberFormat)
msgbox (.getCellRangeByName("B2").NumberFormat)
msgbox (.getCellRangeByName("C2").NumberFormat)
msgbox (.getCellRangeByName("D2").NumberFormat)
.getCellRangeByName("A2").setValue(368.690002)
.getCellRangeByName("A3").setValue(362.616943)
.getCellRangeByName("A4").setValue(371.908722)
.getCellRangeByName("A5").setValue(349.899323)
' Why is D2 format changed when recalculated?'
ThisComponent.CalculateAll
msgbox (.getCellRangeByName("D2").NumberFormat) ' Percent: -12.95%; Format Code: 0.00%'
' Reapplying number format after recalculation corrects.'
.getCellRangeByName("D2").NumberFormat = 131 ' Percent: -12.950%; Format Code: #,##0.000%;[RED]-#,##0.000%'
msgbox (.getCellRangeByName("D2").NumberFormat)
end with
End Sub
What makes you even think that any of those numbers {138,130,131} would denote a valid specific format? i.e. running that macro in a fresh new Calc document there even aren’t such number format keys and the format will be just General. Your document seems to have additional user defined number formats for which by chance the format key is quite stable, but that is not guaranteed.
See also Basic NumberFormat Codes - #3 by erAck.
And no, without the document I can’t say why the format would be changed when calculating. Conditional formatting? Another event macro running?
That’s why comments are included to show the format codes. Format the cell with those, then use the first set of msgbox output to get the format numbers and put them in the macro.
But here’s a spreadsheet with the formats and macro.
Updated with simplified spreadsheet example:
ReCalc Format Change Rev2.ods (10.7 KB)
Let’s check together:
Option Explicit
' Manually format A1:B2 as #,##0.000%;[RED]-#,##0.000%
' Then use the MsgBox to get the number format value and put into the macro.
Sub Main
Dim nformat As Long
With ThisComponent.Sheets(0)
nformat=.getCellRangeByName("B1").NumberFormat
ThisComponent.EnableAutomaticCalculation(False)
.getCellRangeByName("A:B").ClearContents(1023) ' all formats are removed!
.getCellRangeByName("B1").setFormula("=AVERAGE(A1:A2)")
.getCellRangeByName("A1").setValue(0.50)
.getCellRangeByName("A2").setValue(0.6667)
.getCellRangeByName("B1").NumberFormat=nformat ' restore format B1
MsgBox "Format before calculation:" & .getCellRangeByName("B1").NumberFormat
ThisComponent.EnableAutomaticCalculation(True)
MsgBox "Format after calculation:" & .getCellRangeByName("B1").NumberFormat
End With
End Sub
Thanks, but that is not valid due to removed formatting from A1:A2.
Try this:
Option Explicit
' 1) Manually format A1:B2 as #,##0.000%;[RED]-#,##0.000%
' 2) Run Sub Main
' Result: B1 is formatted as -12.95%; Format Code: 0.00%
' Expected: B1 formatted as -12.950%; Format Code: #,##0.000%;[RED]-#,##0.000% (unchanged)
Sub Main
Dim nformat As Long
With ThisComponent.Sheets(0)
nformat=.getCellRangeByName("B1").NumberFormat
ThisComponent.EnableAutomaticCalculation(False)
.getCellRangeByName("A:B").ClearContents(1023) ' all formats are removed!
.getCellRangeByName("B1").setFormula("=AVERAGE(A1:A2)")
.getCellRangeByName("A1").setValue(0.50)
.getCellRangeByName("A2").setValue(0.6667)
.getCellRangeByName("A1:B2").NumberFormat=nformat ' restore formats A1:B2
MsgBox "Format before calculation:" & .getCellRangeByName("B1").NumberFormat
ThisComponent.EnableAutomaticCalculation(True)
MsgBox "Format after calculation:" & .getCellRangeByName("B1").NumberFormat
End With
End Sub
I’d consider this a bug. It does not happen if the lines setting the number format and the formula are swapped
.getCellRangeByName("A1:B2").NumberFormat = 120 ' Replace with obtained format number
.getCellRangeByName("B1").setFormula("=AVERAGE(A1:A2)")
Please submit a bug and attach the example file there and report the bug number back here in the form tdf#123456.
The bug does not reproduce on my configuration. NumberFormat does not change (11).
Version: 7.4.2.3 (x64) / LibreOffice Community
Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf
CPU threads: 6; OS: Windows 10.0 Build 19044; UI render: default; VCL: win
Locale: ru-RU (ru_RU); UI: ru-RU
Calc: CL
Simply use a template instead of Basic code and everything will just work fine.
You probably did not follow the instructions given with the document’s BASIC code. The user defined format code
#,##0.000%;[RED]-#,##0.000%
can’t be format number 11.
I downloaded the file from this post and ran the macro from here. It was assumed that the cells were already formatted properly. 
Now the bug is reproduced!
Bug submitted.
tdf#154576
2 Likes