As mentioned last week, this week will be the final post. Since I have completed the project, I have really nothing more to add to my previous posts. I do have my presentation this Thursday with Pam to go over the entire project. My plan is to first go over what my initial plans were and what I thought about the project before I began. I will then go over what I did with the folder scheme and the map that pinpointed the devices. I will demonstrate the folder scheme on the computer, showing off the before and then the after. I will then go through an entire downloading session. I will gather all of the data from the devices and put it on the HOBO Shuttle. From there, I will show how to go through the entire downloading procedures and how to get the data into the spreadsheets. I will then demonstrate how to get the out of range results sheet using the macros I created. Finally, I will wrap up by discussing what needs to be looked at in the future and what possible things could be improved. I feel confident about my plan for the presentation and I think it will go very well. With that said, to wrap up this blog, I am going to discuss my experiences with this project. This will include what I learned, what could I have done differently, etc.

I have to say that I am somewhat surprised with how well everything went with this project. For the most part, the project unfolded exactly how I expected it to. When I expected to get something done, I was able to do it and move on. There were some rough parts here and there where I had to wait to download the data, or I had to stop for a day to do other work, but pretty much everything went as planned. The new folder scheme came together just as I had planned. The revival of the two none working devices was actually easier than I expected. I was expecting to have to do some research or price up new ones, but Pam found two more working devices elsewhere and I just put those into commission instead. The downloading procedures came together quite nicely. One of Pam’s staff members assured me that my documentation was very easy to follow. She was able to properly download the data using them, so I had to make very little changes to the documentation.

Now, the out of range macro was a little more up and down. At first, I feared that it was going to take longer than I originally thought. However, as I began working with it, I found out that it was going take me about as long as I had expected. In fact, I was able to make it better than I anticipated. I never expected to make it as automated as I did. However, the more automation brought about other issues to worry about, so I had to spend some extra time making sure that I wasn’t sacrificing ease of use in order to get full automation. Particular parts were just too tricky to automate, so in the end, I had to give up a bit of automation to make it more stable. In the end, I feel that the out of range procedures ended up being very balanced. Most of it was automated, but to make it stable, some basic user involvement was needed. Overall, everything with this project went as planned. However, I need understand that not every project will be like this and that there will be future projects where things will not go as smoothly. When planning for any future projects, I must still take into account unforeseen complications, even though I ran into very few issues with this project.

Even though the project did pretty much go as planned, I still ran into some challenges. My biggest challenge was just managing everything on my plate. With two jobs, other classes and the project, I can easily say stress was high. However, having dealt with this much on my plate for two years now, I have somewhat gotten used to it. So as usual, I buckled down and did what needed to be done. However, it is and will always be a challenge. With all this going on, I had to make sure I still set time away to do this project and complete my tasks for the week. This was sometimes frustrating as I would want to get a part of the project done real quick, but would have to put it aside because I had more pressing issues to attend to. I also ran into that the other way as well and it was just as frustrating. I would be free of other school work and such and would be ready to start working on the project only to have to wait for something else. I overcame both these frustrations but just moving on ahead. When I was waiting to be able to download the data, I worked ahead and began working on the out of range algorithm or started the documentation. When I was busy with other work, I just sacrificed other things to fit in some time for the project, making sure to continually move ahead and keep on schedule.

Throughout the project I realized that I was using many of the skills that I had learned from the classes I have taken. The technical communication skills I learned in my classes were crucial here. Also, assignments that dealt with understanding your users were also helpful. There is not a specific class that helped me out since almost every class dealt heavily with communication and users. However, there is no doubt the skills I gained from these courses aided me in this project. When I began writing the documentation I had to make sure it focused exactly on my audience. I had to make the documentation easy enough to follow for someone with minimal technical knowledge. I assumed that the user going through the procedure had the basic understanding of Windows, navigating folders and using Excel, but I still made sure to note more advanced features or make sure I detailed more complicated steps. I used screen shots to help the user along. The documentation was made better thanks to the skills I had picked up through many of my courses.

I can easily say that this project helped me mostly with time management and planning ahead. With having so much going on around me, I had to quickly learn how to plan and estimate time properly so that I had enough time to get everything completed. I also learned how to communicate with a manager in a more professional environment. Sure I had done projects at school before and I do some projects at work, but this was different as it was truly a full length project. The projects I had done before this one were smaller projects and were done usually in a day or a week. With this project, I had to work with Pam for over three months, keeping her up-to-date, asking her questions, getting feedback, making sure I was on track, and more. Throughout all of this, I learned more then just time management and communication skills. I also learned some new technical skills.

