Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Macro (Office 97)

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Office 97)

    You could use:

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

    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Office 97)

    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

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Office 97)

    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.
    Legare Coleman

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Macro (Office 97)

    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

  6. #6
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Office 97)

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

  7. #7
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Office 97)

    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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Macro (Office 97)

    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

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Office 97)

    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.
    Legare Coleman

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Macro (Office 97)

    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.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Macro (Office 97)

    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

  12. #12
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Office 97)

    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)

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Macro (Office 97)

    <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

  14. #14
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Office 97)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •