## Calc, Basic Macros and ISBLANK()

Keyboard macros or custom scripts

### Calc, Basic Macros and ISBLANK()

Hi,
I'm trying to write a basic Macro to compute scores in a soccer match. Simple enough if I do a big, hard to debug nested =IF(isblank(...)...) function in all the cells. This is painful. So I'm trying to
write a Basic Macro useable in OO Calc 3.1 and higher. Most of the logic is fine, but I need to figure out how to check for a Blank or Empty cell and just skip my calculations. I only care to give a result if BOTH cells have valid numbers.

Unfortunately, the following doesn't work:

Code: Select all   Expand viewCollapse view
`function MYPOINTS(OurScore,TheirScore) AS Integer  if IsNumeric(OurScore) AND IsNumeric(TheirScore) then    if OurScore = TheirScore then         MYPOINTS=4    elseif OurScore < TheirScore then         MYPOINTS=2     elseif OurScore > TheirScore then        MYPOINTS=6    else        MYPOINTS=0    end if  end ifend function`

The problem is that IsNumeric(OurScore) on an empty cell returns TRUE. I checked with

function JOHN(X)
JOHN = IsNumeric(X)
end function

and it's always TRUE for an empty Cell when I call is like: =JOHN(C1)

So how can I easily check whether a Cell passed into a BASIC Macro is really empty or not? I checked the:

IsEmpty(X)
IsNumeric(X)
TypeName(X)

functions and none of them return a usable result I can check. I then tried to do:

Code: Select all   Expand viewCollapse view
`function JOHN7(X) AS BOOLEAN  DIM svc As Object  svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )    'Create a service to use Calc functions  DIM res As Boolean  res = svc.callFunction("ISBLANK",Array(X))  JOHN7 = resend function`

It would be just so much simpler if the Basic Macros could access more functions, or returned Empty Cells as some easily testable value.

Thanks,
John
Last edited by MrProgrammer on Sun Aug 04, 2019 4:05 pm, edited 1 time in total.
Reason: Moved from Calc forum to OpenOffice Basic, Python, BeanShell, JavaScript
OpenOffice 3.1.1 on RHEL (CentOS) 5.6
l8gravely

Posts: 4
Joined: Tue May 08, 2012 9:13 pm

### Re: Calc, Basic Macros and ISBLANK()

Hi,

l8gravely wrote:Simple enough if I do a big, hard to debug nested =IF(isblank(...)...) function in all the cells. This is painful.

Not so difficult with a formula.

OurScore in A1, TheirScore in B1 :

Code: Select all   Expand viewCollapse view
`=COUNT(A1:B1)*(SIGN(A1-B1)+2)`
LibreOffice 6.2.4 on Windows 10
gerard24
Volunteer

Posts: 948
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

### Re: Calc, Basic Macros and ISBLANK()

Gerard,

Thanks for the reply, and yes it's simple in a formula, but as I said in my original email, my formula will be looking at values across multiple columns (five eventually) and to keep things
easier on me, I want to just put all the work into a single basic function. The results all depends on the various values stored in each of the five columns, and I don't want to calculate anything
unless they ALL have values in then. And some of the valid input values include zero (0), so your COUNT() idea won't work. I *really* need to know if the cell is empty or not.

Maybe I can do a regular expression on the contents of the cell and determine if it's got an empty string or not? Hmm... might work.

Thanks,
John
OpenOffice 3.1.1 on RHEL (CentOS) 5.6
l8gravely

Posts: 4
Joined: Tue May 08, 2012 9:13 pm

### Re: Calc, Basic Macros and ISBLANK()

Simpler than using ISBLANK() with FunctionAccess:

Code: Select all   Expand viewCollapse view
`Function CellIsBlank(Cell As Object) As Boolean   CellIsBlank = (Cell.Type = com.sun.star.table.CellContentType.EMPTY)End Function`

Note that that won't work entered in a cell, but ISBLANK() by itself works for that.

