CODEWITHSUNDEEP
vbaexcel
Michael Reichel
Michael Reichel

Reputation: 11

Need a method for storing obscenely long numbers in number format (not scientific)

How would I go about storing a very large number in number format and not scientific.
Please bear in mind that the number I will be storing is too big for the Long data type.
I've set it as a String.
I have a userform with a command button and a textbox. Below is the sample code:

Private Sub Cmd_Click()

 Dim bits As Integer
 Dim out As String

 bits = 64
 out = 2 ^ (bits - 1)

 Txt_Output.Value = out

End Sub

The above will return: 9.22337203685478E+18. But I want 9223372036854775807.
Can anyone explain how to avoid this?

Thanks in advance.

P.S. I'm hoping to avoid having to use an array.

Upvotes: 1

Views: 47

Answers (1)

YowE3K
YowE3K

Reputation: 23974

You can achieve that specific calculation using Decimal data types and a modification to the calculation routine:

Private Sub Cmd_Click()

 Dim bits As Integer
 Dim out As Variant
 Dim i As Long
 bits = 64
 out = CDec(1)
 For i = 1 to bits - 1
     out = out * 2
 Next

 Txt_Output.Value = out

End Sub

By forcing out to be a Variant/Decimal, the calculation does not lose precision as it is being calculated. However some things, such as CDec(2) ^ CDec(63) would still lose precision as the calculation would be done using an intermediate Double precision, so you will need to be very careful as to what calculations you do.

This might give you clues as to how to generalise that method to achieve what you need.

If you have 64-bit Excel, you can use the LongLong data type.

Upvotes: 1

Related Questions