☰

使用巨集將儲存格參考替換為值，無需 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如果公式同時包含 a 的引用，則細胞「A1」和其他包含「A1」的文本，匹配的文本也會被替換為細胞「A1」。
  • SUM如果您嘗試用儲存格值取代引用，則需要範圍才能執行的公式（例如 ）將會中斷。

答案2

儘管給定的 @natancodes 解決方案似乎可行，但原始問題並不是一個簡單的任務，因為它正在替換文字上的文字。在這種簡單的情況下它會崩潰：當出現 A1、A11、AA11 或 AA1 時搜尋 A1 的公式，引用將嘗試用錯誤的值覆蓋，巨集甚至無法工作（除了 On Error Resume Next）。最好使用正規表示式進行搜尋和取代操作。這裡提供了一個正規表示式，但由於對表達式缺乏信心，不確定是否仍然有效，需要進一步測試：('?[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/，看起來有點麻煩，但它可能適用於這些特殊情況。

相關內容