# Complex ConditionalFormatting ignored by calc?

asked 2012-10-25 16:11:29 +0200

Gaffer
96 1 4 12

It seems that a macro that creates a simple ConditionalFormat with a formula that can be immediately calculated, such as "TRUE", works as expected. However, something more complex such as a formula containing "row()" fails. Oddly, when I open the ConditionalFormatting dialog, the formula is there and if I click OK, the formatting is applied(!)

The macro below creates a simple spreadsheet with 2 columns. The cells in Column A are individually conditionally formatted to "Heading" if the simple formula "TRUE" is true (which is always is...)

The cells in Column B are the problem. The ConditionalFormat is "mod(row(),2)" which is true only on odd rows, so every odd row should be in "Result" format. When the macro populates the cells, the conditional formatting instructions appear to be applied but the formatting is not updated. Look at conditional formatting in cell B1 and you can see the formula there. Press "OK" and the formatting is applied (the cell is formatted as "Result"). To see how it should look, copy B1 into the column below.

I have tried Recalculate (F9) but that doesn't help.

What am I doing wrong?

Sub Main
oDoc = ThisComponent
For iRow = 0 To 20
oDoc.sheets(0).getCellByPosition(0, iRow).setFormula("Simple")
oldCondFormat = oDoc.sheets(0).getCellByPosition(0, iRow).ConditionalFormat

MakePropertyValue( "Operator", com.sun.star.sheet.ConditionOperator.FORMULA ),_
MakePropertyValue( "Formula1", "TRUE"), _
MakePropertyValue( "SourcePosition", oDoc.sheets(0).getCellByPosition(0, iRow).getCellAddress() ), _
MakePropertyValue( "StyleName", "Heading" ) )
oDoc.sheets(0).getCellByPosition(0, iRow).setPropertyValue("ConditionalFormat", oldCondFormat)

oDoc.sheets(0).getCellByPosition(1, iRow).setFormula("Complex")
oldCondFormat = oDoc.sheets(0).getCellByPosition(1, iRow).ConditionalFormat

MakePropertyValue( "Operator", com.sun.star.sheet.ConditionOperator.FORMULA ),_
MakePropertyValue( "Formula1", "mod(row(),2)"), _
MakePropertyValue( "SourcePosition", oDoc.sheets(0).getCellByPosition(1, iRow).getCellAddress() ), _
MakePropertyValue( "StyleName", "Result" ) )
oDoc.sheets(0).getCellByPosition(1, iRow).setPropertyValue("ConditionalFormat", oldCondFormat)
next iRow

End Sub
Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
Dim oPropertyValue As New com.sun.star.beans.PropertyValue
If Not IsMissing( cName ) Then
oPropertyValue.Name = cName
EndIf
If Not IsMissing( uValue ) Then
oPropertyValue.Value = uValue
EndIf
MakePropertyValue() = oPropertyValue
End Function

delete close retag edit

1
1. If Recalculate (F9) not worked, try Ctrl+Shift+F9 (Recalculate Hard)... What the result?
2. Try change comma to semicolon: mod(row() ; 2)
( 2012-10-26 09:51:28 +0200 )edit

Bingo! Changing mod(row() , 2) to mod(row() ; 2) makes all the difference. Would you agree that this is a bug? if so, I'll put together a more specific test case and submit it.

( 2012-10-26 11:39:35 +0200 )edit
1

Bug? I'm not sure... Tools - Options - LibreOffice Calc - Formula - Separators

( 2012-10-26 11:55:11 +0200 )edit

Sort by » oldest newest most voted

answered 2013-02-26 23:35:15 +0200

qubit
5816 3 50 43

Hi @Gaffer,

I agree with @JohnSUN -- this could just be an issue with different separators used in Calc.

If you think that there is, in fact, a problem with Calc, please file a bug and provide a list of steps that can reproduce your problem. The QA team will be happy to help you track down this issue!

Please post a link to any bugs you file in a comment below using the format "fdo#123456".

Thanks!

## Donate

LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!

## Stats

Asked: 2012-10-25 16:11:29 +0200

Seen: 217 times

Last updated: Feb 26