BASIC: callFunction("VLOOKUP",...) : discern 0 and empty cell

[kxr]

When I use VLOOKUP in a Calc formula like “=VLOOKUP(…)” then the result will exactly mirror the located source cell content: differentiating between empty or 0 or whatever non-zero value,string…
However when I do the same from BASIC, then in case of an empty cell at the located source position the returned result is 0.0 of type double - even if I format the source cell explicitly as text!
How can I discern a 0.0 value from empty cell content here?

Example code:

FA = CreateUnoService("com.sun.star.sheet.FunctionAccess")
args = Array(123, mySheet.getCellRangeByName("my_named_range"), 3)
r = FA.callFunction("VLOOKUP", args)  
print "VLOOKUP:", typename(r), r, IsMissing(r)

[erAck]

Btw, it always helps to post actually working example code and don’t let readers figure out how you declared and initialized variables.

[erAck]

It’s not possible. A callFunction() result is one of array, double, string or empty(void) (i.e. the #N/A error) or some runtime error signalled via exception. The numeric double 0 case here is the same as the result of a reference to an empty cell (e.g. =A1 with A1 being empty) being resolved.

Formatting a cell with a display format does not have any effect on its content, in this case it’s still empty with no data.

[kxr]

Thanks, … so I tried it half way and fed the pre-calced VLOOKUP from the table cell formula like

=NutMul($J33;VLOOKUP($I33;nut_table;7);"debug")

, but the type of this pre-calced VLOOKUP arriving in the example function (* as arg “conc”) is also double/0.0 - and no improvement.
However if I make a simple cell like

=VLOOKUP($I33;nut_table;7)

This reflects the (empty) source cell 1:1 from the lookup - no “0.0”. How does Calc transfer/discern the extra info about empty cell, and how to do so via BASIC function?

*)

Function NutMul(amount, conc, name_) 
  ' it should return empty string (not 0.0) if conc comes from empty VLOOKUP cell
  if name_ = "debug" then  'rare debug test
    print amount, conc, typename(conc), IsEmpty(conc), IsMissing(conc), IsNull(conc)
  endif
  if amount > 0 then
    if conc <> "" then
      conc = 0.01 * amount * conc
    endif
  else 
    conc = ""
  end if
  NutMul = conc
End Function

[kxr]

And this cell

=("" = VLOOKUP($I33;nut_table;7))

even displays TRUE when emtpy source cell !?
As if VLOOKUP yields an empty string in Calc, but 0.0/double when fed to the function. how come ?

[kxr]

and

=(0 = VLOOKUP($I33;nut_table;7))

also shows TRUE, but

=("" = 0)

shows FALSE. So is there some extra multi-type zero / variant / reference type magic going on, which is not visible from BASIC?

[erAck]

Yes, in Calc a comparison of a reference to an empty cell results in TRUE for both, 0 and “” empty string. Of course a numeric 0 does not equal an “” empty string. A cell reference result like that of VLOOKUP() keeps the indeterminate empty state until resolved, i…e you’ll also notice that such empty cell result displays as empty and not 0. When passing to BASIC for the result of callFunction() there is no “empty cell” type.

[kxr]

With VLOOKUP still calc’ed on the Calc side, I tried NutMul argument spec conc as String , which moves “0” into the function both for empty cell and 0.0.
ByRef, ByVal arg specs also didn’t seem to help to discern incoming VLOOKUP result from cell formula.

The only way I now manged to discern things at least this way was feeding the macro function like this via CONCATENATE:

=NutMul($J33;CONCATENATE(VLOOKUP($I33;nut_table;7));"debug")

CONCATENATE(ref) makes a string: “” from empty cell and “0” from 0 - same way as does a Calc cell.
Somewhat clumsy and possibly slow - as I need it in a big table to be calced in ~2000 cells.

So at least builtin CONCATENATE can discern. Does CONCATENATE have I/O powers which a user defined BASIC function is excluded from? Or is there another ‘secret’ trick ?

[kxr]

And is there some API to do something like this

r = calc_api_magic_eval("CONCATENATE(VLOOKUP(?,?,?))", arg_array)

completely in a macro?