16  Macros

OBJECTIVES

  • Explanation of macros and their use.
  • Activating Developer tab.
  • Recording a macro with the macro recorder.
  • Modify a macro.
  • Create command button.
  • Security settings.

You can automate a task that you have to perform regularly in Microsoft Excel with a macro. A macro consists of a series of commands and functions that are executed in succession by a single command or the press of a button.

A macro is therefore primarily intended to be used for frequently occurring actions or for complex actions. By using a macro, efficiency can be increased and the chance of errors can be reduced.

Simple macros consisting of a number of actions performed in succession can be recorded with the built-in macro recorder. Complex macros are programmed in the VBA programming language. Only simple macros are discussed here, you do not need any programming knowledge for this.

The language in which the Excel macros are written is Visual Basic for Applications (VBA). The commands and functions of the macro are stored in a Visual Basic module. And with the Visual Basic editor, you can edit macros, copy macros from one module to another, copy macros between different workbooks, and rename modules and macros.

16.1 Developer tab

o be able to record, edit and execute macros, the tab Developers must be made visible.

A number of Excel functions, including those for Macros and VBA, are located under the Developers tab. This tab is not shown by default when Excel is installed and will therefore have to be made visible first. This is a one-time action.

First check whether the tab Developers is present on the ribbon. If so, you can skip this task.

Figure 16.1: Tab ontwikkelaars.

Task 16.1  

  1. Choose File > Options > Customize Ribbon.
Figure 16.2: Excel options to show the developer tab.
  1. Select under Main Tabs Developer and click OK.

The tab Developers should now be visible on the ribbon (see Figure 16.1).

16.2 Macro security settings

Macros can be used for malicious purposes, such as installing a virus. This can be blocked with the security settings. However, if the settings are too high, you will not be able to run macros, and if they are too low, you will not be protected. Neither is a good option. A good compromise is the setting that gives you the option to decide whether to allow macros or not. This is also the default setting.

Change settings

If you want to view or change the security settings for macros, then you need to click on Developer tab > Macro Security (group Code).

Figure 16.3: Macro security button on the ribbon.

The dialog box Trust Center appears.

Figure 16.4: Macro settings in Trust Center.
Table 16.1: Overview of Macro Settings.
Setting Explanation
Disable macros without notification Choose this option if you don’t want to allowmacros. All macros in documents are disabled, as well as the security alerts about macros. Documents that contain macros that you do trust can be saved in a trusted location. Documents in trusted locations are executed without control by the security system.
Disable macros with notification This is the default setting and the best choice. With this setting you will receive a security alert where you can choose whether or not to run macros.
Disable macros except digitally signed macros The creator of a macro can digitally sign it. If you then mark this creator as a trusted authority, the macro will be executed without warnings. All unsigned macros will be disabled without notification.
Enable all macros Choose this option if you want to run all macros. This option is not recommended because it will no longer protect you against malicious macros.

16.3 Recording macros

Simple macros can be created by recording the operations with the built-in macro recorder. After starting the recorder, all actions are recorded and converted into VBA code in the background. This continues until the recorder is stopped.

In order to get a properly working macro, it is important to first perform the actions to be performed without recording and write them down in order. You can then use this script to record the actions with the macro recorder.

The button with which you can start recording macros is on the Tab Developers in the group Code, see Figure 16.1.

When this button is clicked to start recording a macro, a dialog box first appears in which a number of properties of the macro can be entered.

Figure 16.5: Dialog box record macro.

Macro name

Each macro must have a name. To do this, Excel fills in the default text “Macro” followed by a digit. You can change this name by typing a different name.

The first character of the macro name has to be a letter. After that, you can use other characters such as letters, numbers, or underscore. Spaces are not allowed in a macro name. Always give meaningful names showing what the macro is going to do. The underscore can be used as a separator between words.

Shortcut key

Macros can be executed through the menu, but this can also be done using keyboard shortcuts. These are shortcuts with the CTRL key combined with a letter key. If you want to assign a keyboard shortcut to the macro then you can enter the letter in the box. Putting for example the letter A in the box, then the key combination CTRL-A is the shortcut that allows you to start the macro.

It is not required to assign a keyboard shortcut to a macro, but this can be very useful for frequently used macros.

Store macro in

The locations where the macro can be stored are:

  • This Workbook
  • New Workbook
  • Personal Macro Workbook

It is important to think thoroughly about it. If the macro is to be used only in the active workbook, choose This Workbook. Should the macro be used in other workbooks, so it is always available, then the Personal Macro Workbook is the best choice. The option New workbook is actually never used.

You should always specify the storage place. The default value is This Workbook.

Description

It is recommended that you give a brief description of what the macro does. When you create the macro you know it, but after a while, you may forget. It is not mandatory to specify a description.

All made choices can always be changed at a later stage.

16.4 Macro Cell Format

Information need

Create a macro that should format the text in the selected cell with the font Comic Sans MS, bold, italic, 16 pt, and red. The column width must be adjusted to the width of the text in the cell. The name of the macro should be CellFormat.

Task 16.2 Record macro

  1. Start with an empty worksheet and enter a text in a cell.

  2. Select the cell that contains the text.

  3. Choose Developer tab > Record Macro (group Code).

  4. Enter in the dialog box the name and a description of the macro.

