Results 1 to 11 of 11
  1. #1
    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

    IsBlank Function in VB (XL(97))

    I don't recall this question coming up, but is there a general consensus on how to check to see if a cell is BLANK/EMPTY in VB. In excel you can use "isblank", but there seems to be no equivalent in VB and it is NOT a part of application.worksheetfunction.

    In code, most times people seem to compare to a null string (""), but this is NOT the same, since many formulas could return a null string [="", the simplest, though many IFs do this to "hide errors" (eg =if(b1<>0,a1/b1,"")]. Also just putting a single quote (') in a cell will still have it equal to a null though the cell is NOT empty!

    The only way I have come up with is to check 3 criteria:
    Value = ""
    It does NOT have a formula
    and the Prefix Character = ""

    With those you you could create your own function:

    <pre>Function IsBlank(rng As Range)
    With rng.Cells(1)
    IsBlank = .Value = "" And _
    Not .HasFormula And _
    .PrefixCharacter = ""
    End With
    End Function</pre>


    Are there other criteria? Is there another way to test for truly "blank/empty" cells in a worksheet?

    Steve

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsBlank Function in VB (XL(97))

    You will also occasionally get NULL values - particularly if you import from Access.
    Gre

  3. #3
    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: IsBlank Function in VB (XL(97))

    Are they equivalent to cverr(xlerrnull)?
    If so they could be trapped in VB and the cells then made BLANK if desired.

    They shouldn't show up as "blank" in excel since they contain a value.

    Steve

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsBlank Function in VB (XL(97))

    We had a thread about these on this Board a couple of years ago. On the surface, they look completely blank, but if you try =1+[cellref] to any of these it comes up #VALUE!. Can you let us know how the attached (slimmed down) example fits into the picture?
    Gre

  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: IsBlank Function in VB (XL(97))

    Steve, do you intend to count cells with only a space or spaces as blank, and do you intend to count cells with a prefix as blank? If so, this -doesn't- do what you want:

    Public Function isblank(rng As Range) As Boolean
    On Error Resume Next
    Set rng = Intersect(rng.Cells(1), rng.SpecialCells(xlCellTypeBlanks))
    isblank = Not rng Is Nothing
    End Function
    -John ... I float in liquid gardens
    UTC -7DS

  6. #6
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: IsBlank Function in VB (XL(97))

    The code that has generally worked for me is...

    x = Len(Range('A1"))

    or the following to be thorough...

    x = Len(Range("A1").Formula)

    New files should always be trimmed and non-printing chararcters removed before working on them.
    It saves a lot of headaches.

    Jim Cone
    San Francisco, CA

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IsBlank Function in VB (XL(97))

    Steve,

    Although ISBLANK < is NOT a part of application.worksheetfunction >, as you say, you can access it via Excel VBA by using Excel's Evaluate method as follows,

    Application.Evaluate("ISBLANK(A1)")

    or simply

    [ISBLANK(A1)].

    The following approach should work from VB or any non Excel VBA :

    Dim xlApp
    Set xlApp = GetObject(, "Excel.application")
    MsgBox xlApp.Evaluate("ISBLANK(A1)")
    'or MsgBox xlApp.[ISBLANK(A1)]

    Andrew

  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: IsBlank Function in VB (XL(97))

    No I wasn't trying to count them, I was looking for a function (which I think I have) which can tell me if a given cell is empty as compared to having a null string or a prefix. A cell with a prefix is NOT blank and a cell with a null string is NOT blank.

    My question was more, am i missing anything?
    Steve

  9. #9
    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: IsBlank Function in VB (XL(97))

    Len(Range("A1")) will be = 0 for null strings, blank cells and the single quote in a cell not just blank cells (it is equiv to testing if value = "")

    Len(Range("A1").Formula) will = 0 for blank and the single quote character in a cell

    Steve

  10. #10
    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: IsBlank Function in VB (XL(97))

    Yes!
    [ISBLANK(A1)]

    is simple and works much better than a custom function!

    Steve

  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: IsBlank Function in VB (XL(97))

    Your spreadsheet lead me to an alternative to using Andrew's suggestion of evaluate:
    When a cell is blank the VALUE of the cell is literally empty and you can check that with the isempty function in vb:
    <pre>isempty(range("a1").value)</pre>

    will be true if A1 is blank, otherwise it will be false.

    In your example, the cells are NOT empty/blank. They LITERALLY contain JUST a null string. You can accomplish the same thing by having a formula which yields the null string (eg ="") and then copy - paste special values. This cell is NOT blank, it has no formula, it has no prefix character. It has a value of "" (the null string).
    A BLANK cell is also NOT blank, it has no formula, it has no prefix character. BUT it has NO value: the value (and value2) property is EMPTY.
    1+Empty = 1
    1+"" (or any text string) = #value since you can NOT add a number and text

    Steve

Posting Permissions

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