Before the addition of Power BI functionality to Excel (starting with PowerPivot in Excel 2010) developing reporting solutions in Excel, although very common, was slow, bug-prone and produced brittle solutions.
In the past I often mitigated these weaknesses by developing a lot of the more complex logic in a back-end SQL databases with Excel mainly providing the end-user UI. For situations where such a back-end was not available or desirable, I developed a .NET add-in called HAMMER, built from a collection of open source libraries, to add a robust database (in-memory) core to my Excel reporting tasks. I use it both standalone, and in component form (HAMMER Inside) to craft other add-ins.
HAMMER (and Hammer Inside) uses a port of SQLite to C# as my SQL engine, IronPython as my .NET Scripting engine, JINT as my JSON handler, and lots of my own code to handle common (but difficult to do in Excel) data wrangling tasks. All delivered in a single, no-install-required, xll add-in file.
My need for HAMMER has reduced as modern Excel has developed over the years (adding PowerPivot, guaranteed MS Access drivers, Power Query, first-class table support from Excel 2007 onwards, etc.), but, for pre-2010 versions, and for tasks that are not possible in VBA (such as some Power Pivot automation tasks e.g. creating Measures) I continue to depend on it.
An example of “HAMMER Inside” in the wild is SEOGadget for Excel.
If you need help with Excel automation, contact me here.