Excel VBA: Switch Column Headings to Numbers

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 a Macro to Switch Column Headings to Numbers

To see the Excel VBA code that changes the setting, we can use the Excel Macro Recorder.

  1. Turn on the Macro Recorder
  2. Name the macro, ToggleR1C1, and store it in the Personal Macro Workbook
  3. With the Macro Recorder on, turn the R1C1 reference style on, then turn it off
  4. Turn off the Macro Recorder

Step 2: View the Recorded Excel VBA code

  1. On Developer tab, click Macros
  2. In the list of Macros, click ToggleR1C1
  3. Click Edit, to see the recorded code, that will look something like this:
    ToggleR1C1_01
  4. 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:

Switch Column Headings to Numbers

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.)

  1. At the right end of the QAT, click the drop down arrow
  2. Click More Commands
  3. In the Choose commands from drop down, click Macros
  4. In the list of macros, click the PERSONAL.XLS!ToggleR1C1 macro
  5. Click Add, to move it to the Quick Access Toolbar
    QATMacroAdd
  6. In the QAT list, click the PERSONAL.XLS!ToggleR1C1 macro
  7. Click Modify, and click on an icon for the macro (I use the 8-ball), then click OK
    QATMacroIcon
  8. 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, please watch this short video.

______________

You may also like...

12 Responses

  1. Rick Rothstein (MVP - Excel) says:

    Since the macro is a toggle, you can reduce your code to this one-liner…

    Sub ToggleR1C1
    Application.ReferenceStyle = _ xlA1 + xlR1C1 – Application.ReferenceStyle End Sub
  2. Thanks Rick — nice solution.

    • mermaldad says:

      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.

  3. mikii says:

    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 ;)

  4. Thanks mikii — I’m glad you liked the toggle A1/R1C1 macro.

  5. Contextures Blog » Excel VBA: Show a Message to Users says:

    […] Excel VBA: Switch Column Headings to Numbers […]

  6. Contextures Blog » Excel VBA: Show Message Automatically says:

    […] Excel VBA: Switch Column Headings to Numbers […]

  7. Contextures Blog » Add a Macro Button to the Excel QAT says:

    […] 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 […]

  8. Peter Allen says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *