With this option, you can record a sequence of functions that can easily be edited and refreshed. Macros are created and edited using the Task Pane located to the right of the sheet.
Starting a New Macro
Select ‘– new macro –‘ from the dropdown in the Task Pane. A popup will appear asking for a macro name.
Note that when you click Refresh All, macros will be run in alphabetical order. You can create a specific sequence of macros by choosing appropriate names, such as ‘1 setup’, ‘2 get data’, ‘3 summarize’.
Macros with names that start with an underscore (such as ‘_prep data’) will not run with Refresh All — they can only be run interactively from the Task Pane, or by using a Run Macro function (where one macro can call another macro).
Add Functions
Once the macro creation has started, selecting any function will add to the macro at the current position. Intermediate results from the previous step are shown in the ‘ᴁ Step Results’ temporary sheet.
Additional functions can be added in the same way – just keep adding functions as required to get the results you want, writing them to a worksheet or file as desired.
Macro Execution
When a macro runs, progress will be displayed in the task pane, coloring completed functions gray and the current function yellow.
If you Step through a macro, intermediate step results will be displayed in the temporary AE Step Results worksheet (green background). Clicking Stop or executing a Write to Worksheet function will remove the temporary worksheet.
Macro Buttons
Macros are selected from the drop-down, and can be run by clicking the Run button. You can also step through a macro one function at a time, or stop a macro in mid-execution. Note that the current step may complete before the execution is stopped.
Changes to macros can be undone (or re-done) for as long as you keep the workbook open. Once you close the workbook, all changes to the macro are saved and previous versions are lost.
The macro can also be renamed or deleted by clicking the appropriate button.
Function Buttons
Individual function steps in your macro can be selected by clicking on them. You can select multiple steps by holding Ctrl or Shift down while clicking, on by click-dragging down a range of functions.
Once selected, functions can be rearranged, edited, deleted, copied or pasted by clicking the appropriate button.
Copy / Paste
You can select any number of function steps and click the Copy button, then select the destination macro and click Paste. You can copy within the same macro, within macros in the same workbook, and even between macros in different workbooks. Note: the destination macro must exist before you start the copy/paste operation. If the Paste button does not appear enabled when you switch to the destination macro, try re-selecting that macro in the drop-down selector.