Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNumber (in Rick Rothstein's example)
I'm trying to write a function that checks whether an input is a positive
number. Below is one of Rick Rothstein's examples that he recommands for use in replacement of IsNumeric. But I need to expand it further so only "positive whole number or integer" (i.e with or without decimal point) returns true. While anything else with text, sign or symbol need to return false. Any help is appreciated. Function IsNumber(ByVal Value As String) As Boolean IsNumber = Len(Value) 0 And Not Value Like "*[!0-9.]*" End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNumber (in Rick Rothstein's example)
Rightfully, the function you posted should have been called IsDigits, not
IsNumber. Give this function a try... Function IsNumber(ByVal Value As String) As Boolean ' Uncomment the next statement out if you ' want to provide for plus/minus signs ' If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9.]*" And _ Not Value Like "*.*.*" And _ Len(Value) 0 And Value < "." And _ Value < vbNullString End Function Rick "Sam Kuo" wrote in message ... I'm trying to write a function that checks whether an input is a positive number. Below is one of Rick Rothstein's examples that he recommands for use in replacement of IsNumeric. But I need to expand it further so only "positive whole number or integer" (i.e with or without decimal point) returns true. While anything else with text, sign or symbol need to return false. Any help is appreciated. Function IsNumber(ByVal Value As String) As Boolean IsNumber = Len(Value) 0 And Not Value Like "*[!0-9.]*" End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNumber (in Rick Rothstein's example)
Try...
Function IsNumber(ByVal Value As String) As Boolean If (Len(Value) 0 And Not Value Like "*[!0-9.]*") Then If Value 0 Then IsNumber = True Exit Function End If End If IsNumber = False End Function Hope this helps, Hutch "Sam Kuo" wrote: I'm trying to write a function that checks whether an input is a positive number. Below is one of Rick Rothstein's examples that he recommands for use in replacement of IsNumeric. But I need to expand it further so only "positive whole number or integer" (i.e with or without decimal point) returns true. While anything else with text, sign or symbol need to return false. Any help is appreciated. Function IsNumber(ByVal Value As String) As Boolean IsNumber = Len(Value) 0 And Not Value Like "*[!0-9.]*" End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNumber (in Rick Rothstein's example)
I just re-read what you wrote and I'm not sure what your question really is
anymore. You wrote "so only 'positive whole number or integer' (i.e with or without decimal point) returns true"... and so, picking up on the "decimal point" part (and reading over the rest of what you wrote kind of quickly), I gave you a function for seeing if an entry is a floating point number or not. But I now think maybe you are asking about permitting a trailing decimal point. If that is actually what you want, try this function instead... Function IsNumber(ByVal Value As String) As Boolean If Value Like "." Then Value = Left(Value, Len(Value) - 1) IsNumber = Len(Value) 0 And Not Value Like "*[!0-9.]*" End Function Rick "Rick Rothstein (MVP - VB)" wrote in message ... Rightfully, the function you posted should have been called IsDigits, not IsNumber. Give this function a try... Function IsNumber(ByVal Value As String) As Boolean ' Uncomment the next statement out if you ' want to provide for plus/minus signs ' If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9.]*" And _ Not Value Like "*.*.*" And _ Len(Value) 0 And Value < "." And _ Value < vbNullString End Function Rick "Sam Kuo" wrote in message ... I'm trying to write a function that checks whether an input is a positive number. Below is one of Rick Rothstein's examples that he recommands for use in replacement of IsNumeric. But I need to expand it further so only "positive whole number or integer" (i.e with or without decimal point) returns true. While anything else with text, sign or symbol need to return false. Any help is appreciated. Function IsNumber(ByVal Value As String) As Boolean IsNumber = Len(Value) 0 And Not Value Like "*[!0-9.]*" End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNumber (in Rick Rothstein's example)
Below is one of Rick Rothstein's examples that he recommands
for use in replacement of IsNumeric. Function IsNumber(ByVal Value As String) As Boolean IsNumber = Len(Value) 0 And Not Value Like "*[!0-9.]*" End Function Rightfully, the function you posted should have been called IsDigits, not IsNumber. I just noticed... you added a decimal point inside the square-brackets that I had not included in my original posting. Doing that invalidates the function... for that modification you did, things like the following will now pass the test... Debug.Print IsNumber(".") Debug.Print IsNumber("...") Debug.Print IsNumber("1.2.3") See my other posts in this thread for the function you want. Rick |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNumber (in Rick Rothstein's example)
Hi Rick
Thanks for your explaination. Sorry you were right that I was really after IsNumber, not IsDigits of your example. I want to only allow positive whole number (without decimal point) and real number (with decimal point) to return TRUE. For example, the acceptable input might be 0.25 or 100 say. Your IsNumber here works great for this purpose. But I also need input with signs (e.g. $123) or exponential (1E2) to return FALSE. Is this doable? Function IsNumber(ByVal Value As String) As Boolean IsNumber = Not Value Like "*[!0-9.]*" And _ Not Value Like "*.*.*" And _ Len(Value) 0 And Value < "." And _ Value < vbNullString End Function Many thanks Sam "Rick Rothstein (MVP - VB)" wrote: Below is one of Rick Rothstein's examples that he recommands for use in replacement of IsNumeric. Function IsNumber(ByVal Value As String) As Boolean IsNumber = Len(Value) 0 And Not Value Like "*[!0-9.]*" End Function Rightfully, the function you posted should have been called IsDigits, not IsNumber. I just noticed... you added a decimal point inside the square-brackets that I had not included in my original posting. Doing that invalidates the function... for that modification you did, things like the following will now pass the test... Debug.Print IsNumber(".") Debug.Print IsNumber("...") Debug.Print IsNumber("1.2.3") See my other posts in this thread for the function you want. Rick |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNumber (in Rick Rothstein's example)
You are going to explain in some detail exactly how you are using this
function. The reason is because the function already returns False for numbers like $123 and 1E2... that is what it was designed to do. Rick "Sam Kuo" wrote in message ... Hi Rick Thanks for your explaination. Sorry you were right that I was really after IsNumber, not IsDigits of your example. I want to only allow positive whole number (without decimal point) and real number (with decimal point) to return TRUE. For example, the acceptable input might be 0.25 or 100 say. Your IsNumber here works great for this purpose. But I also need input with signs (e.g. $123) or exponential (1E2) to return FALSE. Is this doable? Function IsNumber(ByVal Value As String) As Boolean IsNumber = Not Value Like "*[!0-9.]*" And _ Not Value Like "*.*.*" And _ Len(Value) 0 And Value < "." And _ Value < vbNullString End Function Many thanks Sam "Rick Rothstein (MVP - VB)" wrote: Below is one of Rick Rothstein's examples that he recommands for use in replacement of IsNumeric. Function IsNumber(ByVal Value As String) As Boolean IsNumber = Len(Value) 0 And Not Value Like "*[!0-9.]*" End Function Rightfully, the function you posted should have been called IsDigits, not IsNumber. I just noticed... you added a decimal point inside the square-brackets that I had not included in my original posting. Doing that invalidates the function... for that modification you did, things like the following will now pass the test... Debug.Print IsNumber(".") Debug.Print IsNumber("...") Debug.Print IsNumber("1.2.3") See my other posts in this thread for the function you want. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rick Rothstein | Excel Programming | |||
ISNUMBER | Excel Worksheet Functions | |||
Question for Rick Rothstein | Excel Discussion (Misc queries) | |||
answer to Rick | Excel Programming | |||
ISNumber VBA | Excel Programming |