WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA String to Integer

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.

VBA String to Integer

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.

CINT Function

  • Expression is the value or the variable which contains the value to be converted to Integer Data Type.
Note 1: Just when you pass the expression value must between -32768 to 32767. Otherwise, it will lead to “Over Flow Error,” i.e. assigning the value which is over the limit of the data type.
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.”

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

You can download this VBA String to Integer Excel Template here – VBA String to Integer Excel Template

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.

VBA String to Integer Example 1

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

VBA String to Integer Example 1-1

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.

Example 2

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 2-1

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

Example 3

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.

Example 3-1

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 –

  • VBA Split String into Array
  • VBA String to Date
  • VBA String Comparison
  • VBA Replace String
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA String to Integer Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More