Okay, I maybe didn't wake up properly yet, take a look further below… 2009/6/19 Johnny Rosenberg <[email protected]>: > 2009/6/19 Niklas Nebel <[email protected]>: >> 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: [email protected] >> For additional commands, e-mail: [email protected] >> >> > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
