Page 1 of 2 12 LastLast
Results 1 to 10 of 15

VBA: how to check if the value of a cell is a number

This is a discussion on VBA: how to check if the value of a cell is a number within the Excel Questions forums, part of the Question Forums category; Hi All, I am a newbie with VBA and I have 2 simple question...but I couldn't find the answers in ...

  1. #1
    New Member
    Join Date
    Nov 2008
    Posts
    30

    Default VBA: how to check if the value of a cell is a number

    Hi All,
    I am a newbie with VBA and I have 2 simple question...but I couldn't find the answers in the forum or by googling it

    1) check if in cell A1 there is a number. Something like
    Code:
    If .Cells(1,1).Value is number then ....
    2) how to get the "code" of cell A1 in VBA (i.e =code(A1)).I tried
    Code:
    .Cell(1,1).Code
    but it didn't work.

    Many thanks for your help.

    Peace

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: VBA: how to check if the value of a cell is a number

    #1
    IsNumeric(Cells(1,1))

    #2
    What do you mean by code? Perhaps
    Application.WorkSheetFunction.Code(Range("A1"))

    lenze
    Last edited by lenze; Jul 22nd, 2009 at 07:27 PM.
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  3. #3
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,776

    Default Re: VBA: how to check if the value of a cell is a number

    Hi

    1)

    Can you be more specific?

    Do you mean like the worksheet function IsNumber()?

    Questions:
    - If the cell has text convertible to a number like the string "123" do you want True or False?
    - If the cell has a date, the worksheet function IsNumber() would give you a True. Is this what you want?


    2)

    I guess you mean the function Asc(). It can also be its big sister AscW() In case of a Unicode character.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  4. #4
    New Member
    Join Date
    Nov 2008
    Posts
    30

    Default Re: VBA: how to check if the value of a cell is a number

    Thank you for your answers!

    pgc01,
    - If it's a date I would like to get FALSE
    - If I get a string "123" I would prefer TRUE (but if it's too complicated, FALSE will do it).

    2) by code I mean the worksheet function =Code(), so I'm guessing that your suggestion Asc(Cell(1,1)) will do the job

    Thank you guys

  5. #5
    New Member
    Join Date
    Nov 2008
    Posts
    30

    Default Re: VBA: how to check if the value of a cell is a number

    I have a related question:
    1a) I would like to count how many numbers (IsNumeric() will do) different from 0 I have in a given range, say A1:A5.
    In the worsheet I would write =SUMPRODUCT(--Isnumber(A1:A5), (A1:A5<>0)).
    To find the numbers (even 0), as a first step, I tried
    Code:
    HowMany = Application.WorksheetFunction.SumProduct(--IsNumeric(rng))
    but didn't work

    Any suggestions?
    Last edited by fab54; Jul 22nd, 2009 at 08:26 PM.

  6. #6
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,776

    Default Re: VBA: how to check if the value of a cell is a number

    Quote Originally Posted by fab54 View Post
    - If it's a date I would like to get FALSE
    - If I get a string "123" I would prefer TRUE (but if it's too complicated, FALSE will do it).
    Use IsNumeric() as Lenze suggested. It accepts numbers and strings convertible to numbers and refuses dates.
    Remark: it also accepts booleans, if you think it's relevant to your problem test against it.

    P. S. Just saw your other post.

    Instead of the worksheet function SumProduct() you can use a loop.
    Last edited by pgc01; Jul 22nd, 2009 at 08:40 PM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  7. #7
    New Member
    Join Date
    Nov 2008
    Posts
    30

    Default Re: VBA: how to check if the value of a cell is a number

    Thank you!
    Managed to create the loop...yay!! (was quite easy though)

    Great suggestions guys

  8. #8
    ZVI
    ZVI is offline
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    2,751

    Default Re: VBA: how to check if the value of a cell is a number

    Take into account that string "1D2" is recognized as numeric equal to 1*10^2 = 100.
    Therefore IsNumeric("1D2") = True
    To avoid it you can use: IsNumeric(Replace(ActiveCell, "D", "?")

    The code below can help you:
    Code:
    
    ' Count the numerical values in the Rng range
    ' VBA usage:
    '   NumsCount(Range("A1:A5"))       <-- Zeroes are not numerical
    '   NumsCount(Range("A1:A5"), True) <-- Zeroes are numerical
    ' Formula usage: =NumsCount(A1:A5)
    Function NumsCount(Rng As Range, Optional UseZero As Boolean) As Long
      Dim arr, v
      arr = Rng
      If Not IsArray(arr) Then ReDim arr(0): arr(0) = Rng
      For Each v In arr
        If IsNum(v) Then
          If UseZero Then
            NumsCount = NumsCount + 1
          ElseIf v <> 0 Then
            NumsCount = NumsCount + 1
          End If
        End If
      Next
    End Function
    
    ' The same as IsNumeric() but different for strings like "1D2", and skips the boolean values
    ' If NumOnly=True then strings are not recognised as numeric at all.
    ' If UseD=True then strings like "1D2" are recognised as numeric.
    Function IsNum(TxtOrNum, Optional NumOnly As Boolean, Optional UseD As Boolean) As Boolean
      Select Case VarType(TxtOrNum)
        Case 2 To 6, 14
          ' Any type of numbers
          IsNum = True
        Case 8
          ' vbString
          If Not NumOnly Then
            Dim d As Double
            If Not UseD Then
              If InStr(UCase(TxtOrNum), "D") > 0 Then Exit Function
            End If
            On Error Resume Next
            d = TxtOrNum
            IsNum = Err = 0
          End If
      End Select
    End Function
    Regards,
    Vladimir
    Last edited by ZVI; Jul 22nd, 2009 at 09:23 PM.

  9. #9
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,776

    Default Re: VBA: how to check if the value of a cell is a number

    Hi Vladimir

    Good idea, your IsNum().

    Maybe you want to deal also with another problem. The vba does not care about commas in expressions when it converts to numbers.

    For example if in a cell you have "12,3,45" you would say it's a list, but vba and your IsNum() will say it's a valid number. Also for "1,23,4.5,67", if you assign it to a double you get 1234.567, the commas don't matter to vba, but you would not say that's a number.

    In case of a string maybe the best is to use regular expressions and check the possible formats.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  10. #10
    ZVI
    ZVI is offline
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    2,751

    Default Re: VBA: how to check if the value of a cell is a number

    Quote Originally Posted by pgc01 View Post
    Hi Vladimir

    Good idea, your IsNum().

    Maybe you want to deal also with another problem. The vba does not care about commas in expressions when it converts to numbers.

    For example if in a cell you have "12,3,45" you would say it's a list, but vba and your IsNum() will say it's a valid number. Also for "1,23,4.5,67", if you assign it to a double you get 1234.567, the commas don't matter to vba, but you would not say that's a number.

    In case of a string maybe the best is to use regular expressions and check the possible formats.
    Hi PGC,

    May be additional checking of comma in string is enough for solving the list recognising issue:
    Code:
    
    ' The same as IsNumeric() but different for strings like "1D2", and skips the boolean values
    ' If NumOnly=True then strings are not recognised as numeric at all.
    ' If UseD=True then strings like "1D2" are recognised as numeric.
    ' Comma in string means the list and recognised as not numerical
    Function IsNum(TxtOrNum, Optional NumOnly As Boolean, Optional UseD As Boolean) As Boolean
      Select Case VarType(TxtOrNum)
        Case 2 To 6, 14
          ' Any type of numbers
          IsNum = True
        Case 8
          ' vbString
          If Not NumOnly Then
            If InStr(TxtOrNum, ",") > 0 Then Exit Function ' <- Comma means the list
            Dim d As Double
            If Not UseD Then
              If InStr(UCase(TxtOrNum), "D") > 0 Then Exit Function
            End If
            On Error Resume Next
            d = TxtOrNum
            IsNum = Err = 0
          End If
      End Select
    End Function
    Regards,
    Vladimir
    Last edited by ZVI; Jul 22nd, 2009 at 10:16 PM.

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com