Custom cell function - how to detect empty cell?

[Johnny Rosenberg]

Function MyFunction(A As Variant) As Variant
   Dim Something As Variant

   REM Lots of stuff here…
   MyFunction=Something
End Function

Using it in a cell:
=MyFunction(A1)

So A should be the content of the cell A1 in this case. However, if A1
is empty, A ends up as a double with the value 0. This makes it quite
impossible to detect wether a cell is empty or not, doesn't it? At
least I can't find a way to do that.

Well, I have a theory, which I didn't test yet:
Let's say that I just entered a custom cell function in cell B45. Can
I be sure that B45 is the currently active cell when the function
runs? Even if B45 is only one of a lot of cells that are calculated
when autofill is performed? If so, I could just check the
ActiveCell.getValue() and ActiveCell.getString() after first creating
the object ActiveCell, of course, right?

Johnny Rosenberg

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Jan Holst Jensen]

Johnny Rosenberg wrote:

> Function MyFunction(A As Variant) As Variant
>    Dim Something As Variant
>
>    REM Lots of stuff here…
>    MyFunction=Something
> End Function
>
> Using it in a cell:
> =MyFunction(A1)
>
> So A should be the content of the cell A1 in this case. However, if A1
> is empty, A ends up as a double with the value 0. This makes it quite
> impossible to detect wether a cell is empty or not, doesn't it? At
> least I can't find a way to do that.
>  

Hi Johnny.

Hmmm.... that's strange. I could not find a straightforward way either.
Tried IsEmpty() and IsNull() but neither of them worked as expected.
That doesn't mean that there isn't a solution; only that I don't know one.

However, I found this workaround that might be acceptable:

Function MyFunction(A As Variant) As Variant
  if A = "" then
    MyFunction = Null
  else
    MyFunction = "Input is " + A
  end if
End Function

and rewrite the cell function to be

=MYFUNCTION(IF(ISBLANK(A1);"";A1))

Should work if you can live with encoding Null as a blank string.
Thousands of Oracle DBA's, myself included, do that every day even
though it is silly.

Hope this helps.

Cheers
-- Jan Holst Jensen

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Niklas Nebel]

On 06/18/09 22:31, Johnny Rosenberg wrote:
> So A should be the content of the cell A1 in this case. However, if A1
> is empty, A ends up as a double with the value 0. This makes it quite
> impossible to detect wether a cell is empty or not, doesn't it? At
> least I can't find a way to do that.

To a Basic function, an empty cell is passed as value 0, the function
can't tell the difference.

> Well, I have a theory, which I didn't test yet:
> Let's say that I just entered a custom cell function in cell B45. Can
> I be sure that B45 is the currently active cell when the function
> runs? Even if B45 is only one of a lot of cells that are calculated
> when autofill is performed? If so, I could just check the
> ActiveCell.getValue() and ActiveCell.getString() after first creating
> the object ActiveCell, of course, right?

That won't work. You mention autofill, but the function will also be
called if input values are changed, when the formula is adjusted for
inserted rows, after loading, and more.

If you make an add-in component instead of the Basic function, you can
specify parameter type XCellRange and examine the passed object.

Alternatively, you can modify the formulas, as Jan suggested.

Niklas

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Johnny Rosenberg]

2009/6/19 Niklas Nebel <Niklas.Nebel@...>:

> On 06/18/09 22:31, Johnny Rosenberg wrote:
>>
>> So A should be the content of the cell A1 in this case. However, if A1
>> is empty, A ends up as a double with the value 0. This makes it quite
>> impossible to detect wether a cell is empty or not, doesn't it? At
>> least I can't find a way to do that.
>
> To a Basic function, an empty cell is passed as value 0, the function can't
> tell the difference.
>
>> Well, I have a theory, which I didn't test yet:
>> Let's say that I just entered a custom cell function in cell B45. Can
>> I be sure that B45 is the currently active cell when the function
>> runs? Even if B45 is only one of a lot of cells that are calculated
>> when autofill is performed? If so, I could just check the
>> ActiveCell.getValue() and ActiveCell.getString() after first creating
>> the object ActiveCell, of course, right?
>
> That won't work. You mention autofill, but the function will also be called
> if input values are changed, when the formula is adjusted for inserted rows,
> after loading, and more.

Yes, and also, I realized this morning right after waking up, the
wrong cell would be active anyway, that is the cell in which I put the
formula, which is meaningless…
>
> If you make an add-in component instead of the Basic function, you can
> specify parameter type XCellRange and examine the passed object.
>
> Alternatively, you can modify the formulas, as Jan suggested.

