如何使Excel单元格等于空，可能通过VBA代码 [英] How to make an Excel cell equal to empty, possibly via VBA code
问题描述
当然你会用 =公式来回答这个问题，写在上述单元格中，但这不是我的情况。 >
实际上，如果一个函数的参数等于一个空单元，那么我的功能只能， code> del 在它或这样的事情。
显然，我不能简单地删除单元格的内容，因为它从外部来源，有时我需要该单元格的内容存在并在我的电子表格中可见。
当前单元格的公式类似于
= if（[condition]，，[formula]）
但是，如果单元格的值等于而不是简单的空白，则该函数不再工作如果内容等于 [formula] ），它的工作正常。
我不能修改函数，但是我可以编写一个额外的VBA代码来实现结果。
Daniel Cook要求发布我的代码之后，这里是来自 QuantLibXL 的 qlFloatingRateBond 功能：
= qlFloatingRateBond（,,EUR，3 ,,,obj_0005＃007，2 ,,实际/ 365,,, obj_0004＃008,,,,,,,, $）
如果您在
= qlFloatingRateBond（,,EUR，3 ,,,obj_0005＃007，2 ,,实际/ 365，0 ,,obj_0004＃008,,,,,,,,
或
= qlFloatingRateBond（,,EUR，3 ,,,obj_0005＃007，2 ,,实际/ 365，， ，obj_0004＃008,,,,,,,,
由于提供了一个错误的参数（修改涉及 Floors 参数，我希望不要对逗号和th做任何错误e空默认参数...）。
使其无需层次管理的唯一方法是提供一个真正的空单元格，但如果您有一个外部数据源，根据现场或不存在的地板，在该单元格中返回一些值，您将无法使其工作。
这可能无法解决您的问题，因为我没有QuantLibXL，但是如果要删除所有评估为的单元格，您可以尝试：
sheet.UsedRange.SpecialCells（xlCellTypeFormulas，2）。选择
Selection.ClearContents
这将找到导致文本，然后删除它们的所有公式。注意：如果没有，可能会失败，也可能太过分了。
另一个诀窍是将您的公式更改为：
= if（[条件]，NA（），[公式]）
现在，而不是空单元格，值将为错误（＃N / A）。也许图书馆理解错误，而不是0或的值。
如果您想从表单中删除所有错误，您将修改上述VBA代码找到所有公式导致错误：
sheet.UsedRange.SpecialCells（xlCellTypeFormulas，16）。选择
选择。 ClearContents
再次，如果没有，这会出错。
不是最好的答案，但也许是一些想法的食物。
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.
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.
这篇关于如何使Excel单元格等于空，可能通过VBA代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
