如何在VBA for Excel中使用r1c1格式做出绝对的参考？ [英] How to I make an absolute reference using r1c1 format in VBA for Excel?

问题描述

我正在使用VBA（r1c1格式）为Excel编写一个宏，我想引用总是位于E3的单元格。有没有可以使用的语法，将会在r1c1格式中做出绝对的引用？或者有一种方法可以切换到A1并返回到r1c1？

我看到无处不在，我找不到答案...提前感谢。 p>

解决方案

如果我坚持 = E3 到 G5 和 = $ E $ 3 到 G6 然后启动一个VB窗口并在立即窗口这样做：

 ？ ActiveSheet.Range（G5）。Formula 
 = E3 
？ ActiveSheet.Range（G5）。FormulaR1C1 
 = R [-2] C [-2] 
？ ActiveSheet.Range（G6）。公式
 = $ E $ 3 
？ ActiveSheet.Range（G6）。FormulaR1C1 
 = R3C5 
  

所以R和C使其相对于当前单元格。当数字为负数时，您需要使用方括号，否则Excel会认为您从无效单元格引用中减去一个数字。

编辑：它值得一提的是，绝对与亲戚之间的参考方式不同。

• 对于相对引用，您从公式中的单元格开始计算。 E3 是 R [-2] C [-2] 远离 G5 。即2列向上，2列左。




• 绝对值从左上角算起。所以 E3 是 R3C5 。即3行向下，5列。 （感谢@GeorgeDooling的澄清）

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?

I looked everywhere and I can't find the answer...thanks in advance.

解决方案

If I stick =E3 into G5 and =$E$3 into G6 and then start a VB window and in the immediate window do this:

? ActiveSheet.Range("G5").Formula
=E3
? ActiveSheet.Range("G5").FormulaR1C1
=R[-2]C[-2]
? ActiveSheet.Range("G6").Formula
=$E$3
? 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.

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. E3 is R[-2]C[-2] away from G5. i.e. 2 rows up, 2 column left.

• For absolute values you are counting from the top left corner. So E3 is R3C5. i.e. 3 rows down, 5 columns over. (thanks to @GeorgeDooling for the clarification)

这篇关于如何在VBA for Excel中使用r1c1格式做出绝对的参考？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！