Even though I use Excel at work all the time, I still managed to learn a few things about some macros. I learned that opening up files using macros is tricky and leads to potential folder and file location problems. I also learned how to better code if then and else statements that are more than one level deep. I also learned a lot about the HOBO devices. Coming in, I did not have the first clue about HVAC monitoring systems. I had no knowledge of the devices before, so I had to read up on the devices and I had to learn how to use the software. I learned this mostly from just looking through some web documentation and by just trial and error, trying things here and there seeing what worked and didn’t work. This project taught me a lot about these devices, how they work and how they collect data. I learned how to change the device’s settings and how to use the software and tools to download and store the data. Now I may never run into the HOBO devices again, but should I ever need to implement an HVAC monitoring system, I will have some useful knowledge gained from my experiences with the HOBO devices to help guide me along.

Other than the communication skills, and the understanding of my audience, the many projects I completed for all of the courses definitely prepared me for this larger project. At no point did I get overwhelmed. I had managed several smaller projects before for several other classes. So when I had to gotten to this project, I felt like I had done most of this before. I knew where to start and how to attack it. I can easily say that all of the projects I had to complete for my courses most certainly prepared me for this project and will prove helpful with the projects I will face in the future.

Finally, for this project, I had to learn how to install this blog. I learned how to install WordPress on a server so I could get this blog up and running. It may seem like something trivial, but it was something I had never done before. By following the online installation guide, I was able to install it and set it up. Once again, through reading documentation and through trial and error, I learned how to setup WordPress. In the future, should I ever need blogging software, I know where to turn and the skills I gained from installing WordPress for this project will be helpful.

As mentioned, this project taught me a lot about managing time in regards to long-term projects. For the most part, I felt my time estimation was quite good actually. There were some small miscalculations here and there, and that was mostly due to having to wait to download the data, something I did not originally anticipate. I do think that near the end it got a little more stressful and the crunch time was more than I expected, but that was due to other school work. If I do need to work on one thing, I would say I have to do a better job looking at the larger picture. This is really hard to do since there is always so much going on. I mean I set the final date of March 28th based on how long I felt that project was going to take me. I did not look at my other classes to see what was due that week. So when the week of the 28th came around I was swamped with school work from my other classes and I still had to complete the project. So I think I need to be more cautious next time and prepare projects with everything considered, not just how long it will take to complete all the parts. For the most part though, the project took about as long as I expected it to. I think this was because I set a good plan early on and followed through. I also had pretty good luck with the project as nothing major happened that delayed my work. In the end, I feel really good about my work on estimating the time and planning out the project.

In the end there was a lot to take away from this project. I took away how to write project plans and how to manage tasks with so many other things going on around me. I also took away how to communicate with management during long term projects. What I have learned here will help me out when it comes time for me to do my first real long-term project. I will definitely make sure to plan with everything in mind, leaving nothing out. I will need to look at all schedules to see what else could be due then, or what else could conflict with that completion date. I need to continue to work on my time management skills, making sure to plan everything and include backup plans and more. I am confident that the soft and technical skills I have learned in this project have certainly helped prepare me for a career in the ever changing and the ever demanding IT field.

Well that is it. Those are my reflections on the project. In the end, I am very pleased with my work on the project and I hope my efforts will help the library avoid any future mold problems. I would like to thank everyone for reading my blog for the past few months. Many of my posts were detailed and long, so thanks to everyone who read them all. I really appreciate it. For one last time, thanks for reading.

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.

Downloading Procedures

Creating the Out of Range Results

Changing a HOBO Device’s Settings

HOBO Device Locations

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.

This was the final week before my set end date of March 28. As such, I had a few loose ends to wrap up, mostly with the documentation. The main thing I had to deal with this week was the pull data macro. This is the macro that opens each device’s spreadsheet and automatically copies and pastes the data into the master spreadsheet. After reviewing this problem in more detail, I found that the best way to handle it was to just have the user do that part. I was hoping to avoid having the user do this part since the user could incorrectly copy and paste the data into the wrong worksheet in the master spreadsheet. This would lead to major problems as you would then think a certain device registered certain points when in reality it was another device instead. Even with that though, I found that it was the best way to go, especially when this week, I ran into another problem with the pull data macro.

