☰

使用宏将单元格引用替换为值，无需按 F9

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007 vba
使用宏将单元格引用替换为值，无需按 F9

这是一道 Excel 宏题。给定一系列如下公式： 

= A1 + A2
= B2 + B2

ETC。， ...

我想用值替换每个单元格引用，而不替换整个单元格。

= 1 + 2
= 3 + 4

手动执行此操作的方法是突出显示公式中的每个引用，然后按F9将引用替换为值。但是，我必须对数千个单元格执行此操作。是否有宏可以自动完成此操作？

答案1

以下模块采用一个范围，并用在引用单元格中找到的值替换单元格引用。用于Range.Precedents将引用的单元格地址转换为字符串并将Split其转换为单元格地址数组。然后使用此数组查找和替换目标单元格公式中的匹配文本。

用法

  • 将整个代码复制到 Visual Basic 编辑器中的标准代码模块。
  • 用 调用子程序Call ReplaceReferencesWithValues(yourRange)。

宏Test调用子程序来替换选定范围内的引用。

代码

Option Explicit


Sub Test()
    Call ReplaceReferencesWithValues(Selection)
End Sub


Sub ReplaceReferencesWithValues(rng As Range)
    Dim cl As Range
    Dim ws As Worksheet
    Dim strTemp As String
    Dim strRef() As String
    Dim intIndex As Integer

    For Each cl In rng
        Set ws = cl.Worksheet
        strTemp = cl.Formula
        strRef() = ReferenceArray(cl)

        For intIndex = LBound(strRef) To UBound(strRef)
            strTemp = Replace(strTemp, strRef(intIndex), _
                ws.Range(strRef(intIndex)).Value)
        Next

        cl.Formula = strTemp
    Next
End Sub


Function ReferenceArray(rngSource As Range) As Variant
    Dim rngRef As Range
    Dim strTemp As String
    On Error Resume Next

    For Each rngRef In rngSource.Precedents.Cells
        strTemp = strTemp & ", " & rngRef.Address(False, False)
    Next
    If Len(strTemp) > 0 Then strTemp = Mid(strTemp, 3)

    ReferenceArray = Split(strTemp, ", ")
End Function

担忧

  • 替换发生为String，因此，如果公式包含对细胞“A1”和其他包含“A1”的文本，匹配的文本也将被替换为细胞“A1”。
  • SUM如果您尝试用单元格值替换引用，则需要范围才能起作用的公式（例如）将会中断。

答案2

虽然给出的 @natancodes 解决方案似乎可行，但原始问题并不简单，因为它是在文本上替换文本。在这种简单情况下，它会中断：当出现 A1、A11、AA11 或 AA1 时，公式搜索 A1，引用将尝试用错误的值覆盖，并且宏甚至不起作用（除了 On Error Resume Next）。最好使用 RegEx 进行搜索和替换操作。这里提供了一个 RegEx 表达式，但由于对表达式缺乏信心，不太确定是否仍然有效，需要进一步测试：('?[a-zA-Z0-9\s[].]{1,99})?'?!?$?[a-zA-Z]{1,3}$?[0-9]{1,7}(:$?[a-zA-Z]{1,3}$?[0-9]{1,7})?（它来自https://www.get-digital-help.com/extract-cell-references-from-a-formula/）。

其他路径，详见此处https://fastexcel.wordpress.com/2013/10/27/parsing-functions-from-excel-formulas-using-vba-is-mid-or-a-byte-array-the-best-method/，看起来有点麻烦，但它可能对这些特殊情况有效。

相关内容