Chapter 7 - Else Without If

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.