关闭记录的VBA宏的R1C1参考样式 [英] Switch off R1C1 reference style for recorded VBA macros

问题描述

在记录的VBA宏中，它们的公式似乎使用 R1C1参考样式。例如，要填入 B4 与 B2 + 1 ：

  Range（B4）。选择
 ActiveCell.FormulaR1C1 == R [-2] C + 1
 有人知道是否有可能关闭此模式？例如，让记录宏看起来像：
  Range（B4）。选择
 ActiveCell.Formula = = B2 + 1
  
 
 
解决方案
我相信你不能这样做。宏将始终以R1C1格式记录。
 
 
 
您可以随时切换样式，但只能应用于工作表，如果您记录一个宏，它仍然会显示R1C1参考样式。
 
 
 
很容易理解R1C1样式
 
 
 
在R1C1参考样式中，范围是引用单元格距离您正在调用的单元格多远。例如，如果从R1C1到R5C1有5个值，并且从R7C2调用该范围，则范围将为R [-6] C [-1]：R [-2] C [-1]。这里的范围内的第一个单元格是单元格R7C2之前的6行，单元格R7C2之前的第一个单元格，以及范围内的最后一个单元格类似。 
 
 
 
如果我拿你的例子，那么= R [-2] C + 1意味着公式是指的是两列向上（-2）和相同列（0）的行。您的公式与= R [-2] C [0] +1 
 
 
 
 编辑 
 
 
 
这是一个我写的一个小功能，可以帮助您将R1C1转换为A1字符串
  Sub Sample（）
'~~>这将给你$ B $ 2 
 Debug.Print R1C12A1（B4，R [-2] C）
 
'~~>这将给你E227 
 Debug.Print R1C12A1（O9，R [218] C [-10]，True）
 
'~~>这将给你$ Y $ 217 
 Debug.Print R1C12A1（O9，R [208] C [10]）
 End Sub 
 
功能R1C12A1（baseCell As String，sRC As String，可选RemDollar As Boolean = False）As String 
 Dim MyArray（）As String 
 Dim r As Long，c As Long 
 
 sRC = Replace（sRC ，R，）
 
如果Left（sRC，1）=C然后
r = 0 
 Else 
r =替换（替换sRC，C）（0），[，），]，）
如果
 
如果右（sRC，1）=C然后
c = 0 
 Else 
c =替换（替换（拆分（sRC，C）（1），[，），]，）
结束如果
 
如果RemDollar = False然后
 R1C12A1 = Range（baseCell）.Offset（r，c）.Address 
 Else 
 R1C12A1 = Replace（Range （baseCell）.Offset（r，c）.Address，$，）
如果
结束函数
  
 注意：我没有在这里做任何错误处理。如果需要，我相信你可以加入。
 
In recorded VBA macros, it seems that their formulas use R1C1 reference style. For instance, to fill in B4 with 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"

解决方案
I believe you cannot do that. The macro will always record in R1C1 style.

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.

It is very easy to understand the R1C1 style

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. 

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"

EDIT

Here is a small function that I wrote which can help you convert R1C1 to A1 string
Sub Sample()
    '~~> This will give you $B$2
    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 $Y$217
    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.

                        
这篇关于关闭记录的VBA宏的R1C1参考样式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！