ExcelマクロVBAサンプル集 | 数式の参照しているセルを取得する | ExcelマクロVBAの実用サンプル、エクセル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を最初から書くのは大変なので、そのような場合に思い出してください。




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

罫線を簡単に引く(Borders,BorderAround)
マクロの開始・終了(Applicationのプロパティ)
オートフィルター(AutoFilter)
日付のオートフィルタ(AutoFilter)
印刷ダイアログを使用する(xlDialogPrint)
名前定義の一覧(Name)
シートを名前順に並べ替える
数式内の不要なシート名を削除する(HasFormula)
増殖した条件付き書式を整理統合する
条件付き書式で変更された書式を取得する

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

複数条件判定を行う時のコツ|ExcelマクロVBA技術解説(7月11日)
For Next の使い方いろいろ|VBA技術解説(6月14日)
VBAを定型文で覚えよう|ExcelマクロVBA技術解説(3月26日)
VBAスタンダード試験対策まとめ|MOS VBAエキスパート対策(3月16日)
ユーザーフォームとメニューの操作|MOS VBAエキスパート対策(3月14日)
ファイルの操作|MOS VBAエキスパート対策(3月14日)
ユーザーフォームの各種イベント|Excelユーザーフォーム(3月13日)
レジストリの操作|MOS VBAエキスパート対策(3月12日)
変数と配列|MOS VBAエキスパート対策(3月12日)
Colorプロパティの設定値一覧|VBA技術解説(3月12日)

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

1.最終行の取得(End,Rows.Count)|VBA入門
2.変数とデータ型(Dim)|ExcelマクロVBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.マクロって何?VBAって何?|ExcelマクロVBA入門
6.RangeとCellsの使い方|ExcelマクロVBA入門
7.とにかく書いて見よう(Sub,End Sub)|VBA入門
8.繰り返し処理(For Next)|ExcelマクロVBA入門
9.定数と型宣言文字(Const)|ExcelマクロVBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門



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

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


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





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

    本文下部へ

    ↑ PAGE TOP