Excel VBA String to Integer
String and Integer are common data types, but often as part of our data analysis, we may want to convert string data type variable to integer data type and this is possible by using “CINT” function in VBA.
Syntax of CINT Function
“CINT” is categorised as a data type conversion function. This function converts provided expression into an Integer data type. When I say “Expression”, it must be a numerical value which is in the range of Integer data type in VBA, i.e. between -32768 to 32767.
Now, look at the syntax of the CINT function in VBA.
- Expression is the value or the variable which contains the value to be converted to Integer Data Type.
In this article, we will show you about integer data type conversion from string to an integer using the function “CINT”.
Examples of CINT Function in VBA
Example #1
Let’s perform the first example of converting one fraction number to Integer Data Type.
Code:
Sub CINT_Example1() Dim IntegerNumber As String Dim IntegerResult As Integer IntegerNumber = 2564.589 IntegerResult = CInt(IntegerNumber) MsgBox IntegerResult End Sub
- In the above code for the variable “IntegerNumber”, I have assigned the value as “2564.589”.
- In the next line, I have applied a CINT function. So it will convert the provided value to the nearest integer number.
So, we got the result – 2565, and the supplied number is 2564.589. When the decimal value is more than 0.5 CINT functions round up the number to the next integer number. When the decimal value is less than or equal to 0.5 CINT functions round down to the same Integer number.

4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
For an example look at the below code where the number is less than 0.5
Code:
Sub CINT_Example1() Dim IntegerNumber As String Dim IntegerResult As Integer IntegerNumber = 2564 - 0.5 IntegerResult = CInt(IntegerNumber) MsgBox IntegerResult End Sub
This should give the result as 2564, not 2565.
Example #2
Now I will try and assign the number which is more than the limit of the Integer data type.
Code:
Sub CINT_Example2() Dim IntegerNumber As String Dim IntegerResult As Integer IntegerNumber = 51456.785 IntegerResult = CInt(IntegerNumber) MsgBox IntegerResult End Sub
I have assigned the number as “IntegerNumber = 51456.785” which is more than the limit of the Integer data type limit. Now if I try to run the code, it will throw an overflow error in VBA.
Whenever the provided number is more than the Integer data type lets show the simple message box saying “Number is More Than the Limit of the Integer Data Type”.
Code:
Sub CINT_Example2() Dim IntegerNumber As String Dim IntegerResult As Integer IntegerNumber = 51456.785 On Error GoTo Message: IntegerResult = CInt(IntegerNumber) Message: If Err.Number = 6 Then MsgBox IntegerNumber & " is More Than the Limit of the Integer Data Type" End Sub
This will show the message like the below whenever the provided number is out of the range of Integer data type.
Example #3
Now I will try and assign the value which is not numeric.
Sub CINT_Example2() Dim IntegerNumber As String Dim IntegerResult As Integer IntegerNumber = "Hello" IntegerResult = CInt(IntegerNumber) MsgBox IntegerResult End Sub
I have assigned the value as “Hello” which is not numeric. This causes “Type Mismatch Error”.
Instead of showing the error like this, let’s put a simple information message box in front of the user. The below code will put the message box saying “The Provided Expression Value is Non-Numeric, So cannot be converted”.
Code:
Sub CINT_Example2() Dim IntegerNumber As String Dim IntegerResult As Integer IntegerNumber = "Hello" On Error GoTo Message: IntegerResult = CInt(IntegerNumber) Message: If Err.Number = 13 Then MsgBox IntegerNumber & " : The Provided Expression Value is Non-Numeric, So cannot be converted" End Sub
This will show the message box below.
Things to Remember
- CINT converts value from another data type to an integer data type.
- Integer can hold values from -32768 to 32767 anything above these numbers will cause an overflow error.
- Only string data type contains numerical data that will be converted to an integer data type.
Recommended Articles
This has been a guide to VBA String to Integer. Here we discuss examples of string to integer in Excel VBA by using CINT function along with practical examples and downloadable excel template. Below are some useful articles related to VBA –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion