How to find first empty cell in a named range's first column using BASIC?

English
,
#1

I’m writing a BASIC function in Calc and have a variable containing a cell range using the following code:

		dataSheet = ThisComponent.Sheets.getByName("Materials")
		componentList = dataSheet.getCellRangeByName("Components")
		componentNames = dataSheet.getCellRangeByName("ComponentNames")

componentList is a 2D block of cells that I later use VLOOKUP to access data contained in this range.
This range has a lot of empty rows, reserved for future data, after the populated data section finishes.
componentNames overlays componentList but only references the first column (i.e. the keys).

Want I want to do is search componentNames for the first empty cell, record its address, and then populate that row with new data. I’m stuck on some code which is giving me a Data Type Mismatch error for reasons I am unable to work out.

		For Each c in componentNames.AbsoluteName()
			IF c.value = "" Then
				MsgBox "No value in " & c.range
			END IF
		NEXT

I’m assuming that I’m not invoking either the right method or accessing the correct property of componentNames but since there’s no auto-completion in Calc, I have no idea what is required here.
Searching this forum, and others, hasn’t helped on this occasion.
I also suspect that once I figure this out, I will not need the overlayed range as I will be able to iterate through the first column of componentList.

#2

You can not do it this way:
For Each c in componentNames.AbsoluteName()
The AbsoluteName prop is not iterable.

oRanges = componentNames.queryEmptyCells()
Print oRanges(0).AbsoluteName    REM first
Print oRanges(oRanges.Count - 1).AbsoluteName    REM last
1 Like
#3

@eeigor thanks!
But how did you know that?

When I searched the Doxygen documentation for the getCellRangeByName method, I found it returns a XCellRange:
https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1table_1_1XCellRange.html#a92c77dc3025ac50d55bf31bc80ab118f

However, I was basically caught in a circular path as I tried to find out more about XCellRange. It kept showing me getCellRangeByName, among a few other methods, but not the one you pointed out.

Now that you’ve supplied the answer, I see that queryEmptyCells is a method of the XCellRangesQuery interface used by the sheet class.

So, how does one navigate the Doxygen documentation to get from XCellRange back to sheets and then to queryEmptyCells()?

#4

I put an interrupt, I use the Xray Tool.

#5

Ok, I give up. What is the XRay Tool?

I’m running Libre Office 7.2 on a Macbook Pro. And, to be honest, the Macro Editor is REALLY POOR AND BAD. Are you suggesting there is a better IDE that I can use to write macros for Libre Office?

#6

Xray Tool

Снимок экрана от 2022-01-20 00-09-32
Снимок экрана от 2022-01-20 00-09-32888×688 129 KB

#7

Thanks for the image, that helps me understand much better.

#8

Turn on in menu: Tools >> Development Tools:

Снимок экрана от 2022-01-20 00-15-33
Снимок экрана от 2022-01-20 00-15-33927×1046 153 KB


But when working in the editor, it is more convenient to use the Xray Tool.

There is a better IDE for Python and MRI - UNO Object Inspection Tool.

#9

I don’t have the option for Development Tools under my Tools menu. I went into Manage Extensions and whilst my installation is ‘up-to-date’, I’m now browsing through the list of extensions available.

I also looked up X Ray and found MRI. MRI is also old (2018 last commit on github) but it is a lot more up-to-date than X Ray is.

Now that you’ve opened my eyes, I will have a look around and see what I can find.

спасибо!!

#10

It’s a lot of fun to program macros, but by many experienced users it’s supposed to be

  • not efficient
  • against the grain of what spreadsheets basically are
  • functionally replaceabe with the help of (efficient) standard functions
  • ?

The fact that many users rashly start to write (or ask for) user code may be induced by MS-Office programs which are, each single one, more voluminous than a complete LibreOffice, and may consist a very great deal of VBA related code.

MS encourage users to rely on user code. I would suspect them to do so because it helps in their struggle for user-lock-in. At the same time users having taken the bait, will complain about the “poor” LibO Basic.

Try the formula

="("&SHEET()&";"&SUMPRODUCT(MIN(IF(NOT(ISBLANK(INDEX(F5:H28;;1)));"empty";ROW(INDEX(F5:H28;;1))))) & ";" & COLUMN(INDEX(F5:H28;1;1))&")"