You can also get all the blank cells in a range with queryEmptyCells.
Apache OpenOffice 4.1.1
Windows XP

Charlie Young
Volunteer

Posts: 1559
Joined: Fri May 14, 2010 1:07 am

### Re: Calc, Basic Macros and ISBLANK()

There are several cell properties you could use, for example,
string | formula | formulalocal = "" { | separates the properties available}
or
type = com.sun.star.table.CellContentType.EMPTY

There is also the method computeFunction.
Apache OpenOffice 4.1.6 on PCLinuxOS

kingfisher
Volunteer

Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

### Re: Calc, Basic Macros and ISBLANK()

Hi Charlie,

Thank you for your quick reply, but I just tried out the =CellIsEmpty(C22) and all I get back is a "False" result and an error popup which says:

BASIC runtime error:
Object variable not set

I'm running this inside a Calc spreadsheet from OpenOffice 3.1.1 on Linux RHEL 5.7 32bit, if that makes a difference. I can certainly look into
upgrading if need be since I'll be getting a new computer one of these days.

Function CellIsBlank(X As Object) As Boolean
CellIsBlank = (X.Type = com.sun.star.table.CellContentType.EMPTY)
End Function

So then I tried to see if I could use kingfisher's suggestions to access cell properties, but I'm not sure I can do this, because as I have been reading, the arguements passed into a basic macro function are just the values of the referenced field, not the address of the field itself. So without opening up a new sheet and trying to figure out the location of the cell being passed into the sheet, how do I do this?

And just to explain, I'm trying to score soccer matches. I don't want to assign a score to a team until I have both the team's score and a score entered for the opponent. So if I put in a 2 for my team's score, but the other team's score is still blank (empty, null, whatever) then the score is zero. But once I have to scores, I can them go and compute the real points to be assigned to a team.

It gets more complicated since I also have to take into account Coed status and forfeits. I guess I could just score a forfeit as a -1 score, but it's simpler to have a column explicity defined. So I want to have a function which does all this. currently I have a monster in-cell function using 'if(isblank(C3),......) which gets crazy hard to keep track of when I'm looking at five columns. A basic macro would be much simpler to deal with.

Thanks guys,
John
OpenOffice 3.1.1 on RHEL (CentOS) 5.6
l8gravely

Posts: 4
Joined: Tue May 08, 2012 9:13 pm

### Re: Calc, Basic Macros and ISBLANK()

l8gravely wrote:Hi Charlie,

Thank you for your quick reply, but I just tried out the =CellIsEmpty(C22) and all I get back is a "False" result and an error popup which says:

Thanks guys,
John

As I said, it doesn't work ientered in a cell. to use it in a macro, you have to pass the cell object, not just the name.

There are many ways this might be done, and you need to consider what sheet. To do cell 22 on the first sheet:

Code: Select all   Expand viewCollapse view
`   if CellIsBlank(ThisComponent.Sheets(0).getCellRangeByName("C22")) then      do blank stuff   else      do something else   endif`
Apache OpenOffice 4.1.1
Windows XP

Charlie Young
Volunteer

Posts: 1559
Joined: Fri May 14, 2010 1:07 am

### Re: Calc, Basic Macros and ISBLANK()

Charlie,

Thanks for the clarification. But I'm not sure this is going to work for me because I need to use this formula for each and every game the teams play over a season.
Thinking about it more, it might make more sense to just expand the function so that it loops over all the fields in the sheet, looking for data and then posting the results back as
found. So instead of having =MyPoints(A2,B2,C2, E2, G2) replicated down the sheet for each match, I'd just loop through the entire spreadsheet inside one function.

I guess that could work. It's a shame there's no easier way inside a basic macro to tell is the value of the cell passed into the function is blank, 0, true, false, etc without jumping
through so many hoops. It seems so limiting that when you have a reasonably nice way to do a macro, but can't actually use cells the same way you can from the spreadsheet
functions directly. Am I being clear in my desires here?

So instead of something convoluted like this:

=IF(OR(ISBLANK(L37);ISBLANK(E37));0;IF(E37=L37;4;IF(E37>L37;6;2)))+IF(ISBLANK(F37);0;F37)

which doesn't do all that Ireally need, I'd like to just use a Basic Macro to do the heavy lifting. I guess I could just write something tacky/hacky like this:

=IF(OR(ISBLANK(A3);ISBLANK(B3);ISBLANK(C3);isblank(f3);isblank(h3));-1;MYPOINTS(a3;b3;c3;f3;h3))

and then just write the MYPOINTS basic function to do the rest of the work.

Thanks,
John
OpenOffice 3.1.1 on RHEL (CentOS) 5.6
l8gravely

Posts: 4
Joined: Tue May 08, 2012 9:13 pm

### Re: Calc, Basic Macros and ISBLANK()

I regret that I can't spend time testing any ideas, so this is a small brain dump. What about using the COUNT function? That was the flag I anticipated using with computeFunction. For example: IF(AND(COUNT(cell range)>1;cell1>cell2);result1;result2)

You will need more than 2 results of course. If the formula becomes too cumbersome, you could split it into smaller formulas in columns to the right of your working range.
Apache OpenOffice 4.1.6 on PCLinuxOS

kingfisher
Volunteer

Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

### Re: Calc, Basic Macros and ISBLANK()

Hi, and welcome to the forum.

l8gravely wrote:I'm trying to write a basic Macro to compute scores in a soccer match.
The proper place to ask about this is the Macros and UNO API forum.

l8gravely wrote:And some of the valid input values include zero (0), so your COUNT() idea won't work.
Note that COUNT(0;0) is 2; zeros are valid numbers and are counted as such.

l8gravely wrote:I only care to give a result if BOTH cells have valid numbers.
Just pass COUNT(cell;cell) as a parameter to your function. See attached example. I didn't see any explanation for how your five score function would work. I used the two score example in your first post, modifying it because your final MYPOINTS = 0 statement will never be executed since one of the three preceeding tests (OurScore=TheirScore, OurScore<TheirScore, OurScore>TheirScore) must be true. For your five score function, pass COUNT(cell;cell;cell;cell;cell) to your function and return 0 unless the count is 5 indicating that all scores were present.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Attachments
201205101033.ods
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3778
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Calc, Basic Macros and ISBLANK()

Charlie Young wrote:Simpler than using ISBLANK() with FunctionAccess:

Code: Select all   Expand viewCollapse view
`Function CellIsBlank(Cell As Object) As Boolean   CellIsBlank = (Cell.Type = com.sun.star.table.CellContentType.EMPTY)End Function`

Note that that won't work entered in a cell, but ISBLANK() by itself works for that.

You can also get all the blank cells in a range with queryEmptyCells.

I've been battling with this piece of code for a couple of hours now. I keep getting the error "BASIC run-time error. Object variable not set" and can't fathom why.

I've tried putting the macro in both the module for the file and under my macros.

Can anyone shed any light on this?
Apache OpenOffice 4.1.6
macOS High Sierra 10.13.6
AngusOg

Posts: 4
Joined: Tue Feb 12, 2019 3:40 am

### Re: Calc, Basic Macros and ISBLANK()

I've been battling with this piece of code for a couple of hours now. I keep getting the error "BASIC run-time error. Object variable not set" and can't fathom why.
What do you pass to the parameter of that user function CellIsBlank? If it actually is an object supporting the service "com.sun.star.sheet.SheetCell", in other words: a spreadsheet cell, I cannot find any chance to get an error. Of course, you need to distinguish the cell object from what you get passed via the parameter of a user function used in a cell's formula.
Code: Select all   Expand viewCollapse view
`=MyFunction(A1)`
doesn't pass the cell object to the function's body, but the content as a variable, say v, of type Variant. The value's actual type then can be Variant/Empty (meaning TypeName(v) = "Empty"), Variant/String or Variant/Double.
Note: A cell containing a formula resulting in the empty string will pass Variant/String, not Variant/Empty.

