数式の参照しているセルを取得する
セルに入っている数式の参照しているセルを取得するには、
セルが直接または間接に参照している参照元を表す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サンプル集」の記事
名前定義の一覧と削除（Name）
シートを名前順に並べ替える
数式内の不要なシート名を削除する（HasFormula）
数式の参照しているセルを取得する
増殖した条件付き書式を整理統合する
条件付き書式で変更された書式を取得する
セル結合/解除でセル値を退避/回復
セル結合なんて絶対に許さないんだからね
セルの数式をネスト色分けしてコメント表示
セル結合して表を見やすくする（非推奨）
シートを削除：不定数のシート名に対応
新着記事NEW ・・・新着記事一覧を見る
AIは便利なはずなのに…「AI疲れ」が次の社会問題になる｜生成AI活用研究(2026-02-16)
カンマ区切りデータの行展開｜エクセル練習問題(2026-01-28)
開いている「Excel/Word/PowerPoint」ファイルのパスを調べる方法｜エクセル雑感(2026-01-27)
IMPORTCSV関数（CSVファイルのインポート）｜エクセル入門(2026-01-19)
IMPORTTEXT関数（テキストファイルのインポート）｜エクセル入門(2026-01-19)
料金表（マトリックス）から金額で商品を特定する｜エクセル練習問題(2026-01-14)
「緩衝材」としてのVBAとRPA｜その終焉とAIの台頭｜エクセル雑感(2026-01-13)
シンギュラリティ前夜：AIは機械語へ回帰するのか｜生成AI活用研究(2026-01-08)
電卓とプログラムと私｜エクセル雑感(2025-12-30)
VLOOKUP/XLOOKUPが異常なほど遅くなる危険なアンチパターン｜エクセル関数応用(2025-12-25)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBAサンプル集
- 数式の参照しているセルを取得する
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.