シート内のすべての数式とその入力範囲を取得する

シート内のすべての数式と、その式が入力された範囲を取得するコードを紹介します。

数式	セル範囲
=A2	B2:B10
=XLOOKUP(C2,マスタ!A:A,マスタ!B:B)	D2:D10

このような数式とセル範囲の組を、
「Key:数式、Item:セル範囲」のDictionaryとして取得します。

ソースコード

' 同一数式ごとにRangeを分けたDictionary
Function Get同じ数式を持つセル範囲をItemとするDictionary(ws対象シート As Worksheet) As Dictionary

    Dim Dic結果値 As New Dictionary
    
    ' 数式があるセルをループ
    Dim 数式セル As Range
    For Each 数式セル In ws対象シート.UsedRange
        If 数式セル.HasFormula Then
            
            ' KeyはR1C1数式
            Dim keyR1C1数式 As String: keyR1C1数式 = 数式セル.FormulaR1C1
            
            ' 新出の数式を登録
            If Dic結果値.Exists(keyR1C1数式) = False Then
                Dic結果値.Add keyR1C1数式, 数式セル
            
            ' 既出の数式であればItemに本セルをUnion
            Else
                Set Dic結果値(keyR1C1数式) = Union(Dic結果値(keyR1C1数式), 数式セル)
            
            End If
            
        End If
    
    Next
    
    Set Get同じ数式を持つセル範囲をItemとするDictionary = Dic結果値
    
End Function

解説

数式をKey、その入力範囲をItemとするDictionaryを取得します。

入力されている数式が同じであるかどうかは、
FormulaR1C1を比較することで判定できます。

例えばB1セルに「=A1」と入っていた場合は、
B2セルは「=A2」になるため、Formulaでは判定できません。

その点FormulaR1C1であれば上記のB1,B2セルの数式は、
どちらも「=RC[-1]」を返すため一致として判定できるようになります。

この基本的なロジックについてはこちらの記事をご覧ください。

あとは数式(Key)ごとにセル範囲をItemに格納していき、
同じ数式のセルが見つかるたびにUnionでセル範囲を拡大していきます。

Dictionaryの教科書的な書き方のコードですので、
Dictionaryの使い方の参考にしてみてください。