Excel VBA Functions to Create and Use Macros

Excel VBA is a programming language that helps you control a lot of different things in Excel. It can seem difficult to master, but is quite easy once you understand the components. There are many online tutorials to help with learning Excel VBA.

The first step is to show the Developer tab to the tabs toolbar. If you are using Excel 2007, you would click File, Options, and then click Customize Ribbon. Under “Customize the Ribbon”, you would make sure the box beside Developer is checked. You can then close the screen and move on. If this is complicated, consider a course in Excel 2007 introduction to get familiar with the ribbon setup as it is a newer addition to Microsoft Excel.

Before running any macros, it is important to remove any security warnings that may appear. To do this, click Macro Security under the Developer tab. You will then want to select Enable all macros and check the box next to “Trust access to the VBA object model.” This allows you to create the macros and play around with them easily; you can always enable macro security before exiting the spreadsheet.

Before creating macros, you will need to open the VBA editor. Courses like Excel 2007- Advanced can help you to navigate these more experienced areas of Excel. To open the VBA editor, go to the Developer tab and look at the Code panel. From there, click the Visual Basic button. You can also hold down the left ALT key on the keyboard and while pressing down on the ALT key, press the F11 key.

Writing a Macro

Once you get to the VBA editor, you will see a white space with Sheet1, Sheet 2, etc. This is where the objects will be placed. The large gray space is where you will write the codes. You will need to open up the coding screen first, so double click where you want it in the white space (Sheet1, etc.). A white screen will be opened in the gray area, and this is where you write the codes.

You will start most codes by typing in “Sub”. You will then need to name the subroutine or code. On the next line, you will need to type in the message or what will be inputted.“End Sub” is at the end of the code, but this will automatically generate as you type your code. An example:

Sub HelloWord()
MsgBox “Hello VBA!”
End Sub

The name “HelloWord” can be anything, though it is usually something that corresponds to what the macro will do. The parentheses must be included after the name. The message will usually be different.

There are some rules that must be followed when naming your subroutine. For instance:

• The name cannot contain spaces, but can use underscores. “Hello Word” is incorrect, though “Hello_Word” is correct.
• Numbers can be used, but not at the beginning. “1Space” is incorrect, but “Space1”  is correct.
• You cannot use a period in the name at all.
• Special characters (like #, %, $, &, and !) can’t be used anywhere.

After you have created your macro, it is time to run it. At the top of the editor is a toolbar with a “play,” “pause,” and “stop” button. Click the green play button. You can also press the F5 key to run the code.

Because the code you used above had “MsgBox”, this means a special message box will appear, and it will include your phrase.

Recording a Macro

To record a macro, you must click on the Developer tab, locate the Code panel, and click on Record Macro. A dialog box will appear.

Under Macro Name, you will type in the name you want for the Macro. Again, this can be anything you want, but it would be nice if it gave an idea as to what the Macro will do. The next area is the Shortcut Key. You can create your own shortcut for this macro, which is helpful if you will be using it a lot. Click in the white space and type any letter or number.

The “Store Macro in” section refers to where the macro will be located. The default is in the workbook you are currently working in. However, you can also make the macro available to other workbooks.

On the Excel spreadsheet, select the area you want to be used in the macro. You can select cells, change fonts and alignments, along with others. For the example, you will select the cells A1 through A6. Click the Home tab on the ribbon and select the right-align option. Then go to the Developer tab and click Stop Recording on the Coding panel.

To find your macro, go back to the editor; in the Project area, you will see the new item in the object window below the sheets. Click the plus symbols to expand the entries, then click on the folders. Double click on the Module1 folder and you will see the code of the above macro (where you highlighted the cells and right-aligned them).

The macro code should look like this:

Sub TestMacro()
TestMacro Macro
Range(“A2:A6”).Select
With Selection
HorizontalAlignment = x1Right
Wrap Text = False
Orientation = 0
AddIndent = False
IndentLevel = 0
ShrinkToFit = False
ReadingOrder = x1Context
MergeCells = False
End With
End Sub

Of course, if you would have written the code, it would have been a lot shorter, but it is nice to know that Excel will write the code for you, especially if you don’t know what you need to write.

After you create a macro, whether you have Excel do it or you write the code yourself, you will need to save it; to do this, click on the File menu in the VB editor and select Save.

If you decide to delete a macro, simply click Macros on the Code panel; all of the macros will appear in a dialog box. Select the Macro you wish to delete, and click the delete button.

There are so many things you can do with a macro. You can run a macro to input a specific number that you will need throughout the workbook or a word or phrase. You can also create a button and add it to the spreadsheet.

When others view the workbook, they can click those buttons and the information they need is available, either through a text box or inputted into the appropriate cells. Udemy offers courses in VBA Macro Development to help you learn the most advanced uses for Excel VBA functions and macros.

Microsoft Excel 2007 was the first version to make significant changes to the layout of the program. Courses like Mastering Excel with MS-2007, 2010, 2013 can help you learn how the new version of this program works and prepare you for the later versions.