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