☰

マクロを使用して F9 を使わずにセル参照を値に置き換える

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007 vba
マクロを使用して F9 を使わずにセル参照を値に置き換える

これは Excel マクロに関する質問です。次のような一連の数式があるとします。 

= A1 + A2
= B2 + B2

などなど

セル全体を置き換えずに、これらの各セル参照を値に置き換えたいと思います。

= 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 は別として)。検索と置換の操作には、正規表現を使用する方がよいでしょう。正規表現式はここで提供されていますが、式の信頼性が低いため、まだ機能するかどうかはよくわかりません。さらにテストする必要があります: ('?[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/少し面倒に思えますが、これらの特殊なケースでは機能する可能性があります。

関連情報