Breakthrough on Testing Excel Files

In my last post I mentioned that I was testing a revamped spreadsheet and was having a difficult time of it because the testing required detailed testing of each cell. The revamp was extensive and the testing is massive (large number of cells and probably at least 15 tabs to go through). I'm not really good at repetitive stuff which is why I try to automate such activities - either by formulas or macros. So I was really just struggling with the question of how to efficiently test the file and do a good job of it.

I finally settled down on the idea of creating a macro to fill in the cells with numbers and then looking at the results. I didn't want the macro to try to fill in every single cell because the majority of them are protected and didn't want the macro to abort due to said protection. I finally worked out a partial process of instructing the macro to fill in certain kinds of cells. I say partial because there are some cells left unfilled which should have been filled but at least the macro takes me much further and quicker. Already, after the first successful pass, I found two calculation errors.

Because my boss might have already found said error, she gave me a new file to work off of. Unfortunately, she has made some significant changes to the file so that my other macros (macros to go with the file) had to be updated. I'm in the midst of updating those macros.

And then, it's going to be a massive effort to test the file.

The key takeaway is to always try to go to partially automated if you can't figure out a full automation. A partial will be better than doing everything manually. Start small and then build on that small effort. My small effort was to at least get a large number of cells filled with numbers to enable testing of calculations. Maybe later on I will figure out how to fill all of the required cells without messing up something, but I can't do that without having that something small to start with.

Just start something.