API Detect and Mark an Empty Cell?

How do you detect if a cell is empty or full/has text using getString()?

IsNull and = do not work as expected… [I could get it working using Len]

Sub MarkEmptyCell
	Dim oDoc as Object
	Dim nRed As Long: nRed = RGB(255, 0, 0)
	oDoc = ThisComponent
	For rowno=169 To 170
		oCell0 = oDoc.Sheets(0).getCellByPosition(0, rowno)
		oCell2 = oDoc.Sheets(0).getCellByPosition(2, rowno)

		txt0 = oCell0.getString()
		txt2 = oCell2.getString()

		MsgBox rowno & " " & txt0 & txt2
		If NOT IsNull(txt2) Then
			If IsNull(txt0) OR txt0 = " " Then
				MsgBox "die"
				oCell0.CellBackColor = nRed
		If MsgBox("Exit?", 1) = 2 AND rowno = 170 Then Exit For
End Sub

also doesn’t code short-circuit? If rowno = 297 AND MsgBox(rowno & " " & “Exit?”, 1) = 2 Then Exit For displays MsgBox every time starting with rowno = 0

What do you mena as “Empty”. A cell what contains a formula

= “”

look like as an empty cell but it is not empty really.

Please install and use the object inspection tool named XrayTool.


Then you will able to list all of properties and methods of a programming object in this case the Cell what you want to examine:

xray oCell

The Xray shows you: the object has a method named “getType”. The return value is an integer. The value means:

0: empty cell
1: numeric value
2: string
3: formula

Then you can delete or ramark the line “xray oCell” in your code and you can substitute it (for example) by this command:

print oCell.getType

Try it:

REM  *****  BASIC  *****
option Explicit

Sub ExamineIfEmpty
 Dim oDoc as object
 Dim oSheets as object
 Dim oSheet as object
 Dim oCell as object
	oDoc = ThisComponent
	oSheets = oDoc.Sheets
	oSheet = oSheets.getbyIndex(0)
	oCell = oSheet.getCellByPosition(0, 0)
	REM xray oCell
	Print oCell.getType

End Sub