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 typeStorageRange
Byte1 byte0 to 255
Integer2 bytes-32,768 to 32,767
Long4 bytes-2,147,483,648 to 2,147,483,647
Single4 bytes-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values
Double8 bytes-1.79769313486231E308 to-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
LongLong8 bytes

-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Valid on 64-bit platforms only.

Currency8 bytes-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal14 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.

FunctionReturn typeExample
CByteByteCByte("65.75") returns 66
CCurCurrencyCCur("$256,000.50") returns 256000.5
CDblDoubleCDbl(128.239856 * 4.8 * 0.04) returns 24.622052352
CDecDecimalCDec("15000000.5678") returns 15000000.5678
CIntIntegerCInt("1234.56") returns 1235
CLngLongCLng("1,500,000.88") returns 15000001
CLngLngLongLongCLngLng("1,250,500,000.88") returns 1250500001
CSngSingleCSng("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