CODEWITHSUNDEEP
excelvba
excel34
excel34

Reputation: 799

How do I express "if value is not empty" in the VBA language?

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

Upvotes: 79

Views: 760988

Answers (8)

Aly Abdelaal
Aly Abdelaal

Reputation: 322

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

Upvotes: 0

Yamen Khazbak
Yamen Khazbak

Reputation: 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

Upvotes: -2

iDevlop
iDevlop

Reputation: 25262

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

Upvotes: 42

alejofv
alejofv

Reputation: 411

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

Upvotes: 9

Jon Crowell
Jon Crowell

Reputation: 22338

Use Not IsEmpty().

For example:

Sub DoStuffIfNotEmpty()
    If Not IsEmpty(ActiveCell.Value) Then
        MsgBox "I'm not empty!"
    End If
End Sub

Upvotes: 108

Marcand
Marcand

Reputation: 314

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.

Upvotes: 5

Todd Main
Todd Main

Reputation: 29153

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

Upvotes: 0

Anthony
Anthony

Reputation: 923

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

Upvotes: 3

Related Questions