VBA changeイベント 別ブックと連携

Question

いつもお世話になっております。
changeイベントを勉強し始めたばかりで拙い質問とかもしれませんが、よろしくお願いいたします。

changeイベントを使うことで、vlookupでできるような、あるIDを打ち込まれたらそのIDを参照して名前などの値を出力することを考えています。
changeイベントですと別ブックを参照する際に、効率的な方法はございますでしょうか。

データリストはchangeイベントが発火するブックには無く、別ブックにまとめられて管理されています。
changeイベントが発火するブックと同一のブックにデータリストがあるという状況でしたら、今の私であってもvlookupのように入力したIDに従ったデータを引っ張ることができます。
しかしデータリストが別ブックにあるとなりますと、changeイベントが発火するたびに別ブックのデータリストを開いて、閉じてというのを繰り返す必要が出てきてしまい、動作が重くなってしまうということが起きました。

changeイベントを使い、vlookupを使わない理由は、関数ですと常にセットし置かねばならず、ファイルが重くなってしまうのと、実際どの行まで関数をセットし置くのかという問題があったからです。

以下に私が書いたサンプルコードを載せさて頂きます。
このサンプルコードは別ブックのデータリストを開いて、閉じてというコードでは無く、同一ブックにデータリストがあるとした時のコードとなりますが、
私の行いたいことが伝わりましたら幸いです。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim foundId As Range
Dim foundName As Range
Dim foundAddress As Range
'sheet17がデータリストになります。
Set foundId = Sheet17.Cells.Find(what:="id", lookat:=xlWhole)
Set foundName = Sheet17.Cells.Find(what:="name", lookat:=xlWhole)
Set foundAddress = Sheet17.Cells.Find(what:="address", lookat:=xlWhole)

'targetがA列でさらにデータ型が数値だった時
If (VarType(Target.Value) = 5 And Target.column = 1) Then
    Dim foundInputId As Range
    Set foundInputId = Sheet17.Cells.Find(what:=Target.Value, lookat:=xlWhole)
    Target.Offset(, 1).Value = Sheet17.Cells(foundInputId.Row, foundName.column).Value
    Target.Offset(, 1).Value = Sheet17.Cells(foundInputId.Row, foundAddress.column).Value
End If

End Sub

上記のコードではsheet17がデータリストですが、このsheet17のデータリストが別ブックにある場合にどうしたらよいかということになります。

分かりにくい点がございましたら申し訳ありませんが、
宜しくお願い致します。

Answer 1

外部参照式を使うというのもありかな。

Private Sub Worksheet_Change(ByVal Target As Range)
    Const datalist = ",'C:\test[datalist.xlsx]Sheet1'!$A:$C"

    'targetがA列でさらにデータ型が数値だった時
    If (VarType(Target.Value) = 5 And Target.Column = 1) Then
        
        With Target.Offset(, 1)
            .Formula = "=VLOOKUP(" & Target.Address & datalist & ",2,FALSE)"
            .Value = .Value
        End With
        With Target.Offset(, 2)
            .Formula = "=VLOOKUP(" & Target.Address & datalist & ",3,FALSE)"
            .Value = .Value
        End With
    End If

End Sub

---

項目数が多いときはループにするといいでしょう。

Private Sub Worksheet_Change(ByVal Target As Range)
'参照先 A列からZ列まで（26列）
    Const datalist = ",'C:\test[tera330470_list.xlsx]Sheet1'!$A:$Z"
    Const colCnt = 26
    'targetがA列でさらにデータ型が数値だった時
    If (VarType(Target.Value) = 5 And Target.Column = 1) Then
        Dim i As Long
        For i = 2 To colCnt
            With Target.Offset(, i - 1)
                .Formula = "=VLOOKUP(" & Target.Address & datalist & "," & i & ",FALSE)"
                .Value = .Value
            End With
        Next
    End If
End Sub

Comments

[tttkkk]

私もchangeイベントで関数を入れるというのは思い付いた部分ではありますが、そうなると項目が増えるに従ってコードの行数が増えてしまうことが悩みでした…。
でもやはりこれが一番簡単なやり方になりますよね。
ありがとうございます。

[hatena19]

コードをよくみてください。関数をセルに入れているわけではないですよ。
関数で外部データを取得はしてますが、取得した後、.Value = .Value の部分で値に変換してます。ですのでシートが重くなることはありません。
また、外部参照式はブックを開くわけではないので、動作も軽いです。

項目数が多い場合は、ループを使えばコードが増えることはないですね。回答に追記しておきます。

[xail2222]

そうですね。コード量的にDictionaryでやる方が多くなると思います。
テストしてみましたが外部参照って重い印象があったのですが１セルだけなら
それほど遅くないんですね。知りませんでした。

[hatena19]

Worksheet_Change時の速度はDictionaryの方が高速だと思います。
ただ、Workbook_Open時に別ブックを開くことになりますので、開くのがちょっと遅くなりますね。
どちらにしも体感できる差はでないかと思います。

Answer 2

私ならどうするかで回答します。

データリストは別のExcelファイルで管理したい。これが前提条件だとすると
何度も開くのは確かに大変なので、一度だけ開いてDictionaryに格納して
その後はDictionaryから値を参照する。という手法をとるかと思います。

Dictionaryのキーはidで値がArray([name],[address])という感じですね。

初めに格納する方法としては、Dictionaryを標準モジュールで定義して
changeイベントが発火するブックのWorkbook_Openイベントで
データリストを（表示せずの方がいいかも？）オープンして
Dictionaryにデータをセットしてって感じでしょうか。

Comments

[tttkkk]

ありがとうございます！
dictinaryという方法は初めて知りました。
どの程度私のやりたいことに使えるか調べてみたいと思います！

[tttkkk]

私のやりたいことをdictinaryを使うことで実現できそうでしたので、ベストアンサーにさせていただきます！