Ask Your Question

how to retrieve a cell value using LibreOffice Basic

asked 2017-11-26 14:27:31 +0200 gravatar image

In CALC I want to retrieve a cell value, change the cell's style property (NB: without relying on predefined styles!) depending on that value and write the whole back again (or to a different location). What is the LO macro code text for achieving that?

edit retag flag offensive close merge delete


In what way do you want to give (identify) the specific cell to work on?

Lupp gravatar imageLupp ( 2017-11-26 17:10:24 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-11-27 04:07:58 +0200

librebel gravatar image


Please see if the following macro does what you want:

Sub cellRange_Colorize( strRange as String )
REM Set the background color of each cell within <strRange> to the color value specified within that cell.
REM Example call: cellRange_Colorize( "A1:A99" )
    Dim oSheet As Object  : oSheet = ThisComponent.CurrentController.ActiveSheet
    Dim oRange As Object  : oRange = oSheet.getCellRangebyName( strRange )
    Dim oCell As Object
    Dim i As Integer, j As Integer
    For i = 0 To oRange.Rows.getCount() - 1             REM Traverse all cells in the specified range:
        For j = 0 To oRange.Columns.getCount() - 1
            oCell = oRange.getCellByPosition( j, i )
            If oCell.String <> "" Then oCell.CellBackColor = oCell.Value
        Next j
    Next i
End Sub

HTH, lib

edit flag offensive delete link more


Well, that looks pretty much like what I am looking for, Librebel. This helps. Thnx-a-lot!! I started out from macro recording and, hence, was confronted with UNO-code BASIC. This looks more like the good old BASIC I remember from earlier days ;-) gravatar ( 2017-11-27 14:02:40 +0200 )edit

answered 2017-11-26 17:04:53 +0200

Lupp gravatar image

updated 2017-11-27 15:28:00 +0200

Generally it is suggested to study the api and its usage from LibO BASIC in advance of writing user code. There is this BASIC guide as available from an independent server (probably no longer provided by the document foundation). It should be virtually the same content as the guide made for BASIC. How to use the api may be much better explained by the famous texts by Andrew Pitonyak available here.

If you need help with a specific task like the one you talked of, you should always explain for what reason you think to need user code for what you eventually want toi achieve. There are to many requests by users who didn't thoroughly consider the appropriate means (probably mislead by Excel and VBA).

Frankly spoken your request seems to be of the kind. What good for? There is conditional formatting and there are named cell styles applicable (under conditions if needed) with the help of the STYLE() function. For a thorouhghly designed sheet there should be no need of user code doing things of the kind. By theway: There are lots of cell properties.

There are many details you need to learn about the LibreOffice api (here in specific concerning Calc). I already hinted some sources, and will not write a new tutorial. You may study this attached example. Please note that I do not obey some common ways of naming.
Concerning the attachment: The code contained a naming error. For reasons I do not understand it worked nonetheless for me. As I don't know how this might be on a different system I replace the old attachment with one having the mentioned error eliminated.

edit flag offensive delete link more


Thnx for your comment, Lupp. What I want to achieve is this: given any output of the function COLOR in a cell, have the cell (or another) show the corresponding background color. Has something to do with generation of (random) palettes. You need too many a predefined conditional formatting styles to realize that using STYLE, agree? And a lot of dumb handwork too. That's why one automates tasks. gravatar ( 2017-11-26 23:15:21 +0200 )edit

quoting"...given any output of ... COLOR in a cell..."
What cell? IoW: In what way do you intend to pass the cells you want to work on to the Sub. Any one of the operations you want to perform will have a source cell and a target cell regarding the property .CellBackColor. If you know the ways to access all the pairs of cells you want to work on as pairs of objects it is simple:
targetCell.CellBackColor=sourceCell.CellBackColor will do the trick.

Lupp gravatar imageLupp ( 2017-11-26 23:32:02 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-11-26 14:27:31 +0200

Seen: 12,314 times

Last updated: Nov 27 '17