How do I express "if value is not empty" in the VBA language?
ExcelVbaExcel Problem Overview
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
Excel Solutions
Solution 1 - Excel
Use Not IsEmpty()
.
For example:
Sub DoStuffIfNotEmpty()
If Not IsEmpty(ActiveCell.Value) Then
MsgBox "I'm not empty!"
End If
End Sub
Solution 2 - Excel
It depends on what you want to test:
- for a string, you can use
If strName = vbNullString
orIF strName = ""
orLen(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") = ""
orIsEmpty(myRange)
Extended discussion available here (for Access, but most of it works for Excel as well).
Solution 3 - Excel
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
Solution 4 - Excel
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.
Solution 5 - Excel
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
Solution 6 - Excel
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
Solution 7 - Excel
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
Solution 8 - Excel
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