Identify Which calculations are causing delays

[kgkev]

I have a workbook which used to be very slick. It now runs like a dog and I can't find where the bottle neck is.
Turning calculations off when running portions of code brings its life back, but when I am making manual changes the pauses and delays are unbearable.

I found this 10 year old thread and wondered if anyone was aware of a similar tool which I could use?

Mr Excel
->
NullSkull

Maybe this code will still work, but needs changing for a 64Bit system as this portion is highlighted red

Private Declare Function getFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" _
Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long

any help is appreciated

 

[TedX]

Hi kgkev, I'm certain you know far more than me about both Excel and VBA, but I had a similar experience to yours and it wasn't actually a programmatic issue. My spreadsheet was going like a lazy dog after it had previously been quite robust. The problem was very simple and the fix was even easier.

I had done a few weird tests on on a couple of different worksheets and unbeknown to me at the time, the odd cell here and there almost to the end in both the horizontal and vertical directions had been used and left a remanent there. So when I was doing a regular process, it had to check every cell nearly all the way across and down on the spreadsheet, this was slowing it down as you can imagine.

The fix of course was to just go to the last actual column and then highlight every column to the right and delete any contents that might have been in there. Then go to my last used row and highlight every row down to the last one, delete everything and save it and run the process again and boom instant speed again.

I suspect this is not your issue but until I figured this out I was stumped for quite a while and simply accepted that I had just made too many complex formulas, of course, it was just ignorance on my behalf. Just as a final thought, I used to e-mail my spreadsheet to my girlfriend and it started growing, so I changed to the binary version and that reduced the size. So a cleaner set of worksheets and a smaller file overall and everything was as it should be.

 

[Jimmypop]

I have a workbook which used to be very slick. It now runs like a dog and I can't find where the bottle neck is.
Turning calculations off when running portions of code brings its life back, but when I am making manual changes the pauses and delays are unbearable.

I found this 10 year old thread and wondered if anyone was aware of a similar tool which I could use?

Mr Excel
->
NullSkull

Maybe this code will still work, but needs changing for a 64Bit system as this portion is highlighted red

Private Declare Function getFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" _
Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long

any help is appreciated

Hi there

To update for 64 change

Private Declare Function getFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" _
Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long

to

#If VBA7 Then
    Private Declare PtrSafe Function getFrequency Lib "kernel32" _
            Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare PtrSafe Function getTickCount Lib "kernel32" _
            Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
    Private Declare Function getFrequency Lib "kernel32" _
            Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare Function getTickCount Lib "kernel32" _
            Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If

Maybe test and see if it will work with this...

 

[kgkev]

Thanks Jimmy, that change got it working for me.

nice bit of code and has immediately identified 3 columns which take 1-3 seconds to calculate.

Thanks also Ted, I have been manually going through each sheet. I found one which was keep a log of some data by copying data from one book. I found it was doing a full copy past so I have 10k + conditional format rules on the page. All deleted and code now pastes values only.

 

[kgkev]

which obviously leads me onto....whats the most efficient lookup method?

this is currently running over 1250 rows and takes a little under 2 seconds to calculate

=IF(ISNUMBER(MATCH(LEFT(A6,6),INDEX(LEFT('PO Links'!$B$1:$B$10000,6),0),0)),"Y",IF(G6<>"00","N/S",""))

 

[Fluff]

How about

=IF(COUNT(FILTER(ROW('PO links'!$B$1:$B$10000),LEFT('PO links'!$B$1:$B$10000,6)=LEFT(A6,6))),"Y",IF(G6<>"00","N/S",""))

 

[kgkev]

perfect, thanks Fluff

Would you be able to work your magic on this formula?
its a bit messy, but does work
its one of those which has probably been added to every time I've had a bright idea.

=IF(ISNUMBER(MATCH(LEFT(A6,6),INDEX(LEFT('Ack Links'!$B$1:$B$10000,6),0),0)),"Y",IF(G6<>"00","N/S",IF(ISNUMBER(MATCH(E6,No_acks_lookup!$A$1:$A$1000,0)),"NO_S",IF(ISNUMBER(MATCH(A6,No_acks_lookup!$H$1:$H$10000,0)),"NO_P",IFERROR(ROUND($N$1-VLOOKUP("P"&A6,No_acks_lookup!$C$1:$D$10000,2,0),0),"N")))))

 

[Fluff]

If you change the first part of that formula the way I showed, the rest should be fine.