如何使 Excel 单元格等于空，可能是通过 VBA 代码

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

提问by Lisa Ann

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 onlyif its argument is equal to an empty cell, that is, a cell that has been completely erased pressing delover it or such a thing.

实际上，我有一个函数只有在它的参数等于一个空单元格时才起作用，也就是说，一个被完全擦除的单元格按下del它或这样的东西。

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

但是如果单元格的值等于""而不是简单地为空，则该函数不再起作用（相反，如果内容等于 ，则它可以正常工作[formula]）。

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

我无法修改函数，但我可以编写额外的 VBA 代码来实现结果。

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

按照 Daniel Cook 发布我的代码的请求，这里是来自QuantLibXL的qlFloatingRateBond函数的公式：

=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 Floorsargument, I hope to not have done any mistake with the commas and the empty-default arguments...).

由于提供了错误的参数，您会收到 Object Handler 错误（修改涉及Floors参数，我希望没有对逗号和空默认参数做任何错误......）。

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.

让它管理没有底线的债券的唯一方法是为其提供一个真正的空单元格，但是如果您有一个外部数据源，根据底线的存在或不存在返回该单元格中的某些值，您就不会能够使它工作。

采纳答案by Richard Morgan

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:

这可能无法解决您的问题，因为我没有 QuantLibXL，但是如果您想删除工作表上所有评估为“”的单元格，您可以尝试：

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 "".

现在，而不是空单元格，这些值将是一个错误 (#N/A)。也许库理解错误而不是 0 或“”的值。

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:

如果您想从工作表中删除所有错误，您可以修改上面的 VBA 代码以查找导致错误的所有公式：

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.

不是最好的答案，但也许有些值得深思。

回答by Bathsheba

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.

例如，将一个单元格设置为 =A1，您会看到该单元格中将出现一个 0；即使 A1 本身是空白的。