Is it possible to write Basic macros for Libre Office Calc in Windows that access the cell and range objects??

English
, , , , ,
#1

I have written VBA code in Excel for many years and want to write some in Libre Office. Specifically I want to write functions that I can enter into cells on Libre Office Calc. I am able to create such functions but for some reason when I pass in a range of cells I get only an array of Basic variants, not the references to ranges that I can manipulate.

Any attempt to use code copied and pasted from Andrew Pitonyak’s Macros Explained book (for example Listing 418 on page 478) results in a ‘BASIC runtime error. Object variable not set.’ at the first attempt to access the argument as a range object (the oRange.queryContentCells line).

Here is the code from Andrew Pitonyak:

Function NonEmptyCellsInRange(oRange, sep$) As String

  Dim oCell 'The cell to use!
  Dim oRanges 'Ranges returned after querying for the cells
  Dim oAddrs() 'Array of CellRangeAddress
  Dim oAddr 'One CellRangeAddress
  Dim oSheet 'Sheet that contains the cell range
  Dim i As Long 'General index variable
  Dim nRow As Long 'Row number
  Dim nCol As Long 'Column number
  Dim s As String
  REM First, find the cells that are NOT empty in this range!
  REM I consider a cell to be not empty if it has a value,
  REM date/time, string, or formula.
  oRanges = oRange.queryContentCells(_
    com.sun.star.sheet.CellFlags.VALUE OR _
    com.sun.star.sheet.CellFlags.DATETIME OR _
    com.sun.star.sheet.CellFlags.STRING OR _
    com.sun.star.sheet.CellFlags.FORMULA)
  oAddrs() = oRanges.getRangeAddresses()
  For i = 0 To UBound(oAddrs())
    REM Get a specific address range
    oAddr = oAddrs(i)
    For nRow = oAddr.StartRow To oAddr.EndRow
       For nCol = oAddr.StartColumn To oAddr.EndColumn
        oCell = oRange.Spreadsheet.getCellByPosition(nCol, nRow)
        s = s & oCell.AbsoluteName & sep$
      Next
    Next
  Next
  NonEmptyCellsInRange = s
End Function

I am assuming that this has to do with the fact that I am on Windows and that Libre Office behaves a little differently. I using Libre Office Version 6.2.8.2 on a 64 bit Windows 10 PC.

I really want to use Libre Office Calc for data modelling on my Windows PC and I need to be able to create my own functions. Also I contribute money monthly to Libre Office. Any help much appreciated.

#2

I hope I have made myself clear

No. In addition to mixing different concepts (VBA which is something from MS Office Basic language, partially supported in a dedicated mode by LibreOffice, and Andrew Pitonyak’s code), you also don’t provide a sample code copy-pasted from your macro (or better a sample document with the macro) that doesn’t work for you. That not only requires a potential helper to go looking the pages in the book instead of seeing that right here, but also leaves for possible typos you made when wrote the macros (so that the Andrew’s code could work fine, and that’s your typo that we don’t see that fails).

#3

And how do you call the code?

#4

Mike - I have adjusted the question, I hope that is clearer

#5

@Davig: unfortunately it’s still unclear how do you call the code: from which function; what do you pass to the function’s arguments?

#6

@Mike2 I enter the function into a Calc sheet as a formula, I select a row of cells for the first argument and a string literal in quotes for the second. That is the type of functionality I need for the modelling, to create my own functions to use in Calc cells. I need the range argument to be passed in by Calc as some sort of range object (which is how Excel does it) rather than just variants. I assumed from the examples that Calc can do this but perhaps not.

#8

Hello,

Have found problem with code and using MRI found two lines to modify:

oRanges = oRange.queryContentCells(_

changed to:

oRanges = oRange.getCellRange.queryContentCells(_

and

    oCell = oRange.Spreadsheet.getCellByPosition(nCol, nRow)

changed to:

    oCell = oRange.getCellRange.Spreadsheet.getCellByPosition(nCol, nRow)

With those two changes result:

image description

This was done on Linux Ubuntu 18.04 using LO v 6.3.3.2

#9

Note that field separator in function should be ;. Too lazy to change until now. Corrected line:

=NONEMPTYCELLSINRANGE(A1:A9;" ; ")
#10

Hello,

I have tried the solution you have provided.
Unfortunately I was not able to get the same results.

I have created a new module in the “Standard” library of my spreadsheet, copy pasted the code provided by Davig and changed the lines accordingly to Ratslinger suggestions.

When I run refresh the formula of the B19 cell of Ratlinger “like” document I have created, I get the following error :

BASIC runtime error.
Object variable not set.

The faulty line is apparently :

oRanges = oRange.getCellRange.queryContentCells(_

What is the problem ? WHy can’t I reproduce above result of Ratslinger ?

Bests

#11

@Blender,

The code should be in Standard under the Document and not in Standard under My Macros & Dialogs.

#12

Thanks for your answer.
However the function is already in the Standard library under the document.
The probelm is still the same. I don’t what I can be missing…
I am working on LO v6.4.4.2 on Windows 8.1 can it be a problem ?

#13

@Blender,

Have found problem - add Option VBAsupport 1 before defined function.

#14

Perfect ! Everything works, thanks a lot !
Actually this is a very interesting feature.

#7

I wonder if other users use Calc and Basic to write user defined functions they can use in the spreadsheet, maybe I am the only one. I find that using spreadsheet and macros/Basic to write user defined functions works really well for data modelling but perhaps this is not common with Calc.

My conclusion from my own playing around is that Calc does have the capacity to call macro functions from within the spreadsheet, but always passes any cells passed in as arguments as two dimensional arrays of (Basic native) variants and not as cell ranges. This means an empty cell will always be passed in as a zero and dates are passed in as numbers. I worked around this by using characters to represent different data types, e.g. ^ indicates empty string and ~ represents empty cell.

If I am wrong about this limitation of Calc please let me know, although using the characters is working well as a work around.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.