Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

Converting strings to numbers using Excel VBA

Posted by John Wright on June 13, 2001 6:49 AM
Hi there... was wondering if someone could answer a question for me?? When using the InputBox function in Excel VBA ... it returns a string value.. if I want to convert this string value to an actual numeric value so I may be able to use it for calculations, how would one do this. i.e say the user enters 40... the value 40 is returned as a string.. however I actually want this as a number 40. I'm not sure how to convert this to a number from a string. Perhaps there is another function that lets you enter numeric data directly from a promt??????


Check out our Excel VBA Resources

Re: Converting strings to numbers using Excel VBA

Posted by Ron on June 13, 2001 7:27 AM
There are various VBA functions that return change
data types. For example to convert a string called strString to an integer you could use code as follows:

Dim intInteger as integer

Check the help file under type conversion functions for other data types.

Re: Converting strings to numbers using Excel VBA

Posted by Joe Was on June 13, 2001 7:41 AM
Open a new workbook. Create a macro and name it "MyBox" and paste the code below in to it. Assign a hot-key to it, Macro menu, Options, I used "b". The code below when you type Ctrl-b, put the value 234567.8 in cell A1. Then a Box pops up and displays A1 first as a Value then on OK a new Box displays A1 as Text, on OK this time the Macro Exits. Hope this helps you with data as "Values" or "Text." JSW

Sub MyBox()
Set x = Worksheets("sheet1").Range("A1")
x.Value = 234567.8
x.NumberFormat = "$#,##_);($#,##0)"
MsgBox x.Value
MsgBox x.Text
End Sub

This archive is from the original message board at
All contents © 1998-2004
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.