Ask Your Question
0

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

asked 2019-11-06 09:48:23 +0100

Davig gravatar image

updated 2019-11-06 10:18:15 +0100

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.

edit retag flag offensive close merge delete

Comments

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).

Mike Kaganski gravatar imageMike Kaganski ( 2019-11-06 09:54:21 +0100 )edit

And how do you call the code?

Mike Kaganski gravatar imageMike Kaganski ( 2019-11-06 10:18:05 +0100 )edit

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

Davig gravatar imageDavig ( 2019-11-06 10:19:07 +0100 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2019-11-06 11:04:26 +0100 )edit

@Mike 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.

Davig gravatar imageDavig ( 2019-11-07 10:39:03 +0100 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2019-11-11 01:58:19 +0100

Ratslinger gravatar image

updated 2019-11-11 02:01:47 +0100

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

edit flag offensive delete link more

Comments

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

=NONEMPTYCELLSINRANGE(A1:A9;" ; ")
Ratslinger gravatar imageRatslinger ( 2019-11-11 06:14:39 +0100 )edit
0

answered 2019-11-11 01:02:20 +0100

Davig gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-11-06 09:48:23 +0100

Seen: 49 times

Last updated: Nov 11