7

Of course you're going to answer this question with the ="" formula to be written in the aforementioned cell, but this is not my case.

Actually, I have a function that works only if its argument is equal to an empty cell, that is, a cell that has been completely erased pressing del over it or such a thing.

Obviously I cannot simply erase the content of the cell because it gets data from an external source and sometimes I need the content of that cell to be present and visible in my spreadsheet.

The current cell's formula is something like

=if([condition], "", [formula])

but if the cell's value is equal to "" instead of being simply empty, the function doesn't work anymore (on the contrary it works fine if the content is equal to [formula]).

I cannot amend the function, but I can possibly write an additional VBA code to achieve the result.

Following Daniel Cook request of posting my code, here is the formula from QuantLibXL's qlFloatingRateBond function:

=qlFloatingRateBond(,,"EUR",3,,,"obj_0005#007",2,,"Actual/365",,,"obj_0004#008",,,,,,,)

If you amend it in 

=qlFloatingRateBond(,,"EUR",3,,,"obj_0005#007",2,,"Actual/365",0,,"obj_0004#008",,,,,,,)

or

=qlFloatingRateBond(,,"EUR",3,,,"obj_0005#007",2,,"Actual/365","",,"obj_0004#008",,,,,,,)

you get Object Handler error due to a wrong argument provided (the modification regards the Floors argument, I hope to not have done any mistake with the commas and the empty-default arguments...).

The only way to make it to manage a bond without floor is to provide it a true empty cell, but if you had an external data source which returns some value in that cell according to the presence or the absence of a floor you would not be able to make it work.

Improve this question
7
  • Any chance you can share the function you can't change? Or possibly even its signature? Without that, I doubt any useful answer will be forthcoming. Commented Sep 18, 2013 at 19:55
  • Yes, it comes from [quantlib.org/quantlibxl] (QuantLibXL) and it's this one: [quantlib.org/quantlibxl/…. The true function is written in C++, this is everything I know about that. Commented Sep 18, 2013 at 19:59
  • Here's a better link to the method qlFloatingRateBond How are you calling the method? Show us your code. Commented Sep 18, 2013 at 20:01
  • Yeah, it's that one. Sorry for my bad using of SO formatting :) Commented Sep 18, 2013 at 20:02
  • @DanielCook code provided. Commented Sep 18, 2013 at 20:13

2 Answers 2

Reset to default
6

This may not fix your problem as I don't have the QuantLibXL, but if you wanted to delete all the cells that evaluated to "" on a sheet, you could try:

sheet.UsedRange.SpecialCells(xlCellTypeFormulas, 2).Select
Selection.ClearContents

This will find all formulas that result in text and then delete them. Note: this could fail if there are none, and also may be too aggressive.

Another trick is to change your formula to something like:

=if([condition], NA(), [formula])

Now, instead of empty cells, the values will be an error (#N/A). Perhaps the library understands errors instead of values of 0 or "".

If you wanted to then delete all the errors from your sheet, you would modify the VBA code above to find all the formula's resulting in errors:

sheet.UsedRange.SpecialCells(xlCellTypeFormulas, 16).Select
Selection.ClearContents

Again, if there are none, this would error.

Not the best answer, but perhaps some food for thought.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4

Bad news: you can't. Once a cell contains a formula it is never in a truly blank state.

E.g. set a cell to =A1, and you see that a 0 will appear in that cell; even when A1 is itself blank.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.