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
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
Thanks,
John