Updating log on screen fails

[jtable]

Hello,

I created a heavy workbook with millions of entries.
Some macros have to run some operations on those entries, and this takes quite some time.
So, to gain time and still know at all times at which point we're at, I created a specific macro to write a log :

Sub Write_Log(Message)
    For i = 40 To 25 Step -1
        Worksheets("Control Panel").Cells(i, 10).Value = Worksheets("Control Panel").Cells(i - 1, 10).Value
    Next
    Application.ScreenUpdating = True
    Worksheets("Control Panel").Select
    Worksheets("Control Panel").Cells(25, 10).Value = Time & " : " & Message
    Application.ScreenUpdating = False
End Sub

This shifts down the existing log data then writes a message on the top row (25 here)
This works perfectly fine when the macros run fast. But when the amount of data to process gets bigger, then, only the first few messages appear, I get the hourglass during the rest of the work (which is normal), nothing appears in my log cells, and then, all at once, all the log lines appear.

Log.png
(in this example, only "doing server #1" appeared at first.)

I am running this on Excel 2003.

Anyone would have an idea why ?

Thanks

[HaHoBe]

Hi, jtable,

have you thought about deleting the last entry and adding a new cell at the top? Don´t use Select or Activate in your code, and maybe think about using the statusbar for information like this.

Ciao,
Holger

[Ed_Collins]

I second HaHoBe's suggestion... using the status bar to display exactly where you're at can be ideal.

Something like: Application.StatusBar = time & ": Gathering data : doing server #" & server_number

If you want more detail, you can print the row you're on every 10,000 lines or so:

counter = counter + 1
if counter = 10000 then
   counter = 0
  Application.StatusBar = time & ": Gathering data : doing server #" & server_number & ".  Currently at record number #" & record_number
end if

[jtable]

Thank you for your comments.

I used the Application.StatusBar method, but it faces the same problem : it starts showing information, but after a while, it stops doing so, and Excel is "not responding" till the end of the process.
There might be something wrong with the code elsewhere...

What's wrong with using Select or Activate ? I use those a lot in these macros...

Thanks

[HaHoBe]

Hi, jtable,

unnecessary and time consuming as any command will take a minimum of time.

Man braucht den Cursor nicht wie einen Hund über den Bildschirm zu führen.

Please check it by yourself. Open a new workbook, enter these two codes and let them run:

Sub Sample_MoveCursor1()
Dim rngCell As Range
Dim dteStart As Date
Dim dteEnd As Date

dteStart = Timer
For Each rngCell In Range("A1:X1000")
  rngCell.Select
  rngCell.Value = rngCell.Row * rngCell.Column
Next rngCell
dteEnd = Timer
Debug.Print "MoveCursor1: " & Format(dteEnd - dteStart, "0.00")
End Sub
Sub Sample_MoveCursor2()
Dim rngCell As Range
Dim dteStart As Date
Dim dteEnd As Date

dteStart = Timer
For Each rngCell In Range("A1:X1000")
  rngCell.Value = rngCell.Row * rngCell.Column
Next rngCell
dteEnd = Timer
Debug.Print "MoveCursor2: " & Format(dteEnd - dteStart, "0.00")
End Sub

In the ImmediateWindow I found these two lines which speak for themselves (might be less by using ScreenUpdating):

MoveCursor1: 14,29
MoveCursor2: 0,47

Right now I would try and give DoEvents a chance and see if that command helps but first I would work with objects for ranges and Worksheets and avoid Select or Activate.

Ciao,
Holger

[Kyle123]

Select and Activate really really slow down your code. There's no real need to select or activate anything normally

[jtable]

I looked through my code, but didn't find much about Select problems.
Here is a sample of it. The most time consuming procedure :