This week a staff member of Pam’s ran through the data downloading procedures and she informed me of the results. I was glad when she told me the documentation was easy to follow and that she was able to download the data. However, she did tell me something interesting. One of the devices registered no data points for some reason. So, she was only able to create five of the six spreadsheets. This is a problem for the pull data macro. The macro was recorded to open all six files in order. If one file does not exist, the whole macro cannot run at all. Also, if the name of one of the saved files from the data download was incorrect and did not match what is in the macro, the macro would again not run at all. So, the first thing I wanted to do was see what happened when a device did not have any data. I wanted to see why it was not possible to at least create an empty sixth spreadsheet so that the macro could still run.

To do this, I needed to redo the downloading procedures myself to see what happened. The HOBO shuttle keeps the data until a new set of data from a HOBO device is loaded off onto it. The shuttle will keep the previous months data until a new month is run. This is good in case something happens to the spreadsheets on the computer. All you would need to do to get the spreadsheets back would be to redo the procedures by simply plugging in the shuttle. Since the data was still on the HOBO shuttle, I was able to redo the procedures myself so that I could see what happened when the procedure came to the device that had no data points. After pulling the data off the shuttle as normal, I skipped straight ahead to the device that had no data (it was device L6 in Pam’s office). I tried to export the empty device’s data to a spreadsheet, but to no avail. BoxCar Pro informed me that it would not create the spreadsheet since the device had no data. So for every device that does not have data, the only way to get the file for the macro run would be to create it yourself. This was something I did not want the user to have to do as it would increase the probability of incorrectly naming the file, thus making the macro far more confusing for the user. Needless to say, this was pretty much the last straw with the pull data macro.

Although I wanted full automation, the pull data macro just created too many problems. If the files were incorrectly named or were not there, the macro would not run. If a device had no data, you would have to create the spreadsheet yourself. Also, if for some reason you needed to move all of the data to a new directory, you would have to recreate the macro. Due to all of this, I decided it would be better to have the user just copy the data out themselves and paste it into the master spreadsheet. From there the user could still run the rest of the master macro and most of the procedures would still be automated. With that decided, I had to start creating the documentation for this procedure. I also now had to edit the downloading procedures to let the user know what happens when a device does not have any data.

Before I could begin writing up the procedures, I still had to figure out why the sixth device did not collect any data points. First, I plugged the device directly into the computer and read it out using BoxCar. BoxCar picked up the device so it was indeed still functioning. While doing this, I noticed that BoxCar does not allow for apostrophes in the device name. Because of this, it was saving the spreadsheet file as Pam_s office instead of Pam’s. I went ahead and changed the name of the device to just Pams Office. While doing this, I remembered that I still needed to write the documentation on how to change the name of a device. So I started over, this time making sure to write down the necessary steps involved with changing the name of a device. Once completed, I wrote up the documentation on how to change both the name of the device and the interval (how often the device collects data points). With that documentation in place, the user could now change the interval and the name of the device should the need arise.

After completing that documentation, I still had to figure out what was wrong and why there was no data.  I was stumped. The device seemed to be working. I set the delay time on the device and put it back to its location. The delay time is merely a date and time you want the device to start collecting data points. I set the delay time for five minutes after I was finished. This would cause the device to hold off on collecting data points until that delay time hit. After putting the device back to its location, I still had no idea what the problem was so I decided to move on.

It was only until just now that I realized what may have happened. Pam had mentioned to me that her staff member told her that the shuttle at one point became full. At the time it did not hit me. I just assumed it was full because of the last months run. I thought I was just going to have to write a procedure to clear the shuttle each time. But then I realized that the shuttle is supposed to clear itself after a new HOBO device is loaded onto it. This means that once her staff member transferred the first device over, the shuttle should have been cleared and room should have been made for the new devices. This makes sense as when I ran it myself in February there were no remnants of January’s data.

