VBA Convert String to Integer: CInt, CLng, and Errors

VBA & macros · Updated November 18, 2026

In VBA, convert a numeric string with CInt for a 16-bit Integer or CLng for a 32-bit Long: for example, result = CLng("12345"). Choose the function based on the range you need, and remember that both functions round fractional values rather than simply cutting off the decimal part.

Choose the conversion function for your range

VBA does not have one conversion function that fits every integer-sized value. The key choice is the destination type. An Integer holds values from -32,768 through 32,767. A Long holds values from -2,147,483,648 through 2,147,483,647, so it is usually the more practical choice for general whole-number work in VBA.

Use the conversion function that matches that type:

  • CInt(expression) converts to an Integer and raises an error if the value is outside the Integer range.
  • CLng(expression) converts to a Long and raises an error if the value is outside the Long range.

For example, CLng("25000") returns 25000. That value also fits in an Integer, but CLng gives more headroom if the input later grows. Declaring a variable as Long does not automatically make every conversion safe: the string still needs to represent a value VBA can convert, and the result still has to fit in the type.

How CInt and CLng handle text

A conversion function evaluates an expression and tries to represent its value using the requested VBA type. If the string contains a valid numeric value, CInt or CLng produces a number you can assign to a matching variable. A string with ordinary surrounding spaces can generally be converted, but blank text, words, malformed numbers, and values outside the target type’s range can cause a run-time error.

Here is a small example that converts text to a Long and reports a conversion failure. It prints the converted value in the Immediate window, which you can show in the Visual Basic Editor with Ctrl+G on Windows. On Mac, use the equivalent shortcut available in the Visual Basic Editor or open the Immediate window from its View menu.

Save a copy of the workbook before running any macro: VBA macros cannot be undone with Ctrl+Z.

Sub ConvertTextToLong()
    Dim sourceText As String
    Dim result As Long

    ' Example input; replace this with the text you need to convert.
    sourceText = "12345"

    On Error GoTo ConversionError
    result = CLng(sourceText)
    Debug.Print result
    Exit Sub

ConversionError:
    MsgBox "The text could not be converted to a Long."
End Sub

In a real workbook, the input might come from a cell, a form field, or a file. If it comes from a worksheet cell, read the cell value into an appropriately typed variable or a Variant first, then validate it before conversion. Treat imported or user-entered text as uncertain even when it looks like a number on screen.

Rounding is different from truncation

A common surprise is that CInt and CLng round fractional values to the nearest whole number. They do not discard everything after the decimal point. VBA uses round-to-even behavior for halfway values: a value exactly halfway between two integers is rounded to the nearest even integer. For example, CInt(2.5) returns 2, while CInt(3.5) returns 4. Similarly, converting a value such as 7.8 with CLng gives 8.

If the input must contain a whole number and decimals should be rejected, do not rely on IsNumeric alone. A decimal such as “7.8” is numeric, but it is not an integer-valued input. Test for a fractional part before converting. The following example handles a numeric string, rejects a fractional value, and uses error handling to catch values that cannot be represented as a Long.

Sub ConvertWholeTextToLong()
    Dim sourceText As String
    Dim numericValue As Double
    Dim result As Long

    ' Example input; change this to the text being checked.
    sourceText = "42"

    If Not IsNumeric(sourceText) Then
        MsgBox "Enter a numeric value."
        Exit Sub
    End If

    On Error GoTo ConversionError
    numericValue = CDbl(sourceText)

    If numericValue <> Fix(numericValue) Then
        MsgBox "Enter a whole number without a fractional part."
        Exit Sub
    End If

    result = CLng(numericValue)
    Debug.Print result
    Exit Sub

ConversionError:
    MsgBox "The value is not within the Long range."
End Sub

Fix removes the fractional part toward zero, making the comparison useful for detecting a fraction. The conversion itself remains important: a very large numeric string can pass a basic numeric check but still exceed the Long range. Error handling is the final safeguard rather than a substitute for deciding what inputs your application should accept.

Validate input before converting

Use checks that match the requirements of your data. If a field must contain digits only, accepting any expression VBA considers numeric may be too permissive: decimal values and other number formats may also pass a numeric test. If signed values are allowed, a leading minus sign may be valid; if the field represents an identifier, even a mathematically valid number may be the wrong data type.

  • Check whether the input is blank after trimming spaces if an empty value is not allowed.
  • Use IsNumeric as an initial check for numeric content, not as proof that the value is a whole number or within range.
  • Check whether fractional values are allowed before calling CInt or CLng.
  • Use error handling around conversion so malformed text and overflow do not stop the macro unexpectedly.
  • Give users a clear message describing the expected input, such as “Enter a whole number from 1 to 100.”

