Excel Macro (Office 97)

[mickey11030]

In an Excel macro, I test for a blank cell using the statement:

Do Until ActiveCell.Value = Empty

but I sometimes run into trouble because the cell is not empty, but contains spaces, which should be treated as empty. How can I most easily check for a cell that is either empty or contains only spaces?
Thanks
Michael Trombetta

[LegareColeman]

You could use:

<pre> Do Until TRIM(ActiveCell.Value) = ""
</pre>

[mickey11030]

Thank you, thank you. I had thought of writing a new loop, going through each cell and TRIMing it, but of course, your solution is 1000% more elegant and efficient.
By the way, is testing TRIM(ActiveCell.Value) = "" the same as TRIM(ActiveCell.Value) = Empty?
Thanks again,
Michael Trombetta

[LegareColeman]

Yes, there is a difference in using "" and EMPTY. There is no VBA defined variable named EMPTY. What you are doing only works because when you use EMPTY without defining (DIMing) it, VBA defaults it to a variable type Variant. If you don't assign a value to this variable, it defaults to an empty string when used in a string expression which is the same as "". If you ever assign a value to EMPTY, or create a function named EMPTY, your method will probably fail. If you put the statement below in the module above all procedure statements (or select the "Require Variable Definition" VBA Editor option, which is highly recommended), VBA will give you an error when you do something like this. This can save you hours of looking for errors in things like variable spelling.

[sdckapr]

If you truly want to test if the cell is empty (contains "nothing") as opposed to a null string or spaces or whatever you can use:
<pre>if isempty(activecell) then</pre>


Steve

[mickey11030]

Again, I want to thank everyone for their help.
Michael Trombetta

[mickey11030]

As a side issue to my question of spaces causing problems in my Excel macro, we had a discussion about Empty vs "". Legare Coleman suggested that my statement Do Until ActiveCell.Value = Empty, worked only because VBA created a variable named Empty. However, I've discovered the following macro works just fine:

Option Explicit

Sub Test()
If ActiveCell.Value = Empty Then
MsgBox ("Active Cell is empty")
Else
MsgBox ("Active Cell is not empty")
End If
End Sub

So Empty does seem to have a meaning in VBA

[sdckapr]

Your code says a cell is empty when the value is zero or if it contains a null string (along with actually being blank)

This works correctly:
<pre>Sub Test()
If IsEmpty(ActiveCell) Then
MsgBox ("Active Cell is empty")
Else
MsgBox ("Active Cell is not empty")
End If
End Sub</pre>


Steve

[LegareColeman]

It appears that I was incorrect, and Empty is a defined VBA constant since VBA also will not let me do:

<pre> Empty = 45
</pre>


Since I can't find any documentation on it, I am not positive how it is defined, but it does appear to be a null string. Therefore, what you were doing should work.

[HansV]

I typed Empty in the VB Editor in Excel 2002 and pressed F1. The online help came up with this definition:

<hr>The Empty keyword is used as a Variant subtype. It indicates an uninitialized variable value.<hr>

.No further information or examples. You can use it like this:

Sub TestEmpty()
Dim x As Long
Debug.Print (x = Empty)
x = 6
Debug.Print (x = Empty)
x = 0
Debug.Print (x = Empty)
End Sub

results in

True
False
True

Note that resetting x to 0 the default value for Longs, results in True for the test.

[sdckapr]

This also fits in with what I saw earlier.

Blank cells, 0, null string all yield "empty" as true

Blank cells because they are empty
0 because that is the "default value" for any numeric variable
null string because that is the "default value" for a string variable

All in all, it is not a good means to test for empty cells however, since I would not judge a null string or a zero as an empty cell.

Steve

[mickey11030]

Again, I want to thank everyone for their help. I wrote some code and discovered, to my amazement, that a cell with a 0 in it, returns True when tested
ActiveCell = Empty. So as Steve says, the surest way to test if a cell is truly empty is to use IsEmpty(ActiveCell)

[sdckapr]

<hr>I wrote some code and discovered, to my amazement, that a cell with a 0 in it, returns True when tested<hr>

You wrote more code? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

I just tested it using the test code you had in <post#=391354>post 391354</post#> putting a zero, a null or leaving the activecell blank <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Steve

[mickey11030]

What I meant was that I wrote a baby macro to test the various ideas we've been kicking around.

It's amazing how much I don't know about Excel macros!