Excel 2024: Improve Your Macro Recording

November 11, 2024 - by Bill Jelen

I loved the 1985 version of the Macro Recorder in Lotus 1-2-3. The code was hard to understand, but it worked. There are some defaults in the Excel Macro Recorder that cause misery for anyone trying to record macros. Here are three tips to make the macro experience possibly better.

Tip 1: Turn on Relative Reference for Every Macro

Say that you start in A2 and record a simple macro that moves to A4. I would call that macro MoveDownTwoCells. But if you run this macro while the cell pointer is in J10, the macro will move to cell A4. This is rarely what you want to have happen. But you can change the behavior of the macro recorder by selecting View, Macros, Use Relative References before you record the macro.

There is a Macros drop-down on the View tab. The third choice is called Use Relative References.

If you record the MoveDownTwoCells macro with this setting turned on, the macro will work from any cell.

Caution

The setting stays turned on only until you close Excel. Tomorrow, you will have to turn it back on again.

Tip

What if you actually need to record a macro that always jumps to cell A4? Even with Relative References enabled, you could press F5 for Go To and use the Go To dialog to go to A4. The macro recorder will record code that will always jump to A4.

Tip 2: Use Ctrl+Shift+Arrow to move to the end of a data set.

Say that you want to add a total at the bottom of yesterdayâ€˜s invoice register. You want the total to appear in row 9 today, but tomorrow, it might need to be in row 17 or row 5, depending on how many rows of data you have.

Find a column that is reliably 100% filled. From the top of that column, record the action of pressing Ctrl+Shift+Down Arrow. Press the Down Arrow key one more time, and you will know you are in the row where the totals should be.

If this is a CSV file, you will always be in cell A1 when it is open. Press Ctrl+Shift+Down Arrow in the macro will get you to the last row with data today.

Tip 3: Type =SUM(E$2:E8) instead of pressing the AutoSum button

The macro recorder will not record the intent of AutoSum. When you press AutoSum, you will get a sum function that starts in the cell above and extends up to the first non-numeric cell. It does not matter if you have Relative References on or off; the macro recorder will hard-code that you want to sum the seven cells above the active cell.

Instead of using the AutoSum icon, type a SUM function with a single dollar sign before the first row number: =SUM(E$2:E8). Use that formula while recording the macro, and the macro will reliably sum from the cell above the active cell all the way up to row 2, as shown below.

With numbers in E2:E8, you would normally use a formula of =SUM(E2:E8). However, if you record the macro to use =SUM(E$2:E8), the formula will work for any size data set.

Bonus Tip: Use TypeScript to Write Macros for Excel Online

VBA has been the macro language in Excel for decades. This was fine when "Excel" meant Excel running on a PC with Windows or a Mac. But today, there are versions of "Excel" running on an iPhone, on Android, or in a browser. Microsoft is never going to support VBA in those endpoints.

In 2020, they debuted a new macro language called TypeScript. It was designed for those people running Excel in a browser. TypeScript will let you run macros on a PC or Excel Online. As the usage of Excel Online picks up, there will be more scenarios where you want your macros developed in TypeScript.

The new language is hard to find. You need to opt into a preview. This video will get you started.

This article is an excerpt from MrExcel 2024 Igniting Excel

Title photo by Lucas Alexander on Unsplash