VBA dictionary モジュール間で連携

ブックモジュールのOpenイベントで特定シートの表をdictionaryに登録し、
その登録したdictionaryをシートオブジェクトのChangeイベントで、VLOOKUP関数のようにしようしたいです。

しかし、Openイベントで特定シートの表をdictionaryに登録するまでは問題なくできたのですが、
そのdictionaryをいざChangeイベントの方で使用しようと思うと、どうしてもdictionaryの変数定義がされていない状態になってしまいました。

以下にサンプルコードを記載致します。

VBA
1'Openイベントのコードでございます。
2Private Sub Workbook_Open()
3
4Dim NAMEdictionary As Object
5Set NAMEdictionary = CreateObject("Scripting.Dictionary")
6
7Dim foundCd As Range
8Set foundCd = Cells.Find(what:="コード", lookat:=xlWhole)
9
10Dim i As Long
11For i = foundCd.Row + 1 To Cells(Rows.Count, foundCd.column).End(xlUp).Row
12'    dictionaryにコードとそれに対応する名前を登録。
13    NAMEdictionary.Add Cells(i, foundCd.column).Value, Cells(i, foundCd.column + 1).Value
14Next i
15
16Debug.Print NAMEdictionary(6049)
17
18End Sub

VBA
1'Changeイベントのコードでございます。
2Private Sub Worksheet_Change(ByVal Target As Range)
3
4'下記の「NAMEdictionary」の部分でエラーが出てしまいます。
5Cells(Target.Row, Target.column + 1) = NAMEdictionary(Target.Value)
6
7End Sub

Excelだけを見ると、行いたいことはVLOOKUP関数を使った場合とほとんど同じなのですが、
VLOOKUP関数を使わない理由としては、関数ですと常にセットし置かねばならず、ファイルが重くなってしまうのと、実際どの行まで関数をセットし置くのかという問題があったからです。

最後に表の画像も掲載致しましたので、合わせて参考にして頂けますと幸いです。

tttkkk

2021/04/16 12:59

申し訳ありません。エラーメッセージの内容について記載し忘れておりました。 NAMEdictionary の部分でエラーメッセージが出てしまうのですが、そのエラーメッセージは Sub または Function が定義されていませんでございます。

行動規範の内容に同意します

回答1件

ベストアンサー

スコープを変えたらどうでしょうか。

ThisWorkbook
1
2Dim NAMEdictionary As Object
3
4'Openイベントのコードでございます。
5Private Sub Workbook_Open()
6
7  Set NAMEdictionary = CreateObject("Scripting.Dictionary")
8
9  '以下略
10

投稿2021/04/15 14:50

jinoji

総合スコア4585

tttkkk

2021/04/16 03:26

アドバイス頂けたように私も最初はしてみたのですが、やはり Sub または Function が定義されていません。というエラーメッセージが出てきてしまいまして、改善に至ることができませんでした。

jinoji

2021/04/16 03:52

あ、Worksheet_Changeはシートのコードですかね。 Public NAMEdictionary As Object にして、 ThisWorkbook.NAMEdictionary.Item(Target.Value) としたらどうでしょう。（いまのコードのままだと、Worksheet_Changeが連続着火して大火事になりそうですが）

tttkkk

2021/04/16 23:57

ありがとうございます！狙っていた通りのことができました！ですが、私が無知で申し訳ないのですが、なぜ ThisWorkbook.NAMEdictionary.Item(Target.Value) という表記が必要となるのでしょうか。単純に NAMEdictionary(Target.Value) だけの記述だとエラーになってしまう理由が分からず、教えて頂けないでしょうか。

jinoji

2021/04/17 04:21

Public NAMEdictionary As Object を、（ThisWorkbookのコードではなく）標準モジュールに書けば、 NAMEdictionary(Target.Value)の記述のままでも大丈夫です。 .Itemとつけたのは、最初はそこが原因かなと思ってつけてみたときの名残りで、実際はつけなくても大丈夫です。

tttkkk

2021/04/17 07:39

なるほど！本当にご丁寧にありがとうございます。ということは、ブックモジュールにpublicステートメントで変数を宣言し、その変数をブックモジュール以外で使う場合は、 VBAのルールとして ThisWorkbook の記述が必要になるということでしょうか（ブックモジュールで宣言した変数ですよって分かるように ThisWorkbook なんですかね？）。

行動規範の内容に同意します