Reverse Engineering a Spreadsheet into a Model and Controller

Question

I need some help with the approach to a specific project. We have a large excel workbook with a ton of worksheets. There are many user inputs, which formula fields pull, return, repeat. This all ends up in a final report. Each worksheet has essentially become its own Model, View and Controller :).

I want to find a way to split the "model" and "controller" - basically to create a base data dictionary and a rules engine.

Here is what I think my strategy should be:

With VBA,

1. Crawl through the workbook looking up every formula field.
2. Record the results, which kind of becomes the rules engine list.
3. Look at all of the rules generated and any input cell that isn't itself a formula field is likely a raw input field. Have these form the data dictionary.

The above approach is quite flawed - there are raw data fields that have "default values" which are generated via formula. Furthermore, there are formulas that just "fix" fields, like phone numbers. These aren't really "rules".

Before I just jump into the project, I thought I would ask if anyone has done this before, trying to reverse engineer a large workbook into a model and controller in order to create an application? Perhaps you have some insight that I am lacking (Excel is NOT my domain) or perhaps a better strategy?

• Robert Harvey: These aren't really "rules" -- yes they are.
• gnat: please don't cross-post : stackoverflow.com/questions/36578722/… "Cross-posting is frowned upon as it leads to fragmented answers splattered all over the network..."

Answer 1

Don't try to automate this process. Get up from behind your monitor and go talk to some end users. This workbook was built to solve a problem, but that doesn't mean it solves that problem particularly well. Your best bet is to go do whatever it is you need to do in order to understand that problem. Once you understand the problem that needs to be solved, you can do a clean room design of an appropriate solution.

You can always refer back to the existing solution if you need to, but if you simply reverse engineer it, you'll miss many opportunities to build something better than you've got right now.

Answer 2

I had to write a query for excel recently to pull data from a spread sheet and display it on a screen. I'm not clear on what your trying to do but I hope this might help get you started. Use a query in excel to grab the data you need to populate your object/control. I recommend you pull your excel data to a SQL database and populate control from SQL but that's a added step may not be possiable for you. Here is a link for querying Excel

https://support.office.com/en-us/article/Add-a-query-to-an-Excel-worksheet-Power-Query-ca69e0f0-3db1-4493-900c-6279bef08df4