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??????
Thanks
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
intInteger=CInt(strString)
Check the help file under type conversion functions for other data types.
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
