Numeric values are defined by data types like integer or byte. These data types are used for optimizing the processing and memory allocation in Excel. In this guide, we’re going to show you how to convert string into number in Excel VBA.
Data types in VBA
Like in some other programming languages, VBA uses data types to identify what variables it can store and how they are stored. Most of the data types in VBA define numeric values. Here is a brief list of numeric data types:
| Data type | Storage | Range |
| Byte | 1 byte | 0 to 255 |
| Integer | 2 bytes | -32,768 to 32,767 |
| Long | 4 bytes | -2,147,483,648 to 2,147,483,647 |
| Single | 4 bytes | -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values |
| Double | 8 bytes | -1.79769313486231E308 to-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values |
| LongLong | 8 bytes |
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 Valid on 64-bit platforms only. |
| Currency | 8 bytes | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
| Decimal | 14 bytes | +/-79,228,162,514,264,337,593,543,950,335 with no decimal point; +/-7.9228162514264337593543950335 with 28 places to the right of the decimal |
Since there are many numeric data types, there are many functions to convert a string into number in Excel VBA.
Functions to a convert string into number in Excel VBA
All conversion functions use the same syntax: Each requires a single string argument to convert into a number. For example:
Each function returns an error If the string argument either is not a numeric or is outside the range of the data type being converted.
CByte("1250") returns an overflow exception.
| Function | Return type | Example |
| CByte | Byte | CByte("65.75") returns 66 |
| CCur | Currency | CCur("$256,000.50") returns 256000.5 |
| CDbl | Double | CDbl(128.239856 * 4.8 * 0.04) returns 24.622052352 |
| CDec | Decimal | CDec("15000000.5678") returns 15000000.5678 |
| CInt | Integer | CInt("1234.56") returns 1235 |
| CLng | Long | CLng("1,500,000.88") returns 15000001 |
| CLngLng | LongLong | CLngLng("1,250,500,000.88") returns 1250500001 |
| CSng | Single | CSng("5.67854") returns 5.67854 |
Bonus: Use IsNumeric function to verify value
To avoid type mismatch errors, you can use the IsNumeric function to check if the expression is numeric or not. The function returns Boolean value based on the expression. TRUE if the expression is numeric, FALSE otherwise.
Here is a sample function that can check the data first, and convert the it into an integer if it is numeric. If the argument is not a valid number, the function returns 0.
Function ConvertInt(arg As String) As Integer If IsNumeric(arg) Then ConvertInt = CInt(arg) End Function
