excel - Optimizing VBA macro

Question

Macro works as follows:

1. We have a program recording values in intervals of 1-120s, data from it is on Sheet2, dynamic data range B:W columns
2. User is entering target and deviation value on Sheet3
3. On Sheet2, if max value in a row is bigger than "target value minus deviation value" it will start copying rows into table on Sheet1
4. In table on Sheet1 values needs to be displayed every 2-min, so macro will copy every X row (depending on intervals)

Macro is working properly but I need to run it 8 times on 8 different target values. Was wondering if anyone have any idea to speed things up

'Cell address with target value"  
target = Sheets(3).Cells(93, 2).Value   
'Cell address with deviation value"  
deviation = Sheets(3).Cells(95, 2).Value 

'Calculate time between measurements'  
lngRowMultiplier = 120 / Sheets(3).Cells(81, "B").Value 



'First row to copy into'  
pasteRow = 34  
'Row number to search through'  
For i = 2 To 8000 

    'Range to search through'
    s_max_value_range = "B" & i & ":W" & i

    'Max value in a row'
    max_value = Application.WorksheetFunction.Max(Sheets(2).Range(s_max_value_range))

    If (Abs(target - max_value) <= deviation) Then

        'Copy up to 5 hours or until lowest value in a row will be bigger than target value + deviation'
        For j = 1 To 150

            'Minimum value in a row'
            min_value = Application.WorksheetFunction.Min(Sheets(2).Range("B" & i + (j - 1) * lngRowMultiplier & ":W" & i + (j - 1) * lngRowMultiplier))

            If (min_value <= target + deviation) Then


            s_copyRange = "B" & i + (j - 1) * lngRowMultiplier & ":W" & i + (j - 1) * lngRowMultiplier
            s_pasteRange = "C" & pasteRow & ":V" & pasteRow

            'Copy to Sheet1'
            Sheets(2).Range(s_copyRange).Copy Destination:=Sheets(1).Range(s_pasteRange)
            Sheets(1).Range("B" & pasteRow) = Sheets(2).Range("B" & i + (j - 1) * lngRowMultiplier)
            pasteRow = pasteRow + 1
            End If
        Next j
        i = 8001
    End If
Next i

All help appreciated

Answer 1

There are a few Rules for VBA with what you can make your code faster.

---

Rule #1. Don't Copy and Paste

The Copy and Paste (or PasteSpecial) functions are slow. It is about 25 times faster to use the following to copy and paste values.

Range("A1:Z100").value = Range("A101:Z200").value

If you are doing it this way your Code will Probably work. There is maybe a problem with the Mamory if your are doing this on to many Rows.

---

Rule #2. Calculation

Normally, Excel will recalculate a cell or a range of cells when that cell's or range's precedents have changed. This may cause your workbook to recalculate too often, which will slow down performance. You can prevent Excel from recalculating the workbook by using the statement:

Application.Calculation = xlCalculationManual

At the end of your code, you can set the calculation mode back to automatic with the statement:

Application.Calculation = xlCalculationAutomatic

Remember, though, that when the calculation mode is xlCalculationManual, Excel doesn't update values in cells. If your macro relies on an updated cell value, you must force a Calculate event, with the .Calculate method like Worksheets(1).Calculate.

---

Rule #3. ScreenUpdating

The Other Speed Problem with VBA is, every time VBA writes data to the worksheet it refreshes the screen image that you see. Refreshing the image is a considerable drag on performance. The following command turns off screen updates.

Application.ScreenUpdating = FALSE

At the end of the macro use the following command to turn screen updates back on.

Application.ScreenUpdating = TRUE

---

Rule #4 Ignore Events

If you have a Worksheet_Change event implemented for the Sheet1 of your workbook. Any time a cell or range is altered on the Sheet1, the Worksheet_Change event will run. So if you have a standard macro that manipulates several cells on Sheet1, each time a cell on that sheet is changed, your macro has to pause while the Worksheet_Change event runs. You can imagine how this behavior would slow down your macro.

Application.EnableEvents = False

At the end of your code, you can set the EnableEvents mode back to True with the statement:

Application.EnableEvents = True

---

Rule #5 With statement

When recording macros, you will often manipulate the same object more than once. You can save time and improve performance by using the With statement to perform several actions on a given object in one shot.

The With statement utilized in the following example tells Excel to apply all the formatting changes at one time:

With Range("A1").Font
.Bold = True
.Italic = True
.Underline = xlUnderlineStyleSingle
End With

Getting into the habit of chunking actions into With statements will not only keep your macros running faster but also make it easier to read your macro code.

---