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
oldCondFormat.addNew( Array( _
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
oldCondFormat.addNew( Array( _
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