The function name determines the return type as shown in the following: Function The required expression argument is any string expression or numeric expression. CLngLng( expression ) (Valid on 64-bit platforms only.).Enter your email address below and we'll send you a copy along with our VBA Developer Kit, loaded with VBA tips, tricks and shortcuts.Each function coerces an expression to a specific data type. We put together a giant PDF with over 300 pre-built macros and we want you to have it for free. Once you subscribe, please share this article on Twitter and Facebook. In the meantime, if you’re looking for more string conversion articles, check out my tutorials onįor more VBA tips, techniques, and tactics, subscribe to our VBA Insiders email series using the form below. I plan to highlight more conversions in future lessons. The CInt Function is just one of several VBA data type conversion functions. You will be able to check to see if the user entered a string in the correct format, then you can convert the string to an integer and use it in loops, counters, conditional statements and mathematical expressions. The ability to convert VBA data types, especially strings to integers, is handy when your program relies on user input. For example, if you are attempting to convert a string or a double data type from “5.5” to an integer, the resulting integer will be reported as a “6.” Likewise, converting a “5.4” will yield an integer of “5.” Keep in mind that the CInt function rounds decimal numbers. Doing this will cause a Type mismatch error. The third common reason that CInt conversions fail is that you are attempting to apply the CInt function to an array. If you want to convert whole numbers outside this range, consider using CLng to convert to a Long data type. Anything outside this range would yield an Overflow error (Run-time error ‘6’). You see, the integer data type can only store whole numbers between -32,768 and 32,767. The second common reason for conversion failure is that the numeric value you want to convert is either too large or too small to store as an integer. To be numeric, the string must yield a “True” response in the following code IsNumeric(str1). For example, the string “test” in the image above cannot be converted to an integer. The first reason the conversion may fail is that your string is not numeric. Why would the conversion fail? There are three common reasons. If the conversion fails, a message box will pop up letting you know it failed and your macro execution will abort. If the conversion to an integer succeeds, the value of your string will be changed to an integer and you can use it however you please, including in mathematical expressions. The statement i = ConvertToInteger("12345") will work, as well. In the CInt example, str1 was the value in cell “A1,” but you don’t have to pass the function a value stored in a cell. You call the function and pass it a string or other numeric value, like I did in the procedure DemoCInt with the command i = ConvertToInteger(str1). The function ConvertToInteger is the heart and soul of this VBA example.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |