libreoffice basic - inserting conditional format

[Peter Eberlein]

Hi José
Am 01.08.2013 19:57, schrieb joseramos at bb.com.br:
> Dear Lady/Sir,
>
> My apologies to bother you with such insignificant thing. But I found
> nothing so far at internet to explain my mistake.
> I learned VBA by my self. This means I don't have too much knowledge
> about programming.
>
> Usually I make spreadsheets in Excel. But for now on, I'll have to use
> libreoffice as well.
> I need to create a spreadsheet with conditional format. Lots of cells
> (over 300) with, at least 2 conditional formulas.
>
> So I search lots at internet and found how to do it with libreoffice basic.
> But something is wrong. Please look at example code below:
>
> Sub Test
>      Dim mCond (2) As New com.sun.star.beans.PropertyValue
>      Dim oEntrys As Variant
>      Dim oExtension as object
>      Dim oDoc as object
>
>      oDoc = ThisComponent
>
>      oExtension =
> oDoc.getSheets().getByName("Test1").getCellRangeByPosition(0,5,0,5) 'A6
>
>      oEntrys = oExtension.getPropertyValue("ConditionalFormat")
>
>      mCond(0).Name = "Operator"
>      mCond(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA
>      mCond(1).Name = "Formula1"
>      mCond(1).Value = "OR(B6>1;B6<7)"
The condition matches to all values, nevertheless, try 
"OR($B$6>1;$B$6<7)", this works at least in AOO 4.0
>      mCond(2).Name = "StyleName"
>      mCond(2).Value = "Result"
>
>      oEntrys.clear()
>      oEntrys.addNew(mCond())
>      oExtension.setPropertyValue("ConditionalFormat", oEntrys)
> End Sub
>
> So far, so good.
>
> The formula, as you see, seems right and functional. But when I execute
> de script, in cell A5
> the formula appears as "OR(B11>1;B11<7)" (print screen attached).
>
> In original script I used a line counter like this: "OR(B" & LineCounter
> & ">1;B" & LineCounter & "<7)".
> The LineCounter starts at 6. When I watched the result in cell, I do not
> understand why 6 become 11.
>
> So I used a string var 'Auxiliar' to mount and watch the result with
> step by step execution of script.
> The formula value in 'Auxiliar' was right, just like in example code above.
>
> I was using libreoffice 3.3.3. I thought maybe there was a bug at this
> version. I Uninstall it and install version 4.0.3.
> Same thing happend.
>
> I'm still searching (about 30 days so far) and found nothing!
>
> Please, could tell me where is my mistake?
>
> José Ramos
> joseramos at bb.com.br
>
>
> _______________________________________________
> LibreOffice mailing list
> LibreOffice at lists.freedesktop.org
> http://lists.freedesktop.org/mailman/listinfo/libreoffice
>