It’s Friday, and your brain is almost full, but let’s try to cram a little bit of Excel VBA in there before the weekend.
We’ve talked about the Excel Column Headings before, and seen how to change the Reference Style setting from R1C1 (Numbers) to A1 (Letters).
That setting is pretty well hidden, and it’s a bit of a pain to switch on and off.
We’ll create a macro that will let us quickly switch from A1 to R1C1, or from R1C1 to A1.
Step 1: Record Macro to Switch Column Headings to Numbers
To see the Excel VBA code that changes the setting, we can use the Excel Macro Recorder.
- Turn on the Macro Recorder
- Name the macro, ToggleR1C1, and store it in the Personal Macro Workbook
- With the Macro Recorder on, turn the R1C1 reference style on, then turn it off
- Turn off the Macro Recorder
Step 2: View the Recorded Excel VBA code
- On Developer tab, click Macros
- In the list of Macros, click ToggleR1C1
- Click Edit, to see the recorded code, that will look something like this:
- The lines that start with an apostrophe are comments, and you can delete them in this example.
Step 3: Edit the Recorded Excel VBA code
In the recorded Excel VBA code, you can see the two lines that change the reference style. The first line sets it to R1C1 and the second line sets it to A1 style. We’d like our macro to toggle the setting.
If it’s currently A1, our macro will change it to R1C1, and vice versa. To do that, we’ll add bit of If…Then code, similar to an IF formula on the worksheet.
Our code, if written in English instead of Excel VBA, would say this: If the current reference style is A1, change it to R1C1, otherwise (if it’s not A1), change it to A1.
Here’s how you can edit your code to say the same thing in Excel VBA:
The Macro Recorder showed us how to change the reference style setting.
By adding If…Then…Else…End If, we created a simple macro that will make it easy to switch between the settings.
Step 4: Add the Macro to the QAT
To make this macro easy to use, you can add it to the Quick Access Toolbar (QAT) in Excel 2007. (If you’re using Excel 2003, or an earlier version, you can customize the Excel toolbars, and add a button for the macro.)
- At the right end of the QAT, click the drop down arrow
- Click More Commands
- In the Choose commands from drop down, click Macros
- In the list of macros, click the PERSONAL.XLS!ToggleR1C1 macro
- Click Add, to move it to the Quick Access Toolbar
- In the QAT list, click the PERSONAL.XLS!ToggleR1C1 macro
- Click Modify, and click on an icon for the macro (I use the 8-ball), then click OK
- Click OK, to close the Excel Options window.
Step 5: Test Your Macro to Switch Column Headings to Numbers
On the QAT, click the new button, to toggle the reference style setting between A1 and R1C1.
Congratulations! Your Excel VBA work is done, and now you can easily switch column headings to numbers.
Watch the Video
To see the steps for adding the macro to the QAT, watch this short video.
______________
Since the macro is a toggle, you can reduce your code to this one-liner…
Thanks Rick — nice solution.
I agree, a very elegant solution, Rick. One caution, I cut and pasted this into Excel 2013 and it gave me an “Expected: end of statement” error. I was able to trace the error to the minus sign between “xlR1C1” and “Application.ReferenceStyle”. My suspicion is that the blog software replaces the minus with some sort of dash.
@mermaldad, thanks, and I’ve wrapped the code, and retyped the minus sign, so it should work better now, if copied and pasted.
tnx, great tip. i always wanted a quick way to toggle A1/R1C1, but i was too lazy to create a macro of my own 😉
Thanks mikii — I’m glad you liked the toggle A1/R1C1 macro.
[…] Excel VBA: Switch Column Headings to Numbers […]
[…] Excel VBA: Switch Column Headings to Numbers […]
[…] part of the Excel programming series, I described how to run an Excel macro by adding a button to the Quick Access Toolbar (QAT). This video shows the […]
I know this is years after the blog post, but Oh! So useful. I just had to add a thank you.
My Toggle macro no appears on all my new sheets in my Custom Menu tab.
Thanks
Peter
@Peter Allen, you’re welcome, and I’m glad it helped you, after all these years!
Originally posted in 2009 and still very helpful, cheers Debra.
Thanks Tim, I’m glad it’s still helpful!