Sub Gather_Data()
'
    ' Formats data if necessary
    If InStr(Worksheets("Data 1 sec").Cells(1, 1), ",") Then
        Worksheets("Data 1 sec").Select
        Columns("A:A").Select
        Application.DisplayAlerts = False
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True
        Application.DisplayAlerts = True
        Cells(1, 1).Select
    End If
    If InStr(Worksheets("Data 30 secs").Cells(1, 1), ",") Then
        Worksheets("Data 30 secs").Select
        Columns("A:A").Select
        Application.DisplayAlerts = False
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True
        Application.DisplayAlerts = True
        Cells(1, 1).Select
    End If

    ' Cleans unused data if not done before
    If (Worksheets("Data 1 sec").Cells(2, 2).Value) < TimeValue("07:39:59") Then
        Worksheets("Data 1 sec").Rows("2:6000").Delete
        Worksheets("Data 1 sec").Rows("32002:51000").Delete
        Worksheets("Data 1 sec").Range("A3:A33000").ClearContents
        Worksheets("Data 30 secs").Range("A3:A1564").ClearContents
    End If
    
    ' Writes times every 30 seconds to the "1 sec work" worksheet
    For j = 3 To 32000
        If Round((j - 4) / 30, 0) = (j - 4) / 30 Then Worksheets("1 sec work").Cells(j, 3).Value = Worksheets("Data 1 sec").Cells(j - 1, 2).Value
    Next
    
    ' Calculates the "1 sec work" worksheet
    For i = 3 To Worksheets("Data 1 sec").Cells(1, Columns.Count).End(xlToLeft).Column - 1
        'Writes log
        Call Write_Log("Gathering data : doing server #" & i - 2)
        
        Worksheets("1 sec work").Cells(1, ((i - 2) * 2) + 2).Value = Mid(Worksheets("Data 1 sec").Cells(1, i).Value, 6)
        Worksheets("1 sec work").Cells(2, ((i - 2) * 2) + 2).Value = "= ""> "" & $B$4"
        Worksheets("1 sec work").Cells(2, ((i - 2) * 2) + 3).Value = "= ""> "" & $B$5"
        
        For j = 3 To 32000
            ' Writes log
            If Round(j / 5000, 0) = j / 5000 Then Call Write_Log("Calculated " & j & " entries for server #" & i - 2)
            ' If value of current cell from Data 1 sec is greater than threshold T2
            If Worksheets("Data 1 sec").Cells(j - 2, i).Value > Worksheets("1 sec work").Range("B4") Then
                ' Increments value from previous cell if this is not the first line
                If j > 3 Then
                    Worksheets("1 sec work").Cells(j, ((i - 2) * 2) + 2).Value = Worksheets("1 sec work").Cells(j - 1, ((i - 2) * 2) + 2).Value + 1
                Else
                    Worksheets("1 sec work").Cells(j, ((i - 2) * 2) + 2).Value = 1
                End If
            Else
                ' If this is not the second line and if value of current cells from Data 1 sec is NOT greater than threshold BUT the previous one is
                If j > 4 And Worksheets("Data 1 sec").Cells(j - 3, i).Value > Worksheets("1 sec work").Range("B4") Then
                    ' Copies value from previous cell
                    Worksheets("1 sec work").Cells(j, ((i - 2) * 2) + 2).Value = Worksheets("1 sec work").Cells(j - 1, ((i - 2) * 2) + 2).Value
                Else
                    ' Resets last and current values to nothing
                    If j > 3 Then Worksheets("1 sec work").Cells(j - 1, ((i - 2) * 2) + 2).Value = ""
                    Worksheets("1 sec work").Cells(j, ((i - 2) * 2) + 2).Value = ""
                End If
            End If
            ' If value of current cell from Data 1 sec is greater than threshold T3
            If Worksheets("Data 1 sec").Cells(j - 2, i).Value > Worksheets("1 sec work").Range("B5") Then
                ' Increments value from previous cell
                If j > 3 Then
                    Worksheets("1 sec work").Cells(j, ((i - 2) * 2) + 3).Value = Worksheets("1 sec work").Cells(j - 1, ((i - 2) * 2) + 3).Value + 1
                Else
                    Worksheets("1 sec work").Cells(j, ((i - 2) * 2) + 3).Value = 1
                End If
            Else
                ' If this is not the second line and if value of current cells from Data 1 sec is NOT greater than threshold BUT the previous one is
                If j > 4 And Worksheets("Data 1 sec").Cells(j - 3, i).Value > Worksheets("1 sec work").Range("B5") Then
                    ' Copies value from previous cell
                    Worksheets("1 sec work").Cells(j, ((i - 2) * 2) + 3).Value = Worksheets("1 sec work").Cells(j - 1, ((i - 2) * 2) + 3).Value
                Else
                    ' Restes value to nothing
                    If j > 3 Then Worksheets("1 sec work").Cells(j - 1, ((i - 2) * 2) + 3).Value = ""
                    Worksheets("1 sec work").Cells(j, ((i - 2) * 2) + 3).Value = ""
                End If
            End If
        Next
    Next
    Call Write_Log("Done gathering data")
End Sub

You will note there are a few Select at the very beginning, but that is not what takes time. The 2 loops are, on the contrary, very time consuming.
That is why I wanted to use a logging function.

[HaHoBe]

Hi, jtable,

you could try setting objects to the worksheets and using With-Statements whereever more than one action is to be done on one sheet. Maybe some of the data may be loaded into an array and processed in there and be rewritten afterwards opposite to have everything done within a worksheet.

Ciao,
Holger