Chapter 8 - The Master Macro

I bring good tidings. Upon my return from Spring Break, as promised, I have completed the algorithm. I have also developed a macro that honestly exceeds even my original expectations. As I mentioned in my last post, I solved the problem with the out of range algorithm (see the 3/10 Update in the last post). From there, I had to begin testing some data, making sure to test the algorithm for every possible scenario. First, I needed to test the algorithm to see if it correctly pulled out the data points that had just temperatures above 70 degrees Fahrenheit. I then needed to make sure that it pulled out the data points that had just relative humidity above 55%. Finally, I needed to ensure that it pulled out the data points that had both high temperature and high relative humidity.

I began testing with some of the data from February. As mentioned in the 3/10 update, at first, I didn’t feel like the algorithm was working properly as there were no points with high RH. However, after further review, I noticed that it was correct because there were actually no data points with high RH (in the data I was testing anyway). So to test the algorithm properly, I had to make up a few data points. I created data points that had just high RH and then a few that had both high RH and high temperature. After adding those points in, I ran the out of range macro and took a look at the results. From what I could see, the algorithm seemed to be working perfectly. It pulled out the ones with just high temperatures. It also pulled out the data points I created, ensuring me that the algorithm was functioning as anticipated. I was excited to see the algorithm working as intended. Next, I tested it with all of the data points from each of the devices to see what the results were. As before, it pulled out the correct out of range points for each device. With each data set tested, I was confident in the algorithm and I was ready to move on.

With the out of range algorithm in place and looking good, I had to start working on a procedure that would get the data from the device spreadsheets into the master spreadsheet. As mentioned in my last post, I determined that it was unnecessary for the user to have to input anything. All they need to do is copy the data from each device’s spreadsheet and paste it into the separate sheets in the master spreadsheet. From there, I can make the macro run for each sheet, thus avoiding the user from having to enter anything in. As such, errors should be at a minimum and the data points should stay connected to their respective devices.

With that in mind, I had to figure out the best way to approach this. The first thing I did was copy all of the data from each device and paste it into the individual worksheets in the master spreadsheet. I then recorded a macro that automatically clears the data. My plan is to use this master spreadsheet either in the individual monthly folders, or as one main spreadsheet in the root directory. As such, I created a small, quick macro that cleans all of the data out of the six worksheets, allowing it to be used again next time. With the clear data macro up and running, I had to get the data back in the master spreadsheet. This time however, I wanted to see if there was a way that I could create a macro that would automatically open the files and copy the data in. I had never attempted this in any macro I had created before, so I was not sure if it was a possibility. After I ran some quick tests, I realized that it was definitely possible. This was the moment where I began to realize that I may very well be able to record a single macro that would automate the entire process. Needless to say, I was stoked. I originally assumed I was going to have to make a few macros and have a few steps in between. However, after seeing you could have the macro open and take out the data, I determined the user may very well just be able to run a single macro. In excitement, I continued on.

I completed the macro that opened up each individual device spreadsheet. I had the macro open up each spreadsheet and copy the data to the corresponding worksheet in the master spreadsheet. After recording the macro, I opened it in the visual basic editor to see what it looked like. This is where I noticed a potential problem that I feared when recording it. To open each spreadsheet, it had to set the directory to where each file was. In the macro, it opened the directory for February 2008 so it could open the files. So every time this macro would run, it would automatically open the February 2008 folder, regardless if you wanted it to or not. This is an issue as when you go to run the macro for March, April and so on, it would automatically open February’s folder.

At this time I am still working on the best way to fix this. Worse case, I will have to make the user go into the macro and change the folder path to the monthly folder they are working with. However, that could be rather complex and it is something I would prefer to avoid since a single mistype could stop the macro from functioning properly. So I will need to find a way to have the macro realize that the master spreadsheet is in the same folder with the rest of the device spreadsheets. This way all the user has to do is put the master spreadsheet in the monthly folder they just created and it can just open up the files regardless of which month. However, this pretty much eliminates the single master spreadsheet idea. There would need to be a master spreadsheet put into each new month.

I also determined another possible problem. If the files are not named exactly as they are in the macro, or are not in the folder, it will not run correctly. So if the name of device has to change later on, or the user changes the name of the device spreadsheet by accident, the macro will simply stop and cannot be run to completion. This is just one of the problems that can occur when trying to create complete automation. To get complete automation, I have to be aware that automation comes with the higher potential for problems. However, I feel complete automation is the best way to go. In the end, I am still trying to figure out how to correct this macro. There are a few more things I think I can test to see if they can help solve this problem.

