-1

I faced an interesting problem with my macro that parses log files (1GB).

Of course there are some settings as follows:

Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
Application.DisplayAlerts = False
Application.CutCopyMode = False

And also there is one general loop by log file lines with DoEvents within (to prevent excel screen from freezes).

The problem is the macro is very slow when my mouse pointer hovers over cells. Once the pointer moves away from the excel cells, the macro starts working 30 times faster! Any ideas why this happens and how to resolve the problem?

One proposed way is

application.visible=false

but it looks like excel crashes while the macro is running.

sku144
  • 119
  • 1
  • 2
  • 10
  • 4
    Help us to help you, **Post your current code.** – Gary's Student Jun 05 '17 at 12:52
  • 1
    My guess would be to do it in memory (i.e. in an array) instead of reading through the sheet. But as Gary says non of us will have a clue until we see some code – Tom Jun 05 '17 at 12:55
  • @ Gary's Student: Unfortunately it is impossible for security reason. Moreover there are 1k+ rows of code. The only thing I can additionally provide is screencast (please take a look at statusbar). https://vimeo.com/220304328 – sku144 Jun 05 '17 at 13:23
  • OK, can you tell us how the macro is triggered? – J Brazier Jun 05 '17 at 13:28
  • @ Tom: it is not easy to edit such huge part of code as I have to make all operations in memory + it will cause a lot of bugs and all these will eat weeks of work :( – sku144 Jun 05 '17 at 13:29
  • @ J Brazier: custom button from ribbon checking dropdown state before start. – sku144 Jun 05 '17 at 13:32
  • 1
    When reading 1 GB+ of data, I'm quite confident Excel is not the right tool for the task you're trying to complete. Other than that, do as much as possible in memory (see @Tom). Also, since you're reading log files (plain text?) you probably want to stream it and not open and parse (if you aren't already). Have a look at this question: https://stackoverflow.com/questions/1376756/what-is-a-superfast-way-to-read-large-files-line-by-line-in-vba – Rik Sportel Jun 05 '17 at 13:39

1 Answers1

1

Something you can definitely add is:

Application.Cursor = xlWait

Then switch it back at the end

Application.Cursor = xlDefault

This will get rid of the cursor flickering

dwirony
  • 5,487
  • 3
  • 21
  • 43