Too fast executions for sorting and formatting? (VBA)

Question

I am in the end of my script formatting cells (mainly interior colors depending on cell value) and sorting the rows with regards to those cells' values.

If I run the whole script in break mode (one line at a time) it executes the procedure perfectly.
However, when I run the whole script from start to finish without pausing the formatting and sorting does not occur.
I have two sub procedures, one for formatting and one for sorting. If I run these again seperately after the whole code is executed they do what they are supposed to do.

The only reason I can think of is that the code is executed too fast for the amount of memory left and some commands become overwritten. I am but a VBA novice however, so please enlighten me if I am wrong.

I have tried activating ScreenUpdating, as well as tried to place DoEvents both before and after the relevant part of the code.

What are your way of handling too fast code execution? Are there other ways other than DoEvents to free memory inside VBA code?

My way of using DoEvents is to place OpenForm = DoEvents on a line where I think the local queue might need to be processed, or together with an If statement and Mod inside for loops.

My sorting code looks like this (The variable ws is the worksheet):

'Sort rows
    With ws.Range("AE2").CurrentRegion
            On Error Resume Next
        .Sort.SortFields.Clear
            On Error GoTo 0
        .Cells.Sort Key1:=Range(ws.Cells(2, 33), ws.Cells(LastRow, 33)), Order1:=xlDescending, Orientation:=xlTopToBottom, Header:=xlYes
    End With

My formatting code is too long and probably not relevant. But it is basically made up of If statements and interior coloring, eg.

If AbsVal < 3 And AbsVal >= 1 Then DiffCell.Interior.Color = vbRed

score 1 · Accepted Answer · answered Sep 08 '17 at 12:22

Faced the same problem myself a few weeks back, I found the solution for it in some forum which sadly I don't remember. Credits go to unknown However, I have the code and it works fine:

Sub Sort()
'Sorting code
Application.OnTime Now + TimeValue(a few seconds depending on your choice, pre), " Color "
End Sub


Sub Color()
'coloring code
Exit Sub

Too fast executions for sorting and formatting? (VBA)

1 Answers1