What to Do When Your Macro No Longer Works

My usual macro that I use to collect Excel files into a summary report failed me on Monday. But someone else could use the macro which told me that the problem was with my machine, not the macro.

This post will be short since it is late but I do want to point out some steps one can take to figure out what the issue could be. My macro was the kind of macro that dealt with a large number of Excel files: it collected 60+ files into one file.

1. Have someone else run the macro on their machine to make sure the issue is not with the macro itself. If the macro works, then it is likely something on your machine changed.
2. If your macro has to communicate with Dropbox, copy the files from Dropbox to your machine to make sure that the problem does not lie with the communication between your machine and Dropbox. If the macro does not work on your machine, even with the files on your machine, then it is likely that the problem is not with the connection between your machine and Dropbox.
3. If your macro has to collect a huge number of files and combine into one file, try reducing the number of files to be collected. In my case, I reduced the number of files from 62 to 5, and the macro finally worked.

Because my macro finally ran when I reduced the number of files it had to collect, I realized that my "working memory" was impacted. I put working memory in quotes because I'm not 100% sure what was impacted but I felt it was something that had to do with calculations or handling files. Anyway, when I looked at the disk space of my machine, I saw I had almost zero space.

So I spent a good part of the afternoon deleting files to make room and now my macro works.