数式の参照しているセルを取得する
セルに入っている数式の参照しているセルを取得するには、
セルが直接または間接に参照している参照元を表す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サンプル集」の記事
増殖した条件付き書式を整理統合する
新着記事NEW ・・・新着記事一覧を見る
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
累計を求める数式あれこれ｜エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE｜エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。｜エクセル雑感(2023-12-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBAサンプル集
- 数式の参照しているセルを取得する
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。