1

I want to count the occurrences of yellow, orange, green, and red within the selection using a LibreOffice Basic macro. I have been to this Stackoverflow question, but it doesn't work for me.

Here's the code:

Sub Main
    dim selection,cell as Object
    dim i,j as integer
    selection = ThisComponent.getCurrentController().getSelection()

    for i=selection.RangeAddress.StartRow to selection.RangeAddress.EndRow
        for j=selection.RangeAddress.StartColumn to Selection.RangeAddress.EndColumn
            cell = ThisComponent.Sheets(0).getCellByPosition(i,j)
            print cell.CellBackColor
            rem TODO: Count colors
        next
    next
End Sub

It aways says -1 except when the cell color is black, then it says 0. What am I doing wrong?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
lukmi
  • 31
  • 4
  • probably because `-1` means no cell color and `0` means black cell color? Did you try any other colors in these cells? – Pᴇʜ Mar 26 '18 at 11:06
  • @Pᴇʜ Yes, I tried differently colored cells and it always says `-1`. You can get the color values from [the documentation](https://help.libreoffice.org/Basic/Information#farbcodes). – lukmi Mar 26 '18 at 11:08
  • 1
    `-1` means no cell background color. So I ask some (maybe dump) questions just to ensure: Are we talking about *background* color or *text* color? And is there a possibility you are on the wrong sheet `Sheets(0)`? Or the wrong cell position? Did you check the values for `i` and `j` when you think the output is wrong? – Pᴇʜ Mar 26 '18 at 11:24
  • 2
    @Pᴇʜ We're talking about background color. Yes, I checked the position too. Changing `print cell.CellBackColor` to `print cstr(i) & "x" & cstr(j) & ": " & cell.CellBackColor` and selecting `B16`, which is green, on the **only sheet in the worbook** yields `15x1: 0`. But this time it says `0` for a non-black cell. Something smells fishy here... – lukmi Mar 26 '18 at 11:31

1 Answers1

2

I switched i and j in getCellByPosition(i,j). It should actually be getCellByPosition(j,i).

lukmi
  • 31
  • 4
  • 1
    Now I remember why I always use `iRow` and `iCol` as counters. It is much more descriptive than `i,j` :) – Pᴇʜ Mar 26 '18 at 12:26
  • 1
    @Pᴇʜ Actually, I confused which parameter comes first in `getCellByPosition`. I think the documentation on LibreOffice Basic is beneath contempt - I had to gather everything I know from questions like this one cause I could not find much on the official documentation. This point goes to MS Office... – lukmi Mar 26 '18 at 12:33
  • 2
    Search for "uno api getCellByPosition" to produce https://www.openoffice.org/api/docs/common/ref/com/sun/star/table/XCellRange.html#getCellByPosition, which shows that the parameters are (nColumn, nRow). – Jim K Mar 26 '18 at 17:47
  • @JimK Thank you, this looks like a bit better of documentation – lukmi Mar 27 '18 at 08:08
  • @lukmi you can install the MRI tool extension (https://github.com/hanya/MRI/releases) which is great for discovering object properties and methods. It can also fetch the details of these from the documentation. You can check out a brief tutorial of its usage here https://forum.openoffice.org/en/forum/viewtopic.php?f=74&t=49294 – Jeromy Adofo Sep 01 '22 at 23:23