API Detect and Mark an Empty Cell?

[vek.m1234]

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
			EndIf
		EndIf
		If MsgBox("Exit?", 1) = 2 AND rowno = 170 Then Exit For
	Next
End Sub

[vek.m1234]

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

[Zizi64]

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

= “”

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

[Zizi64]

Please install and use the object inspection tool named XrayTool.

https://berma.pagesperso-orange.fr/index2.html

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