Excel Debugger Extremely Slow

[missingsc]

Starting this week, I noticed the functions in my spreadsheet going extremely slow compared to weeks prior. I don't have time indicators because the change is so drastic it's on the minutes order of magnitude. A function call that took seconds last week takes a few minutes this week. What changed? Nothing as far as I can tell, no data change, no code change, I just hit the function again because I couldn't remember if I had run it at the end of last week.

So, I decided to look into the VBA debugger and step through lines, and I now see that the debugger itself is extremely slow. For example, a simple if statement which compares a variable to a string literal, when I hit the "Step Into" option (F8), it takes noticeable seconds for it to move past that simple if statement. It does this on all my lines of code, even when setting a variable to a string literal. Lines such as this take seconds for the debugger to move from one line to the next:
If my_var = "TEST" Then
my_new_var = "TESTING"
End If

Does anyone have a suggestion of what to look at? I've watched the task manager and CPU and Memory do not change much when going through the debugger. CPU is usually 10% or lower, and my Memory is usually 49% or lower. I've tried with all applications except Excel closed and have the same results.

Thank you for anything you can suggest. I really appreciate it.

 

[missingsc]

It may actually be something specific with this one particular spreadsheet. I opened a blank spreadsheet and coded a small function to run, and it ran fine with no lag in realtime or in the debugger. I'm starting to wonder if there is some sort of memory leak in the VBA logic I've written, or is there something with the clipboard because I'm moving, creating, deleting large sums of data in this spreadsheet. Is there any way to check or monitor that?

 

[Joe4]

If you post your VBA code, we might be able to comment on it, and determine if there are some issues with it that could be contributing to your issues.

 

[missingsc]

The problem is I have hundreds of lines of code, but the problem begins as soon as I open the Excel workbook during which I have no code firing. I tested by adding a break to a function at the first line of code that gets hit in the function, which are just variable instantiations. By the second line the debugger already began slowing down. I tried closing the workbook, opening it again and doing the same in a different function, and the same happened. So across multiple functions right at the beginning of the call the lag begins to take place. Do I maybe have too much login in the workbook for it to compile and workthrough? I don't know how extensive workbooks can get in terms of lines of code, but I felt at a couple hundred lines mine wasn't too expansive.

 

[Joe4]

Are there any external links in this workbook?
What is the size of the workbook?
How many worksheets?
How much data?
Does it use a lot of lookup/matching formulas or Conditional Formatting?

 

[missingsc]

No external links in the workbook.
Workbook size is right at 10MB.
There are 9 individual worksheets.
7 worksheets have small amounts of data, under 105 rows. 2 worksheets have around 70,000 rows each.
No lookup or matching formulas or conditional formatting, unless pivot tables and slicers count. One page has several of those. Almost all functionality is in VBA code.

 

[Joe4]

I could definitely see your amount of data (2 sheets with 70,000 rows) and your use of Pivot Table and Slicers affecting performance.

 

[missingsc]

Shoot, I think you might be right. I made a copy of my spreadsheet and deleted all the pivot tables and slicers. Saved it, closed it, and came back in and ran some of my functions. They ran amazingly fast and the debugger didn't appear to have an issue.

So, knowing that... not sure how to proceed. My VBA does a lot of background calculations, moves and combines data between two sheets, and then the pivot tables were used to consolidate and display that information to the user. What's another way to do this without pivot tables? Build my own tables with the data I'm calculating?

 

[Joe4]

You may want to take a look at using Power Query within Excel. We have a whole forum on it here: Power Tools

One thing that you may want to try doing that could help speed up some of your code. Place this block of code at the beginning of each VBA Sub procedure:

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

and then at the end of each procedure (or any other place where you might be exiting your code early), add this block of code:

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True

If your code has a lot of event procedures, does a lot of calculations, or updates a lot of cell values, doing this should help speed your code up.

 

[missingsc]

I gave this a shot, and the results are still confusing. I went into all my subs that manipulate the tables the pivottables are based on and added the code suggestions you made. When I run a function now for the first time, it goes very fast and the pivottables update quickly after finishing. However, when I run the same function a second time, it slows down to a crawl... doing the exact same thing I did a moment ago. No changes were made to anything in the interim, I'm literally running the same calculation back to back, with the second one being supremely slow.

I'm not sure how to proceed here. Could it be the data being read in is being cached somewhere for the second iteration and it's a much slower read?