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

 

[lenze]

#1
IsNumeric(Cells(1,1))

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

lenze

 

[pgc01]

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.

 

[fab54]

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

 

[fab54]

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

HowMany = Application.WorksheetFunction.SumProduct(--IsNumeric(rng))

but didn't work

Any suggestions?

 

[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).

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.

 

[fab54]

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

Great suggestions guys

 

[ZVI]

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:

<font face=Courier New>
' 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</FONT>

Regards,
Vladimir

 

[pgc01]

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.

 

[ZVI]

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:

<font face=Courier New>
' 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</FONT>

Regards,
Vladimir