Calc, Basic Macros and ISBLANK()

[l8gravely]

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

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

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

[gerard24]

Hi,

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

=COUNT(A1:B1)*(SIGN(A1-B1)+2)

[l8gravely]

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

[Charlie Young]

Simpler than using ISBLANK() with FunctionAccess:

Code: Select all

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.

[kingfisher]

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.

[l8gravely]

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

[Charlie Young]

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

	if CellIsBlank(ThisComponent.Sheets(0).getCellRangeByName("C22")) then
		do blank stuff
	else
		do something else
	endif

[l8gravely]

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

[kingfisher]

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.

[MrProgrammer]

Hi, and welcome to the forum.

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.

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.

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.

[AngusOg]

Simpler than using ISBLANK() with FunctionAccess:

Code: Select all

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?

[Lupp]

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

=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.

[AngusOg]

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

=MyFunction(A1,isBlank(A1))

but there has to be a neater way to do this surely...?

[lader]

Code: Select all

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 

[AngusOg]

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

=MyTestFunction(A1)

in my macro

Code: Select all

Function MyTestFunction(p_cell)
  If p_cell <> A_blank_cell then
    my code goes here...
  end if
end function.

[AngusOg]

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/Docume ... 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.

[Zizi64]

e.g. in my sheet

Code: Select all

    =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.