Avoid Type Mismatch Error when using CDBL()

[ExcelMonkey]

Hi I am testing for whether or not a string variable is a number or not.
When I try:

IsNumeric(CDbl(var1))

It fails giving a Type Mismatch error when the variable is actually not a
number. I would like this to return a True/False. How do I do this?

Thanks

EM

 

[Dave Peterson]

You could try:

if isnumeric(var1) then

But be aware that VBA's isnumeric is very forgiving. If the string looks like
it could be interpreted as a number, then you'll see True.

"9E3"
(9*10*10*10 in scientific notation)

 

[ExcelMonkey]

That does not work either

?IsNumeric(Val("Summary"))
True

I will use a Regex function which returns a boolean:

If ReturnWordOnly("50") = True Then
'Do something
End if

Private Function ReturnWordOnly(y As String) As Boolean
Dim Match As Boolean
Dim objRegExp As Object
Dim tempstring As Variant
Dim Counter As Double

Set objRegExp = CreateObject("Vbscript.RegExp")
objRegExp.Global = True
objRegExp.IgnoreCase = IgnoreCase
objRegExp.MultiLine = MultiLine
objRegExp.Pattern = "\D+" 'word

Match = objRegExp.Test(y)

ReturnWordOnly = Match

End Function

 

[Dave Peterson]

Why did you add the val function?

val("summary")
returns a 0

So isnumeric(0) is gonna be true.

How about just using:
?isnumeric("Summary")