EDUCBA

Home VBA VBA Resources VBA Conversion Functions VBA CInt
 

VBA CInt

Madhuri Thakur
Article byMadhuri Thakur

VBA CINT

Excel VBA CInt Function

In this article, we will see an outline on Excel VBA CInt. CInt is a type conversion function. So if we try to understand it in general terms this function is used to convert a value from any other data type to integer data type. The syntax to use this function is actually very simple as it takes just one argument.

 

 

Syntax:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

VBA CINT Syntax

Though this function is used to convert a value to an integer it is to be noted that the value should be numeric. A non-numeric value will give an error. Actually there are three types of Error that can be encountered while using this function.

How to Use the CInt Function in Excel VBA?

The use of this function is very easy as it takes a single expression as an argument. This function is very useful in certain applications when there is data in multiple forms. This function can be used in a single statement or we can use a user-defined function to call it in another function. We will learn how to use the CInt function in Excel by using the VBA Code.

You can download this VBA CInt Excel Template here – VBA CInt Excel Template

Example #1

Let us begin with the most basic example to show how this function works. As we know CInt function converts any given expression to an integer. So we will provide a basic input and see the result. For this, follow the below steps:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Insert Module

Step 2: When the module is inserted we can start with our subprocedure for example 1.

Code:

Sub Example1()

End Sub

VBA CInt Example 1-1

Step 3: We will simply provide a decimal value to the function and display it using the Msgbox function.

Code:

Sub Example1()

End Sub

Decimal Value Example1-2

Step 4: Run the code by pressing the F5 key or by clicking on the Play Button.

VBA CInt Example 1-3

We can see that we have given the input a decimal value but the function converted the value to an integer.

Example #2

Let us have some value in any sheet and try to convert it to an integer using the function. The value what I have in sheet 1 for cell A1 is as follows. For this, follow the below steps:

VBA CInt Example 2-1

Step 1: Declare another subprocedure for example 2 for this circumstance.

Code:

Sub Example2()

End Sub

VBA CInt Example 2-2

Step 2: Now declare two variables one as String and another one as Integer.

Code:

Sub Example2()

Dim A As String, B As Integer

End Sub

Integer Example 2-3

Step 3: Assign the value of cell A1 to variable A.

Code:

Sub Example2()

Dim A As String, B As Integer
A = Range("A1").Value

End Sub

VBA CINT Example 2-4

Step 4: And then in the variable B we will convert the value of A to Integer using the CInt function.

Code:

Sub Example2()

Dim A As String, B As Integer
A = Range("A1").Value
B = CInt(A)

End Sub

VBA CInt Example 2-5

Step 5: To see our result if the value is displayed or not let us use the Msgbox function as variable A is a string data type.

Code:

Sub Example2()

Dim A As String, B As Integer
A = Range("A1").Value
B = CInt(A)
MsgBox B

End Sub

String Data Type. Example 2-6

Step 6: Run the code by pressing the F5 key or by clicking on the Play Button.

VBA CInt Example 2-7

Example #3

The practical example of this function will be very good when we take any input from the user. So now for this example let the user give any values and we will display it by converting the value to an integer using the CInt function. For this, follow the below steps:

Step 1: Declare another sub procedure to start with this example.

Code:

Sub Example3()

End Sub

VBA CInt Example 3-1

Step 2: Now we will declare two variables, one as Integer and one as any other data type but for this example, I have taken it as a long data type.

Code:

Sub Example3()

Dim A As Long, B As Integer

End Sub

VBA CInt Example 3-2

Step 3: In the variable A store the input we will get from the user using the input box function.

Code:

Sub Example3()

Dim A As Long, B As Integer
A = InputBox("Enter a Decimal Value")

End Sub

VBA CInt Example 3-3

Step 4: Now in the variable B store the input received from the user and convert it to an integer using the CInt function.

Code:

Sub Example3()

Dim A As Long, B As Integer
A = InputBox("Enter a Decimal Value")
B = CInt(A)

End Sub

VBA CInt Example 3-4

Step 5: Now we can go ahead and display the value which we have converted to the user as follows.

Code:

Sub Example3()

Dim A As Long, B As Integer
A = InputBox("Enter a Decimal Value")
B = CInt(A)
MsgBox "The Number you entered is " & A

End Sub

VBA CInt Example 3-5

Step 6: Now when we execute the code above first we will get a pop up to enter a decimal value.

VBA CInt Example 3-6

Step 7: When we press Ok we can see the result is converted to an integer.

VBA CInt Example 3-7

Example #4

Now as in example 3 we asked a user to provide any value and we tried to convert it to an integer. But multiple users can insert different types of values so what if someone puts value which is not in range for integer we will get an error. For this, follow the below steps:

Step 1: We can again execute the code of example 3 and put the values.

VBA CInt Example 4-1

Step 2: Now we can click on OK.

Run Time Error Example 4-2

We receive a run time error because the storage capacity of an integer value is overflown and variable B cannot store this value.

Step 3: So in this example, we will put an error handler for the CInt function, so for example 4 let us use the same code of example 3 as shown below.

Code:

Sub Example4()

Dim A As Long, B As Integer
A = InputBox("Enter a Decimal Value")
B = CInt(A)
MsgBox "The Number you entered is " & A

End Sub

Error Handler Example 4-3

Step 4: But we will make a few changes before we begin to process the input let us use the error handler on error Go to.

Code:

Sub Example4()

Dim A As Long, B As Integer
On Error GoTo 100:
A = InputBox("Enter a Decimal Value")
B = CInt(A)
MsgBox "The Number you entered is " & A
100:
MsgBox "The value cannot be Converted"
End

End Sub

VBA CInt Example 4-4

Step 5: Now when we run the code by pressing the F5 key. It provides the same input.

VBA CINT Example 4-5

Step 6: Let us see what happens when we press Ok.

Type of Error Example 4-6

This is one of the type of error we have handled for CInt function.

Conclusion

So we saw what CInt function can do and we have said earlier that there can be three types of errors associated with this function. So let us discuss those three types.

In the first case, we saw that if a user or any value is provided to the variable which is out of the range for the integer. The second instance can be if the value is non-numeric. The third and last type of error we can encounter using this function is for an array.

Things to Remember

The important things to remember about these functions are:

  • Non Numeric Values will not be converted and it will show as an error.
  • CInt function cannot be used in an array.
  • If the values exceed the range of integer we will encounter an error.
  • If the values are in decimal places this function rounds the values with the respective decimals.

Recommended Articles

This is a guide to VBA CInt. Here we discuss how to Use the CInt Function in Excel VBA along with practical examples and downloadable excel template. You can also go through our other related articles to learn more –

  1. VBA SendKeys
  2. VBA Name Worksheet
  3. VBA On Error Goto
  4. VBA Msgbox Yes/No