Now that you know about the VBE, you can finally write your own macro. Start with a new workbook, open the VBE and create a new module. To create a new module, right click on your project and choose Insert>Module.

Open the module by double clicking on it. This will open an empty code pane for you to type your code. Type the following code in so that your VBE window looks like the picture below.

Let’s deconstruct this macro to death. There are basically two kinds of macros; sub procedures and function procedures. Function procedures return a value, everything else is a sub procedure (more on that another day). Because we’re writing a sub procedure our first line is Sub procedurename. Your first line of every macro you write will be Sub or Function.

Skipping to the end (don’t worry, I’ll come back to the middle), there is an End Sub statement. This delineates the end of your procedure and everything between the Sub and End Sub lines is your procedure.

Notice how all the lines of the procedure are indented (using the tab key). This is a good technique for making your macros more readable. It’s easy to see where this macro begins and ends.

The second and third lines are where I declare (or dimension) the variables. I’ve set up two string variables to hold the message and the title that I will be using later in the macro. I’m telling Excel to reserve some memory for me because I know that I’ll need to store something in these variables. And, I know that they will be strings. Here’s the quick and dirty for data types:

Of course, there are lots more data types for you to use when declaring variables, but these will get you started. You can look up data types in help to get a list of them all. If you only remember two things about variables, remember these: Always declare all of your variables and don’t not never forget to declare all your variables.

Now that I’ve declared my variables, I put some strings in there. Variable on the left, stuff you want to store in the variable on the right with an equal sign in the middle. Anytime I use those variable, it will be like using the text directly, as we’ll see.

Finally, I use the MsgBox function to display a message. MsgBox has a number of arguments, but most are optional and I only use three here. They are: Prompt, Buttons, and Title. I use my sMessage variable for the prompt, my sTitle variable for the Title, and a built-in constant, namely vbOkOnly, for the buttons. There are a lot of options for buttons which you can get by looking up MsgBox in help (good old help).

Well that’s it, thanks for reading. Oh wait, now you want to run the macro? Okay. There are a lot of ways to run macros, but I only use a few of them. Here’s what you need to know. While in the VBE, place your cursor anywhere within the macro and press F5. If you’ve typed it in correctly, it runs. Another way is to go back to Excel, press F8 and choose the macro from the Macros dialog box.

There. Your first macro. Doesn’t if feel good?

Tomorrow: Your First Function.