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.