PDA

View Full Version : [SOLVED] Issue with large multiplication on code



folk754
04-13-2005, 11:00 AM
I am trying to run the following code, but i am getting an overflow error. Any hints?:banghead:



Private Sub createflat()
Dim i As Integer, j As Integer, s As Long
Dim dest As Range
Set dest = Sheet28.Range("A1")
For i = 1 To 212
For j = i To 212
s = posit(i, j)
dest.Offset(s, 0) = dist(i, j)
dest.Offset(s, 0).Select
Next j
Next i
End Sub

Function posit(i As Integer, j As Integer) As Double
Dim s As Double
If i > j Then
s = CLng(250 * j + j * (j + 1) / 2 + i)
Else
s = CDbl(i * 250) 'Here it is where it gives the error...
s = s + i * (i + 1) / 2
s = s + j
End If
posit = s
End Function



I tried using CDbl and CLng, but both returned overflow error. Any hints? (The dist function returns some x value, not important on this macro.):help

What I am trying to do is create a different way to store a symmetric matrix on a spreadsheet. Because there are only 256 columns, I would rather use a horizontal storage algorithm, which would allow me to store approx 362 on a single column. So the position (posit function) would tell me where to store element (i,j) of the matrix.

Zack Barresse
04-13-2005, 11:04 AM
Try Dim'ing all your variables as Long instead of Integer. Haven't read too deep into your code, but that is usually it with that error.

I mean change this line ...


Function posit(i As Integer, j As Integer) As Double

.. to this ...


Function posit(i As Long, j As Long) As Double

.. and your other subsequent variable types. This thread (http://www.vbaexpress.com/forum/showthread.php?t=599&highlight=long+variables+efficient) may help somewhat; this one (http://www.vbaexpress.com/forum/showthread.php?t=2044&highlight=Long+Integer) too.

BlueCactus
04-13-2005, 05:12 PM
firefytr's fix works for that problem, but you're going to run into a problem with dest.Offset when i and j reach 190, because s will be too high. So you might want to check your formulae in posit().

BTW, you might want to stick an Application.ScreenUpdating = False at the start of your code, and reset it to True at the end. (To speed up the code.)

folk754
04-14-2005, 06:41 AM
Excellent guys, thanks so much for the prompt reply.