Chapter 10 - Macros & Documentation
As mentioned last week, this week would be very quiet. I was very busy all week completing an interactive resume for one of my courses, which consumed all of my time. The IR can be found at http://www.andrewbertino.com. I also spent most of last week recording a How-To-Video to include on that IR. You can view the How-To-Video here. With both those going on, and with the fact that project is pretty much complete, this week was a quiet week. Well a quiet week for the project anyway. The last two weeks have probably been the busiest two weeks I have ever had. However, this week I am still going to share with you some of the macros, the final project plan and all of the final documentation. Next week, I will have one final post that goes over what I have learned from this entire experience.
Final Project Plan
First let me share with you the final project plan. I cleaned and tightened it up a bit and then turned it in, thus completing the assignment for the course. I added a few new things as well, including some cost values for repairing the mold damage. Putting this information in there shows the importance of the project. It also shows that if the project is not complete, these costs could come back. To give you some context, the last time the library had problems with mold, it cost them roughly $65,000 to simply replace the carpet with rubber flooring. This does not even include other damage to walls and books. I also included a “wow” number that Pam calculated for me. Pam did some research, and based on the average prices of IT and technology related books, the estimated replacement cost of the Goldstein materials is approximately $3,435,126.89. Granted not all of the material would be damaged of course, but this figure shows that mold is a major problem and that this data and this project is important. So, without further ado, here is the final project plan.
Download the final HOBO Project Plan
Final Documentation
Next, I am going to share the final documentation. On top of the documentation for the downloading procedures, I created documentation for pulling the out of range data and for changing the description and the interval of a single device in BoxCar. I also touched up and changed a few parts of the downloading procedures documentation as well as the HOBO device locations. Below are the final versions of all documentation.
Creating the Out of Range Results
Changing a HOBO Device’s Settings
The Macros
Finally, I am going to talk about the final macros a bit. First, click here to download the master spreadsheet. Now, if you are using Excel 2007, go the View tab and then all the way on the right there will be Macros. Click the macro icon and this will bring up the macro list. If you are using Excel 2003 or earlier, click Tools, go to Macro and then select Macros. Once you have the macro list open, you will see all of the macros I created for the master spreadsheet. To view the visual basic code for any these macros, highlight any macro and then click Edit. This will load the visual basic editor where you can review the macro’s code. With that said, here is a description of each macro.
ClearMaster - This macro simply clears the entire HOBO_Master spreadsheet so that it can be copied over to next month’s created folder and used again next month.
Copy-To-Results - This macro takes all of the out of range data points for each sheet and puts them all into one final worksheet called Results. From there, the user can copy just that worksheet out and save it as the results spreadsheet for that month.
MasterRun - This is the simplest macro, but also the most powerful. All this does is run all of the other macros in order. So once the user copies and pastes in the data, they can run this macro and it will sort the data, run the out of range macro and copy the data to the results worksheet, all in one click.
OutOfRange - This macro is the main algorithm. This is the only macro that I had to hand code. The rest were simply recorded using the Macro record feature. This macro goes through each data point (for which ever worksheet it’s on) looking to see if the temperature is above 73. If it is, it then checks to see if the RH is above 55% as well. If it is, it copies out that record and pastes it into another section. If the temperature is not above 73, it looks to see if the RH is above 55%. If it is, it copies that out. If it is not, then the point is a good point and it skips to the next one. This macro only runs on one sheet at a time.
Pull_ALL_OutOfRange - Since the OutOfRange macro can only work on the worksheet the user is currently on, this macro goes to each sheet and runs the OutOfRange macro automatically. Basically this macro is running the OutOfRange macro six times, one for each device worksheet.
SortData - This macro quickly sorts all of the data in each sheet. Once the user pastes the data from each of the individual device sheets into the master spreadsheet and runs the MasterRun macro, this is the first macro that it runs. It sorts the data by temperature and then relative humidity.
Well that is it for this week. I have shown you the final project plan, the final documentation, and I have described the macros. As mentioned, next week will be the last post. In this post, I will discuss what I have learned from this project and the entire process. Until next week, thanks for reading.