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 variables to Integer data type. It is possible by using the CINT function in VBA.

The CINT is a data type conversion function. This function converts provided expressions into an Integer data type. When we say “Expression,” it must be a numerical value 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 that needs to convert to an Integer data type.

Note 1: When you pass, the expression value must be between -32768 to 32767. Otherwise, it will lead to an “Overflow” error, i.e., assigning the value over the data type's limit.

Note 2: Expression must be a numerical value. If the expression is string value or text value, it will cause a “Run Time Error 13: Type Mismatch.”

This article will show you the Integer data type conversion from a string to an integer using the function “CINT.”

Example #1

Let us perform the first example of converting one fraction number to an 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,” we have assigned the value as “2564.589.”
• In the next line, we 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, the 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.

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

It should give the result as 2564, not 2565.

Example #2

Now, we will try and assign the number 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

We have assigned the “IntegerNumber = 51456.785” more than the Integer data type limit. So now, if we try to run the code, it will throw an overflow error in VBA.

Whenever the provided number is more than the Integer data type, let us 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

It will show the message below whenever the provided number is outside the Integer data type range.

Example #3

Now, we will try to 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

We have assigned the value “Hello,” which is not numeric. It causes a “Type Mismatch Error”.

Instead of showing the error like this, let us 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

It will show the message box below.

• The CINT function converts the value from another data type to an Integer data type.
• Integers 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 convert to an Integer data type.

This article has been a guide to VBA String to Integer. Here, we discuss examples of string to integer in Excel VBA using the CINT function, practical examples, and a downloadable Excel template. Below are some useful articles related to VBA: -

• VBA Split String into Array
• VBA String to Date
• VBA String Comparison
• VBA Replace String