to get a designator for the first empty cell in the first column of `F5:H28.

Too complicated?
Well, start to simplify it. That’s spreadsheet style.

#11

I tried that function on a fresh sheet, where I pasted text into all the cells in the range F5 - H28, inclusive.
The output of the cell with this formula read (15;0;6). When I deleted the contents of any cell in column F, then the middle digit of the formula’s output correctly identified the row number of the empty cell. However, if I kept all of column F’s cells filled and deleted the contents of a cell in either of columns G or H, the output of the formula did not change. So that particular formula only seems to work with regards to column F. I believe this behaviour to be due to a typo in the provided formula, as having the extra dimension does not change the underlying logic. However, the formula is very complicated to understand by simple observation.

Having said that, my response to the the points about the appropriateness of writing macros for spreadsheets (and the other std office tools) can be summarised by comparing functional programming to other forms of programming. Functional programs thrive on pure functions, currying, and recursion, but clearly not every problem can be solved with these kinds of approaches. Moreover, when something goes wrong, the difficulty of understanding where the problem is is proportional the the degree of recursiveness and currying that’s going on. Mere mortals don’t have stacks like computers do to store temporary results. Thus, the question needs to take into account the job at hand as well as the mindset of the individual who is solving whatever problem they’re attempting to solve. Personally, I view spreadsheets as a pigeon-holed canvas that provides a lot of functionality for free and I only wish it were possible to manually control the re-calculation engine. I also miss using VB (not VBA) since that enabled me to leverage Office objects while avoiding some of the nonsensical idiosyncrasies of VBA.

#13

I worked out how you were able to bring that panel into view. I didn’t realise it was hidden at the bottom of the spreadsheet window.
I also noticed that some properties could be drilled into to see their properties etc. However, I was disappointed when I wasn’t able to find the definitions of my named ranges amongst the information.

#14

I think @Lupp read your request as only looking for the blank cell in column F, the “key” column. When I first looked at his function, I wondered that, and reread your OQ.

Anyone wanting something of a rundown on how this function composition works can check out How this works (=SUMPRODUCT(MAX(ROW($C$52:$C$190)*($C$52:$C$190<>"")))) - #4 by LarryH, which covers finding the last used rather than first blank cell.

#15

Thanks @Lupp & @joshua4

#16

From the subject:

This is what you wrote. It may not be what you meant. How should I know?

Yes. See above.

No. See first quote.

My remark on “macros” didn’t aim at theoretical concepts concerning programming, and I don’t feel sure that experttise in functional programming will help much with spreadsheet formulas. This variant of functional programming is restricted.

However, lots of tasks needing an hour for a specialized macro solution can be solved within a minute by formulas if you know the standard functions, are experienced with their usage, and are ready to consider the limitations concerning scaling, and additional aspects**…**

See also:
tempFormulaDemo.ods (16.6 KB)

The demo addresses more than one special case. Matching against 2D arrays is a relevant thing for some spreadsheet applications. However, if user code is developed to support it, it should not be specialized to find empty cells columnwise. It should pay for the development by wide-range applicability.

#17

Formulas are a separate issue. Macros also need to be learned somehow.

First, select the range in which you want to find empty cells. Then run the SelectEmptyCells.

Sub SelectEmptyCells()
'''	Selects only empty cells in the **selected** range of the **used** area.
'''	Similar to CellTypeBlanks in Excel.
'''
'''	Calls: GetUsedArea(), [ActivateFrame]
'''	queryEmptyCells() returns the empty cells of the current cell range(s).

	Dim oSheet As Object, oUsedArea As Object, oRanges As Object
	Dim s$  'MsgBox prompt

Rem	oRanges = ThisComponent.CurrentSelection.queryEmptyCells()
	oSheet = ThisComponent.CurrentController.ActiveSheet
	oUsedArea = GetUsedArea(oSheet)
	oRanges = ThisComponent.CurrentSelection _
	 .queryIntersection(oUsedArea.RangeAddress).queryEmptyCells()

Rem	Call ActivateFrame(True)
	ThisComponent.CurrentController.select(oRanges)

	If oRanges.Count > 0 Then
		s = ThisComponent.CurrentSelection.AbsoluteName  'semicolon-separated
		s = Join(Split(s, ";"), Chr(10))
	Else
		s = "No empty cells found."
	End If
	MsgBox s, , "Empty Cells in Selection ∩ UsedArea"  'Buttons:=MB_ICONINFORMATION
End Sub

Function GetUsedArea(oSheet As Object)
	Dim oCursor As Object
	oCursor = oSheet.createCursor()
	oCursor.gotoStartOfUsedArea(False)  'bExpand:=False
	oCursor.gotoEndOfUsedArea(True)  'bExpand:=True
	GetUsedArea = oCursor  '.getCellRangeByName(oCursor.AbsoluteName) -> CellRange
End Function

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