Testing My Macros and Failing

There were five solutions to add to my boss' machine and the first set of macros for the first solution (automation of filtering proper GL accounts when a site is chosen) did not work. Oops.

These macros weren't just any ol' macros - they were macros to go into the PERSONAL.xlsb file and they were event handler type of macros (one was a class macro). I was trying to do the "easy" way for my boss by uploading these macros rather than copying and pasting macros into the PERSONAL file but it turned out to be a bit more difficult.

I think initially we had problems finding her PERSONAL file (my boss said she had a personal file and my memory is a little fuzzy on the order of problem solving) so we thought there may be something different about her machine. My machine is on Windows 10 and hers is not. So to test the idea that her machine was dramatically different from mine, I decided to move on to the other solutions to see if we still had problems importing the macros.

I picked the one I thought should be easy to import: the auto-close spellchecking macro initiated upon closing the file. This macro went through cleanly. Whew!

The next solution was to bring in macros that rearranged data into a format suitable for pivot tables. This macro and the associated pivot tables were embedded into the budget files in previous years but this year, my boss wanted them outside of the budget file because the data and pivot tables used up space and contained information on ALL sites, thus enlarging the file size. Importing the macro into the PERSONAL file was easy but bringing over the pivot tables was a problem. That's when excel started aborting and closing down. The first time that happened, we thought we had lost the PERSONAL file because we could not find it. Usually, when you open up excel, and if you have a PERSONAL file, that file opens up too, but remains hidden. But after the first abort, the PERSONAL file was no longer loading. I looked at my boss and the look on her face was, at least to me, "just keep calm". I was kind of getting worried that this wasn't going to work.

Persist! We went looking for the PERSONAL file through the File Explorer and when that didn't work, through the search box in the START button. Her machine was set up such that there was no option in the File Explorer to unhide hidden files. There were no menu system. The search box in the START button yielded better results but it was still problematic. Searching for PERSONAL didn't always work. I think what worked on her machine was XLSTART. We eventually found it and got it working again.

I think when you "lose" your PERSONAL file or when it doesn't load when starting Excel, you need to find it and open it up manually, to kick start it again.

We still needed to bring over two tabs of pivot tables into the PERSONAL file. In the end, I was able to copy over the tabs, one by one, into the PERSONAL file and, this  is key, re-do the references in the pivot tables. Re-linking the references to the data sitting in the PERSONAL file rather than the budget file was what made Excel keep aborting. I just had to copy over everything very patiently and constant saving of the file.

After pivot tables and associated macros were installed, I proceeded to the next solution that provided my boss options to create reports for our clients. Previously, she had been creating PDFs from her budget files but this year she wanted to give them excel files so that our client could use the numbers in their own excel files. These excel files were to be stripped of their formulas. I figured she would want different choices of what to include in the reports so I created a userform type of macro where a box of options, much like you see in excel, opens up and she makes her selections.

I had to try twice importing these macros. The key for the importation of userform macros is that there are probably at least 3 macros to import: the regular macro that calls the userform box, the macro that does the actions associated with the userform and the selections and a macro for the userform object itself (kind of a picture or form of the selection box). It was the third macro that I missed the first go round.

The fifth solution to bring on the formulas that handle electricity (or water or gas) for startups. That was easy enough.

Finally, it was time to go back to the first solution and try to install it again. By that time, I was calmer and had already successfully imported the other solutions so it was easier to see what the issue was. The first solution entailed two macros: a class module (I still don't understand the class modules) and the ThisWorkbook module. When I exported the ThisWorkbook macro from my PERSONAL file, it was exported as a class module. I needed it to be a regular module. So my solution was to insert a regular module rather than importing and then to copy the macro that I had pasted to an excel file.

Lesson learned: stay calm and keep pushing, trying to find the easiest problem to solve first and then proceeding on to the harder ones. Maybe by the process of solving, you will start to generate ideas on how to solve the harder ones. But take that first step instead of giving up.