Excel VBA for Loop calls 100% CPU
Application.ScreenUpdating = False
Dim r As Range
Dim a As Long
Set op = Worksheets("ZVCTOSTATUS")
Set CP = op.Columns("J")
Set CTO = op.Range("J1")
Set OD = op.Columns("G")
Set ZV = op.Columns("H")
op.Activate
fa = op.Range("J" & Rows.Count).End(xlUp).Row
Set r = op.Range("J2:J" & fa)
For Each C In r
CTO = CP.Cells(C.Row, 1).Value
If CTO = "FG BOOKED" Or CTO = "CLOSED" Then
ZV.Cells(C.Row, 1) = 0
ElseIf CTO = "NOT STARTED" Or CTO = "UNCONFIRMED" Then
ZV.Cells(C.Row, 1) = OD.Cells(C.Row, 1).Value
End If
Next C
Hi guys, I am using this code to go through my worksheet by creating a For loop to change the value in column H by referencing column J.
When this code is used on a separate sheet it works fine, but once I move it to a much larger data connection file and I only run this macro individually it makes my CPU run 100% and takes up to 10 minutes.
Does anyone know why this is happening?
source to share
To make your macro smoother, you can paste the below code before your main code (just below a subset) and right after your code (just before the tail end) This will turn off screen updates, warnings, and set the calculation manually so no formulas will update until the process starts.
'Please Before Main Code'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual
'Insert main code here'
'Place After Main code'
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
source to share
You seem to be caught in a trap that has the following functions:
- You are using a large excel file several MB in size.
- Excel document filled with formula and data connection
- In addition, it can have pivot tables and charts.
- Calculation parameter for Formula is automatic
Try the following: 1. Go to the formula tab 2. Click on "Calculated option" 3. Select "Manual"
Now run the created macro. It should be good to go. After executing the macro. You can change the calculation option.
Note. You can also control the calculation parameter using the snippet below:
Dim CalcMode As Long
' This will set the calculation mode to manual
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
<< Add your macro processing here >>
' Again switch back to the original calculation option
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
Excel tries to calculate values (based on a formula) every time a cell changes. This is done for the entire document for each cell updated with your macro. So, for a large excel document, this is very CPU intensive.
source to share
You set the values of the cells one at a time, causing the recalculation. The way to do it right is to first read the columns in memory, set the values, and write the results with a single operation.
Public Sub AnswerPost()
Dim r_status As Range, r_value As Range, r_calc As Range
Dim i As Long, n As Long
Dim op As Worksheet
Set op = Worksheets("ZVCTOSTATUS")
' Find the number of items on cell "J2" and below
n = Range(op.Range("J2"), op.Range("J2").End(xlDown)).Rows.Count
' Set the n×1 range of cells under "J", "G" and "H" columns
Set r_status = op.Range("J2").Resize(n, 1)
Set r_value = op.Range("G2").Resize(n, 1)
Set r_calc = op.Range("H2").Resize(n, 1)
Dim x_status() As Variant, x_value() As Variant, x_calc() As Variant
' Read cells from the worksheet into memory arrays
x_status = r_status.Value2
x_value = r_value.Value2
x_calc = r_status.Value2
' Set values of x_calc based on x_status, row by row.
For i = 1 To n
Select Case x_status(i, 1)
Case "FG BOOKED", "CLOSED"
x_calc(i, 1) = 0#
Case "NOT STARTED", "UNCONFIRMED"
x_calc(i, 1) = x_value(i, 1)
End Select
Next i
' Write the resulting array back into the worksheet
r_calc.Value2 = x_calc
End Sub
Test code for the above code
source to share