Gfrktyl

I am using Microsoft Office Excel 2007.

Suppose I have the number 12500000000000.

Dim value1 as long



value1 = CLng(copyrng2.value)



'where copyrng2 is a range (cell) variable containing 12500000000000

The above code will fail because of overflow.

Can I store that number in any Excel (VBA) variable? If not, what can I do to store it, preferably in a format (Integer, Long, etc.) on which calculations can be performed?

The existing answer is correct in that you need to use the Double data type. That solves the problem, now I'll bring in some technical details.

The largest number that the Double data type can store (also the biggest number Excel can deal with) is 1.79769313486231570 • 10³⁰⁸. The biggest number that you can put in a cell (without a formula), however, is 9.99999999999999 • 10³⁰⁷. Note that you'll lose a good deal of precision when you're working with numbers that huge - adding a comparatively tiny number to things of that magnitude has a good chance of doing nothing. Floating point can get a little weird.

The maximum size of the Long data type is a drop in the ocean compared to Double's range; a signed 64-bit integer can only go up to 9,223,372,036,854,775,807. At least you're guaranteed to lose nothing to rounding there, as long as you're dealing with only whole numbers.

Sources: Excel specifications and limits, Data Type Summary

You need to use Double rather than Long. With B2 like:

This code will throw an error:

Sub dural()

    Dim d As Long

    d = Range("B2").Value

End Sub

But this code will not:

Sub BigNumber()

    Dim d As Double

    d = Range("B2").Value

    MsgBox d

End Sub

Use the Decimal variable type. You can't declare a number as Decimal but you can store it in a Variant variable.

Dim value1 as Variant

value1 = CDec(copyrng2.value)

NOTE: With the large numbers you are dealing with (12500000000000) if you try to store a decimal, Excel seems to round that number to 1 decimal place.

An excel can store only 15 digits. As far as the largest number be 999999999999999. If you try to type 16 digits number, it rounds the last number by 0 from 16th place.