ExcelマクロVBAサンプル集 | 数式の参照しているセルを取得する | Excelマクロの実用サンプル、エクセルVBA集と解説



最終更新日:2017-03-18

数式の参照しているセルを取得する

セルに入っている数式の参照しているセルを取得するには、

RangeのPrecedentsプロパティを使いますが、このプロパティは他のシートの参照には対応していません。

また、セルの参照先を取得するプロパティには、Dependentsプロパティがあります。


Precedents
セルが直接または間接に参照している参照元を表すRangeオブジェクトを返します
A1セルに=B1と入っている場合に、A1セルのPrecedentsでB1セルを取得できます。

Dependents
セルを直接または間接に参照している参照先Rangeオブジェクトを返します
A1セルに=B1と入っている場合に、B1セルのDependentsでA1セルを取得できます。


PrecedentsDependentsも、どちらも他のシートの参照には対応していません。
そこで、以下のサンプルコードでは、
指定のセルの数式が参照している(参照元)セルを配列で返します。

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
このように表示されます。

PrecedentsDependentsと組み合わせて使用する場合は、
For Each var In aryRange
この中で、
var.ParentとRange("A1").Parentが同じであれば同じシート内なので、
PrecedentsDependentsで取得済と判定すれば良いでしょう。

滅多に必要となることもないと思いますが、
いざ必要となった時に、VBAを最初から書くのは大変なので、そのような場合に思い出してください。




同じテーマ「ExcelマクロVBAサンプル集」の記事

シートの追加・削除(Add,Delete)
シートの複数選択(Select)
複数シートの印刷(PrintOut)
重複削除してコピー(AdvancedFilter)
ブックを安全確実に開く方法(Open)
ブックを閉じる(Close)
他ブックを開いて閉じる(Open,Close)

新着記事 ・・・新着記事一覧を見る

SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
4.変数とデータ型(Dim)|ExcelマクロVBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
6.定数と型宣言文字(Const)|ExcelマクロVBA入門
7.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
8.マクロって何?VBAって何?|ExcelマクロVBA入門
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • 数式の参照しているセルを取得する

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。

    ↑ PAGE TOP