78

How do I express the condition "if value is not empty" in the VBA language? Is it something like this?

"if value is not empty then..."
Edit/Delete Message
Teamothy
  • 2,000
  • 3
  • 16
  • 26
excel34
  • 789
  • 1
  • 6
  • 4
  • 11
    I summon Joel Spolsky! – Tamas Czinege Dec 31 '09 at 03:08
  • 2
    Worth noting, that this is something like the null value in other mainstream languages. Emtpy value IS NOT empty string (eg:"")... – Vajk Hermecz Jun 16 '13 at 21:50
  • @VajkHermecz Totally wrong ! In VBA a string initializes to "", an integer to 0, a boolean to False....Only Variants have a special behavior...which is still different from SQL `Null`. – iDevlop Apr 04 '16 at 13:30

8 Answers8

106

Use Not IsEmpty().

For example:

Sub DoStuffIfNotEmpty()
    If Not IsEmpty(ActiveCell.Value) Then
        MsgBox "I'm not empty!"
    End If
End Sub
Jon Crowell
  • 21,695
  • 14
  • 89
  • 110
42

It depends on what you want to test:

  • for a string, you can use If strName = vbNullString or IF strName = "" or Len(strName) = 0 (last one being supposedly faster)
  • for an object, you can use If myObject is Nothing
  • for a recordset field, you could use If isnull(rs!myField)
  • for an Excel cell, you could use If range("B3") = "" or IsEmpty(myRange)

Extended discussion available here (for Access, but most of it works for Excel as well).

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • I have a recordset with a `field` `kein aktueller Datensatz`. `Isnull` returns `false` which is `shit`.. – Timo Aug 25 '20 at 08:35
  • [Here](https://stackoverflow.com/a/17336418/1705829) is a `SO` solution to check for `null` in a `recordset field`. – Timo Aug 25 '20 at 11:24
9

Try this:

If Len(vValue & vbNullString) > 0 Then
  ' we have a non-Null and non-empty String value
  doSomething()
Else
  ' We have a Null or empty string value
  doSomethingElse()
End If
captainpete
  • 6,162
  • 3
  • 28
  • 26
alejofv
  • 411
  • 2
  • 6
5

Why not just use the built-in Format() function?

Dim vTest As Variant
vTest = Empty ' or vTest = null or vTest = ""

If Format(vTest) = vbNullString Then
    doSomethingWhenEmpty() 
Else
   doSomethingElse() 
End If

Format() will catch empty variants as well as null ones and transforms them in strings. I use it for things like null/empty validations and to check if an item has been selected in a combobox.

Marcand
  • 314
  • 2
  • 3
3

I am not sure if this is what you are looking for

if var<>"" then
           dosomething

or

if isempty(thisworkbook.sheets("sheet1").range("a1").value)= false then

the ISEMPTY function can be used as well

Anthony
  • 913
  • 3
  • 23
  • 32
0

Alexphi's suggestion is good. You can also hard code this by first creating a variable as a Variant and then assigning it to Empty. Then do an if/then with to possibly fill it. If it gets filled, it's not empty, if it doesn't, it remains empty. You check this then with IsEmpty.

Sub TestforEmpty()

    Dim dt As Variant
    dt = Empty

    Dim today As Date
    today = Date
    If today = Date Then
        dt = today
    End If

    If IsEmpty(dt) Then
        MsgBox "It not is today"
    Else
        MsgBox "It is today"
    End If

End Sub
Todd Main
  • 28,951
  • 11
  • 82
  • 146
0

You can use inputbox function in a for loop:

Sub fillEmptyCells()
    Dim r As Range
    Set r = Selection
    For i = 1 To r.Rows.Count
        For j = 1 To r.Columns.Count
            If Cells(i, j).Value = "" Then
                Cells(i, j).Select
                Cells(i, j).Value = InputBox( _
                    "set value of cell at column " & Cells(1, j).Value & _
                    " and row " & Cells(i, 1))
            End If
        Next j
    Next i
End Sub
Toni
  • 1,555
  • 4
  • 15
  • 23
Aly Abdelaal
  • 89
  • 6
-1

I think the solution of this issue can be some how easier than we imagine. I have simply used the expression Not Null and it worked fine.

Browser("micclass").Page("micclass").WebElement("Test").CheckProperty "innertext", Not Null
Michael Wycisk
  • 1,590
  • 10
  • 24
Yamen Khazbak
  • 1