Excel: Magic Macros

In honor of the wizardry of technology, and because everyone should be having a blast during summer break, I made a fun exercise for us in Excel… a crystal ball… via the magic of Excel Macros.

To give credit where credit is due, this was inspired by an exercise Mr. Excel showed us at Excelapalooza last fall.

Exercise File

Here it is… ask it anything! Crystal Ball Document

Allow Macros

This magic involves macros, so if you want to use this crystal ball, you might be prompted to “allow macros” or “Enable Content.” If so, go ahead and allow them. And more on this later…

How to Use it

Alright, this is about a sophisticated as a Magic 8 ball, which is to say, you will think of a yes or no question, then press the “Ask Me Anything” button on the Crystal ball.

Your answer will appear in cell B2, next to the “Your Answer” writing. Keep pressing the button for different answers (there are some good ones in there).

Look Behind the Curtain

How did I do this? Let’s take a peek behind the curtain.

Unprotect Workbook

This is a protected workbook. Go to the Review tab and select Protect Workbook to toggle off the protection. I did not apply a password. This will allow you to discover a sheet I have sneakily hidden.

Unhide Sheet

Right click on the tab of the sheet (where you see the sheet name) and select Unhide.

Hey there is a hidden sheet in this workbook! This looks a lot like the results we have been seeing when we ask the crystal ball for answers. But where do these results come in to play?

Formula in B2

Go back to the Crystal Ball sheet and click on cell B2, where the results appear. Look up in the formula bar… it looks like there is a pretty nifty nested formula that is selecting a random number between 1 and 14 and matching it up to possible results.

If you want to learn more about nested formulas, come to one of my Excel Advanced Formulas sessions! Those of you who have attended before, I could have also used VLOOKUP on this cell, but for some reason I was feeling old fashioned and went with the old Match/Index one-two punch.

Macro

Okay, the hidden sheet makes sense, and the formula is logical, but what does this button have to do with anything? How exactly does this work? The answer is… Macros!

What are Macros?

Macros are basically a shortcut you create that will perform a series of commands. If you get tired of copy/pasting as values in Excel, you could make a macro button that would do that for you. I once talked to someone who consistently wanted to print only the first page of a document, so we made a macro that would do just that.This saved her a lot of clicks throughout the day.

Basically, any combination of commands, formulas, you name it, you can create a macro for it. Macros use a special code called VBA (Visual Basic for Applications). If you want to take a look at the super simple macro behind this button, press Alt + F11 on your keyboard.

Now, don’t let this scare you off, macros can also be recorded via a macro recording tool in Excel. You don’t have to learn VBA to create macros, though it can really help.

I Thought Macros Were Something Bad…

I hear this a lot. Sometimes you open a document and receive a stern warning about macros… you may have even received a message like this when you downloaded this exercise. Because macros carry coding for actions, they can hide malicious code from ne’er-do-wells. Think of it this way, macros are a tool, and any tool can be used for good or bad purposes. So if you download something from a source you are unsure of, and you want to be extra careful, don’t allow macros to run, though know this will possibly reduce functionality. But if you made the macros yourself, or it is sent to you from a trusted friend (like me), you are probably fine.

Are You Interested in a Macros Session?

This Byte was a super fly-by narrative about macros. A couple of you have told me you are interested in learning more about macros… if you would like to attend a session like this, let me know! If there is a lot of interest, maybe I can put something together. You can either comment on this post or send me an email. I always love to hear from you anyway.

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser