« Return to Thread: Custom cell function - how to detect empty cell?
Re: Custom cell function - how to detect empty cell?
by Jan Holst Jensen
::
Rate this Message:
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
> 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
« Return to Thread: Custom cell function - how to detect empty cell?
| Free embeddable forum powered by Nabble | Forum Help |
