Introduction | Example | Tutorial | Applications | Comments
Introduction - CInt
This VBA tutorial shows you how to convert a data type from a string to an integer with the VBA Type Conversion function CInt. Robust error checks included!
Example - CInt
Convert a String to an Integer
Sub DemoCInt() Dim i As Integer, str1 As String str1 = Range("A1") i = ConvertToInteger(str1) MsgBox i, , "Successful Conversion" End Sub Function ConvertToInteger(v1 As Variant) As Integer On Error GoTo 100: ConvertToInteger = CInt(v1) Exit Function 100: MsgBox "Failed to convert """ & v1 & """ to an integer.", , "Aborting - Failed Conversion" End End Function
Tutorial - CInt
The function ConvertToInteger is the heart and soul of this VBA example. You call the function and pass it a string or other numeric value, like I did in the procedure DemoCInt with the command
i = ConvertToInteger(str1) .
In the CInt example,
i = ConvertToInteger("12345") will work, as well.
If the conversion to an integer succeeds, your string will be changed to an integer and you can use it however you please, including in mathematical expressions.
If the conversion fails, a message box will pop up letting you know it failed and your macro execution will abort.
3 Common Conversion Failures
Why would the conversion fail? There are three common reasons.
The first reason the conversion may fail is that your string is not numeric. For example, the string “test” in the image above cannot be converted to an integer. To be numeric, the string must yield a “True” response in the following code
The second common reason for conversion failure is that the numeric value you want to convert is either too large or too small to store as an integer. You see, the integer data type can only store whole numbers between -32,768 and 32,767. Anything outside this range would yield an Overflow error (Run-time error ‘6’). If you want to convert whole numbers outside this range, consider using CLng to convert to a Long data type.
The third common reason that CInt conversions fail is that you are attempting to apply the CInt function to an array. Doing this will cause a Type mismatch error.
Converting Decimals to Integers
Keep in mind that the CInt function rounds decimal numbers. For example, if you are attempting to convert a string or a double data type from “5.5” to an integer, the resulting integer will be reported as a “6.” Likewise, converting a “5.4” will yield an integer of “5.”
Application Ideas - CInt
The ability to convert VBA data types, especially strings to integers, is handy when your program relies on user input. You will be able to check to see if the user entered a string in the correct format, then you can convert the string to an integer and use it in loops, counters, conditional statements and mathematical expressions.
The CInt Function is just one of several VBA data type conversion functions. I plan to highlight more conversions in future lessons. In the meantime, if you’re looking for more string conversion articles, check out my tutorials on
Share this article with a friend if you like what you see and let me know how you plan on using the VBA script by leaving a comment below! Follow me on Google+ for more great VBA content.
About Ryan Wells
Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.Follow