[clausok]

Good point. I've seen miracles occur simply by refactoring an Excel workbook such that the inputs, outputs, and calculations to map the inputs to the outputs were all delineated into their own clearly-formatted bounded context. And also, when you do drop into vba code, simply organizing the code so that interactions with the sheet are easier to understand, and isolated -- and focusing on "seriality" (no feedback loops, minimize code triggered by events) can transmogrify vba code from the Bogeyman into a friendly neighbor.

[cosmie]

For sure with the transmogrification! If the rest of your workbook is properly designed, dropping into VBA for some array operations and direct references[1] can drastically improve performance as opposed to per-cell calculations and looping. And can cleanly compartmentalize functionality in a way that can be refactored into other languages during post-spreadsheet migration as needed.

Although if it fits your usage, PowerQuery and M[2] can be even more performant, if for no other reason than the data being in a more efficient/compressed format. With the nice side effect of creating logic that's transferable as-is from Excel to PowerBI or SQL Server Analysis Services (making for a clean migration path as your solution matures).

[1] https://www.microsoft.com/en-us/microsoft-365/blog/2009/03/1...

[2] https://docs.microsoft.com/en-us/powerquery-m/