Figure 16.6: Properties macro CellFormat.

  1. Click OK.

  2. Perform the following actions on the selected cell Right mouse click > Format Cells > tab Font and make the following settings:

    • Font: Comic Sans MS
    • Font style: Bold Italic
    • Size: 16
    • Color: Red

  3. Choose tab tab Home > Format (group Cells) > Autofit Column Width.

  4. Choose Developer tab > Stop Recording (group Code).

Figure 16.7: Button stop recording macro.

Now the macro can be tested for a correct operation.

Task 16.3 Testing macro

  1. Enter a text in another cell and select this cell.

  2. Choose tab Developers > Macros (Program code group).

  3. Select macro Cell Format and click Run.

Figure 16.8: Dialog box macro CellFormat.

  1. Verify that the cell is correctly formatted.

  2. Save the workbook as Excel Macro Workbook (*.xslm) with the name Cell Format.xlsm.

When you want to save a workbook with macros, Excel suggests to save it as an Excel workbook with the extension *.xlsx. However, in this format, no macros can be stored. You should change this yourself in an Excel workbook with macros with the extension *.xlsm.

16.5 Editing Macros

To perform this task, it is necessary that the macro CellFormat in Task 16.3 is created.

A macro can be changed without recording the macro again. Both the options of the macro as the program code can be viewed and modified.

Information need

Make the following changes to the CellFormat macro:

  • Assign the shortcut key “Control t”.
  • Change the font size to 18 pt.

Task 16.4 Continue with the file from Task 16.3.

  1. If necessary, open the file.

  2. Choose Developer tab > Macros (group Code).

You now get the dialog box with available macros again, see Figure 16.8.

  1. Select macro CellFormat and then Options.
Figure 16.9: Options macro CellFormat.

  1. Enter the letter t in the box for the shortcut key and change in the description 16pt in 18pt. Then click OK.

  2. Choose now Edit.

A new Microsoft Visual Basic window will now appear with the program code of the macro in the code screen. In this code you will find 16 for the text size on two places:

  • Format cell in Comic Sans, 16pt, bold, italic and red
  • .Size = 16

  1. Change it to 18 in both places.

  2. Close the Microsoft Visual Basic window with the close button in the upper right corner.

  3. Test the macro on a cell with text using the keyboard shortcut CTRL t.

16.6 Creating a Command Button

You can run a macro in one of the following ways:

  • Via the menu Developers tab > Macros > select macro > Run.
  • Via a hotkey when one is assigned to the macro.
  • Via a command button for the macro.

This task creates a command button on the worksheet for the previously created macro Cell Format.

Task 16.5 Continue with the file from Task 16.4.

  1. If necessary, open the file.

  2. Choose Developer tab > Insert (group Controls).

  3. Click on control Button (Form Control)

Figure 16.10: Form controls, the highlighted one is Button (form control).

The cursor will change to a +. With this cursor, you can draw a rectangle in the worksheet for the button.

  1. Click the left mouse button and draw a rectangle with the desired dimensions for the button somewhere in the worksheet. Then release the left mouse button. The Assign macro dialog box then appears.
Figure 16.11: Link macro CellFormat to the button.
  1. Select the macro CellFormat and click OK.

The drawn rectangle will turn into a button with a text like Button 1.

  1. Select the text on the button and change it to a more appropriate text, for example, Cell format.
Figure 16.12: Command button.

  1. Deselect the button by clicking anywhere in the worksheet.

  2. Test the macro via the command button on a cell with some text.

16.7 Tips

16.7.1 Relative References

Recorded macros will use absolute cell references by default. This means that the exact locations of cells, rows, and columns in the program code of the macro will be used. The macro works then always at the same addresses.

When you want to use relative references in the macro you need to click on the button Use Relative References.

Figure 16.13: Relative references in macros.

This setting remains active until you undo the setting by clicking on this button again.

16.7.2 Deleting macros

Macros can be deleted by Developer tab > Macros (group Code) > Select macro > Delete.

16.8 Exercises

Exercise 16.1 Background color (macr001)

Record a macro that gives the active cell a gray background color.

An example of a solution.

Sub Background_Gray()
'
' Lightgray background for active cell
'
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
        .PatternTintAndShade = 0
    End With
End Sub

Exercise 16.2 Percentage formatting (macr002)

Record a macro with the name PercentageFormat that is formatting a numeric value as a percentage with 1 decimal and also centers the content in the cell.

An example of a solution.

Sub PercentageFormat()
'
' Numeric value formatted as percentage with 1 dec. and horizontally centered
'
    Selection.NumberFormat = "0.0%"
    With Selection
        .HorizontalAlignment = xlCenter
    End With
End Sub

Exercise 16.3 Formatting cell A1 (macr003)

Record a macro that performs the following:

  • Height of row 1 is 18
  • Width of column 1 is 3
  • Font of text in cell A1 is Tahoma 14, bold, blue color

An example of a solution.

Sub FormatA1()
'
' exercise macr003
'
    Rows("1:1").Select
    Selection.RowHeight = 18
    Columns("A:A").Select
    Selection.ColumnWidth = 3
    Range("A1").Select
    With Selection.Font
        .Name = "Tahoma"
        .Size = 14
        .Bold = True
        .Color = -4165632
    End With
End Sub

Exercise 16.4 Printing gridlines (macr004)

Create a macro that ensures that the grid lines will be printed at printing.

You can find this property at the tab Page Layout > Page Setup > tab Sheet.

An example of a solution.

Sub Print_Gridlines()
'
' exercise macr004
'
    With ActiveSheet.PageSetup
        .PrintGridlines = True
    End With
End Sub