Testing empty cells in excel vba

Question

I'm creating a formula in Excel VBA of the form

myformula(cell1, cell2, range)

where the range looks something like the first 7 columns of this:

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.

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.

The VB I have been using works with

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.

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.

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

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.

You could use the Or operator... e.g. If IsEmpty(D2) Or D2="" Then. Add as many Ors as you want to describe what you consider is "empty enough". — Jean-François Corbett
– Jean-François Corbett, Commented Sep 19, 2013 at 19:15

Stepan1010 · Accepted Answer · 2013-09-19 13:50:03Z

1

For zero you can just check if Range("D2").Value = 0

To check if a formula is returning a blank you can use 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)

answered Sep 19, 2013 at 13:50

Stepan1010

3,1341 gold badge18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ajor Over a year ago

I tried using vbNullString but it doesn't work with the formulae. It still works fine with the empty cells but not with those set to "". Same for using ="".

Stepan1010 Over a year ago

@apkdsmith You can also try if LEN(Range("D2").Value) = 0

Stepan1010 Over a year ago

@apkdsmith you cannot use vbNullString in a formula directly - that is only for VBA. - not sure if you knew that. - ISBLANK() is another one you might want to try if you are only worried about your formula.

ajor Over a year ago

I'm not using it in a formula directly? I'm writing a formula in VBA which I want to use it in.

ajor Over a year ago

It may be that I'm not implementing it properly though - I'll just edit my post to show what I'm trying to do.

|

David Zemens · Accepted Answer · 2013-09-19 13:57:33Z

1

You could create a custom function. Use the function below, and you can call it like:

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, False as expected.

answered Sep 19, 2013 at 13:57

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Comments

tigeravatar · Accepted Answer · 2013-09-19 14:43:03Z

0

Generally speaking, try to avoid VBA when you can. In which case, given a data setup like this:

enter image description here

The formula in cell H2 and copied down is:

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

answered Sep 19, 2013 at 14:43

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Collectives™ on Stack Overflow

Testing empty cells in excel vba

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related