I doubt that that works, since A, declared as Variant, will be a
double when A1 is empty in my example (that's what I've seen anyway
when debugging the function and that's my problem in the first place).
That should mean that ”If A="" Then” should give an error, shouldn't
it?

But I am open minded, so I will try… In fact right now.

OK, tried it.
A1=<Empty>
B1=MYFUNCTION(A1)

B1 displays:
Input is 0

However, there was no error message.

So, the problem persists. The ”If A=""” will never be true.

I have a little follow up question to this:
When OpenOffice.org 3 arrived, some of my formulas stopped working,
because in some of my calculations a few empty cells were apart of it.
They told me that empty cells doesn't have the value 0 anymore, like
it had before and they also said that this is a good thing and so on.
So I had to modify a lot of formulas to make my spreadsheets work
again. Not too hard, but a lot of work. Now it seems like empty cells
are considered 0 again, at least I couldn't reproduce that ”new”
behaviour anymore.

The problem described in this thread was tested on OpenOffice.org Calc
3.1.0. I also have a OpenOffice.org Calc 3.0.1 installation on a
Mandriva system. Maybe I should try the same thing on that one too.
Maybe the outcome will be different?

Johnny Rosenberg



>
> Niklas
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@...
> For additional commands, e-mail: dev-help@...
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Johnny Rosenberg]

Okay, I maybe didn't wake up properly yet, take a look further below…

2009/6/19 Johnny Rosenberg <gurus.knugum@...>:

> 2009/6/19 Niklas Nebel <Niklas.Nebel@...>:
>> On 06/18/09 22:31, Johnny Rosenberg wrote:
>>>
>>> So A should be the content of the cell A1 in this case. However, if A1
>>> is empty, A ends up as a double with the value 0. This makes it quite
>>> impossible to detect wether a cell is empty or not, doesn't it? At
>>> least I can't find a way to do that.
>>
>> To a Basic function, an empty cell is passed as value 0, the function can't
>> tell the difference.
>>
>>> Well, I have a theory, which I didn't test yet:
>>> Let's say that I just entered a custom cell function in cell B45. Can
>>> I be sure that B45 is the currently active cell when the function
>>> runs? Even if B45 is only one of a lot of cells that are calculated
>>> when autofill is performed? If so, I could just check the
>>> ActiveCell.getValue() and ActiveCell.getString() after first creating
>>> the object ActiveCell, of course, right?
>>
>> That won't work. You mention autofill, but the function will also be called
>> if input values are changed, when the formula is adjusted for inserted rows,
>> after loading, and more.
>
> Yes, and also, I realized this morning right after waking up, the
> wrong cell would be active anyway, that is the cell in which I put the
> formula, which is meaningless…
>>
>> If you make an add-in component instead of the Basic function, you can
>> specify parameter type XCellRange and examine the passed object.
>>
>> Alternatively, you can modify the formulas, as Jan suggested.
>
> I doubt that that works, since A, declared as Variant, will be a
> double when A1 is empty in my example (that's what I've seen anyway
> when debugging the function and that's my problem in the first place).
> That should mean that ”If A="" Then” should give an error, shouldn't
> it?
>
> But I am open minded, so I will try… In fact right now.
>
> OK, tried it.
> A1=<Empty>
> B1=MYFUNCTION(A1)

Ooops, sorry, I missed the rewrite part of the original suggestion
(=MYFUNCTION(IF(ISBLANK(A1);"";A1))). Ok, then I guess that would
actually work.
Thanks, Jan.

>
> B1 displays:
> Input is 0
>
> However, there was no error message.
>
> So, the problem persists. The ”If A=""” will never be true.
>
> I have a little follow up question to this:
> When OpenOffice.org 3 arrived, some of my formulas stopped working,
> because in some of my calculations a few empty cells were apart of it.
> They told me that empty cells doesn't have the value 0 anymore, like
> it had before and they also said that this is a good thing and so on.
> So I had to modify a lot of formulas to make my spreadsheets work
> again. Not too hard, but a lot of work. Now it seems like empty cells
> are considered 0 again, at least I couldn't reproduce that ”new”
> behaviour anymore.
>
> The problem described in this thread was tested on OpenOffice.org Calc
> 3.1.0. I also have a OpenOffice.org Calc 3.0.1 installation on a
> Mandriva system. Maybe I should try the same thing on that one too.
> Maybe the outcome will be different?
>
> Johnny Rosenberg
>
>
>
>>
>> Niklas
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: dev-unsubscribe@...
>> For additional commands, e-mail: dev-help@...
>>
>>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Niklas Nebel]

On 06/19/09 11:50, Johnny Rosenberg wrote:

> I have a little follow up question to this:
> When OpenOffice.org 3 arrived, some of my formulas stopped working,
> because in some of my calculations a few empty cells were apart of it.
> They told me that empty cells doesn't have the value 0 anymore, like
> it had before and they also said that this is a good thing and so on.
> So I had to modify a lot of formulas to make my spreadsheets work
> again. Not too hard, but a lot of work. Now it seems like empty cells
> are considered 0 again, at least I couldn't reproduce that ”new”
> behaviour anymore.
>
> The problem described in this thread was tested on OpenOffice.org Calc
> 3.1.0. I also have a OpenOffice.org Calc 3.0.1 installation on a
> Mandriva system. Maybe I should try the same thing on that one too.
> Maybe the outcome will be different?

Handling of empty cells in formulas was changed in 3.0
(http://sc.openoffice.org/servlets/ReadMsg?list=features&msgNo=263), but
not for Basic functions. That's still valid in 3.1. It's hard to say
what causes your changed results without seeing the formulas.

Niklas

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Johnny Rosenberg]

