VBA String to Integer

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.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

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