Category: VBA Functions and Keywords

The Range.Find method and a FindAll function

Posted on by Tushar Mehta

Two things that could be better about the Range.Find method have been 1) up-to-date and correct documentation, and 2) adding the UI’s ‘Find All’ capability to the Excel Object Model. As of Office 2013 neither has happened.

Consequently, every time I want to use the Find method, I continue to have to jump through hoops to figure out the correct values for the different arguments.

I also discovered that FindNext does not work as expected when one wants to search for cells that meet certain format criteria. Consequently, I updated my long available FindAll function so that it works correctly with format criteria.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1001%20range.find%20and%20findall.shtml

Tushar Mehta

Testing for Empty Cells

Posted on by Dick Kusleika

To test for empty cells, use the IsEmpty function. IsEmpty takes one argument, a variable, and returns True if that variable contains nothing (Technically, I think it returns True if the variable is uninitialized). While it’s true that you can pass variables to IsEmpty, you can also pass object’s properties, specifically the Value property of the Range object.

Many times you will see programmers test for a zero length string, like this

If Range(“A10”).Value = “” Then

In 99% of the cases, that will work. However, if the cell contains a single quote and nothing else, then it will contain a zero length string, but will not really be empty. To test for true emptiness, use IsEmpty on the Value property.

Sub TestForEmpty()

Dim sPrompt As String
Dim rRng As Range

Set rRng = Sheet1.Range("A10")
sPrompt = "Range contains "

rRng.ClearContents

MsgBox sPrompt & "nothing = " & IsEmpty(rRng.Value) & vbNewLine & _
sPrompt & "a zero length string = " & CBool(rRng.Value = "")

rRng.Value = "'" 'single quote

MsgBox sPrompt & "nothing = " & IsEmpty(rRng.Value) & vbNewLine & _
sPrompt & "a zero length string = " & CBool(rRng.Value = "")

End Sub

Update: A newsgroup post by Otto Moehrbach prompted me to look a little deeper into how IsEmpty works. I wanted to determine why a cell with a formula that returns an empty string behaves differently than an empty cell. I set up a watch for A1 and B1 to see what was going on. A1 contains the formula =”” and B1 contains nothing at all.

watch window showing status of A1 and B1

A1 is a Variant/String and B1 is a Variant/Empty which obviously accounts for the difference. There’s nothing too shocking here, it’s just an interesting glimpse into the inner workings of VBA. Although the “interesting” part is debatable.