vba 在excel vba中测试空单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18896196/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Testing empty cells in excel vba
提问by apkdsmith
I'm creating a formula in Excel VBA of the form
我在表单的 Excel VBA 中创建一个公式
myformula(cell1, cell2, range)
where the range looks something like the first 7 columns of this:
其中范围看起来像这个的前 7 列：
V1 D2 V2 D3 V3 D4 V4 RESULT
0.5 2 0.7
There will always be a value in the first column, which will be a value between 0 and 1 inclusive. All subsequent columns may be empty, but if there is a value in D2 there will also be a value in V2 and so on.
第一列中总会有一个值，该值介于 0 和 1 之间（包括 0 和 1）。所有后续列可能为空，但如果 D2 中有值，则 V2 中也会有值，依此类推。
My formula goes in the RESULT column and involves summing the figures in the V columns but only if the preceding D column is filled. i.e. in this case it would calculate V1 + V2 but ignore V3 and V4.
我的公式在 RESULT 列中，涉及对 V 列中的数字求和，但前提是前面的 D 列已填充。即在这种情况下，它将计算 V1 + V2 但忽略 V3 和 V4。
The VB I have been using works with
我一直在使用的 VB 工作
if IsEmpty(D2)
to select the appropriate values and this works fine in most cases.
选择适当的值，这在大多数情况下都可以正常工作。
However, I also want to be able to apply this formula to cases where the V and D values are calculated by a formula. The formula is conditional, outputting either a number, or "" (something like IF(A2="","",3)) So the cell range looks identical but is no longer considered truly empty by VB.
但是，我也希望能够将此公式应用于通过公式计算 V 和 D 值的情况。该公式是有条件的，输出一个数字或“”（类似于IF(A2="","",3)），因此单元格范围看起来相同，但不再被 VB 视为真正的空。
I'm looking for ways to work around this. I realise that the result of a formula will never be truly empty but is there a way of making the contents of the cell "empty enough" for VB to understand it as empty? Alternatively is there a different condition I can use to pick up the empty cells - but I must be able to differentiate between zero values and empty cells.
我正在寻找解决此问题的方法。我意识到公式的结果永远不会真正为空，但是有没有办法使单元格的内容“足够空”以便 VB 将其理解为空？或者，我可以使用不同的条件来拾取空单元格 - 但我必须能够区分零值和空单元格。
ETA: I am struggling to implement Stepan1010's solution. This is what I'm doing - it may be wrong:
ETA：我正在努力实施 Stepan1010 的解决方案。这就是我正在做的 - 它可能是错误的：
Public Function YEARAV(sdate As Date, edate As Date, yearrange As Range) As Variant
Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant
Dim v4 As Variant
Dim b As Date
Dim c As Date
Dim d As Date
v1 = yearrange.Cells(1, 1)
v2 = yearrange.Cells(1, 3)
v3 = yearrange.Cells(1, 5)
v4 = yearrange.Cells(1, 7)
b = yearrange.Cells(1, 2)
c = yearrange.Cells(1, 4)
d = yearrange.Cells(1, 6)
total_days = edate - sdate
a = sdate
e = edate
If Range(yearrange.Cells(1, 6)).Value = vbNullString Then
d = edate
If Range(yearrange.Cells(1, 4)).Value = vbNullString Then
c = edate
If Range(yearrange.Cells(1, 2)).Value = vbNullString Then
b = edate
End If
End If
End If
YEARAV = ((b - a) * v1 + (c - b) * v2 + (d - c) * v3 + (e - d) * v4) / total_days
End Function
I've also tried just using If b = vbNullStringetc as well.
我也试过只使用If b = vbNullString等。
回答by Stepan1010
For zero you can just check if Range("D2").Value = 0
对于零，您可以检查是否 Range("D2").Value = 0
To check if a formula is returning a blank you can use if Range("D2").Value = vbNullString
要检查公式是否返回空白，您可以使用 if Range("D2").Value = vbNullString
You can also normally get away with Range("D2").Value = ""(although some diehard vba people will probably recommend vbNullString instead)
您通常也可以逃脱Range("D2").Value = ""（尽管一些顽固的 vba 人可能会推荐 vbNullString 代替）
回答by David Zemens
You could create a custom function. Use the function below, and you can call it like:
您可以创建自定义函数。使用下面的函数，你可以这样调用它：
Debug.Print IsCellFormulaEmpty(Range("D2"))
Debug.Print IsCellFormulaEmpty(Range("D2"))
Here is the Function:
这是函数：
Function IsCellFormulaEmpty(c As Range) As Boolean
Dim myVal As String
myVal = Trim(c.Value)
Select Case myVal
Case Empty
IsCellFormulaEmpty = True
Case Else
IsCellFormulaEmpty = False
End Select
End Function
I tested this on constant value of 6, a formula =IF(D24=C24,C23,"")(which returned a null string) and a constant value of 0. The results are False, True, Falseas expected.
我在常数值6、公式=IF(D24=C24,C23,"")（返回空字符串）和常数值0. 结果False, True, False如预期。
回答by tigeravatar
Generally speaking, try to avoid VBA when you can. In which case, given a data setup like this:
一般来说，尽量避免使用 VBA。在这种情况下，给定这样的数据设置：
The formula in cell H2 and copied down is:
单元格 H2 中的公式复制下来是：
=A2+SUMPRODUCT(--(B2:F2<>""),--(ISNUMBER(SEARCH("d",$B:$F))),C2:G2)
