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) 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]
