Libreoffice calc - how to write a same value into a range

I know how to 'select' a range in LO ( Calc BASIC ....


But how to write a value, e.g. "1", into that range using BASIC?

myRange = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("D1:H6")
myRange.Value = 1

Gives an "property or method not found" error. But I can't find any properties or values to go after Range to allow me to do what I want. Flailing around and trying

myRange.setValue = 1
myRange.writeValue = 1
myRange.setString = "1"

and numerous other variants don't work either.

Would really appreciate the solution. Thanks.


  • You can edit the value of an individual cell, but not the entire range. You will have to iterate over all the cells in the range one at a time, changing the value of each of them.

    Sub Set1ToD1H6
        myRange = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("D1:H6")
        For i = 0 To myRange.getRows().getCount()-1
            For j = 0 To myRange.getColumns().getCount()-1
                myRange.getCellByPosition(j, i).setValue(1)
            Next j
        Next i
    End Sub

    But since the read-write operation to a cell is comparable in time to the read-write operation to a whole range, it is preferable to use another method - to prepare data in an array and write from it to a range in one operation:

    Sub Set1ToRange
        myRange = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("D1:H6")
        dataOfRange = myRange.getData()
        For i = LBound(dataOfRange) To UBound(dataOfRange)
            For j = LBound(dataOfRange(i)) To UBound(dataOfRange(i))
                dataOfRange(i)(j) = 1
            Next j
        Next i
    End Sub

    (For your example, this will be approximately 30 times faster, for a larger range the time winnings will be even more significant)

    The .getData() and .setData() methods work on numeric range values. To work with text strings (and numbers), use .getDataArray() and .setDataArray(), for working with cell formulas use .getFormulaArray() and .setFormulaArray()