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