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

[fab54]

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

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

.Cell(1,1).Code

but it didn't work.

Many thanks for your help.

Peace

 

[pgc01]

May be additional checking of comma in string is enough for solving the list recognising issue:

Vladimir

I think it's not enough. The comma is OK if it's used as a thousands separator

1,234 = 1234
12,34 - Not number
1,234,56 - Not number
1,234,567.3 = 1234567.3

The comma is OK if it apperars after every third number to the left of the decimal dot or, if there are no decimals, after every third number counting from the right.

This is important because the strings may be numbers imported from an application that exported them formatted with the thousands separator.

 

[ZVI]

Thanks PGS, your parsing rules now are clear for me.
For regret usage of regular expressions is too slow.
But seems that this is working properly:

<font face=Courier New>
' The same as IsNumeric() but skips the text like "1D2" and skips the boolean values.
' If NumOnly=True (default is False) then strings are not recognised as numeric at all.
' If UseComma=True (default is True) then comma can be the thousands separator in the text.
' Usage: Debug.Print IsNum("1,234"), IsNum("12,34"), IsNum("1,234,56"), IsNum("1,234,567.3")
Function IsNum(TxtOrNum, _
               Optional NumOnly As Boolean = False, _
               Optional UseComma As Boolean = True _
               ) 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 Len(TxtOrNum) > 40 Then Exit Function
      If InStr(UCase(TxtOrNum), "D") > 0 Then Exit Function
      Dim v, b As Boolean, iDot As Long, iComma As Long, d As Double
      Static ThSep As String  ' <- depends on localization
      iDot = InStr(TxtOrNum, ".")
      iComma = InStrRev(TxtOrNum, ",")
      If iDot > 0 And iComma > iDot Then Exit Function
      If UseComma Then
        For Each v In Split(Mid$(TxtOrNum, 1, IIf(iDot > 0, iDot - 1, 40)), ",")
          If b Then
            If Len(v) <> 3 Or Not IsNumeric(v) Then Exit Function
          Else
            b = True
            If Len(v) = 0 Or Not IsNumeric(v) Then Exit Function
          End If
        Next
      Else
        If iComma > 0 Then Exit Function
      End If
      ' -> Use the localization language separators (can be skipped for US settings)
      If iComma > 0 Then
        If Len(ThSep) = 0 Then ThSep = Mid(Format(1000, "#,###"), 2, 1)
        TxtOrNum = Replace(TxtOrNum, ",", ThSep)
      End If
      If iDot > 0 Then Mid$(TxtOrNum, iDot) = Mid(1.2, 2, 1)
      ' <-
      On Error Resume Next
      d = TxtOrNum
      IsNum = Err = 0
    End If
  End Select
End Function</FONT>

Regards,
Vladimir

 

[pgc01]

Hi Vladimir

I've done some tests and it seems to work OK for the specs used. This will cover the usual needs for testing if the value is a number written as a number value or as a string.

Other specs could be used, like digit grouping also for the decimals, thousands separator space instead of comma, other regional settings, like 1,000,000 in England is 1.000.000 in France or 10,00,000 in India, but this would be a never ending story. Your function seems to cover the usual needs.

Cheers

 

[fab54]

Vlad thanks for your input!

Thank you guys, now I have a top class Macro

 

[ZVI]

PGC, your clear specs is more than half of result – thanks for that!
Agree with you on the "never ending story"
Ideally the localized decimal & thousands separators would be passed to the function as well as the ParamArray of expected formats patterns. It worth to go this way, may be I’ll think about it.

FAB, you are mostly welcome! Good question is another half of result, isn’t it?


Vlad