Calc, Basic Macros and ISBLANK()

Discuss the spreadsheet application

Calc, Basic Macros and ISBLANK()

Postby l8gravely » Tue May 08, 2012 9:29 pm

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 if
end 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 = res
end 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
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()

Postby gerard24 » Tue May 08, 2012 9:47 pm

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.1.4 on Windows 10
gerard24
Volunteer
 
Posts: 935
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: Calc, Basic Macros and ISBLANK()

Postby l8gravely » Tue May 08, 2012 10:23 pm

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()

Postby Charlie Young » Tue May 08, 2012 11:45 pm

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
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Calc, Basic Macros and ISBLANK()

Postby kingfisher » Wed May 09, 2012 2:13 am

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
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

Re: Calc, Basic Macros and ISBLANK()

Postby l8gravely » Wed May 09, 2012 3:15 pm

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()

Postby Charlie Young » Wed May 09, 2012 4:24 pm

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
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Calc, Basic Macros and ISBLANK()

Postby l8gravely » Wed May 09, 2012 5:03 pm

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()

Postby kingfisher » Thu May 10, 2012 3:25 am

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
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

Re: Calc, Basic Macros and ISBLANK()

Postby MrProgrammer » Thu May 10, 2012 5:47 pm

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
(8.63 KiB) Downloaded 238 times
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).
User avatar
MrProgrammer
Volunteer
 
Posts: 3647
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Calc, Basic Macros and ISBLANK()

Postby AngusOg » Tue Feb 12, 2019 3:48 am

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()

Postby Lupp » Tue Feb 12, 2019 2:26 pm

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
User avatar
Lupp
Volunteer
 
Posts: 2369
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Calc, Basic Macros and ISBLANK()

Postby AngusOg » Wed Feb 13, 2019 2:33 am

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()

Postby lader » Wed Feb 13, 2019 11:07 pm

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
lader
 
Posts: 6
Joined: Mon Jul 02, 2018 6:10 pm

Re: Calc, Basic Macros and ISBLANK()

Postby AngusOg » Thu Feb 14, 2019 12:03 am

Hi Lader,

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 if
end 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()

Postby AngusOg » Thu Feb 14, 2019 4:27 am

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()

Postby Zizi64 » Thu Feb 14, 2019 10:42 am

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; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
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
Zizi64
Volunteer
 
Posts: 7831
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 15 guests