MS Excel Chapter 9: Macros

Owned by Daniel LaGrow, created
Last updated: Oct 16, 2024
4 min read

Instructions

Section 9.1: Macros

Simply put, macros are a tool that automates repetitive tasks in Excel, helping you save time. For instance, imagine you manage a customer database that requires alphanumeric data only. Often, you receive large Excel files with special characters (like dashes in phone numbers). Instead of spending hours manually cleaning the data to remove non-alphanumeric characters, you can use a macro to complete the task in seconds.

Macros are built using VBA (Visual Basic for Applications) code. The simplest way to create a macro is by recording your mouse clicks through a button found on the Developer tab in the Ribbon. This process allows Excel to automatically generate and store the necessary code for you. For more advanced users, there's also the option to write VBA code directly within the Visual Basic Editor.

The good news is that macros can be accessed and executed entirely using the keyboard. However, using them with screen readers can be tricky. When navigating to macros through the Developer tab on the Ribbon with keyboard shortcuts, both JAWS and NVDA read out the titles of each macro. However, NVDA only reads the description of the first macro in the list—even when you navigate to others—while JAWS does not read any descriptions at all. Because of this, it’s crucial to include detailed information about each macro directly within the body of the Excel document. Additionally, there is no text confirmation when a macro runs successfully. However, if an error occurs while attempting to run a macro, an alert will appear.

For this example, we will create a macro for a button.

  1. Go to the Developer Tab.

    Developer Tab in Microsoft Excel.png

  2. Click Insert, and in the Form Controls section, click the Form Control Button.

    The Insert button with the Form Controls section open. The Form Control button is selected.

  3. Click and drag in cell B1 to create a button at a size you want.

  4. The Assign Macro box will appear, click Cancel.

  5. Now double click your button and type “Clear Form.”

Now that your button is made, we need to assign a macro and use some VBA code to make the button perform an action. To do this,

  1. Go to the Developer Tab and click Visual Basic.

  2. In the Visual Basic box, click Insert and choose Module.

  3. In the text box, type in the code for your button.

    1. In the code, it says “Are you sure you want to clear the form?” This is in the code because a person can read “Clear Form” and know what it is. But a screen reader will not be able to read the Clear Form button. Just that a button is present. This question will be read by the screen reader and will alert the reader.

    2. Be sure you made changes to the code if you need it. For example, changing the range to reach your whole form.

  4. When you click the button, the question will pop up. If you answer “Yes”, the form will be cleared. If you answer “No,” nothing will happen.

  5. Right click your button and click Assign Macro in the expansion menu.

  6. In the Assign Macro box, click the Clear Form Macro in the Macro name and click OK.

  7. Next, click the Macro button in the Developer Tab.

  8. In the Macro pop-up box, click the Clear Form Macro and click OK.

  9. In the pop-up box, click the Options button and change the short cut to Control + Shift + C.

    1. Clicking those buttons in that order will allow students who only use keyboards to use the macros and clear the sheet.

  10. To fulfill accessibility requirements, add instructions to cell A1 on how to use the Clear Form button.

Related articles