Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoid Type Mismatch Error when using CDBL()
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoid Type Mismatch Error when using CDBL()
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoid Type Mismatch Error when using CDBL()
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" wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoid Type Mismatch Error when using CDBL()
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") ExcelMonkey wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
runtime error 13 - type mismatch error in Excel 97 on Citrix | Excel Programming | |||
Conditional Formatting - Run Time Error '13' Type Mismatch Error | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming |