CODEWITHSUNDEEP
excelvba
Christian
Christian

Reputation: 303

What is the difference between =Empty and IsEmpty() in VBA (Excel)?

I have used the following VBA code:

Do While .Cell(i,1) <> Empty
  ' doing things
  i = i+1
Loop

to iterate through columns (with Double/Integer values) in Excel. Then I found a case where the test evaluates to False whenever the value of the cell is 0. I have no idea what the difference is between this case and the working ones.

If I change the test to:

Do While Not IsEmpty(.Cell(i,1))
  ..
Loop

it works fine. So my question is: What is the difference between how IsEmpty() and =Empty is evaluated? In what cases will =Empty evaluate to True for cells with value 0?

Upvotes: 30

Views: 133506

Answers (3)

Justin Self
Justin Self

Reputation: 6265

Empty refers to a variable being at its default value. So if you check if a cell with a value of 0 = Empty then it would return true.

IsEmpty refers to no value being initialized.

In a nutshell, if you want to see if a cell is empty (as in nothing exists in its value) then use IsEmpty. If you want to see if something is currently in its default value then use Empty.

Upvotes: 30

iDevlop
iDevlop

Reputation: 25262

From the Help:
IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False. False is always returned if expression contains more than one variable.
IsEmpty only returns meaningful information for variants.

To check if a cell is empty, you can use cell(x,y) = "".
You might eventually save time by using Range("X:Y").SpecialCells(xlCellTypeBlanks) or xlCellTypeConstants or xlCellTypeFormulas

Upvotes: 4

nikola-miljkovic
nikola-miljkovic

Reputation: 670

I believe IsEmpty is just method that takes return value of Cell and checks if its Empty so: IsEmpty(.Cell(i,1)) does -> 

return .Cell(i,1) <> Empty

Upvotes: -4

Related Questions