Creating your first macro using visual basic

The computer is first and foremost a time-saving device. My husband was just telling me about how the railroads used armies of clerks to keep track of each box car and it’s contents “back in the day,” and how all of that labor was now being handled by a few desktop computers. Imagine Mr. Scrooge meets the Nutty Professor to get a visual.

Advanced users of Microsoft Office products like Excel, PowerPoint and Word know that the easiest way to get the computer to save you labor is to teach it repetitive tasks so that you can focus on the important stuff. We’ll teach the computer by making what are called “macros,” which can consist of either sets of tasks or calculated functions.

Macros are written in a computer language called Visual Basic for Applications (VBA), and are edited in a program called the Visual Basic Editor (VBE). I promise, that’s the last two acronyms you’ll need to learn in this tip!

You learned how to turn on the developer tab in Excel in a previous tip, but if you missed that one, be certain and go back and do that before you attempt the skills in this tip.

Getting Started with VBA and the VBE

VBA is simple computer code, and it’s meant to be accessible to the average person. You create VBA code in the VBE window. To write your first macro:

  1. Be certain you only have one Excel document open.
  2. Change to the Developer Tab.
  3. Click the Visual Basic button in the Code Group.
  4. The VBE will open, and you should see the Project window and the Properties window open on the left side. If for any reason you don’t see them, go to the View menu and select each one to view it.
  5. Click the Insert menu and choose Module from the dropdown.
  6. The module is going to store your macro when you create it.

Writing your first macro

Your first macro isn’t going to be rocket science, but it will teach you some important basics about writing a macro from scratch. The type of macro we’re going to create is a Subroutine, so that means it is going to start with sub, followed by the macro name, then an opening and closing parentheses. VBE will automatically add End Sub when you press the enter key.

Important: Macro names cannot contain spaces and must be followed by an opening and closing parentheses, without any spaces between the name and the parentheses!

We are also going to add a Remark to our macro. Remarks in macros always start with an apostrophe (or single quote mark, if you like). After you type the remark and press the enter key the remark will turn green. Remarks are meant to help you remember what different things do, and are very important to learning how to write macros.

As I mentioned, this first remark is going to be pretty easy. We’re going to create a message box that pops up on screen and says “Hello there!” The message box function is a lot more powerful than this, but we’re working in small steps at this point.

Here are the steps to creating the macro:

  1. Be certain you are in the VBE, and have created a module as described above.
  2. Click your mouse in the module, and type Sub MessageMe(), then press enter.
  3. Press the tab key to indent one column.
  4. Type ‘This is my first macro, and then press enter.
  5. Type msgbox(“Hello there!”) and then press enter. Notice that there are no spaces in the msgbox function, but you can have spaces inside the quote marks.

Running your first macro

Now that you’ve created that monster of code, it’s time to actually put it to work.

  1. Click the Close button in the upper right corner of the VBE window.
  2. Change to the Developer tab if necessary.
  3. Click the Macros button in the Code group.
  4. Click the MessageMe macro.
  5. Click the Run button.

That’s it for today. A little bit more lengthy, but really quite simple tip on getting started with Macros. We’ll look at a few more fundamental points about Macros in the future.