Code Optimisation - Run Speed

**swizard** · 06-01-2013, 09:46 AM

Hey everybody.

I have a spreadsheet that simulates mixing across a series of mixed vessels. The equations defining each vessel are connected, creating a circular reference so I have enabled iterative calculation in the workbook.

The attached code generates data to go into a chart by evaluating each function at time (t) = 0 to 120. Since the output cell is a formula, I used paste special / paste values to get the actual number at each t.

In essence, the code preforms the Copy / Paste loop 360 times. Is there an alternative method of writing this to speed up the execution of the macro? I can edit the workbook to upload it if necessary.

Thanks in advance.

Sub Ratio_Change_I()

Application.ScreenUpdating = False

Dim Initial As Long
Dim Target As Long

Sheets("Simple").Select

'Define Initial and Target Ratios
Initial = Range("'Simple'!C23").Value
Target = Range("'Simple'!C12").Value

'Loop to get Data Points
For t = 0 To 120

    Range("g2").Value = t
    Cells(t + 2, 38).Value = t
    
    Range("I15").Copy
    Cells(t + 2, 39).PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    Range("r35").Copy
    Cells(t + 2, 40).PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    Range("I35").Copy
    Cells(t + 2, 41).PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks:=False, Transpose:=False

Next t

'View Chart of Data Points
Sheets("Trace").Select

Application.ScreenUpdating = True

End Sub

**tehneXus** · 06-01-2013, 09:57 AM

try

Sub Ratio_Change_I()

    Application.ScreenUpdating = False
    
    Dim t As Integer
    Dim Initial As Long
    Dim Target As Long
    
    With Sheets("Simple")
        'Define Initial and Target Ratios
        Initial = .Cells(23, 3).Value
        Target = .Cells(12, 3).Value
    
        'Loop to get Data Points
        For t = 0 To 120
            .Cells(2, 7).Value = t
            .Range(.Cells(t + 2, 38), .Cells(t + 2, 41)).Value = Array(t, .Cells(15, 9).Value, .Cells(35, 18).Value, .Cells(34, 9).Value)
        Next t
    End With
    
    'View Chart of Data Points
    Sheets("Trace").Select
    Application.ScreenUpdating = True
End Sub