CODEWITHSUNDEEP
excelvba
Evan Goh
Evan Goh

Reputation: 43

Excel VBA for loop causes 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

I am using this code to go through my worksheet making a For loop to change value in Column H by referencing to Column J.

When this code is used on a standalone worksheet, it seems to work perfectly. But once I port it over to a much bigger file which has data connection, and I run this macro only individually, it causes my CPU to run at 100% and takes up to 10 minutes.

Does anyone know why this is happening?

Upvotes: 3

Views: 4688

Answers (3)

John Alexiou
John Alexiou

Reputation: 29244

You are setting values of cells one at a time triggering a recalculation. The way to do this correctly is to read the columns into memory first, set the values and write the results with one 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 case for above code

screen

Upvotes: 1

Joe W
Joe W

Reputation: 111

To help your macro run smoother you can insert the below code before your main code (just below the sub) and right after your code (just before the end sub) This will turn off screen updates, alerts, and set the calculation to manual so no formulas are updating until after the process has ran.

   '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

Upvotes: 3

ArindamD
ArindamD

Reputation: 241

It seems you fell in a trap which has the following features:

  1. You are using a large excel file which is several MB in size
  2. The excel document is full of formula and data connection
  3. Additionally it might have pivot tables and charts
  4. Calculation option for Formula is Automatic

Try this: 1. Go to formula tab 2. Click "Calculation Option" 3. Select "Manual"

Screenshot for your reference

Now execute the macro you have created. It should be good to go. Once the macro is executed. You can change the calculation option.

Note: You can control the calculation option problematically as well using below snippet:

    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 the values (based on formula) everytime any cell is changed. This is done for the entire document for every cell updated by your macro. So, for large excel document, it causes high CPU consumption.

Upvotes: 1

Related Questions