Excel VBA String to Integer

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA String to Integer (wallstreetmojo.com)

Syntax of CINT Function

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 VBAInteger Data Type In VBAIn VBA, an integer is a data type that may be assigned to any variable and used to hold integer values. In VBA, the bracket for the maximum number of integer variables that can be kept is similar to that in other languages. Using the DIM statement, any variable can be defined as an integer variable.read more, 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.”

Examples of CINT Function in VBA

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.

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.

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 VBAOverflow Error In VBAVBA Overflow Error or “Run Time Error 6: Overflow” occurs when the user inserts a value that exceeds the capacity of a specific variable’s data type. Thus, it is an error that results from the overloading of data beyond the desired data type limit of the variable.read more.

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.”

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 ErrorType Mismatch ErrorWhen we assign a value to a variable that is not of its data type, we get Type mismatch Error or Error code 13. For example, if we assign a decimal or long value to an integer data type variable, we will get this error (Error Code 13) when we run the code.read more”.

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.”

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.

Things to Remember

  • 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 ArrayVBA String to DateVBA String ComparisonVBA Replace String