2009/6/19 Niklas Nebel <Niklas.Nebel@...>:

> On 06/19/09 11:50, Johnny Rosenberg wrote:
>>
>> I have a little follow up question to this:
>> When OpenOffice.org 3 arrived, some of my formulas stopped working,
>> because in some of my calculations a few empty cells were apart of it.
>> They told me that empty cells doesn't have the value 0 anymore, like
>> it had before and they also said that this is a good thing and so on.
>> So I had to modify a lot of formulas to make my spreadsheets work
>> again. Not too hard, but a lot of work. Now it seems like empty cells
>> are considered 0 again, at least I couldn't reproduce that ”new”
>> behaviour anymore.
>>
>> The problem described in this thread was tested on OpenOffice.org Calc
>> 3.1.0. I also have a OpenOffice.org Calc 3.0.1 installation on a
>> Mandriva system. Maybe I should try the same thing on that one too.
>> Maybe the outcome will be different?
>
> Handling of empty cells in formulas was changed in 3.0
> (http://sc.openoffice.org/servlets/ReadMsg?list=features&msgNo=263), but not
> for Basic functions. That's still valid in 3.1. It's hard to say what causes
> your changed results without seeing the formulas.

Well, the workaround Jan suggested works, so I guess I will go with
it, at least until I know a better way.

There is not much formulas to see since I didn't finish my ”project”.
I only wrote a simple test function and debugged it and studied the
involved variable.

Something like this:
Function MyFunction(A As Variant) As Variant
   MyFunction=A
End Function

I then added a break point to the first line.

Then I entered the following in B1:
=MYFUNCTION(A1)

That started the function which stopped at my breakpoint. I then press
F8 to go to the next step and I study A by highlighting A and pressing
F7. A appear in the variables section and I could see that it was a
”Variant/Double”. A1 was empty, so this was surprising since I
expected A to be a ”Variant/String”. So whatever more code I write I
can never see if A1 is empty or just 0, at least not as far as I know.

As I see it, this behaviour is a kind of shortcomming. It would have
been nice if A was an object instead, making code like this possible:

Function MyFunction(A As Object) As Object
   If A.getString()="" Then
      MyFunction="Empty"
   ElseIf A.getValue()>50
      MyFunction="Oh my, you have really big feet!"
   Else
     MyFunction="Well, whatever…"
   Endif
End Function

Well, something like that… this is only something that I came up with
after thinking about it for a minute or two, maybe there are better
ideas out there, when it comes to suggestions for enhancements…

Johnny Rosenberg

>
> Niklas
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@...
> For additional commands, e-mail: dev-help@...
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Jan Holst Jensen]

Johnny Rosenberg wrote:

> 2009/6/19 Niklas Nebel <Niklas.Nebel@...>:
>  
>> Handling of empty cells in formulas was changed in 3.0
>> (http://sc.openoffice.org/servlets/ReadMsg?list=features&msgNo=263), but not
>> for Basic functions. That's still valid in 3.1. It's hard to say what causes
>> your changed results without seeing the formulas.
>>    
>
> Well, the workaround Jan suggested works, so I guess I will go with
> it, at least until I know a better way.
>  

Glad that you can use the suggested workaround.

> There is not much formulas to see since I didn't finish my ”project”.
> I only wrote a simple test function and debugged it and studied the
> involved variable.
>
> Something like this:
> Function MyFunction(A As Variant) As Variant
>    MyFunction=A
> End Function
>
> I then added a break point to the first line.
>
> Then I entered the following in B1:
> =MYFUNCTION(A1)
>
> That started the function which stopped at my breakpoint. I then press
> F8 to go to the next step and I study A by highlighting A and pressing
> F7. A appear in the variables section and I could see that it was a
> ”Variant/Double”. A1 was empty, so this was surprising since I
> expected A to be a ”Variant/String”. So whatever more code I write I
> can never see if A1 is empty or just 0, at least not as far as I know.
>
> As I see it, this behaviour is a kind of shortcomming.

I will second that - I would have expected a cell without content passed
to a Basic function to give a meaningful result when tested with
IsEmpty() or IsNull(). But I don't know how badly this would break
existing sheets if it were implemented.

>  It would have
> been nice if A was an object instead, making code like this possible:
>
> Function MyFunction(A As Object) As Object
>    If A.getString()="" Then
>       MyFunction="Empty"
>    ElseIf A.getValue()>50
>       MyFunction="Oh my, you have really big feet!"
>    Else
>      MyFunction="Well, whatever…"
>    Endif
> End Function
>
> Well, something like that… this is only something that I came up with
> after thinking about it for a minute or two, maybe there are better
> ideas out there, when it comes to suggestions for enhancements…
>  

Variants can hold the Empty or Null value to indicate no-values so I
don't see a need for passing an Object instead. Making the parameter an
Object would probably be more heavy-weight and thus slower than passing
a Variant - but I don't know that for sure.

Cheers
-- Jan