Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Wrong reference when inserting conditional format with basic script

Hi fellows and thanks for your attention. First, I learned VBA with Excel by my self. I have no expertise programming in any language. For now on, I have to use Calc as much as Excel. I'm trying to include conditional format thru a basic program. I have to create a spreadsheet with conditional format with lots of cells. Every cell will have a unique formula. Everything I found so far, led me to code below. This is a very short example, not the real one, for better view. The extrange thing is: the cell reference in the formula gets totally changed. To understand what I'm writing about, copy this code and execute it. Don't forget to change sheet name in code or just rename your sheet with "Example". After, go to cell B7 and look at conditional format using format menu. You will see B7 changed to C13. And at B8, you will see B7 changed to C15. And you may see any other cell inserted by this code has the same problem (B7->C13, C7->D13,B8->C15,C8->D15,...). I used it at version 3.3.3. Also downloaded version 4.0.3 and the problem still remain. Also tried use absolute reference. The formula keeps right but not work in sheet. I have to remove "$" manually. When it done, works fine.

Somebody know what's happening?

For any help, many thanks...

Sub Example

Dim mCond (2) As New com.sun.star.beans.PropertyValue Dim oEntrys As Variant Dim oCell as object, oDoc as object, oSheet as object Dim iLine as integer, iColumn as integer Dim aColumn(9) as string

oDoc = ThisComponent oSheet=oDoc.Sheets.getByName("Example")

aColumn(0)="A": aColumn(1)="B": aColumn(2)="C": aColumn(3)="D" aColumn(4)="E": aColumn(5)="F": aColumn(6)="G": aColumn(7)="H" aColumn(8)="I": aColumn(9)="J"

For iLine = 6 to 9 For iColumn = 1 to 9 oCell = oSheet.getCellByPosition(iColumn,iLine) oEntrys = oCell.getPropertyValue("ConditionalFormat") oEntrys.clear() if iLine mod 2 = 0 then ' this formula start at line 7, first cell B7 ' looking at sheet, format menu, conditional format you will see C13 mCond(0).Name = "Operator" mCond(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA mCond(1).Name = "Formula1" mCond(1).Value = aColumn(iColumn) & iLine+1 & ">80" mCond(2).Name = "StyleName" mCond(2).Value = "Good" oEntrys.addNew(mCond()) oCell.setPropertyValue("ConditionalFormat", oEntrys) mCond(0).Name = "Operator" mCond(0).Value = com.sun.star. Cheap Youtube Views FORMULA mCond(1).Name = "Formula1" mCond(1).Value = aColumn(iColumn) & iLine+1 & ">60" mCond(2).Name = "StyleName" mCond(2).Value = "Average" oEntrys.addNew(mCond()) oCell.setPropertyValue("ConditionalFormat", oEntrys) else ' this start at line 8, first cell B8, and in this case you see C15 mCond(0).Name = "Operator" mCond(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA mCond(1).Name = "Formula1" mCond(1).Value = aColumn(iColumn) & iLine+1 & ">80" mCond(2).Name = "StyleName" mCond(2).Value = "Keep" oEntrys.addNew(mCond()) oCell.setPropertyValue("ConditionalFormat", oEntrys) mCond(0).Name = "Operator" mCond(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA mCond(1).Name = "Formula1" mCond(1).Value = aColumn(iColumn) & iLine+1 & "<60" mCond(2).Name = "StyleName" mCond(2).Value = "Review" oEntrys.addNew(mCond()) oCell.setPropertyValue("ConditionalFormat", oEntrys) end if Next Next