Excel Visual Basic - Slow Code RRS feed

  • Question

  • I have a pretty complicated piece of code that is used for designing equipment, looking at several dozen parameters and coming up with an optimized design.

    In the past, the code has run pretty quickly (hundreds of combinations per second), but recently it now takes 2 seconds per combination.

    If I break the code during its execution and then resume, it executes the code lightning fast. But if I just run the macro and leave it alone, it takes forever.

    Any ideas why the code would run faster after the interruption?

    Thursday, September 3, 2020 2:01 AM

All replies

  • I've had this problem previously but it was in Excel 2003.  It was due to memory leak.  You could watch the memory usage grow using Task Manager.  This has been fixed.  I noticed a weird problem with garbage collection in  Office 365.  When I run a complex process in Excel 365 it would fail due to not disposing of prior objects.  When I single step it gives it time to dispose of object and proceed correctly.  I ended up changing the order of tasks.  It took me almost a full day to isolate problem.  When I run it on Excel 2010 I have no problems.  What I would do is use the Timer function to measure times for different processes in flow and log them to a text file.  This should identify the specific step causing problem.  I use this to identify database queries issues. 
    Thursday, September 3, 2020 1:40 PM