Wednesday, November 30, 2011

Making It Up As I Go Along

Necessity is known as the Mother of Invention. What is less well-known, however, is that she is also responsible for a son: Justin Time. Over the years, Invention’s attention-seeking behaviors and drama queen ways have meant that Justin has labored away in obscurity. But no more. Today we celebrate Justin Time and his contributions to new learning. Without him, I might never have been prodded into developing my first Excel project using VBA.

For those of you Excel Ninjas out there who wield VBA like a weapon, my paltry excursion will not look like much. But we all have to start somewhere…and these are my humble beginnings.

One reason why Justin has not been given proper credit is that he is more commonly known as “You want me to do what? I don’t know how to do that.” But his advantage is that with Justin, you only have to learn something new to you---not new for everyone. And this is what I realized when I was asked to replicate the function of this website using Excel:


It’s a calculator for schools/districts to use to figure out one variable (number of tests, number of computers needed, number of test sessions, number of test days) if they know the other three. The calculations themselves aren’t all that sophisticated. The first variable (number of student tests) is the product of the other three. So, a bit of basic algebra will tell you how to calculate the others.

In Excel, each of the buttons you see—such as “Calculate Minimum Days of Testing Required”—needs to be assigned to a different formula. And, we need some way to update the information when schools want to test out different scenarios (e.g. change the number of test sessions from two to three). It’s possible to do something similar without resorting to writing your own macros. You could use a dropdown menu to allow users to pick the variable they wanted to calculate, then populate a table below based on that (fill in the other variables, assign a formula). But the people who wanted this calculator wanted something sexy. They wanted Justin.

I started by setting up the basic layout of the calculator. I found out later that this is extra important, because when you move cells around on the worksheet, the formula in VBA does not autoupdate the way a formula within the worksheet will. It’s not an insurmountable issue. It just means you’ll have to go back and update some of the code. Whatever you can do to plan ahead now will mean less work later. So, here’s what I’m starting with:




For the next part, I needed Justin Time. So, here is what he taught me about creating the buttons (you can read about my learning-to-learn strategies here). On the Developer ribbon, click on Design Mode in the Controls area. Then click on Insert. This will bring up a menu of items you can use to build a form. If you’ve created a form in MS Word, then this part won’t look new to you. Then, click the item in the upper lefthand corner to create a button. Draw your button on the worksheet. Don’t worry too much about getting the size or other attributes right. You can go in and make changes later. When Excel brings up a dialog box to assign a macro, hit “Cancel” for now. We’ll go back and deal with this after we create the macro. You’ll now have a generic box like this one to use:


Now comes the fun part. It’s time to write the code. On the lefthand side of the Developer ribbon, you’ll find the “Visual Basic” button. Be brave and click it. It will open up a new window where you can tell Excel what to do. We will then assign this code to the button on our worksheet. In the VBA window, click Insert and then Module. This will open another window for you to place the code. Here is what we are going to write:


“Sub” at the beginning stands for “subroutine.” (We all live in a yellow subroutine…) The word “students” follows in order to name this subroutine. The second line tells Excel what to do. Range(“C2”) tells Excel that we want things placed in cell C2 of the worksheet. We then say to execute a Formula. Finally, we have to include what that formula will be. This looks like a formula we would write for the worksheet itself: =C4*C6*C8. Notice the placement of quotation marks and the use of two equals signs. Finally, we tell Excel that we have reached the End of the Subroutine (End Sub).


Now we’re ready to assign the module to the button on our worksheet. Right-click the button and select “Assign Macro.” You should see the “students” macro. Select it and click “OK.” Your button is now ready to use. Fill in the other three variables and see what happens. Don’t like the name “Button 1”? Right-click on the button and edit the text. You’re ready to build the modules for the other three buttons.

Keep in mind that when you save this workbook, you need to save it as an Excel Macro-Enabled Workbook:



Uh-Oh
I discovered that after you calculate one of the variables and then clear out the box, you get ugliness like this:


And this is where Justin let me down. I tried inserting IFERROR as part of the formula statement in the macro, and Excel barfed it up. I looked and looked to find out why or what I could do instead and didn’t have any luck. At least not yet. If you have VBA wisdom to share about this issue, I’d appreciate learning from you.

My workaround was to create a “Clear Contents” button so that users could start over. This one I built a little differently. In the Developer ribbon, I selected “Record Macro.” This brought up a new window where I could name the macro (e.g. “Clear Contents”). Then, I used control+click to select cells C2, C4, C6, and C8, then hit the Delete button on my keyboard. After that, I clicked on “Stop Recording.” Now, I could associate the macro with the Clear Contents button, just like I did the other buttons.


Here is the link to download the completed workbook, if you need a place to play around with the VBA and don’t want to torture your own spreadsheet.

Invention may get all the credit out there---and she deserves some of what occurs when Necessity comes knocking at our door. But don’t forget about Justin Time and the learning he provides along the way. I’m hoping, however, that Necessity will find out about family planning in the near future.

Thursday, November 3, 2011

Friends Don't Let Friends Use Pie Charts

It's time for a little chat. I'm not going to name any names here, but some of you have been using these to represent your data:



Worse than that, a few of you have been using these:


Look, I know how it is. You start off with an x- and y-axis, and a few data points. Later on, line graphs just aren't good enough anymore. You move into bar charts and start colour-coding.  Then Excel comes out with an updated version with even more features and you're in a multimodal haze with all the things you can do. Before you know it, you're hooked.

And you know you've hit rock bottom when a pie chart is your "go to" graph. I know you're better than that. Sure, there are times when a 2D version is appropriate, but it's time to face some cold hard facts. It's time for an intervention.

Pie charts work best when comparing the proportions of two categories. But the problem with pie slices is that it is difficult for users to compare the area of the slices. If you just need to communicate something very basic ("A is bigger than B"), it's probably okay. However, if you need users to understand details (or change over time), then a bar chart is a better graphic. People are much more accurate at reading length, rather than wedges. Labels are simpler to apply. (Although this is kind of an ugly example of pie vs. bar, it does make a good point about why bar graphs are better.) Take a look at this redesign of a pie chart to bar graph on Junk Charts for a good illustration of some of the issues.

As for 3D pie charts? Um, hey, who has three variables that they're trying to plot on a pie graph? (If you do have three, there are other---better---ways to show your data.) Not you. Annie Pettit's Prezi pretty well sums things up on this point. (Hat Tip to Jon Peltier for this.)



So, are we cool? I'm really glad we had this little talk.