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

现在是星期五，您的大脑几乎已满，但是让我们尝试在周末之前在其中塞满一点Excel VBA。 我们之前已经讨论过Excel列标题 ，并了解了如何将“参考样式”设置从R1C1(数字)更改为A1(字母)。

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.

该设置隐藏得很好，打开和关闭它有点麻烦。 我们将创建一个宏，使我们能够快速从A1切换到R1C1，或从R1C1切换到A1。

步骤1：记录宏以将列标题切换为数字 (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.

要查看更改设置的Excel VBA代码，我们可以使用Excel Macro Recorder 。

1. Turn on the Macro Recorder
   打开宏录制器
2. Name the macro, ToggleR1C1, and store it in the Personal Macro Workbook
   将宏命名为ToggleR1C1，并将其存储在“个人宏工作簿”中
3. With the Macro Recorder on, turn the R1C1 reference style on, then turn it off
   在宏录制器打开的情况下，打开R1C1参考样式，然后将其关闭
4. Turn off the Macro Recorder
   关闭宏录制器

步骤2：查看记录的Excel VBA代码 (Step 2: View the Recorded Excel VBA code)

1. On Developer tab, click Macros
   在开发人员选项卡上，单击宏
2. In the list of Macros, click ToggleR1C1
   在宏列表中，单击ToggleR1C1

3. Click Edit, to see the recorded code, that will look something like this:

   单击编辑，以查看记录的代码，该代码将类似于以下内容：

   
4. The lines that start with an apostrophe are comments, and you can delete them in this example.
   以撇号开头的行是注释，您可以在本示例中将其删除。

步骤3：编辑记录的Excel VBA代码 (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.

在记录的Excel VBA代码中，您可以看到两条更改参考样式的行。 第一行将其设置为R1C1，第二行将其设置为A1样式。 我们希望宏可以切换设置。

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.

如果当前是A1，我们的宏会将其更改为R1C1，反之亦然。 为此，我们将添加If ... Then代码，类似于工作表上的IF公式。

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.

如果我们的代码是用英语而不是Excel VBA编写的，则会这样说：如果当前参考样式为A1，请将其更改为R1C1，否则(如果不是A1)将其更改为A1。

Here's how you can edit your code to say the same thing in Excel VBA:

您可以通过以下方法在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.

宏录制器向我们展示了如何更改参考样式设置。 通过添加If ... Then ... Else ... End If，我们创建了一个简单的宏，可以轻松在设置之间进行切换。

步骤4：将宏添加到QAT (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.)

要使此宏易于使用，可以将其添加到Excel 2007中的快速访问工具栏(QAT)中。(如果使用的是Excel 2003或更早版本，则可以自定义Excel工具栏，并为宏。)

1. At the right end of the QAT, click the drop down arrow
   在QAT的右端，单击下拉箭头
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
   在宏列表中，单击PERSONAL.XLS！ToggleR1C1宏

5. Click Add, to move it to the Quick Access Toolbar

   单击添加，将其移至快速访问工具栏

   
6. In the QAT list, click the PERSONAL.XLS!ToggleR1C1 macro
   在“ QAT”列表中，单击“ PERSONAL.XLS！ToggleR1C1”宏

7. Click Modify, and click on an icon for the macro (I use the 8-ball), then click OK

   单击修改，然后单击宏的图标(我使用8球)，然后单击确定

   
8. Click OK, to close the Excel Options window.
   单击确定，关闭“ Excel选项”窗口。

步骤5：测试您的宏以将列标题切换为数字 (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.

在QAT上，单击新按钮，以在A1和R1C1之间切换参考样式设置。

Congratulations! Your Excel VBA work is done, and now you can easily switch column headings to numbers.

恭喜你！ Excel VBA工作已完成，现在您可以轻松地将列标题切换为数字。

观看视频 (Watch the Video)

To see the steps for adding the macro to the QAT, watch this short video.

要查看将宏添加到QAT的步骤，请观看此简短视频。

演示地址