Use of IsEmpty function

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
Hey guys,

Not really a problem, just a question that I’d like a few views on. Whenever I want to check if a cell is empty with VBA I generally use something like If Range(“A1”)=””. However I’ve noticed a lot of code on this site using IsEmpty(Range(“A1”)). While I quite like the idea of the IsEmpty function approach, when I look at the Help files the IsEmpty function doesn’t really seem to be designed to be used in this way, rather it is for checking whether a variable has been initialized. However, it does seem to work perfectly well. I guess my question is, is this a good technique, or are there some unknown pitfalls that I don’t know about?

Any thoughts would be welcome.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi Craig,

True that the IsEmpty function is generally reserved for testing variables, such as the Is Nothing method is reserved for testing objects. This doesn't preclude it's uses in other areas though. Although to test for a null string ( "" ) I'd suggest using the vbNullString, as in the end it will be more efficient for you. I believe it was NateO who did some extensive testing between "" and vbNullString; not sure where the thread is now, but I believe those were the results. The same priciples apply to, say a string text line break in using vbNewLine, vbCrLf or Chr(10). Some pro's and con's to each in certain circumstances, although for the most part they will perform identically.

In the case of testing if a range has any data residing in it I'd use the vbNullString, although sometimes (usually for lack of typing prowess/desire) I go with the ' = "" '.

A good test would be to write some placebo-type code that doesn't do anything. Declare a bunch of various variables. Step through your code with the F8 key. At various times hover your mouse cursor over the different variables and check their value. To be more in-depth, use the Immediate window and Watch window (right click variable --> Add Watch).

There is of course the obvious nature of the function and that is it returns a boolean type result. That is it will produce a True or False result. So these two statments are identical then ...

If IsEmpty(myVariable) = True Then

and

If IsEmpty(myVariable) Then

I guess what it really boils down to is 'are there any hinderences to the method to which you are employing' and 'personal preference'. I'm sure there are others with a much better explanation for you than I. Hope this helps.
 
Upvote 0
You are referring to Empty in two different contexts.

Variant variables can contain two special values - Empty (indicating the variable is empty) and Null (indicating no space is reserved in memory for the variable). IsEmpty, IsNull, along with VarType, IsObject, and IsError are how the data types in residence of a variant variable can be determined.

So that's that where variables are concerned, but IsEmpty is also a function to test the status of a cell or range. In a loop for example, this line
Do Until ActiveCell.Value = ""
would work when there is a zero length string in a cell, which would be the case if the cell were truly empty. However, it would also return a false positive when a zero length string is returned as by a formula such as
=IF(A1="College","Expensive","")
If A1 does not contain the value "College" the formula's host cell would be recognized as empty using the Do Until line for an empty string above, though the cell should not be regarded as empty, because a formula exists in the cell.

A safer route, which I almost always take, is
Do Until IsEmpty(ActiveCell)

Really the code you will write won't activate cells in a loop because it's inefficient, this was just used for demonstration purposes to point out the pitfalls of not using IsEmpty to take the ambiguity away from a cell's true status of being empty or occupied.
 
Upvote 0
Thanks for taking the time guys. Certainly gives me a few different options to think about and play around with.
 
Upvote 0
There is also a very important use for IsEmpty in UDFs.
Because of the way Excel's calculation algorithm works a UDF may be called when one or more of the input arguments to the function have not yet been calculated. When this happens the input argument is empty until it has been recalculated.
Because UDF code can cause problems or consume calculation time when processing uncalculated cells/arguments it is a good idea where possible to test the inputs using IsEmpty, and exit the function if true.

If ISEMPTY(Cell.Value) AND Len(Cell.formula)>0 then exit function

using this kind of test will test if the cell contains a function and has not yet been recalculated.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top