How to make an Excel cell equal to empty, possibly using VBA code

Question

Of course, you will answer this question with the formula ="" , which will be written in the above cell, but this is not my case.

In fact, I have a function that only works if its argument is equal to an empty cell, that is, a cell that has been completely erased by pressing del on top of it or such a thing.

Obviously, I can’t just erase the contents of the cell, because it receives data from an external source, and sometimes I need the contents of this cell to be present and visible in my spreadsheet.

The current cell formula is something like

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

but if the cell value is "" and not just empty, the function no longer works (on the contrary, it works fine if the contents are [formula] ).

I cannot change the function, but I can write additional VBA code to achieve the result.

Following Daniel Cooke's request for the publication of my code, here is the formula from QuantLibXL qlFloatingRateBond :

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

If you change it to

 =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 an object handler error due to an invalid argument (change regarding the Floors argument, I hope I didn’t make a mistake with commas and arguments with an empty default value ...).

The only way to force binding without gender is to provide it with a true empty cell, but if you have an external data source that returns some value in this cell depending on the presence or absence of gender, you cannot make it work.

Answer 1

This may not solve your problem, since I do not have QuantLibXL, but if you want to delete all cells that were rated "" on the sheet, you can try:

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

Here you will find all the formulas that lead to the text, and then delete them. Note: this may fail if there are none, and it may also be too aggressive.

Another trick is to change the formula to something like:

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

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

If you want to remove all errors from your worksheet, you must change the VBA code above to find the whole formula leading to errors:

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

Again, if not, this will result in an error.

Not the best answer, but perhaps food for thought.

Answer 2

The bad news: you cannot. When a cell contains a formula, it is never in a truly empty state.

eg. set the cell to = A1, and you will see that 0 appears in this cell; even when A1 itself is empty.