How do I validate whether a string is just a number or not?

C

CS Hayes

How do I validate whether a string is just a number or not?
if val like...
if val <> ...
someone suggested an imaginary function called isnumber
if isnumber(val) = true/false
this didn't work, any suggestions
 
J

Jay Freedman

How do I validate whether a string is just a number or not?
if val like...
if val <> ...
someone suggested an imaginary function called isnumber
if isnumber(val) = true/false
this didn't work, any suggestions
Click to expand...

First -- don't use 'val' as a variable name. It's a built-in function.
Although VBA will allow you to use it, it may confuse the Basic
interpreter and it will certainly confuse you at some point.

Second -- the built-in (not imaginary) function is called IsNumeric,
and it does indeed return true or false depending on whether the
string input represents a number. It will accept thousands separators
and at most one decimal separator -- I think it checks the current
Regional settings to determine what characters are assigned to the
separators.

Third, the Val function converts a string to a number, if possible,
according to certain rules. Those rules are different than those for
IsNumeric. Val will convert any numeric characters at the start of the
string, and stop at the first nonnumeric character; so Val("123abc")
returns the integer 123. If the string is empty or doesn't start with
a numeric part, it returns 0. In both of those cases, IsNumeric
returns false.

Play with this macro for a bit to see the differences:

Sub Demo()
Dim s As String
s = InputBox("Number:")
If IsNumeric(s) Then
MsgBox s & " is a number" & vbCr & _
"Val(s) = " & Val(s)
Else
MsgBox s & " is not a number" & vbCr & _
"Val(s) = " & Val(s)
End If
End Sub

Finally, there are type conversion functions such as CInt(), CLng(),
CSng(), and CDbl(). See the help topic "Type Conversion Functions" for
the full list. If the input expression for these functions isn't
completely numeric, they'll throw an error, so you need to check first
with IsNumeric or use an On Error trap. Also, CInt() throws an error
if the input is numeric but bigger than 32767.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.
 
C

CS Hayes

First, I acutally am using the Val function in this piece of code. I just
used "val" inadvertantly, sorry (the actual variable is named "inptbx" and it
is a string.) I just learned the Val function myself (reading "Word
Programming" by Roman, wonderful book, wish he'd update it though.)

Second, Thank you Sooo much for verifying this tool: "IsNumeric." Maybe I
misunderstood the person who told me. This is the key to my piece of code.

Thanks,
 
C

CS Hayes

Ok, this is cool. Here's my code:

Sub changeparagraphbold()
'make some variables for the application
Dim inptbx As String
Dim inptbxnum As Single
Dim paracount As Integer
paracount = ActiveDocument.Paragraphs.Count

'open an input box and ask for the paragraph number you want to change
inptbx = InputBox("Enter the paragraph number you want to make bold:", "Make
a paragraph bold!")

'make sure the input is a number
If IsNumeric(inptbx) = False Then
MsgBox ("You must enter a number only!!!")
Exit Sub
Else
End If

'make the input box output a number
inptbxnum = Val(inptbx)

'make sure the number does not exceed the amount of paragraphs or is negative
If inptbxnum <= 0 Then
MsgBox "Number Must Be greater than Zero!!"
Exit Sub
ElseIf inptbx > paracount Then
MsgBox "Number must not exceed documents current amount of paragraphs!!"
Exit Sub
End If

'make the input box selected paragraph BOLD
ActiveDocument.Paragraphs(inptbxnum).Range.Font.Bold = True

'end the program
End Sub

Ok, I'm sure there's someone where who could write one line but I'm pretty
impressed. I've been having the hardest time understanding variables and
definitions but now I think I got it.
 
H

Helmut Weber

Hi Chris,

have a look at these examples in addition:

MsgBox IsNumeric("123E14")
MsgBox IsNumeric("123,4,567")
MsgBox IsNumeric("1.446,345")
MsgBox IsNumeric("1 446,345")
MsgBox IsNumeric("1.446.345")
MsgBox IsNumeric(".3")
MsgBox IsNumeric("3.")
MsgBox IsNumeric("3. ")
MsgBox IsNumeric("3,")
MsgBox IsNumeric("3, ")
 
J

Jay Freedman

By Jove, I think he's got it! Well done.

If you're going to spend some effort learning to handle user input, there
are a couple of principles you should know about early.

- If you know ahead of time that the input has to be limited in some way,
don't make the user guess what the limits are. In this case, you know that
you're looking for a number greater than zero, and less than or equal to the
paragraph count. Put that information in the InputBox prompt, and the user
won't have to wonder "Is 42 too big?". Something like this:

inptbx = InputBox("Enter the paragraph number you want to make bold (0 - " _
& paracount & "):", "Make a paragraph bold!")

- Go easy on the exclamation marks. They get annoying pretty quickly.

- In a "real" application, if the input is invalid, instead of just ending
the macro with Exit Sub you probably want to give the user another chance. A
simple way to do that is to put a label (an identifier followed by a colon)
just before the InputBox statement, and replace the Exit Sub statements with
GoTo statements pointing to that label. A better way is to make a loop with
one of the variations on the While...Wend or Do...Loop Until statements. You
also want to give the user a way to stop the loop -- for instance, if inptbx
is an empty string. (The InputBox is limited and doesn't offer a Cancel
button. You may want to learn about UserForms, which are much more
powerful.)

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.
 
C

CS Hayes

thanks for the vote of confidence.

I was kind of concerned about the idea of validating input.

I'm really going about something in a backward way but right now it appears
the best. I came to a big realization that I had to learn Visual Basic by
itself in some manner whatever to apply it to Access.

I got a book by Steve Roman on "Programming Word" and I found his style best
to my learning curve. So, I learn VB via Word programming but the final
intent is to be able to develop solutions via Access with VB.

Thanks to all who help.

You're a great group of helpful professionals!
 
C

CS Hayes

I've been working in the office for 12 years. I know the problems that
office workers encounter on a regular basis. I've noticed a lot of people
coming to these forums looking for solutions (to what some may think are
trivial.) I have encountered the same problems and solved them. I believe
that their questions are legitimate and if I can, I will help.

Someone I worked for just didn't care to know anything about computers. An
example of a solution I created for her was a very small VBA program in Word
that brought up a three button form that guided her through custom forms to
CREATE A LABLE AND PRINT IT.

Microsoft Office is a powerful tool but some people don't need a digital
power miter saw when they are chopping wood for a fire, they need something
that will lighten the load of chopping. This book may be very interesting.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA word change keys 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
What does Val return? 1
Page numbering with Merged Document and IF statements 1
Making a macro only work on certain files 2
How do I combine IF(ISNUMBER(SEARCH and VLOOKUP( formulas 0
Inputting decimal numbers cause erroneous results 2
cannot delete from specified tables 7

Top