Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Empty cell test in VBA (ExcelXP)

    How to I test if an Excel cell is empty in VBA??

    Now I do:

    If ActiveCell.HasFormula Then MsgBox ActiveCell.Address & " Has formula"

    On Error GoTo NoComm
    If ActiveCell.Comment.Text = "Dummy" Then
    End If
    MsgBox ActiveCell.Address & " Has comment"
    NoComm:
    On Error GoTo 0

    If ActiveCell.Text <> "" Then MsgBox ActiveCell.Address & " Has text"


    Guess I could also test for:
    * background color and see if that's equal to the default setting in the current sheet (how do I get that?)
    * foreground color and see if that's equal to the default setting in the current sheet (how do I get that?)
    * any lines (borders)
    * Conditional formatting
    * Cell validation

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

    Re: Empty cell test in VBA (ExcelXP)

    I don't know if this is still relevant, but it depends on what you meant by "empty". Do you really need to know all those things you mention?

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Empty cell test in VBA (ExcelXP)

    CBool(Application.WorksheetFunction.CountBlank(Act iveCell))

    might be a better generic way to see if the cell has any content, but it doesn't test for comments, formatting, etc.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty cell test in VBA (ExcelXP)

    Yeah... I want to check if a cell in a matrix is really empty (except for the borders). In programming and implementing what I mentioned myself I believe I have this covered now (just had hoped there would be a more elegant way)...

    Here's the code I use now (I realize now there is some code optimization possible ;-)):

    Function CellEmpty() As Boolean
    'test if active cell is empty (except borders)
    CellEmpty = True
    '
    If ActiveCell.Text <> "" Then CellEmpty = False: Exit Function
    If ActiveCell.HasFormula Then CellEmpty = False: Exit Function
    If ActiveCell.Interior.ColorIndex <> xlColorIndexNone Then CellEmpty = False: Exit Function
    If ActiveCell.Font.ColorIndex <> xlColorIndexAutomatic Then CellEmpty = False: Exit Function
    If ActiveCell.FormatConditions.Count <> 0 Then CellEmpty = False: Exit Function
    '
    On Error GoTo NoComm
    If ActiveCell.Comment.Text = "Dummy" Then DoEvents
    CellEmpty = False
    Exit Function
    NoComm:
    On Error GoTo 0
    Resume Go1
    '
    Go1:
    On Error GoTo NoVal
    If ActiveCell.Validation.Formula1 = "Dummy" Then DoEvents
    CellEmpty = False
    NoVal:
    On Error GoTo 0
    End Function

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Empty cell test in VBA (ExcelXP)

    Might this be a simpler way of testing for a Comment?

    On Error Resume Next
    If Not ActiveCell.Comment Is Nothing Then CellEmpty = False: Exit Function
    On Error GoTo 0
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty cell test in VBA (ExcelXP)

    Yes, thanks.. made some more changes... here's how things look now (pity I cannot test validation present without error-trapping):

    Function CellEmpty() As Boolean
    'test if active cell is empty (except borders)
    CellEmpty = False
    '
    If ActiveCell.Text <> "" Then Exit Function
    If ActiveCell.HasFormula Then Exit Function
    If ActiveCell.Interior.ColorIndex <> xlColorIndexNone Then Exit Function
    If ActiveCell.Font.ColorIndex <> xlColorIndexAutomatic Then Exit Function
    If ActiveCell.FormatConditions.Count <> 0 Then Exit Function
    If Not ActiveCell.Comment Is Nothing Then Exit Function
    '
    On Error GoTo NoVal
    If ActiveCell.Validation.Formula1 = "Dummy" Then DoEvents
    Exit Function
    NoVal:
    CellEmpty = True
    End Function

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Empty cell test in VBA (ExcelXP)

    John,

    [Edit: Your edit beat me to the punch! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>]

    Per MSDN:
    <hr>Data validation type. Can be one of the following XlDVType constants: xlValidateCustom, xlValidateDate, xlValidateDecimal, xlValidateInputOnly, xlValidateList, xlValidateTextLength, xlValidateTime, or xlValidateWholeNumber. Read-only Long.<hr>
    xlValidateInputOnly has value 0, incidentally.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Empty cell test in VBA (ExcelXP)

    Thanks anyway. This was an interesting learning experience. To repeat myself, it appears that every cell in a workbook has a Data Validation object with Type set to xlValidateInputOnly. Do you have any ideas for ErikJan?

    I think I'll post the list of 2003 constants in the Excel Forum, since I always forget how to find them in the Help..
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Empty cell test in VBA (ExcelXP)

    <P ID="edit" class=small>(Edited by JohnBF on 29-Sep-04 09:19. )</P>Interesting, I didn' realize that every cell returns a validation object, even if the parameters haven't been set.

    Edited! My wild guess is wrong.

    In a wild guess, it looks like

    If ActiveCell.Validation.Type = 0 Then Exit Sub

    may do what you need, but then I would expect

    If ActiveCell.Validation.Type Then Exit Sub

    to work the same, yet it doesn't.


    OK, found the constants for Validation.Type:

    <table border=1><td align=center>Constant</td><td align=center>Value</td><td>xlValidateCustom</td><td align=right>7</td><td>xlValidateDate</td><td align=right>4</td><td>xlValidateDecimal</td><td align=right>2</td><td>xlValidateInputOnly</td><td align=right>0</td><td>xlValidateList</td><td align=right>3</td><td>xlValidateTextLength</td><td align=right>6</td><td>xlValidateTime</td><td align=right>5</td><td>xlValidateWholeNumber</td><td align=right>1</td></table>
    Apparently evey cell has a Validation.Type of zero until it is set to something else, so your way is probably the best!
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Empty cell test in VBA (ExcelXP)

    Unfortunately not without error handling - using Intersect(Activecell, activesheet.cells.specialcells(xlCellTypeAllValida tion)) still produces an error if there is no validation.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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