Ask Your Question
0

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

asked 2019-10-31 12:27:28 +0100

kxr gravatar image

updated 2019-10-31 12:33:20 +0100

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)
edit retag flag offensive close merge delete

Comments

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

erAck gravatar imageerAck ( 2019-11-01 16:02:59 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-11-01 16:02:16 +0100

erAck gravatar image

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.

edit flag offensive delete link more

Comments

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 ...
(more)
kxr gravatar imagekxr ( 2019-11-13 16:29:06 +0100 )edit

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 gravatar imagekxr ( 2019-11-13 16:38:17 +0100 )edit

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?

kxr gravatar imagekxr ( 2019-11-13 16:55:49 +0100 )edit

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.

erAck gravatar imageerAck ( 2019-11-14 16:56:01 +0100 )edit

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 gravatar imagekxr ( 2019-11-14 21:59:46 +0100 )edit

And is there some API to do something like this

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

completely in a macro?

kxr gravatar imagekxr ( 2019-11-14 22:14:22 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-10-31 12:27:28 +0100

Seen: 60 times

Last updated: Nov 01