This chapter will work more closely with Visual Basic (sometimes called Visual Basic for Applications or VB). First, we'll record another macro similar to the previous chapter that creates a basic table utilizing various formatting features.
Use the workbook you created in the Macros 1 chapter.
Additionally, we will record a macro to remove the table.
Next, we'll look at the code composing the macros. Press the Visual Basic button from the Developer toolbar to open the code in Visual Basic. Here, we can review, edit, and create the code used in Excel macros. (See Figure 29.4)Figure 29.4
Organizing code by inserting notes is an important practice for you and others to make sense of the code. Notes are written beginning with a single apostrophe ( ' ) which the program recognizes as separate from the macro code. If text is written without an apostrophe, the text will be colored red to indicate an error because it is not actual code. (See Figure 29.5)Figure 29.5
Recording macros can be helpful for completing repetitive tasks. However, the recorded macro will likely contain a lot of extra code as a result of selecting cells prior to performing a specific action. In Figure 29.6 below, we can see each action begins with identifying rows, columns, or a range of cells and performing the action on the selected cell(s) (for example, Range("E3:L12").Select). As in Figure 29.7, the code can be simplified by removing the selection and performing the next action on the designated cell(s) directly (for example, Range("E3:L12").ClearContents).
We can take it a step further by simplifying the clear contents and clear formats lines. Instead of performing the actions to clear content and format separately, the ClearContents action can be changed to Clear, and the code will clear everything and the clear formats line can be deleted.Figure 29.6
There will be situations wherein misspellings, changes, or other continuity problems between the macro code and the spreadsheet cause a macro to fail. In the event of an error, Visual Basic will produce an error message and prompt the user to End or Debug the macro. (See Figure 29.8)Figure 29.8
Pressing Debug will direct the user to the line of code that failed. The error will be highlighted yellow with an arrow indicating the line of code. In the following example, the macro attempted to select a range labeled as "abc." However, the range didn't exist. It may have been a typing error, or the user forgot to update the desired cell(s) with the indicated label. The code or the spreadsheet can be modified to correct the error. (See Figure 29.9)Figure 29.9