Results 1 to 9 of 9

Thread: IsNumeric and isEmpty... bug?

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Mar 2006
    Posts
    27

    IsNumeric and isEmpty... bug?

    Using isnumeric and isempty I get this odd result.

    I have this data pulled out from an empty cell in Excel with nothing in it.

    Using IsNumeric on the cell data I get true. Printing it out gives me nothing. Testing with IsEmpty I get false.

    What is in an empty cell that is a number, but prints out as nothing?

    I even tried printing the cell content out between two single quotes and got nothing printed out between the quotes...???

    Driving me nuts. I'm ready to bang my head against the wall.

    If anyone can help, that would be much appreciated.

  2. #2
    I'm about to be a PowerPoster!
    Join Date
    Jan 2005
    Location
    Everywhere
    Posts
    13,651

    Re: IsNumeric and isEmpty... bug?

    At a guess, do you have the Hide zero values option on?

  3. #3
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    Re: IsNumeric and isEmpty... bug?

    What is the cell formatted as? Numeric or ?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    Re: IsNumeric and isEmpty... bug?

    IsEmpty:

    Returns a Boolean value indicating whether a variable has been initialized.

    The required expression argument is a Variant containing a numeric or string expression. However, because IsEmpty is used to determine if individual variables are initialized, the expression argument is most often a single variable name.

    IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False. False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Mar 2006
    Posts
    27

    Re: IsNumeric and isEmpty... bug?

    The cells are formatted as numbers, When I print out the cells I get '' with the following code:

    debug.print "'" & wks.cells(7,7) & "'"

    This is the code I used to check it with:

    IsNumeric(wks.Cells(y, x) & wks.Cells(y + 3, x) & wks.Cells(y + 4, x) & _
    wks.Cells(y + 5, x) & wks.Cells(y + 6, x) & wks.Cells(6, 2) & wks.Cells(7, 2)) And _
    Not IsEmpty(wks.Cells(y, x) & wks.Cells(y + 3, x) & wks.Cells(y + 4, x) & _
    wks.Cells(y + 5, x) & wks.Cells(y + 6, x))

    The isnumeric part returns TRUE
    The isempty part returns FALSE
    When printed out, I get empty string. I also tried checking it with

    if wks.cells(7,7) = "" then
    end if

    but it fails this check too.

    RobDog888, thanks for the info. I misunderstood the use of isempty. It checks is a variant is initialized. I would have preffered the function be called isInit instead though... The cell are formatted as Numeric

    What type of data does wks.cells(x,y) return?

    penagate, zero value option is checked meaning it shows zero values.

  6. #6
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    Re: IsNumeric and isEmpty... bug?

    wks.cells(x,y) return?
    It returns a Range object
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  7. #7
    Frenzied Member cssriraman's Avatar
    Join Date
    Jun 2005
    Posts
    1,465

    Re: IsNumeric and isEmpty... bug?

    You can check whether the particular cell is empty or not as:
    VB Code:
    1. If Range("A1").Value = "" Then
    2.     Debug.Print "A1 Is Empty"
    3. Else
    4.     Debug.Print "A1 Is not Empty"
    5. End If

  8. #8
    Fanatic Member VBAhack's Avatar
    Join Date
    Dec 2004
    Location
    Sector 000
    Posts
    610

    Re: IsNumeric and isEmpty... bug?

    Quote Originally Posted by cssriraman
    You can check whether the particular cell is empty or not as:
    VB Code:
    1. If Range("A1").Value = "" Then
    2.     Debug.Print "A1 Is Empty"
    3. Else
    4.     Debug.Print "A1 Is not Empty"
    5. End If
    Or the following (to screen out blank spaces):

    VB Code:
    1. If Trim(Range("A1").Value) = "" Then
    2.     Debug.Print "A1 Is Empty"
    3. Else
    4.     Debug.Print "A1 Is not Empty"
    5. End If

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Mar 2006
    Posts
    27

    Re: IsNumeric and isEmpty... bug?

    I ended up using isempty(), but might change it later since its functionality is undefined for datatypes other than strings and used mainly for checking if a variable is initialized. Thanks for the suggestions they might come in handy later.

    However, wierdly enough isNumeric() will return true to a cell value that is empty with isEmpty().

    isNumeric(ExcelSheet.cells(1,1)) ==> returns true
    isEmpty(ExcelSheet.cells(1,1)) ==> returns false

Posting Permissions

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



Click Here to Expand Forum to Full Width