For a business rule narrower than the VBA type range, check that rule explicitly too. For example, a quantity might be required to be positive and no greater than 500 even though a Long can hold much larger values. A successful CLng conversion only establishes that the value fits a Long; it does not establish that it is valid for your application.

Locale, separators, and imported text

Numeric text can depend on regional settings. A decimal separator may be a period in one locale and a comma in another, and grouping separators can create ambiguity. VBA’s conversion functions interpret text according to the system’s regional conventions, so the same text can be understood differently on computers with different settings. This matters when a macro processes files created elsewhere or accepts pasted data.

Do not remove commas or periods blindly. In one format, a comma may separate thousands; in another, it may mark the decimal part. Decide what format the source promises, then normalize or validate it deliberately. For a file with a documented invariant format, parse according to that format rather than assuming the current computer uses the same separators. Test representative inputs, including negative values, spaces, separators, and empty fields.

Identifiers deserve particular care. Postal codes, account codes, and product IDs may contain leading zeroes, and some may include letters or punctuation. Converting “00427” to a number produces 427, losing the leading zeroes. If the value is an identifier rather than a quantity for arithmetic, keep it as a String and preserve its original formatting.

CLng, CInt, Val, and other options

Val is not a drop-in replacement for CLng. It reads a numeric portion from the beginning of a string and can stop when it encounters a character it does not recognize. As a result, text such as “12abc” can produce 12 with Val, whereas a conversion intended to validate the complete input should reject the extra letters. Val also uses a period as its decimal separator, which can make it unsuitable for locale-dependent input.

Use CInt or CLng when you want a conversion to a specific integer type and want invalid or out-of-range input to raise an error. Use Val only when its parsing behavior is specifically what you want and you have separately validated the entire string. IsNumeric answers whether an expression can be treated as numeric; it does not convert the value or guarantee a Long result.

If you need to preserve a number with a fractional component, convert to a suitable numeric type such as Double rather than forcing it into an integer. If you need a wider whole-number range, consider whether the data should be held in a different representation; LongLong is available only in 64-bit VBA, and code intended for both 32-bit and 64-bit Office needs to account for that limitation. LongPtr is for pointer-sized values, not a general replacement for integer data.

Common conversion errors and how to diagnose them

Conversion failures usually come from one of a few causes. An error such as Type mismatch commonly indicates that the expression cannot be converted as supplied, while an overflow indicates that the result is outside the destination type’s range. Empty cells, text that contains unexpected characters, and decimal separators that do not match the computer’s locale are frequent sources of surprises.

When diagnosing a failing conversion, inspect the original string rather than only the cell’s displayed value. A cell may show a rounded or formatted number while its underlying value differs. Check for leading or trailing spaces, nonbreaking spaces copied from a web page, currency symbols, thousands separators, and hidden characters. A conversion function does not know whether those characters are harmless formatting or invalid input for your task.

Use a narrow error handler around the conversion and provide a useful message or log entry. Avoid using On Error Resume Next for an entire procedure: it can hide unrelated problems and leave a result variable at its default value, which may look like a legitimate zero. If a conversion fails, make the failure visible and decide whether to skip the record, ask for corrected input, or stop processing.

Frequently Asked Questions

How do I convert a string to an integer in VBA?
Use CInt(text) for a VBA Integer or CLng(text) for a Long. Check the input first and handle errors, because invalid text and values beyond the type’s range cannot be converted successfully.

Does CLng round or truncate?
CLng rounds to the nearest whole number, using round-to-even behavior for exact halfway values. It does not simply remove the decimal portion. Use Fix when you specifically need to remove the fractional part toward zero.

What is the difference between CInt and CLng?
Both convert to whole-number types, but CInt returns an Integer limited to -32,768 through 32,767, while CLng returns a Long with a much wider 32-bit range. Choose based on the range your data requires.

Can I use IsNumeric before CLng?
Yes, it can be a useful first check, but it does not prove that a value is an integer or fits in the Long range. Check fractional values separately if they are forbidden, and handle conversion errors for overflow or other invalid input.

Why does Val(“12abc”) return a number?
Val can parse the numeric prefix and stop at the first unrecognized character, so it may return 12. Use CLng or CInt with validation when the entire input must be a valid number rather than a numeric prefix.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.