Ask Your Question

strange result in multiplying empty cells

asked 2019-04-20 00:34:12 +0200

giul51 gravatar image

updated 2019-04-20 07:41:35 +0200

I am using Calc "Version: Build ID: 1:6.1.5-3 CPU threads: 12; OS: Linux 4.19; UI render: default; VCL: gtk3_kde5; Locale: en-US (en_US.UTF-8); Calc: group threaded".

I am practicing with building and using customer function, and as a first step I inserted the sample function

Function VOL(a, b, c)  
VOL = a*b*c  
End Function

in My Macros & Dialogs/Standard/Module1.
I went and use it in a worksheet, and by accident included in the computation a blank cell with two other containing 2 and 4: the result was 8 (!?).
I then tried with all the three cells empty, and the result was an empty cell.
Multiplying the cells normally (=A1*A2) I get 0 if one cell is empty, as expected.

So, I cannot grasp what's going on with the function: is it a bug ? any other reason

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-04-20 08:12:11 +0200

updated 2019-04-20 08:20:25 +0200

Hello @giul51

Your code does not set types for variables used and for return value, so that is why you can get an unexpected result in some use cases. When an empty cell is passed in your function, the value of corresponding variable is not set to 0, but to empty value, thus ignored by a*b*c expression. It is much more better to declare expected variable types, so your code is less error prone. Something like this is better, but it will return 0 if any of cell used is a text string:

Function VOL (a as Double, b as Double, c as Double) as Double
VOL = a*b*c
End Function
edit flag offensive delete link more


Thanks @SM_Riga for your answer. So, as I understand, such a behaviour is not a bug but a designed feature that an empty variant is not converted to 0 , as in the spreadsheet, nor "mathematically" converted to empty (0 in sums, and to 1 in multiplications). Well, that's worthy to be well understood known. Thanks again

giul51 gravatar imagegiul51 ( 2019-04-23 01:31:05 +0200 )edit

answered 2019-04-20 22:02:10 +0200

erAck gravatar image

Looks like tdf#124605, a Basic error with an empty variant.

edit flag offensive delete link more


Thanks for your answer: in fact it is the same problem.

giul51 gravatar imagegiul51 ( 2019-04-23 01:35:50 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-04-20 00:34:12 +0200

Seen: 39 times

Last updated: Apr 20