Hi All,
I’ve got an interesting problem, I’m currently dealing with excel workbooks 50mb <= with macros. Excel is slow and unresponsive at times, I’m unable to change the excel workbook as it’s supplied by the vendor.
Current PC Specs: AMD 5900x, 32GB DDR4 3600 CL16-16-16-16, Gigabyte Aorus M.2 1TB, MSI 3080ti
Will Optane fix this problem?
I am doubtful it will help if you are already working on any SSD, as the entire workbook should be sitting in ram and the disk of little importance until you hit save.
You can consider setting up a ram disk and using the file from there to tell you if there is any benefit for free.
Implementing Optane helps more so, for ramping up [typically used] programs
If its the file that being a limp dog, you should talk with others, that are also using it
In case its the underpinning macros, that is being clunky- forward complaint to vendor
Vendor would tell me to eat a limp dog 
Generally curious about this too. Dealing with shit models that are 20MB with links and Macros that take 5 min or so to run. Not to mention the person that created the model has a million different styles.
Probably not… First, I would test the ram disk if it will be similar here, there is no point in wasting $.
Here, however, I think more cpu has to say than the disk subsystem, considering that you are not currently operating on very slow I/O. The problem may be the software itself, and you won’t do much with it.
Have you experimented with
Enable Manual Calculation Mode
Disable Excel Add-ins
Are you saying more cores = more better 
I’ve already disabled as much as I can already, I think it’s down to poor macro coding.
Exactly, in 9 out of 10 cases, this is just poor coding. But there’s not much you can do about it in this situation… 
Apparently, automatic calculation likes to cause temporary hiccups and the first thing people recommend is to go to manual and F9.
Instead of a thousand cores, personally, I would prefer that the cpu entered a new dimension of the 10/20/30Ghz clock speed, but it will only remain a dream.
Excel accelerator card when? 
Even if I had the resources to create something like this, I still need to convince the software to work with such an accelerator and this is a big problem.
Don’t apply your logic here or we will cry 
haha
I would make a RAMdisk as suggested. That was going to be my suggestion as well. Place the excel file in the RAMdisk and try to open and edit it then. If you dont get any speedup then optane wont help as it is slower than a RAMdisk anyway.
As other said, it is just slow macro coding. You are probably on a single core calculating it from within excel so your CPU having so many cores isn’t really doing much. The only thing that might help is if you had a 5800X3D, the extra cache maybe be able to speed things up. Probably wouldnt help enough o be worth the cost of changing the CPU though.
edit: actually, check about disabling all anti-virus on the PC and see if you get a speedup. Excel Macros is a well known virus vector, so you may be getting some serious slowdown from AV doing stuff with the macro to check it and make sure things are safe.
Thanks for the excuse to buy more RAM! 32GB in the mail
Did you get a chance to test with a RAM disk? Did you see much difference?
Now that you have plenty of RAM you might want to check out Primo Cache which is tiered HDD caching PrimoCache - Excellent Software Caching Solution to Accelerate Storage
You could PIN the excel files to a fast cache tier getting ram disk like performance for it and get really nice caching for your whole system.
Primo Cache is one of my favorite windows program. The same company also makes a RAM DISK product as well " Primo Ramdisk"
I’m curious about this as well and can test.
I found that npm/nodejs builds also speed up considerably when using an absurd amount of ram with primocache
Created a 32GB disk and moved some working files onto it. I have noticed an considerable increase in speed regardless of the bad macros. I might need to buy more RAM 
Happy to send you the excel template to you, so you can see how bad the macros are haha
Well that’s a bit surprising. That means excel is doing something completely retarded (okay, maby it’s not surprising) and hitting the disk for a bunch of things that should sit entirely in ram.
Edit actually Looking at this: Excel Memory Checking Tool: Using LAA to increase useable Excel memory | Excel and UDF Performance Stuff it seems that excel has historically had some weird limitations on how much ram it’s been able to even use, so in order to aggressively keep that potentially needed space it may not even be trying to cache things out that a sane program would. Huh.
Some other things to try, from here: https://techcommunity.microsoft.com/t5/excel/macros-suddenly-run-much-slower/m-p/3136265
Set the folder the file is in as a “Trusted Location”, Developper Tab → Macro security. (likely security implications)
Turn off automatic recalculation when running a macro
Olivierb61
Feb 14 2022 04:57 AM
I had this same problem. Something has clearly changed on MS’s side. Some macros which used to take around 30 secs were taking anywhere from 20 mins to 2.5 hours. This was a sudden change early last week. However, we’ve found a fix. Once we disabled automatic formula recalculation during execution of the macro, everything went back to normal. If anything, faster than before.At the beginning of each function:
Application.Calculation = xlCalculationManual
At the end of each function:
Application.Calculation = xlCalculationAutomatic
Or, a better way, if you want to preserve the user settings of automatic vs manual calculation following completion of the macro:
Sub x()
lCalc = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
''///your code here
.Calculation = lCalc
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub
(Credit to user royUK on https://www.ozgrid.com/ forum for that addition and code snippet)
This topic was automatically closed 273 days after the last reply. New replies are no longer allowed.