Recalc speed difference if triggered manually or via VBA

So I have 1000 rows x 30 columns doing calculations.
Just basic math in several steps.
A lookup comes in from another sheet and there is a chart with 1000 datapoints da da da da.
No array formulas and no condititional formatting (but there is on the other sheet).
None of this should matter but I wrote it anyway because you're all curious (as would I be).
Edit: There's a UDF I use frequently in this sheet, it works well and seems fast (recently it started fighting me in some sort of event event but that's another story)
And no, sorry, not gonna upload, it's full of client data and work I put my heart and soul into.

Some more basic info:
Windows 10 Enterprise
365 Enterprise
11th Gen Intel(R) Core(TM) i7-1165G7 @ 2.80GHz 2.80 GHz


And now for the question:
If I change something on that sheet it takes about 5 seconds to recalc. 5 full seconds!?! I played with window sizes cause I recall that was a thing at one time, no difference. It claims it calculates 8 threads. I switched off hardware acceleration and what not I could find with a half-donkey googling, no difference.

Switching Calculation from Manual to Automatic, same thing, you have to sit there for 5 seconds while completed percentage slowly goes up...
Same thing when I save.



However...
If I switch to manual calc and then trigger a recalc from the immediate window with application.recalculate it calculates in a fraction of a second!! What is up with that?

So I tried staying in manual and wrote a oneliner macro on the Change event; Application.Calculate. Still superfast!
I have a spinner button that apparently don't trigger a change so I just added the same line of code to the button. Still superfast!

This is nice and all but why can't it be superfast when I just change a number in a sheet manually, with no event macros, like it is supposed to...


Can anyone relate to this problem?
Does anyone have any tips?

This morning I narrowed it down to two suspects, that I think are acting in collusion.

1) The UDF. Yes I used a UDF extensively throught out this troublesome sheet. Is that asking for trouble? Maybe but as always I have this hope that Excel will work as intended. UDF's are probably like asking for trouble, especially if you start playing with Events...

2) A Form Controls Spinner Button. Not only did I get the insanely slow calc respons but the button was also not working properly, it would just go up regardless if I clicked up or down. And it was grouped with a linked textbox and a chart, which I guess is another way of asking for trouble...


I ungrouped and deleted the spinnerbutton and linked textbox and, lo and behold, calculation was fast again. Moving a cell triggers the recalc and is probably a bit slower than just putting in a number.
I choose Form Controls because earlier bad experience with ActiveX directly in a worksheet. My conclusion as of today is that they both suck, just in different ways.

Gonna leave this open for a day or so, tips, inputs and experiences are welcome!

Yes, that is what is used, a Form Controls spinner button. It was super slow. I tried the ActiveX type spinner button also but no difference.
When I removed the buttons completely things sped up by a factor of 10 at least.

Ah yes, clever! I briefly thought about something like that but I decided not to mess with it. It's just for a setting, nothing critical.

If it was for making an interactiv graph (something I like doing) it would be a different story but unfortunately when it comes to these controls, Excel is buggy enough that I may have to learn another plattform to do that with in the future.

Update; I replaced the UDF's (occurring in about 12 000 cells) with a corresponding Lambda function.
Like magic, all problems gone, recalc is way faster than at its fastest before, it's instant.
Used the Forms spinner button, instant update, you can't even tell it's calculating anything.
Works with the spinner button grouped with chart and everything.

Actually the spinner button is a mess anyway, you have to click far down on the down-side of the button or else it will register your click on the up-side.
Oddly enough it happens only on the cursed sheet where it is NOT grouped together with any other shapes or charts. Tried replacing it, no difference.

I guess that would do it yeah. I'm not gonna try that now the spinner button was for a optimizing feature not strictly necessary (any project leader would go ballistic if they knew... ).

I love Excel and my interest and knowledge of Excel has been a very important boost in my career.
Never the less, whenever I try to do something a little bit cool I frequently run into little bugs like the one in this thread is about and it's both frustrating and disappointing.

Well, I think all has been said and done now, I'm closing the thread.