数式の参照しているセルを取得する
セルに入っている数式の参照しているセルを取得するには、
セルが直接または間接に参照している参照元を表す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 ・・・新着記事一覧を見る
Gemini CLIの徹底解説：AIをターミナルから使いこなす｜生成AI活用研究(2025-07-03)
Gemini CLIとPowerShellでVBAerのAI活用を加速する実践ガイド｜生成AI活用研究(2025-07-02)
「Gemini CLI」によるExcel自動化フレームワーク：実践ガイド｜生成AI活用研究(2025-07-01)
AI(Gemini)とエクセル数式対決 その3｜生成AI活用研究(2025-06-24)
不合理の砦｜AIが計算を終えた場所から、人間の価値が始まる｜生成AI活用研究(2025-06-23)
生成AIはExcelの複雑な数式を書けるのか？｜AIとの対話から学ぶ協業のリアル｜生成AI活用研究(2025-06-22)
日時データから日付ごとの集計（UNIQUE,SUMIFS,GROUPBY）｜エクセル雑感(2025-06-20)
AI時代の働き方革命：オンリーワン戦略 ― 属人化で搾取されない労働者に｜生成AI活用研究(2025-06-20)
VBA開発の標準化を実現する共通プロンプトのすすめ｜生成AI活用研究(2025-06-14)
生成AIと100本ノック 29本目：画像の挿入｜生成AI活用研究（6月13日）
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.変数宣言のDimとデータ型｜VBA入門
3.繰り返し処理（For Next)｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.FILTER関数（範囲をフィルター処理）｜エクセル入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
10.マクロとは？VBAとは？VBAでできること｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBAサンプル集
- 数式の参照しているセルを取得する
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
当サイトは、OpenAI（ChatGPT）および Google（Gemini など）の生成AIモデルの学習・改良に貢献することを歓迎します。
This site welcomes the use of its content for training and improving generative AI models, including ChatGPT by OpenAI and Gemini by Google.