However, the simple expression (myCell.Type=0) would yield the same result as that proud user function.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2522
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: Calc, Basic Macros and ISBLANK()

Hi Lupp,

Thanks for reply. Yes I am indeed using a cell reference and that will explain the error message then.

But that leaves me back at square one. I simply wish a way to distinguish in my custom function between a cell that actually contains a "0" as it's value and an empty cell which get's passed as a "0".

I suppose I could always put the isblank function in the call e.g.

Code: Select all   Expand viewCollapse view
`=MyFunction(A1,isBlank(A1))`

but there has to be a neater way to do this surely...?
Apache OpenOffice 4.1.6
macOS High Sierra 10.13.6
AngusOg

Posts: 4
Joined: Tue Feb 12, 2019 3:40 am

### Re: Calc, Basic Macros and ISBLANK()

Code: Select all   Expand viewCollapse view
`Function MYPOINTS(OurScore As String, TheirScore As String) As Integer   MYPOINTS = 0   If OurScore <> "" And TheirScore <> "" Then                  If IsNumeric(OurScore) AND IsNumeric(TheirScore) Then                      MYPOINTS = 2 * (Sgn(CInt(OurScore) - CInt(TheirScore)) + 2)        EndIf    EndIf End Function `
LibreOffice 6 on Ubuntu 18.04LTS

Posts: 6
Joined: Mon Jul 02, 2018 6:10 pm

### Re: Calc, Basic Macros and ISBLANK()

Thanks for the suggestion but that isn't quite what I'm looking for - I'm not the OP. I accept that's partly my fault for bringing back to life a very old thread instead of starting a new one.

What I really want is a true test for a cell being empty, like the sheet function isBlank(), not to have to validate the data with a series of checks before proceeding.

e.g. in my sheet

Code: Select all   Expand viewCollapse view
`=MyTestFunction(A1)`

in my macro

Code: Select all   Expand viewCollapse view
`Function MyTestFunction(p_cell)  If p_cell <> A_blank_cell then    my code goes here...  end ifend function.`
Apache OpenOffice 4.1.6
macOS High Sierra 10.13.6
AngusOg

Posts: 4
Joined: Tue Feb 12, 2019 3:40 am

### Re: Calc, Basic Macros and ISBLANK()

This has been stated in previous responses but here is a quote from the open office 3 user guide (I can't find anything to say that it's any different in 4, the version I'm using).

https://wiki.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide/Passing_arguments_to_a_macro

Arguments are passed as values
Arguments passed to a macro from Calc are always values. It is not possible to know what cells, if any, are used. For example, =PositiveSum(A3) passes the value of cell A3, and PositiveSum has no way of knowing that cell A3 was used. If you must know which cells are referenced rather than the values in the cells, pass the range as a string, parse the string, and obtain the values in the referenced cells.

So I think the bottom line in answer to my question is that due to the way the values are passed to macros, it's not currently possible to directly tell if the cell is truly empty or actually contains a 0. To find that you'd need to, as stated above, pass a the cell reference itself and then get hold of the cell object using that reference.
Apache OpenOffice 4.1.6
macOS High Sierra 10.13.6
AngusOg

Posts: 4
Joined: Tue Feb 12, 2019 3:40 am

### Re: Calc, Basic Macros and ISBLANK()

e.g. in my sheet

Code: Select all   Expand viewCollapse view
`    =MyTestFunction(A1)`

The reference of the cell A1 will not pass the cell object to your function. It will pass only the numeric or textual content, or the result of the formula located in the cell... therefore you can not examine if the cell is really empty, or it contain a null string as a result of a formula...

Just a tip:
Pass the SHEET(A1), ROW(A1) and the COLUMN(A1) to your function: then you will able to get the cell object (based on the passed data), and you will able to examine the cell object if it is really empty or not.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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.

Zizi64
Volunteer

Posts: 8137
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Return to OpenOffice Basic, Python, BeanShell, JavaScript

### Who is online

Users browsing this forum: No registered users and 0 guests