VBA Converting Data Types

In this article I will explain how you can convert data types to one another.

Jump To :

String Conversions:

Convert String to a Numeric Data Type:

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

Numeric Conversions:

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:

Situation 1: Trying to loop through ranges using the “A1:B1” notation. See Referencing Ranges In Excel Using VBA for more information.

Situation 2: When concatenating a numeric value to a string. See VBA Excel String Processing and Manipulation for more information.

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:

Numeric Value to Date

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

Date Conversions:

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:

Numeric Value to Date

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

See Also:

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website www.software-solutions-online.com

One thought on “VBA Converting Data Types”

  1. HelporIWillDie says:

    I need to subtract two textboxes and have the result show up in another textbox.

    The numbers will likely be 0.1 or 0.2 difference so I need the result to be 0.1 in the result texbox.

    I have tried Cdec() but to no avail. I have tried a bunch of other stuff and aaahhhh! I am feeling like an idiot here. I tried to calculate on the spreadsheet and pull from that but I can’t figure out how to pull the most recent “updated” figures, so I went back to the textbox problem. Ugh!

    Please email me any help you can give.

Leave a Reply

Your email address will not be published. Required fields are marked *

privacy policy