What data type?

  • Thread starter Thread starter Srdjan Kovacevic
  • Start date Start date
S

Srdjan Kovacevic

Hi,

I'm having problems finding data type that will sustain big numbers (10 or
more digits). My prog (simple LCG/MLCG random number generator) works best
with Long data type, but even then it's quite limited in terms of size of
the numbers it can crunch. Apparently, Variant data type should allow bigger
numbers (Excel help says: "Numeric data can be any integer or real number
value ranging from -1.797693134862315E308 to -4.94066E-324 for negative
values and from 4.94066E-324 to 1.797693134862315E308 for positive values"),
but when I declare my variables as Variant, the prog reports "overflow" even
for the parameters that Long variable can crunch.

Thanks
srdjan
 
Post the code please. The overflow may be caused by something else that the
receiving variable.
 
Post the code please. The overflow may be caused by something else that
the
receiving variable.
Click to expand...

Thanks for your reply... The code is below...

However, I've made a slight change, that turned out to be a workaround...
See, I've noticed that the program can't perform Mod operation with two
numbers over 6 digits each, if the mod is called from within the code.
However, if I try to do that operation in the worksheet, it works perfectly
(but substantially slower).

That's why I've made it copy certain values in the worksheet, do the
calculation in the cell and then copy the value from the cell to the
variable (the bit in the loop -- I2 is the cell where I've put the Mod()
operation)..

As I said, if I try to do "x = z mod m" operation in the code (where z is of
the scale of E12 and m is a 6-digit number) it reports overflow...

thx.s


Sub LCG_MLCG()
'
' LCG Makronaredba
' Makronaredbu dana 10.10.2003 snimio Srðan
'

Dim a As Double
Dim c As Double
Dim m As Double
Dim x As Double
Dim y As Double
Dim z As Double

Dim varKolicina As Long

Range("B1").Select
a = ActiveCell.Value

Range("B2").Select
c = ActiveCell.Value

Range("B3").Select
m = ActiveCell.Value

Range("B4").Select
varKolicina = ActiveCell.Value

Range("B5").Select
y = ActiveCell.Value



For i = 1 To varKolicina

z = a * y

Range("I1").Select
ActiveCell.FormulaR1C1 = z

Range("I2").Select
x = ActiveCell.Value

Range("A7").Select

ActiveCell.Offset(i, 0).Select
ActiveCell.FormulaR1C1 = x

y = x


Next i

End Sub
 
I can't mod those huge numbers either, so I can only confirm the problems.

You may then regain some speed by not selecting anything. Shorten your code
to (not tested):

a = Range("B1").Value
c = Range("B2").Value
m = Range("B3").Value
varKolicina = Range("B4").Value
y = Range("B5").Value

For i = 1 To varKolicina
z = a * y
Range("I1").FormulaR1C1 = z
x = Range("I2").Value
Range("A7").Offset(i, 0).FormulaR1C1 = x
y = x
Next i
 
Back
Top