I began by first looking at how to clear the shuttle. I found out that there was no manual way of clearing it. The only way to clear it is to transfer new data from a HOBO device to the shuttle. So I was still not sure what to do with this problem. Just now though, I think I realized what may have happened, and I do emphasize “may.” I have not talked to Pam or her staff member to confirm this. Her staff member may have transferred data from only five of the devices. If she went in order, Pam’s office would be the last device. When she got to the sixth device, the HOBO shuttle said it was full. From there she may have followed the procedures with just five of the devices. However, I am not sure what happened to the sixth device’s data. When I went into that device alone, there was no data. If the shuttle was full, I would hope it would just leave the data on the device. However, the shuttle did still recognize the sixth device as it had the devices name and even made the .dtf file for it when her staff member ran the procedure. So it did pick it up at some point, it just had no data points. Perhaps she tried to transfer the sixth and final device and the Shuttle was full, but not full enough to register that the sixth device was being transferred. Since maybe it was full, the device simply just lost its data. I hope this is not the case and that the shuttle does have some kind of a failsafe for this.

I will need to investigate this further.  I need to talk to the staff member and see what she did and what really happened with the full shuttle. I never expected the shuttle to be a problem. However, this was the first time we ran six full devices. So, I am going to have to take a look at this when they run the data set in April. I could have them run one now, but that won’t include a full month’s data set and as such may not fill up the shuttle.

Now, back to writing up the final documentation. I began writing up the procedure for creating the final results spreadsheet. I wrote up the documentation that made the user open all of the device’s spreadsheets. I made sure to note that there should be six files, but if a device had no data, there would be no spreadsheet. If a spreadsheet was missing, the user would just skip copying over anything to the corresponding sheet in the master spreadsheet. I then showed them how to copy and paste the data into the master spreadsheet. I was sure to make this part very descriptive and detailed so that there would be less chance for error. From there, the documentation told the user to just run the master macro. Once that is run, all they have to do is copy out the final results sheet to a new spreadsheet and then save that into the current month’s folder. For example, I went ahead and created the Mar08-results spreadsheet for the month. Every month, the user will run this procedure after the data download and they will create this new results spreadsheet. This spreadsheet shows each device and its out-of-range points all on one sheet. From there the user would then have to run the clear master macro which would clear the master spreadsheet of all data. With the master spreadsheet cleared, the user can copy and paste it into next month’s data folder when the user runs the procedure the following month.

I touched up and finished all documentation and saved them to the network drive in a documentation folder. I am confident in all the documentation I wrote up and I hope the new documentation for the results spreadsheet and how to change a device’s name are easy to follow. With the documents completed, I emailed them to Pam to let her know that I had completed all documentation.

As promised, I delivered all documentation and a working out-of-range algorithm by March 28. I do have to say that the out-of-range algorithm was not as easy as expected. I ran into a few more problems along the way then originally anticipated. I will not go into detail about what I learned from this project at this time as that is a specific assignment that is due two weeks from now. This assignment will be a final post that goes over what I learned from this project.

Even though I have completed the objectives of this project, I am still going to make sure that all is well when they run the data next month. I want to see what happens with the shuttle and what happens with the sixth device. Also, on April 17^th, I have a presentation to give to Pam and the course instructor Melissa Raulston where I will go over what was accomplished in the project among other things. After this presentation, Pam will review my performance and will fill out the sponsor evaluation form. This form is where Pam will have the opportunity to grade me on such things as professionalism, and how well she felt I met the objectives I set forth in the project plan. Once she submits the form, the project will be officially complete.

With that said, next week’s post should be really quiet. Next week, I will showcase the final project plan, which is due tomorrow for the class. I will also showcase some of the macros I created to show you how the master macro actually works. The master macro itself is nothing major as all it does is run a series of other macros. So I will be focusing more on the other macros. Until then, thanks for reading.

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.

This week was another busy week for coursework. It was the week before Spring Break so I had several large projects to complete before the break. As such, I did not get as much completed on the out of range algorithm as I hoped. I did start a bit of it however, only to run into a few problems with visual basic code in the macro.

Through my work on the algorithm so far, I did realize that solution 2, which I mentioned last week, can be altered to work better. Last week I said that one of the problems with solution 2 was that it required the user to copy each sheet for each device and paste all of the data into one sheet. If the user pastes all of the data into one sheet, they would have to put a device identifier on all of the points so that you could differentiate which points belong to which device. If the user were to enter that number incorrectly, or forget to add it entirely, the data would lose its pairing with the device and then you could not tell what device gave you what data.

Well, while working on the algorithm this week, I determined that there is no need to combine all of the worksheets into one worksheet (as described in solution 2). I determined that I could create a macro that repeats the algorithm for each sheet automatically. This would allow the data to stay in separate sheets. With the data in separate sheets, you could tell what outlying points were collected for that particular device simply by looking at what worksheet you were on. By doing this, the user would not have to enter in any information. All they would need to do would be to run the macro and it would pull out the out of range points for each sheet.

