What is largest value (number) that I can store in an Excel VBA variable?

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?

1

4 Answers

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

5

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.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy