Adding VBA code to your macro does not always have to be tricky. There is an easy and useful shortcut you can take to add code in certain situations. Moreover, this particular shortcut can prove to be very educational.

 

First, you have to know how to view the VBA code for a macro. To see the code for a macro, choose Tools->Macro->Macros from the menu bar. This action will display the Macro dialogue box.

 

 

You can see in the image on the left, that there are two macros available for this workbook. If you select one from the list and click the Edit button, you will see the code for the macro that you selected in a Visual Basic window.

 

 

 

 

 

 

 

  

If there are no macros available for editing in your Macro Dialogue box, you can quickly record a simple macro by choosing Tools->Macros->Record New Macro from the menu bar. Next, perform a sequence of Excel actions or keystrokes for your macro, and choose Tools->Macro->Stop Recording to create the macro. Once you have recorded a macro, invoke the macro dialogue box as described and select your new macro. Click the Edit button in the macro dialogue to view the macro code.

 

A macro is essentially a small computer program written in visual basic. When you record a macro, Excel writes the visual basic code for you. When you view the code for a macro, you are looking at the sequence of visual basic instructions that perform the specified tasks required of the macro.

 

Once you can record a macro and view the code for it, you have at your disposal a clever way to learn about visual basic programming. You can easily record a macro, view the code for it, and try to figure out what the code statements do based on the function of the macro. If you experiment with recording different macros and viewing their code, you can increase your understanding of visual basic instructions.

 

Perhaps more importantly, you can use this method as a shortcut for adding code to your macro.

 

The following code is for a recorded macro that shades a selected range light grey, and then changes the font for the selected range to bold.

 

 

 

The name of the macro is Macro 6 (The name of the macro comes after the term Sub in the first line).

 

The statements in green are comments that provide information about the macro.

 

The small section of code between the words With and End With, creates solid background shading for the selection. The colour of the shading, (light grey), is indexed by the number 15. If you change 15 to a different number, the colour of the background shading will be different.

 

The statement Selection.Font.Bold = True, applies a bold font to the selected range.

 

The words End Sub indicate the end of the macro.

 

 

 

 

Here is the worksheet before running the macro.

 

 

Here is the worksheet after selecting cells B1:E1 and running the macro.

 

Now, let’s say that in addition, you want the selection to be italicized and the font colour to be bright blue.

 

First, change the font back to regular and remove the grey background shading by using the appropriate buttons on the formatting toolbar. Then select any cell range and record a macro that applies italics and a font colour of bright blue to the range. Remember to choose Tools->Macro->Stop Recording, when you are finished performing the actions for the macro.

 

(Note: in this example, the cell range is selected before the macros are recorded. This allows the recorded macros to be applied to any cell selection. If you selected the range when recording the macro, the macro would always select that specific range.)

 

If you choose Tools->Macro->Macros, you can then select Macro6 from the list of macros and click the Edit button to view the code. (Remember, you can choose whatever names you like for your macros when you record them.)

 

You can see that there is now a macro named Macro9 available in the worksheet. (Macro7 is a macro that is stored in the Personal.xls folder and is available to all worksheets.) 

Macro9 is the macro that modifies a selection with italics and a blue font. 

Click the Edit button to view the code for the selected macro.

When the Visual Basic window appears, you can see the code for both Macro6 and Macro9.

 

 

 

 

Rather than have to run both macros to achieve all of the modifications; you can copy the lines of code that start with Selection.Font from Macro9, and then paste them into Macro6 immediately below the line that reads Selection.Font.Bold = True.

 

(Select the lines of code with your mouse so they are highlighted in dark blue. Right click on the selection and choose copy from the menu.

 

To paste, place your cursor on an empty line at the correct position in the code, right click and choose paste from the menu.)

 

If you do this correctly, Macro6 will now have two new lines of code, and be able to perform the functions of both macros.

 

The following example of code shows Macro6 with the additional lines of code pasted in place. Notice that we only copied the functional code from Macro9. That is, we left the green coloured comments and the beginning and ending Sub statements, (Sub statements define the start and stop points for the macro), behind. These lines of code were not copied because they would not add any additional functionality to Macro6.

 

 

Now if you close the visual basic window, make a selection, and run Macro6, you will get all of the modifications at once.

 

 

Here is a worksheet before running the improved Macro6.

 

 

Here is the same worksheet after selecting cell range B1:E1, and running Macro6.

 

You can use this method of recording separate macros, and then adding code from one macro to another by copying and pasting to build macros with more complexity. Copying and pasting the code also helps to avoid typing errors that can occur when entering the code directly.

 

Another way to combine the functionality of different macros is to use the visual basic Call statement. If you add a Call statement to a macro, the macro corresponding to the name that follows the word Call will run when the calling macro is executed. This allows you call (or play) a macro from within another macro.

 

The visual basic statement, Call Macro9, when included in a macro, will run the macro named Macro9.

 

 

Here are the original examples, Macro6 and Macro9; but now, Macro6 has a new statement before the End Sub line that reads Call Macro9.

 

When this Call statement is reached, Macro9 will be invoked.

 

 

Here is the result of running Macro6 after adding the Call statement. Both Macro6 and Macro9 are executed.

 

Here are a few points to remember when adding code to a macro.

 

1. A visual basic statement is interpreted as a complete instruction. A statement usually involves one or more keywords, (words reserved in visual basic because they have a special function within the language). When you are editing the code for a macro in the Excel Visual Basic window, the key words will appear in a blue font.

 

2. Try to use a single line for each visual basic statement, (except for comments and if/then /else statements). Doing this will help the readability of the code that you add. If you feel you must, you can join individual statements on one line with a colon (:) but this can clutter your code and make it difficult to understand.

 

 

3. It is always a good idea to add comment statements to your code. Comments help you and anyone else who looks at the code understand what it does. There will be more on adding comments to follow.

 

4. All of the mathematical operators (*, +, -, /, ^) can be used in visual basic statements to perform operations on numbers. The order of precedence is the same in VBA as it is for Excel formulas: exponentiation, multiplication, division, addition, and subtraction. You can also use parentheses to impose your own order of calculation

 

5. Copying and pasting existing code helps to prevent errors. Visual basic statements and keywords must be exact and adhere to certain rules for them to be executed correctly. It is easy to make mistakes in the structure or logic of statements when typing code, especially for beginners. Copying and pasting existing code that is known to work can help to avoid many errors

 

6. There is a wealth of information available on the web, related to visual basic programming and macros. If you run into problems when adding code to a macro, remember this resource.