如何在 VBA for Excel 中使用 r1c1 格式进行绝对引用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21977365/
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
How to I make an absolute reference using r1c1 format in VBA for Excel?
提问by frogjockey
I am writing a macro for Excel using VBA (r1c1 format) and I want to reference a cell that is always in position E3. Is there a syntax I can use that will make an absolute reference in r1c1 format? Or is there is a way to switch to A1 and back to r1c1?
我正在使用 VBA（r1c1 格式）为 Excel 编写宏，并且我想引用一个始终位于 E3 位置的单元格。是否有我可以使用的语法以 r1c1 格式进行绝对引用？或者有没有办法切换到A1并回到r1c1？
I looked everywhere and I can't find the answer...thanks in advance.
我到处找，但找不到答案……在此先感谢。
回答by Jerry Jeremiah
If I stick =E3into G5and =$E$3into G6and then start a VB window and in the immediate window do this:
如果我=E3进入G5并=$E$3进入G6然后启动一个 VB 窗口并在直接窗口中执行以下操作：
? ActiveSheet.Range("G5").Formula
=E3
? ActiveSheet.Range("G5").FormulaR1C1
=R[-2]C[-2]
? ActiveSheet.Range("G6").Formula
=$E
? ActiveSheet.Range("G6").FormulaR1C1
=R3C5
So the R and C make it relative to the current cell. You need to use square brackets when the number is negative otherwise Excel thinks you are subtracting a number from an invalid cell reference.
所以 R 和 C 使它相对于当前单元格。当数字为负数时，您需要使用方括号，否则 Excel 认为您正在从无效的单元格引用中减去一个数字。
EDIT: It is worth mentioning that the reference is handled differently when absolute vs. relative.
编辑：值得一提的是，引用在绝对与相对时的处理方式不同。
For relative references you are counting from the cell the formula is in.
E3isR[-2]C[-2]away fromG5. i.e. 2 rows up, 2 column left.For absolute values you are counting from the top left corner. So
E3isR3C5. i.e. 3 rows down, 5 columns over. (thanks to @GeorgeDooling for the clarification)
对于相对引用您从单元格的公式是计数。
E3是R[-2]C[-2]远离G5。即向上 2 行，向左 2 列。对于绝对值，您从左上角开始计数。所以
E3是R3C5。即向下 3 行，超过 5 列。（感谢@GeorgeDooling 的澄清）
回答by dra_red
Sometimes the 'Application.ConvertFormula' function is useful for going between reference styles. For example, adding this to the immediate window:
有时，“Application.ConvertFormula”函数对于在参考样式之间切换很有用。例如，将其添加到即时窗口：
debug.print application.ConvertFormula("=PRODUCT(O7,P$7)",xla1,xlr1c1)
debug.print application.ConvertFormula("=PRODUCT(O7,P$7)",xla1,xlr1c1)
returned this:
返回这个：
=PRODUCT(RC[-5],R7C[-4])
=产品(RC[-5],R7C[-4])
That is a trivial example but when you have a monster formula on your hands...
这是一个微不足道的例子，但是当你手上有一个怪物公式时......
Also, switching between reference styles in VBA is done via:
此外，在 VBA 中的参考样式之间切换是通过以下方式完成的：
application.ReferenceStyle = xlA1
application.ReferenceStyle = xlA1
or
或者
application.ReferenceStyle = xlR1C1
application.ReferenceStyle = xlR1C1
回答by Regret
The R1C1 format can be used both for absolute and relative references. R1C1 without brackets is absolute, when you use brackets it is relative.
R1C1 格式可用于绝对引用和相对引用。没有括号的 R1C1 是绝对的，当您使用括号时，它是相对的。
R3C5always points at E3no matter where you use it.
As you already know, R[3]C[5]always points 3 down and 5 to the right of your current location.
R3C5E3无论您在哪里使用它，始终指向。如您所知，R[3]C[5]始终指向您当前位置的下方 3 和 5。
As George Dooling already said in one of the comments, the reason R-3C-5is meaningless is because you are off the sheet. The sheet starts at R1C1and the numbers only goes up.
正如乔治·杜林 (George Dooling) 在其中一条评论中所说的那样，原因R-3C-5毫无意义是因为您不在表中。工作表开始于R1C1并且数字只会增加。
As to your second question, you can use the A1 format by enclosing it with quotes. R3C5can be written as "E3"within FormulaR1C1without problems.
至于您的第二个问题，您可以通过用引号将其括起来来使用 A1 格式。R3C5可以写成"E3"insideFormulaR1C1没有问题。
回答by Jon
R3C5
R3C5
No brackets = absolute
没有括号=绝对
Hitting f4 in the Cell window when editing a formula will toggle relative or absolute
编辑公式时在单元格窗口中按 f4 将切换相对或绝对
