There may several situations where you might end up with a string variable that needs to be converted to a numeric data type:
Situation 1: You are using a textbox for getting data from the user. The Textbox.Text property will return a string data type. If you are going to be doing arithmetic operations on the value in the textbox then you are going to have to convert it to a numeric value.
Situation 2: There are numbers among a string input (i.e “1094-wellington St.”) and after extracting the numeric parts you want to to arithmetic operations on them.
Situation 3: The worksheet cells are in the Text format. You copy all the cells to an array. The array might store the numbers in text format. In order to be able to do arithmetic operations on the numeric values you would need to convert the strings to numbers.
Check if String is Numeric, IsNumeric():
IsNumeric() checks if the input string is a numeric value. It would be a good idea to check if the string you are working with is actually a numeric value before trying to convert it. If the string is not numeric and you try to convert it to a numeric value an exception will be thrown. The code below checks if the string strTemp is numeric or not:
If IsNumeric(strTemp) Then 'strTemp is a numeric value Else 'strTemp is not a numeric value End If
Convert String to Integer, CInt():
The following code converts the string “34” to an integer:
Sub Example1() Dim strInteger As String Dim intInteger As Integer strInteger = "34" intInteger = CInt(strInteger) End Sub
Convert String to Double, CDbl():
The following code converts the string “34.5” to a double:
Sub Example2() Dim strDouble As String Dim dblValue As Double strDouble = "34.5" dblValue = CDbl(strDouble) End Sub
Convert String to Long, CLng():
The following code converts the string “34” to a Long:
Sub Example3() Dim strLong As String Dim lngValue As Long strLong = "34" lngValue= CLng(strLong ) End Sub
Convert String to Single, CSng():
The following code converts the string “34.5” to a Single:
Sub Example4() Dim strSingle As String Dim sngValue As Single strSingle = "34.5" sngValue= CSng(strSingle ) End Sub
Convert String to Decimal, CDec():
The following code converts the string “34.54” to a Decimal:
Sub Example5() Dim strDecimal As String Dim decValue As Variant strDecimal = "34.5" decValue= CDec(strDecimal ) End Sub
Note: Decimal data types can’t be declared directly.
Convert String to Date:
Check if String is a Date, IsDate():
The code below checks if the string strDate is a Date:
If IsDate(strDate) Then 'strDate is a Date Else 'strDate is not a Date End If
Convert a String to a Date, CDate():
The following code converts the string “1/1/2004” to a Date:
Sub Example6() Dim strDate As String Dim dateValue As Date strDate = "1/1/2004" dateValue = CDate(strDate) End Sub
Converting a Numeric Value to a String, Str():
There are many situations where you would need to convert a number to a string, below are just a few examples:
All numeric value can be converted to a string using the Str() function:
Sub Example6() Dim intValue As Integer Dim dblValue As Double Dim lngValue As Long Dim sngValue As Single Dim decValue As Variant Dim strTemp As String intValue = 1 dblValue = 34.5 lngValue = 1 sngValue = 34.5 'decimal values can't be declared directly decValue = CDec(45.54) strTemp = Str(intValue) strTemp = Str(dblValue) strTemp = Str(lngValue) strTemp = Str(sngValue) strTemp = Str(decValue) End Sub
Converting a Numeric Value to Another Numeric Data Type:
Numeric values are implicitly converted to the appropriate data type in assignments. In the example below, when assigning the double value to the integer variable, the number 34.5 is implicitly converted to the equivalent integer during the assignment:
Sub Example7() Dim intValue As Integer Dim dblValue As Double dblValue = 34.5 intValue = dblValue End Sub
Although you could always use the explicit conversion functions:
CInt(expression): Converts the input expression to an integer. CDbl(expression): Converts the input expression to a double. CLng(expression): Converts the input expression to a long. CSng(expression): Converts the input expression to a single. CDec(expression): Converts the input expression to a decimal.
Converting a Numeric Value to a Date, CDate():
Numbers can be converted to dates and vice versa. You can see some examples below:
Conversions from a numeric value to a date can be done through the function CDate():
Sub example8() Dim intDate As Integer Dim objDate As Date intDate = 1 objDate = CDate(intDate) End Sub
Converting a Date Value to a String, Str():
Dates can be converted to a string using the function Str():
Sub example9() Dim strDate As String strDate = Str(Now) End Sub
Converting a Date Value to a Numeric Data type:
Dates can be converted to and from numeric data types. You can see some examples below:
Although you can convert integers to dates, but it can’t be done vice versa. Dates can only be converted to double and single data type:
Sub example10() Dim dblDate As Double Dim sngDate As Single dblDate = CDbl(Date) sngDate = CSng(Now) End Sub
Now let us look at a few more practical examples where conversion is required.
Example 1: String to Integer conversion
Say you have a column in a table that lists the amount in various currencies and you want to find the sum of amounts of one particular currency in your VBA code. Here the cell values have the currency code pre-fixed and hence, will be treated as strings. So, conversion will be required to calculate the sum.
Here is how the code will look like if you want to sum the USD amount.
First, we use the in-string function (InStr) to check whether the currency type is what we need. If so, we remove the currency code (first four characters) from the string using the Right function. Now we are left with only the amount, but in a string format and obviously we cannot perform the addition on it.
So, we use data type conversion by using the function: CDbl and finally we sum it up.
Dim amount As Double
Dim strVal As String
Dim i As Integer, lastRow As Integer
Dim dataRange As Range
lastRow = Sheet1.UsedRange.Rows.Count
Set dataRange = Sheet1.Range("A2:A" & lastRow)
For i = 1 To dataRange.Rows.Count
If InStr(1, dataRange.Cells(i, 1), "USD") = 1 Then
Debug.Print dataRange.Cells(i, 1)
strVal = Right(dataRange.Cells(i, 1), Len(dataRange.Cells(i, 1)) - 4)
amount = amount + CDbl(strVal)
The amount can be then used further in your code. The output of this program will be:
You can also define this as a function with the currency type as an input argument. In this way, whenever you call the function, you can specify the currency, thus making it dynamic.
Example 2: Decimal to Hex conversion
Though this is not data type conversion, it is worth mentioning how to convert decimal numbers to hex and vice-versa.
So, for decimal to hex conversion, we use the function Dec2Hex as illustrated below
Dim decVal As String, hexVal As String
decVal = "9999"
hexVal = Application.WorksheetFunction.Dec2Hex(DecVal)
And here is the output
Example 3: Hex to Decimal conversion
Similar to the above example, let’s have a look at hex to decimal conversion. So, for hex to decimal conversion, we will use the function Hex2Dec as illustrated below
Dim decVal As String, hexVal As String
hexVal = "270F"
decVal = Application.WorksheetFunction.Hex2Dec(hexVal)