Menu
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.
825
The best and easiest way to enter a formula programmatically in a cell (even if you are a pro) is to write it in a cell, select it with its equal sign from the formula bar and paste it between quotation marks in your code.
If you wish to test whether a worksheet cell is empty in VBA, you can not use the worksheet function called ISBLANK. In VBA, you must use the ISEMPTY function. In this example, we will test whether cell A1 is empty. If cell A1 is empty, the message "Cell A1 is empty" will be displayed.
Using an Input Box Let's say you want to enter the value in cell A1, the code would be like this: Range("A1"). Value = _ InputBox(Prompt:="Type the value you want enter in A1.") In the above code, the value from cell A1 assigns to the value returned by the input box that returns the value entered by the user.
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.
172
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.
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