I actually proposed the separate sheet idea in solution 1. However, solution 1 had problems with duplicates, which was the main reason why I went with solution 2. The funny thing is that I actually solved the duplicates problem in solution 2 by adding conditionals. The conditionals made the algorithm check both conditions at once rather than having it go back twice as it did in solution 1 (going back twice caused the duplicates). Looking back at it, solution 1 is actually closer to the right answer than solution 2. The conditionals idea from solution 2 makes solution 1 perfect, a fact that I am not entirely sure how I missed when writing solution 2. So, in the end, the best solution was combining the best parts of both solutions. Now that solution 2 has been altered, it should work perfectly. Now all I have to do is just get the macro working.

Now that I had realized the best possible solution, I had to start figuring out the algorithm. I went ahead and tried to get something created quickly, just to see if I can get it to pull out a few records before I made it run a whole sheet. I made up a quick visual basic macro that checked the conditionals I brought up last week. I had it first look to see if the temperature was great than 70. If it was, then I had it check to see if that same data point had a relative humidity greater than 55%. If it did, it copied both of those out. If the temperature was not greater than 70, I then had it check to see if the RH was above 55%. If it was, it copied out just the RH. If neither was high, it would move on to the next record.

I felt pretty good about it, so I went ahead and tried to run it. It did not run. It instead gave me an Else Without If error. This is where I am now. I am pretty sure it just has something to do with the way I setup the If Then statements. I am still not entirely familiar with the way basic does them, so I am sure one is either in the wrong place or I missed one. I wanted to look more at it this week, but due to all the projects, I was unable to. However, with Spring Break coming up next week, I should have sometime to fix the algorithm and at least get it to work on one sheet. From there I can get it to work on all of the sheets. Even though it is Spring Break, I don’t really have time for a break. I really still need to work on this algorithm. I set an end date of March 28th and that date is rapidly approaching. I will continue to work on the algorithm while on break in effort to have at least a good part of it ready to show Pam when I come back. Anyway, for now, here is what I have for the algorithm so far.

3/10 Update - DUH! As I was working with a macro at work today, it dawned on me. I had one of those ah-ha moments. I was about to run a macro that I use at least 3 times a day at work when I noticed something in the visual basic code. END IF! I completely and utterly neglected to include End If statements to close off the Ifs. As such, I kept getting the Else Without If because I was not ending the previous If. So I went ahead and corrected that and the macro works. With it working now, I went ahead and ran it. At first, I thought it was not working right. It appeared that it was just pulling out the temperatures and skipping the relative humidity. I assumed it was something I messed up in the If Thens. However, as I was running through the code, line-by-line, I realized that there is no reason for it to pull out any RHs. Before even creating the macro, I had sorted the data by temperature and then RH, both descending. When I looked at the RH column during my run through, I realized that none of the data points had a RH higher than 55% (highest was 48.5), therefore it did not pull any out. So the macro does appear to be working. Now I have to try out some more test data. This data has to include every possible option to see if the macro properly pulls out just the ones with a temperature above 70, just the points with RH above 55%, and those data points who have both high temperature and high RH. END OF 3/10 Update

I mentioned last week that in the class we were finishing up our project plans. This week, I combined all of the sections I have been working on into one rough draft document. As mentioned last week, the project plan includes sections for a work break down schedule, an executive summary, assumptions and constraints, cost estimates, and alternative and proposed solutions. Once I combined all of the pieces together, I realized that I had a very large, detailed project plan. It was a lot larger than I expected it to be. However, breaking it down as we did was a great idea. This made it seem like it was shorter and easier to manage, thus I never felt overwhelmed at any point with the project plan. Since I had all the parts already done, putting it together was the easy part. I feel pretty confident in my project plan and I feel it describes and outlines this project very well. As promised, I give you the rough draft. Click here to download the full project plan rough draft.

Well that is all I have this week. Although it was a quiet week in terms of the project, I feel I have made decent progress. I have started my work on the algorithm and I comfortable with the direction I am going in. I completed the project plan and I am confident in that. There will be no blog post next week due to Spring Break, but I assure you that I will be working on this project during my break. By my next post, I should have a solid, working algorithm. Until then, thanks for reading.