libre office basic BUG (I believe so)
Andrew Douglas Pitonyak
andrew at pitonyak.org
Wed Aug 28 19:29:54 PDT 2013
I had a lot of trouble with your little macro and I am not sure if it is
because there are Bugs in LO or something else is happening.
First, I believe that your only stated problem is that you enter
"B7>80") and it comes out as "C13". This is because it is relative. In
other words, what you really need if you want to reference the cell that
contains the special formatting is "A1" because it pretends that you are
dealing with a range and you want to reference inside that range.
Oddly, this seems to work if you start empty
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("Sheet1")
'Inspect oSheet.getCellByPosition(1,6).ConditionalFormat
'Exit Sub
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)
'Inspect oCell
'oEntrys = oCell.getPropertyValue("ConditionalFormat")
oEntrys = oCell.ConditionalFormat
'Inspect oEntrys
oEntrys.clear()
'Print aColumn(iColumn) & CStr(iLine+1) & ">80"
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) & CStr(iLine+1) & ">80"
mCond(1).Value = "A1>80"
mCond(2).Name = "StyleName"
mCond(2).Value = "Good"
oEntrys.addNew(mCond())
oCell.ConditionalFormat = oEntrys
oEntrys.Clear
mCond(0).Name = "Operator"
mCond(0).Value =
com.sun.star.sheet.ConditionOperator.FORMULA
mCond(1).Name = "Formula1"
mCond(1).Value = aColumn(iColumn) & CStr(iLine+1) & ">60"
mCond(1).Value = "A1>60"
mCond(2).Name = "StyleName"
mCond(2).Value = "Average"
oEntrys.addNew(mCond())
oCell.ConditionalFormat = oEntrys
'Inspect oEntrys.getByIndex(1)
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) & CStr(iLine+1) & ">80"
mCond(1).Value = "A1>80"
mCond(2).Name = "StyleName"
mCond(2).Value = "Keep"
oEntrys.addNew(mCond())
oCell.ConditionalFormat = oEntrys
oEntrys.Clear()
mCond(0).Name = "Operator"
mCond(0).Value =
com.sun.star.sheet.ConditionOperator.FORMULA
mCond(1).Name = "Formula1"
mCond(1).Value = aColumn(iColumn) & CStr(iLine+1) & "<60"
mCond(1).Value = "A1<60"
mCond(2).Name = "StyleName"
mCond(2).Value = "Review"
oEntrys.addNew(mCond())
oCell.ConditionalFormat = oEntrys
end if
Next
Next
End Sub
If I get the ConditionalFormat property and inspect it, however, only
one conditional object is shown, and I am unable to clear the existing
entries.
I am a bit stumped!
On 08/26/2013 01:16 PM, joseramos at bb.com.br wrote:
> 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?
>
> I post this queston at AskLibo in August 7 and nothing so far.
>
> 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.sheet.ConditionOperator.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
>
> End Sub
>
>
> José**Ramos
>
>
> _______________________________________________
> LibreOffice mailing list
> LibreOffice at lists.freedesktop.org
> http://lists.freedesktop.org/mailman/listinfo/libreoffice
--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
Info: http://www.pitonyak.org/oo.php
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freedesktop.org/archives/libreoffice/attachments/20130828/5d84eeab/attachment.html>
More information about the LibreOffice
mailing list