[Solved] Test cell for zero value and issue warning

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
cmeylaq
Posts: 34
Joined: Wed Apr 19, 2023 3:52 pm

[Solved] Test cell for zero value and issue warning

Post by cmeylaq »

Hi all....can anyone assist me with the below simple macro? Im finding difficult sourcing help material on the net :(

Code: Select all

--Macro to check the contents of cell A5 of the activesheet and display message

Sub Check
sheet = ThisComponent.CurrentController.ActiveSheet
value = Contents of cell A5
If Value = "0" then Msg "All Good" Else Msgbox "Check Cell A5" EndIf
End Sub

 Edit: Changed subject, was If condition 
Make your post understandable by others 
-- MrProgrammer, forum moderator 
Last edited by Hagar Delest on Fri Apr 21, 2023 11:30 pm, edited 2 times in total.
Reason: tagged solved.
OpenOffice 3.1 on Windows Vista
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Test cell for zero value and issue warning

Post by Zizi64 »

Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Test cell for zero value and issue warning

Post by karolus »

Hallo

Code: Select all

…
value = sheet.getCellRangeByName("A5").Value
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
cmeylaq
Posts: 34
Joined: Wed Apr 19, 2023 3:52 pm

Re: Test cell for zero value and issue warning

Post by cmeylaq »

karolus wrote: Fri Apr 21, 2023 9:43 pm Hallo

Code: Select all

…
value = sheet.getCellRangeByName("A5").Value
I amended the code but I am getting the wrong message. The macro should prompt ALL GOOD but it is prompting CHECK CELL A5.
What am i doing wrong?
Sub Check
sheet = ThisComponent.CurrentController.ActiveSheet
value = sheet.getCellRangeByName("A5").value

If Value = "0" then
Msgbox "All Good"
Else Msgbox "Check Cell A5"
EndIf
End Sub

Attachments
ifcondition.ods
(8.86 KiB) Downloaded 41 times
OpenOffice 3.1 on Windows Vista
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Test cell for zero value and issue warning

Post by JeJe »

A cell has a value property and a string property. The value is a number so when writing code it can't be "0" (which is a string), it can only be 0 (a number)

If the cell is empty the value property will be a double with the value of 0 (even though you never put anything in it) but the string will be empty.

If you have typed a 0 in the cell, both the value and string properties will give a msgbox showing 0

You can test this with this code, having an empty cell or a 0 in the cell.

I suspect its the string property that you want to differentiate the two.

Code: Select all

cell =   ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("A5")
msgbox "Value: " & cell.Value & chr(10) & "String: " & cell.string

Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
cmeylaq
Posts: 34
Joined: Wed Apr 19, 2023 3:52 pm

Re: Test cell for zero value and issue warning

Post by cmeylaq »

JeJe wrote: Fri Apr 21, 2023 10:49 pm A cell has a value property and a string property. The value is a number so when writing code it can't be "0" (which is a string), it can only be 0 (a number)

If the cell is empty the value property will be a double with the value of 0 (even though you never put anything in it) but the string will be empty.

If you have typed a 0 in the cell, both the value and string properties will give a msgbox showing 0

You can test this with this code, having an empty cell or a 0 in the cell.

I suspect its the string property that you want to differentiate the two.

Code: Select all

cell =   ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("A5")
msgbox "Value: " & cell.Value & chr(10) & "String: " & cell.string



Hi JeJe, Many thanks for the explanation. I got it!!
OpenOffice 3.1 on Windows Vista
Post Reply