Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index


Hi José
Am 01.08.2013 19:57, schrieb joseramos@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@bb.com.br


_______________________________________________
LibreOffice mailing list
LibreOffice@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice



Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.