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.

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Srdjan Kovacevic" skrev i melding
...
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.

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

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Srdjan Kovacevic" skrev i melding
...
Post the code please. The overflow may be caused by something else that
the
receiving variable.

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

The VBA Mod operator appears to use the Integer data type. The
Worksheet MOD() function also has unexpected limitations

http://groups.google.com/groups?thre...wsra nger.com

If you need full precision, use
=y-x*INT(y/x)
instead of
=MOD(y,x)

Jerry

Srdjan Kovacevic wrote:

Post the code please. The overflow may be caused by something else that

the

receiving variable.


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