With that said, I decided to move on and start wrapping up the whole process. Once the macro opened up all of the files and copied the data in, I recorded a macro that sorts each worksheet’s data by temperature and then RH, both descending. Then I recorded another macro that runs the out of range algorithm for each sheet. This took out all of the out of range points for each sheet. Then I recorded yet another macro that sorts the out of range points by date and time. From there, I needed to make one more macro that would copy out the out of range points from the individual sheets and then paste them into one big sheet. The macro I created extracts the out of range points and neatly puts them all into one sheet so that it is easier to track trends. Finally, I recorded the macro that clears the compiled out of range data worksheet so that the master spreadsheet would be completely cleared and ready to be used next month.

With those completed, I had several macros that needed to be run in order to complete the entire procedure. I ran through the whole procedure, running each macro in the proper order to make sure they worked correctly. I did notice a few errors, nothing with the macros themselves, but an overall concern with all macros that involve copying data. When the user runs the macros, certain cells must be selected for it to run correctly. For example, most of the macros I created need to have cell A3 of the first sheet selected in order for them to copy correctly. If it is on another cell, it may copy the wrong information or it may just copy blank cells, thus making it run incorrectly. In an effort to avoid this problem, I went through each macro I created to see which ones needed that cell selected. I wrote down which macros needed it to be in cell A3 so that I could make sure to note that in the documentation. This will allow the user to know which cells need to be selected before they run the macro. With that done, it was time to make the master macro.

When starting this whole thing, I was not expecting to get complete automation. I was hoping it was possible, but I expected to run into problems at some point. However, it appears that this dream is coming true (disregarding the problem with the opening folders macro). With all the macros ready to go, I simply just recorded one more macro that ran all of the macros together in order. All the user has to do is literally open up the master spreadsheet (they may have to copy it into the new month folder first depending on what I do with the open folder macro situation) and just run the master macro. From there it will open the files, copy each device’s data into the spreadsheet, sort the data by temperature and RH, pull out the out of range points for each device, sort those out of range points by date and time and then paste them all into one sheet. From there, all the user would have to do would be to copy out that final results worksheet with all of the out of range points into another spreadsheet so that the results could be kept for each month. Once the user pulls out the results and saves them into the new month folder, they can then run the macro to clear the data in the master spreadsheet so that it can be used next month. If all goes well, the results are saved in a separate spreadsheet so that they can be viewed whenever. So months later, you could see what the data was like in February and compare it to the data in say December.

I took the results I collected for February and showed them to Pam so that she could review them. After getting the results I did notice something that needed to be changed. The final results worksheet with the out of range points was very large. It had roughly 500 out of range points for each device. There were simply too many points to make the data effective. This stemmed from the fact that we set the temperature limit to 70. There were a lot of data points in which the temperature was higher than 70. Before I could ask Pam, she noticed the same thing and she said we needed to up the limit for the temperature. The results only had one data point where RH was higher than 55%, so the RH limit was perfect and did not need to be changed. But there were way too many points above 70 degrees. So Pam decided that we should increase the limit to 73. This was a very easy fix. All I had to do was go into the out of range algorithm I made and change the greater than 70 to greater than 73. I reran the master macro, copied out the new out of range results and voila, we had far better results. There were a few out of range points for each device now, far easier to read and track trends. With this new limit, Pam should be able properly monitor the results to see where there could be some potential problems.

Pam was very excited to finally see these results. She commented to me that this was a realization of a three year dream. I was very glad that she was so excited about the data. It made me feel very good to know that I had completed what I had set out to do. I created a procedure that took the HOBO data and made it useful. This was the main objective of this project and I have completed it. So from now on, Pam can run these procedures and start collecting out of range worksheets, allowing her to track trends. In fact, if needed, she can even take some of the older data and run them through the master spreadsheet to see what some of the older data looked like. To do that however, I would have to include a section in the documentation for this as most of the old data only had four worksheets instead of six.

For the most part, the procedure is complete. I have taken the data pulled from the HOBO devices, extracted the out of range points and gotten them all into one, printable sheet. All that remains is for me to fix the folder opening problem, touch up the procedures, and document as much as possible. I need to make sure to document the new master macro procedures and I also need to document how to change the name of the devices not only in Box Car Pro, but in the master spreadsheet just in case the devices need to be renamed. I also need to document a procedure so Pam can run the master spreadsheet with the older data which only have four devices. This way she can now at least make use of all of data that has she been collecting for the last three years.

In the end this week was a very good week for the project. The project is pretty much completed. As mentioned, I do have a few things I need to touch up, but I should be completed in time to meet my deadline date of March 28, next Friday. I am glad to see that the project has gone so well. I am also glad to see that Pam is so excited about all of the procedures and the out of range results worksheets.  So far the project has gone very smoothly and most everything has gone according to plan. I am just as excited as Pam is at getting the procedures in place. With them in place, her staff will be able to help her monitor the library. I hope my efforts will help keep the mold out of the library for good.

With that said, this story is not yet at an end. Next week I will discuss the macro folder situation, showcase some of the final documentation, and I will discuss what the few remaining posts will entail. Until then, thanks for reading.