在excelvba中测试空单元。-Testingemptycellsinexcelvba

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之间。所有后续的列可能都是空的，但是如果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.

我的公式在结果列中，涉及到对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的内容“足够空”，让VB理解为空吗?或者，我可以使用不同的条件来提取空的单元格，但是我必须能够区分零值和空单元格。

ETA: I am struggling to implement Stepan1010's solution. This is what I'm doing - it may be wrong:

我正在努力实施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 = vbNullString etc as well.

我也试过用If b = vbNullString等等。

3 个解决方案

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

=A2+SUMPRODUCT(--(B2:F2<>""),--(ISNUMBER(SEARCH("d",$B$1:$F$1))),C2:G2)