返回具有已计算成员的公式

Question

我正在开发一个函数，该函数返回其输入单元格的公式，用单元格的值替换对其他单元格的所有引用。我当前的实现工作良好，除非输入单元格包含绝对引用，这正是我想要改进的地方。

目前，我提取输入单元格的Range.FormulaR1C1属性，并将所有地址替换为各自单元格的值。识别相对地址相当容易，因为它们包含方括号，即[]。一般格式为R[rN]C[cN>，其中rN和cN表示相对的行或列数(整数值(-如果其中任何一个等于零，它将与括号一起被省略，因此公式中的位置有两种可能是地址的开头：

1. 接下来的两个字符是"R[〃
2. 接下来的三个字符是"；RC[〃；(不允许自引用，因此我们可以排除"RC"。(

当我们引入绝对地址时，括号消失，通用地址格式变为RrNCcN。现在，识别地址变得更加复杂，我更喜欢坚持两个简单的规则R[和RC]。如果我可以将绝对单元格引用转换为相对单元格引用，并以R1C1格式返回它们，我可以保持当前实现不变。

我需要获得R1C1格式的单元格公式，将所有绝对单元格引用替换为相对单元格引用。从A1格式(Range.Formula属性(的地址中删除绝对引用在理论上非常简单$"标志。唉，我不知道如何将地址转换回R1C1格式。下面是一个天真的实现，它在倒数第二行失败了，但我认为它抓住了这个想法。我不知道如何进行，也不知道是否有可能像我想象的那样简单地进行。

Function returnRelativeFormulaR1C1(refCell As Range) As String
Dim refAddress As String, addressLength As Integer, posRow As Integer, posCol As Integer, iColNr As Integer
refAddress = refCell.Address(ReferenceStyle:=xlR1C1)
addressLength = Len(refAddress)
iColNr = InStr(2, refAddress, "C")
posRow = Val(Mid(refAddress, 2, iColNr - 1)) ' Begin at 2, because there's always "R" at the beginning
posCol = Val(Mid(refAddress, iColNr + 1, addressLength - iColNr))
Dim formulaText As String
formulaText = Cells(posRow, posCol).Formula ' Get formula in A1 format
Dim formulaTextRelative As String
formulaTextRelative = Replace(formulaText, "$", "") ' Remove "$" signs, i.e. convert absolute references to relative
Dim convRange As Range
Set convRange = refCell
convRange.Cells(1, 1).Formula = formulaTextRelative ' Assign the converted formula to a virtual cell? The code fails here.
returnRelativeFormulaR1C1 = convRange.Cells(1, 1).FormulaR1C1 ' If the previous row worked, this should then take care of the conversion back to R1C1 format.
End Function

Answer 1

非常感谢chris neilsen，ConvertFormula正是我所需要的。

更正了转换的实现：

Function returnRelativeFormulaR1C1(refCell As Range) As String
Dim refAddress As String, addressLength As Integer, posRow As Integer, posCol As Integer, iColNr As Integer
refAddress = refCell.Address(ReferenceStyle:=xlR1C1)
addressLength = Len(refAddress)
iColNr = InStr(2, refAddress, "C")
posRow = Val(Mid(refAddress, 2, iColNr - 1)) ' Begin at 2, because there's always "R" at the beginning
posCol = Val(Mid(refAddress, iColNr + 1, addressLength - iColNr))
Dim formulaTextA1 As String
formulaTextA1 = Cells(posRow, posCol).Formula ' Get formula in A1 format
Dim formulaTextA1Relative As String
formulaTextA1Relative = Replace(formulaTextA1, "$", "") ' Remove "$" signs, i.e. convert absolute references to relative
returnRelativeFormulaR1C1 = Application.ConvertFormula(Formula:=formulaTextA1Relative, fromReferenceStyle:=xlA1, toReferenceStyle:=xlR1C1, RelativeTo:=refCell) ' Convert back to R1C1
End Function