excel 2007 VBA eating memory

Hi folks.

Here's a nice little one. I have a little algorithm on a excel 2007 workbook (it's pure data, no formulae on any cell), in order to make it work i've coded a small macro which runs "n" iterations with the data contained on the spreeadsheets. Each iterations takes about 15 seconds.

So far so good... pretty simple as you can see. The catch it's the 2.7GB of virtual memory excel 2007 is eating up on the 800th iteration until it hangs.

The first 4 days i've optimized the macro code to a bare minimum of 40 lines having the same problem with the memory at the end.

Now.... take a look at this.... i've run the macro on several machines with excel 2007 with the same problem... BUT on excel 2003 machines it doesn't use more than 50MB of virtual memory after 5000 iterations.

Does anybody have a tiny little idea of what the h... can i do in order to profit of excel 2007 best performance and run at least 500.000 iterations?

*************************************************
<here's the code>

Sub gen()
Dim a As Long        
Dim I As Long
Dim r As Single         
Dim m As Single        
Dim x As Single         
Dim ai As Long       
Dim aj As Long       
Dim worst As String      
Dim better As String     
Dim temp As Long
Dim I As Long

Application.ScreenUpdating = False

For a = 1 To Worksheets("C.POBLACION").Range("C4").Value
    worst = Worksheets("C.CONTROL").Range("R12").Value 
    better = Worksheets("C.CONTROL").Range("Q12").Value 
    Application.Calculation = xlCalculationManual
    For ai = 1 To 600 
        For aj = 1 To 50 
            If (Rnd() <= 0.5) Then      
                Worksheets(worst).Cells(ai, aj).Value = Worksheets(better).Cells(ai, aj).Value
            End If
            If (Rnd() <= Worksheets("C.POBLACION").Cells(2, 3).Value) Then
                Worksheets(worst).Cells(ai, aj).Value = Rnd()
            End If
        Next aj
    Next ai
    For I = Worksheets("C.CONTROL").Range("B10").Value To Worksheets("C.CONTROL").Range("C10").Value
         Application.Calculation = xlCalculationAutomatic
      Worksheets("C.CONTROL").Range("C4").Value = I
        Application.Calculation = xlCalculationManual
        Worksheets("C.CONTROL").Cells(I - 794, "E").Value = Worksheets("C.CONTROL").Range("E4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "F").Value = Worksheets("C.CONTROL").Range("F4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "G").Value = Worksheets("C.CONTROL").Range("G4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "H").Value = Worksheets("C.CONTROL").Range("H4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "I").Value = Worksheets("C.CONTROL").Range("I4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "J").Value = Worksheets("C.CONTROL").Range("J4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "K").Value = Worksheets("C.CONTROL").Range("K4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "L").Value = Worksheets("C.CONTROL").Range("L4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "M").Value = Worksheets("C.CONTROL").Range("M4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "N").Value = Worksheets("C.CONTROL").Range("N4").Value
    Next I
    Application.Calculation = xlCalculationAutomatic
Next a

Application.ScreenUpdating = True
End Sub

If there are no formulas, why do you keep turning calculation off and on? Also, you'd be better off writing ranges in one go rather than cell by cell:

        With Worksheets("C.CONTROL")
            .Cells(I - 794, "E").Resize(, 10).Value2 = .Range("E4").resize(, 10).Value2
        End With

rather than:

        Worksheets("C.CONTROL").Cells(I - 794, "E").Value = Worksheets("C.CONTROL").Range("E4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "F").Value = Worksheets("C.CONTROL").Range("F4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "G").Value = Worksheets("C.CONTROL").Range("G4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "H").Value = Worksheets("C.CONTROL").Range("H4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "I").Value = Worksheets("C.CONTROL").Range("I4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "J").Value = Worksheets("C.CONTROL").Range("J4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "K").Value = Worksheets("C.CONTROL").Range("K4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "L").Value = Worksheets("C.CONTROL").Range("L4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "M").Value = Worksheets("C.CONTROL").Range("M4").Value
        Worksheets("C.CONTROL").Cells(I - 794, "N").Value = Worksheets("C.CONTROL").Range("N4").Value