0

I created an Excel sheet with a VBA macro. The data written is read from a binary file. From this binary file, I consider every x byte represents a frame. A frame is written on a row, with a header.

First issue : As I write those data, there is a noticeable performance leak over time : the first 250 frames are done under a minute, then the next 150 take one minute. Overall, at the beginning of the process, ~10 frames are done in 1 second. After a few hundreds of frames, it takes more than a second to process 1 frame. Time is measured by hand, according to a progress bar.

Second issue : I have a Worksheet_SelectionChange event on the sheet that calls Cells(row, 1).EntireRow.Hidden setting it to True or False depending on the situation. After some debugger inspection, I have realized those instructions take an increasing amount of time depending on the amount of data written during the session. Example, it takes a good 10 seconds with ~400 frames.

Now the part that makes me think there is room for improvement : If I save the file, close it, reopen it, and change the selection, Cells(row, 1).EntireRow.Hidden takes an insignificant amount of time. I'd even say, the expected amount of time. If I only save the file and keep it open, there is no noticeable change.

What's I've tried : In order to improve performances, at the beginning of the main sub, I call :

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    Application.Calculation = xlcalculationmanual
    ThisWorkbook.Date1904 = False
    ActiveWindow.View = xlNormalView

(and swap them back to their original values at the end)

I already avoid as much as possible the use of Select and Activate. I also avoid arrays, using Collections instead, since I've read its faster. I use the With statement whenever necessary. I make sure the Worksheet_SelectionChange is disabled before writing into the file.

I believe both issues are related, but I do not understand what is the cause or how I can solve it. What am I doing wrong ?

EDIT: Using Application.SaveWorkspace every 100 frames does the job : it pauses the process (visibly thanks to the progress bar) for ~2 seconds but the amount of time saved to process the next 100 frames is entirely compensated. I think that my problem is solved now, unless there is a cleaner way to do that. Thanks a lot to all of you in comments !

EDIT 2 : Problem solved : I was adding comments to some cells, using selectedCell.Comment.Shape.TextFrame.AutoSize = True every time like so :

    With sheet.Cells(row, column)
        .AddComment txt
        .Comment.Shape.TextFrame.AutoSize = True
    End With

The goal being to make them look pretty. For some reasons, this is what was slowing down the whole process each time it was called.

Instead, I now call the following snippet at the very end. For the information, 100 frames are now processed in under 10s, and the process time is constant. For ~400 frames, the following snippet took 16s (approx).

    Dim comment As Comment
    For Each comment In sheet.Comments
        comment.Shape.TextFrame.AutoSize = True
    Next comment
ape
  • 21
  • 5
  • 1
    First issue: does the performance improve if you comment out writing to Excel cells? Does the performance improve if you remove the `Worksheet_SelectionChange` function? Are there are event handlers? – Łukasz Nojek Jul 10 '19 at 13:16
  • @ŁukaszNojek I've just tried, the performance is significantly better and most importantly linear without writing to the Excel cells. There is no performance change when the `Worksheet_SelectionChange` event is empty of code. I didn't get your question about event handlers, can you develop on it ? – ape Jul 10 '19 at 13:22
  • I understand that you checked both cases separately - not writing to Excel with the SelectionChanged sub, and writing to Excel without the SelectionChanged sub? By event handlers I meant functions similar to `Worksheet_SelectionChange`. Are there any other? – Łukasz Nojek Jul 10 '19 at 13:37
  • The only other event on the sheet is `Worksheet_Activate`, that I've also emptied during the test. I've tested both cases separately and together, to be sure they didn't impact each other. Result went accordingly to what was described. – ape Jul 10 '19 at 13:44
  • Do you do any formatting to the rows/cells? Can you show the code that writes a row? I try to do as many bulk operations as possible, so e.g. I don't set format on a cell in 3rd column immediately when creating a new row, but I set format for the entire 3rd column or range "C2:C700" at once after I finished writing. – Łukasz Nojek Jul 10 '19 at 14:04
  • I actually do, yeah. I'll move all the formatting to the step following all the writing, and edit this comment with the perf. result. – ape Jul 10 '19 at 14:08
  • The performance while writing is indeed better, but the slow down takes effect on all the formatting now, being overall slower :( – ape Jul 10 '19 at 14:30
  • I could see if I can help if you shared some example that is slow. I have no further generic ideas ;). – Łukasz Nojek Jul 10 '19 at 14:33
  • I'll see what I can do about that :) – ape Jul 10 '19 at 14:56
  • Would you accept moving it to chat ? It would be more fitting to our conversation, i think – ape Jul 10 '19 at 15:11
  • I would post all code there may be room for improvement - I believe that arrays are faster than collections, where did you read otherwise? - great observation re the slowdown over time - maybe has to do with memory allocation? – learnAsWeGo Jul 10 '19 at 15:19
  • 1
    All code represents over 2000 actual lines of code, not including comments and blanks. Plus its company work, i can't just publish that online. I'm trying to isolate parts of the code that may be related. It does look like its related to memory allocation, since closing & reopening the file does the job, but i can't find in what way. – ape Jul 10 '19 at 15:38
  • still learning but you may want to take care to set objects = nothing see the following: https://stackoverflow.com/a/38850956/9721351 – learnAsWeGo Jul 10 '19 at 17:21
  • 1
    I'll watch that closely, thanks @learnAsWeGo, you taught me something! – ape Jul 10 '19 at 20:39
  • 1
    Consider reading your data into an array, and then inserting that in one hit into the Excel sheet. Then hide the relevant rows in one command/function (what that looks like will depend on how you choose rows to hide). This approach should significantly improve performance. Of course, without any code and sample (sanitised/anonymised) data can't provide specific hints. – AJD Jul 10 '19 at 22:00
  • @AJD It is indeed a lot faster just sending an array to a range, it helped a lot. @learnAsWeGo setting objets to Nothing does help, but it is not very noticeable because i use a lot of primitives. Right now I use a `Application.SaveWorkspace` every 100 frames and it does the job. Despite pausing for a second, the amount of time saved afterward is a very good compensation. (question updated to add this) – ape Jul 11 '19 at 08:16
  • For whoever follows this thread, problem is solved, explanations in EDIT2 of the question. Thanks ! – ape Jul 11 '19 at 14:35
  • @ape I wonder why application.saveworkspace would speed up? – learnAsWeGo Jul 11 '19 at 15:42
  • I'm not sure, it also created an additional file (extension was .xl something) that referred to the Excel file. To be honest, the speedup was seen once, may be a fluke. I should delete that from the main post, maybe – ape Jul 11 '19 at 16:02
  • @ape maybe it moves memory out of RAM into hardrive or ssd? – learnAsWeGo Jul 11 '19 at 18:43

0 Answers0