数式の参照しているセルを取得する
セルに入っている数式の参照しているセルを取得するには、
セルが直接または間接に参照している参照元を表すRangeオブジェクトを返します
A1セルに=B1と入っている場合に、A1セルのPrecedentsでB1セルを取得できます。
セルを直接または間接に参照している参照先Rangeオブジェクトを返します
A1セルに=B1と入っている場合に、B1セルのDependentsでA1セルを取得できます。
そこで、以下のサンプルコードでは、
指定のセルの数式が参照している（参照元）セルを配列で返します。
Function getFormulaRange(ByVal argRange As Range) As Range()
Dim sFormula As String
Dim aryRange() As Range
Dim tRange As Range
Dim ix As Long
Dim i As Long
Dim flgS As Boolean 'シングルクオートが奇数の時True
Dim flgD As Boolean 'ダブルクオートが奇数の時True
Dim sSplit() As String
Dim sTemp As String
'=以降の計算式
sFormula = Mid(argRange.FormulaLocal, 2)
'計算式の中の改行や余分な空白を除去
sFormula = Replace(sFormula, vbCrLf, "")
sFormula = Replace(sFormula, vbLf, "")
sFormula = Trim(sFormula)
flgS = False
flgD = False
For i = 1 To Len(sFormula)
'シングル・ダブルのTrue,Falseを反転
Select Case Mid(sFormula, i, 1)
Case "'"
flgS = Not flgS
Case """"
'シングルの中ならシート名
If Not flgS Then
flgD = Not flgD
End If
End Select
Select Case Mid(sFormula, i, 1)
'各種演算子の判定
Case "+", "-", "*", "/", "^", ">", "<", "=", "(", ")", "&", ",", " "
Select Case True
Case flgS
'シングルの中ならシート名
sTemp = sTemp & Mid(sFormula, i, 1)
Case flgD
'ダブルの中なら無視
Case Else
'各種演算子をvbLfに置換
sTemp = sTemp & vbLf
End Select
Case Else
'ダブルの中なら無視、ただしシングルの中はシート名
If Not flgD Or flgS Then
sTemp = sTemp & Mid(sFormula, i, 1)
End If
End Select
Next
On Error Resume Next
'vbLfで区切って配列化
sSplit = Split(sTemp, vbLf)
ix = 0
For i = 0 To UBound(sSplit)
If sSplit(i) <> "" Then
Err.Clear
'Application.Evaluateメソッドを使ってRangeに変換
If InStr(sSplit(i), "!") > 0 Then
Set tRange = Evaluate(Trim(sSplit(i)))
Else
'シート名を含まない場合は、元セルのシート名を付加
Set tRange = Evaluate("'" & argRange.Parent.Name & "'!" & Trim(sSplit(i)))
End If
'Rangeオブジェクト化が成功すれば配列へ入れる
If Err.Number = 0 Then
ReDim Preserve aryRange(ix)
Set aryRange(ix) = tRange
ix = ix + 1
End If
End If
Next
On Error GoTo 0
getFormulaRange = aryRange
End Function
使い方としては、以下のようになります。
Sub sample()
Dim aryRange() As Range
Dim var As Variant
aryRange = getFormulaRange(Range("A1"))
For Each var In aryRange
Debug.Print var.Address(External:=True)
Next
End Sub
イミディエイト ウインドウに、参照しているセルのアドレスが表示されます。
A1=TRIM(B$1&'S&h"e ''et2'!$A1)*Sheet1!$C$1+SUM('S&h"e
''et2'!A1:C1,B1)
この場合は、イミディエイト ウインドウには、
[ブック名]Sheet1!$B$1
'[ブック名]S&h"e ''et2'!$A$1
[ブック名]Sheet1!$C$1
'[ブック名]S&h"e ''et2'!$A$1:$C$1
[ブック名]Sheet1!$B$1
このように表示されます。
For Each var In aryRange
この中で、
var.ParentとRange("A1").Parentが同じであれば同じシート内なので、
PrecedentsやDependentsで取得済と判定すれば良いでしょう。
いざ必要となった時に、VBAを最初から書くのは大変なので、そのような場合に思い出してください。
同じテーマ「マクロVBAサンプル集」の記事
印刷ダイアログを使用する（xlDialogPrint）
名前定義の一覧と削除（Name）
シートを名前順に並べ替える
数式内の不要なシート名を削除する（HasFormula）
数式の参照しているセルを取得する
増殖した条件付き書式を整理統合する
条件付き書式で変更された書式を取得する
セル結合/解除でセル値を退避/回復
セル結合なんて絶対に許さないんだからね
セルの数式をネスト色分けしてコメント表示
セル結合して表を見やすくする（非推奨）
新着記事NEW ・・・新着記事一覧を見る
新旧マスタの差異比較｜Power Query（M言語）入門(2023-02-28)
有効な最新単価の取得｜Power Query（M言語）入門(2023-02-26)
有効な最新単価の取得｜Power Query（M言語）入門(2023-02-21)
グルーブ内の最小・最大｜Power Query（M言語）入門(2023-02-17)
2つのテーブルのマージ｜Power Query（M言語）入門(2023-02-15)
「売上」が数値の行のみ取り込む｜Power Query（M言語）入門(2023-02-13)
A列のヘッダー名を変更する｜Power Query（M言語）入門(2023-02-11)
CSVのA列が日付の行だけを取り込む｜Power Query（M言語）入門(2023-02-10)
列数不定のCSVの取り込み｜Power Query（M言語）入門(2023-02-09)
別ブックの最終シートの取り込み｜Power Query（M言語）入門(2023-02-08)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
6.マクロって何？VBAって何？｜VBA入門
7.並べ替え（Sort）｜VBA入門
8.Excelショートカットキー一覧｜Excelリファレンス
9.エクセルVBAでのシート指定方法｜VBA技術解説
10.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBAサンプル集
- 数式の参照しているセルを取得する
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。