如何可能通过VBA代码使Excel计算单元为空

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.

实际上，我有一个函数只有当它的参数等于一个空的单元格，也就是说，一个完全被删除的单元格在它上面按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 cannot amend 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 qlFloatingRateBond function:

根据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",,,,,,,)

或

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

由于提供了错误的参数，您会得到对象处理程序错误(修改处理了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.

使它的唯一方法管理债券没有地板是提供一个真正的空细胞,但如果你有一个外部数据源返回某个值,根据存在或缺乏细胞层你不能使它工作。

2 个解决方案

#1

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.

这不是最好的答案，也许是值得思考的东西。

#2

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本身是空的。

#1