Worksheet_Changeイベント時の別シートのデータを配列に格納する方法

Question

前提

VBAのWorksheet_Changeイベントにて所定のセルの値が変更した場合に、
別シートのデータを二次元配列に格納したいと思っております。

実現したいこと

• Worksheet_Changeイベント時に別シート(Sheet名：「コードマスタ」)のデータを二次元配列に格納する

※Worksheet_Changeイベントが発生しているSheetのSheet名は「入力」です。

発生している問題・エラーメッセージ

下記のコードを削除することでエラーメッセージが表示されます。
なお、エラー内容は｢実行エラー13　型が一致しません｣です。
 'On Error Resume Next

該当のソースコード

Private Sub Worksheet_Change(ByVal Target As Range)
      On Error Resume Next
    'セルの変更箇所が8行目以上で9列以外の場合にはプログラム終了
    If Not (Target.Column = 9 And Target.Row >= 8 ) Then
       End
    'セルの変更箇所が9列目で空欄の場合
    ElseIf  Target.Value <> "" And Target.Column = 9 Then
       Dim MasterDatas() As Variant
       '配列に格納
       MasterDatas = Worksheets("コードマスタ").Range("A1").CurrentRegion

　   End If
　　※以下略
End Sub


### 試したこと
　　１，　下記の通り別シートの選択を行った後に、配列を格納しようとしました。　
　　　結果としては、「入力」Sheetのデータが格納されてしまいました。

　　    Worksheets("コードマスタ").Select
        MasterDatas = Range("A1").CurrentRegion

　　２，　下記のコードを削除することでエラーメッセージが表示されます。
　　　　　エラー内容は｢実行エラー13　型が一致しません｣です。
　　　　 'On Error Resume Next

Comments

[hatena19]

MasterDatas の変数宣言部分のコードを提示ください。

[tamu102]

ご指摘ありがとうございます。
変数の宣言については下記になります。
※その他は特に変数の宣言ありません。
Dim MasterDatas() As Variant

Answer 1

CurrentRegion はRangeオブジェクトなので、配列に代入しようとすると型不一致エラーになります。
CurrentRegion.Value とすれば配列になります。

        Dim MasterDatas() As Variant
        '配列に格納
        MasterDatas = Worksheets("コードマスタ").Range("A1").CurrentRegion.Value

---

以下、質問とは外れますが、参考までに。

Endステートメントで強制終了してますが、これは避けた方がいいでしょう。

End ステートメント (VBA) | Microsoft Learn
End ステートメントを実行すると、すべての モジュール内のすべての モジュールレベル変数およびすべてのスタティック ローカル変数がリセットされます。

上記のリンク先の「注意」の解説も読んでください。いろいろ影響が大きいのでむやみに使うものではありません。

ここは、Exit Sub で単にプロシージャを抜ければいいです。

あと、If文の条件式とその前のコメントが矛盾しているのが気になりますが、大丈夫ですか。

Comments

[tamu102]

ご指摘いただきましてありがとうございます。
早速Endステートメントはやめにいたしました。
また、コメントについも修正させていただきました。

[tamu102]

今ほどCurrentRegion.Valueに変更しましたところ正常に起動できました。
大変助かりました！！
ご親切なご回答ありがとうございました。

Answer 2

On Error Resume Next

これを外すとエラーが出ますね。
型が合わないようなので修正が必要そうです。

Comments

[tamu102]

情報ありがとうございます。

[tamu102]

こちらのエラーの原因を探ることで解決させることができました。