Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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

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 zero values 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)

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

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 zero values 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)

IsMissing(r)

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

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 zero values 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)