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.

Download Workbook

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:

CDec(expression)

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.

CInt("A") returns a type mismatch error, because the "A" is not a numeric value.

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