关闭录制的 VBA 宏的 R1C1 参考样式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11707033/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Switch off R1C1 reference style for recorded VBA macros
提问by SoftTimur
In recorded VBA macros, it seems that their formulas use R1C1 reference style. For instance, to fill in B4with B2+1:
在录制的 VBA 宏中，它们的公式似乎使用R1C1 reference style. 例如，填写B4有B2+1：
Range("B4").Select
ActiveCell.FormulaR1C1 = "=R[-2]C+1"
Does anyone know if it is possible to switch off this mode? For instance, let recorded macro look like:
有谁知道是否可以关闭此模式？例如，让录制的宏看起来像：
Range("B4").Select
ActiveCell.Formula = "=B2+1"
回答by Siddharth Rout
I believe you cannot do that. The macro will always record in R1C1 style.
我相信你不能那样做。宏将始终以 R1C1 样式录制。
You can always switch the style but it will only be applied to the worksheet and if you record a macro it will still show R1C1 reference style.
您可以随时切换样式，但它只会应用于工作表，如果您录制宏，它仍将显示 R1C1 参考样式。
It is very easy to understand the R1C1 style
R1C1风格很容易理解
In R1C1 reference style, the range is referred by how far the cells are located from the cell you are calling. For example, if you have 5 values from R1C1 to R5C1 and the range is called from R7C2, then the range would be R[-6]C[-1]:R[-2]C[-1]. Here the first cell in the range is 6 rows before the cell R7C2 and 1 column before the cell R7C2 and similarly for the last cell in the range.
在 R1C1 引用样式中，范围由单元格距您正在调用的单元格的距离表示。例如，如果您有从 R1C1 到 R5C1 的 5 个值，并且范围是从 R7C2 调用的，那么范围将是 R[-6]C[-1]:R[-2]C[-1]。这里范围中的第一个单元格在单元格 R7C2 之前的 6 行和单元格 R7C2 之前的 1 列，对于范围中的最后一个单元格也是如此。
If I take your example then "=R[-2]C+1"means that the formula is referring to a row which is two rows up (-2) and in the same column (0). Your formula is same as "=R[-2]C[0]+1"
如果我以您的示例为例，则"=R[-2]C+1"意味着该公式指的是向上两行 (-2) 并在同一列 (0) 中的行。你的公式是一样的"=R[-2]C[0]+1"
EDIT
编辑
Here is a small function that I wrote which can help you convert R1C1 to A1 string
这是我写的一个小函数，可以帮助您将 R1C1 转换为 A1 字符串
Sub Sample()
'~~> This will give you $B
Debug.Print R1C12A1("B4", "R[-2]C")
'~~> This will give you E227
Debug.Print R1C12A1("O9", "R[218]C[-10]", True)
'~~> This will give you $Y7
Debug.Print R1C12A1("O9", "R[208]C[10]")
End Sub
Function R1C12A1(baseCell As String, sRC As String, Optional RemDollar As Boolean = False) As String
Dim MyArray() As String
Dim r As Long, c As Long
sRC = Replace(sRC, "R", "")
If Left(sRC, 1) = "C" Then
r = 0
Else
r = Replace(Replace(Split(sRC, "C")(0), "[", ""), "]", "")
End If
If Right(sRC, 1) = "C" Then
c = 0
Else
c = Replace(Replace(Split(sRC, "C")(1), "[", ""), "]", "")
End If
If RemDollar = False Then
R1C12A1 = Range(baseCell).Offset(r, c).Address
Else
R1C12A1 = Replace(Range(baseCell).Offset(r, c).Address, "$", "")
End If
End Function
Note: I have not done any error handling here. I am sure you can incorporate that if needed.
注意：我这里没有做任何错误处理。我相信你可以在需要时加入它。
回答by whytheq
There used to be a facility to toggle relative reference when recording a macro.
曾经有一种工具可以在录制宏时切换相对引用。
When you have started recording, in the macro toolbar - near the stop button - there was a button to toggle relative reference; is this not the same as toggling R1C1 ? or isn't this available anymore?
当您开始录制时，在宏工具栏中 - 停止按钮附近 - 有一个按钮可以切换相对参考；这与切换 R1C1 不一样吗？或者这不再可用？
I never bothered toggling it myself as like Siddharth says the R1C1 isn't too tricky to understand plus, irrespective of whatever you do, the VBA will need some editing so at the same time if you wish to use other syntax it's easy enough to change.
我从来没有费心去切换它，就像 Siddharth 说 R1C1 不太难理解另外，不管你做什么，VBA 都需要一些编辑，所以同时如果你想使用其他语法，它很容易改变.
I've just played around with the following but it doesn't seem to help so maybe I'm mixing up the use of this button with R1C1...
我刚刚尝试了以下内容，但似乎没有帮助，所以也许我将这个按钮的使用与 R1C1 混淆了......
