Last Remaining Problems on Macro Programming

For my automation project, I think we are at the end stages where the focus will be on 1) how to reconcile imbalances and 2) sussing out any calculation errors we want to change. On the second item, this is going to be an outcome of situations that I hadn’t thought of due to being new in the industry. When developing this automated report, the original intent (I think in the minds of those requesting this report) was to just train the folks on our side to complete the spreadsheet being used to consolidate status for our executives. When I looked into this spreadsheet, I decided that I would try to incorporate the downloads into the file itself instead of requiring the managers to key the data in. Since the managers hadn’t been doing this report, I figured that they would be resistant to doing the report and therefore would be the most amenable to automation as a way to cut down their time spent in front of the computer inputting data. Later, the automation via formulas changed to automation through use of both macros and formulas, especially after discussions with those who already manage the consolidation process. (There were two groups: one group had been doing the report for years and the other group hadn’t.) It turned out that there were some additional steps that required the use of macros: basically shifting data to the left to reflect the passage of time. Anyway, I used the core formulas such as summation, averages, etc to develop the totals.

Yesterday while going through one of those weekly updates I found a logic error that crops up under certain situations. I confirmed with my boss that yes, it was not producing the right calculation and then my boss found more incorrect results. After yesterday’s discussion, we decided that we were going to have to go through the spreadsheet to see what else fails in certain situations.

So that is one of my remaining problems: finding hidden logic errors that are invisible until a certain situation (whatever it may be) arises. The other remaining problems are: 1) being able to create a macro that will write changes into a macro module; 2) being able to open, via macro, files that contain auto-open macros; and 3) write, via macro, array formulas into cells. The first problem I haven’t figured out yet, not even a workaround. For the second problem, my boss and I discussed the option of using a button to replace the auto-open macro. The reason why I want to be able to open files with auto-macro is because those files contain lots of data that the managers have downloaded. It would be nice to be able to access that data without me physically open up the files. Right now, I can open files via a macro but only if those files do not contain an auto-open macro. A macro button replacing an auto-open macro could solve that problem. For the third problem, my workaround is to have the array formulas already set up in another file and then use the macro to copy those array formulas to the file and cells where I want them to be. Right now I can’t write them directly into the cells even though I keep the bits length below 255. I have tried various permutations found in Stack Overflow on the web, but to no avail. My workaround is not elegant but it works for the time being.