VBAの配列について

質問

初めまして、VBAの配列の入力方法について質問させてください。
大量のデータの処理を高速化するため、配列を使用して以下のVBAを入力しました。
インターネットで調べ、見よう見まねで入力してみたものです…(T_T)

内容は、シート「資料」のC列とシート「Sheet1」のG列の文字列が同じ
かつ、シート「資料」のL列から最終列（そのときによって変化します）
とシート「Sheet1」のE列の文字列が同じ場合、
シート「資料」のA列～D列及びL列から最終列で文字列の一致したセルを
着色するというものです。

変数「アイス」と「チョコ」にそれぞれシート「資料」のデータと
シート「Sheet1」のデータを格納したつもりなのですが、
実行したところ「配列がありません。」というエラーメッセージが
表示されました。
どうやらデータを配列として格納できていないときに表示される
エラーメッセージのようなのですが、変数の型を変更してみたり、
配列をアイス(2)にしてみたりと、色々方法を変えて試してみたものの、
処理は成功しませんでした(T_T)

一体何が原因で処理が成功しないのか、どなたかご教授いただけると
とても嬉しいです…！よろしくお願いいたします。
ちなみに、配列を使用しない場合の処理は、時間が１５分ほどと
かなりかかりますが、成功しています。

Application.ScreenUpdating = True

Dim アイス, チョコ As Long
Dim i As Integer, j As Integer, k As Integer

アイス = Sheets("資料").Cells(Rows.Count, 1).End(xlUp).Row
チョコ = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 3 To Sheets("資料").Cells(Rows.Count, 1).End(xlUp).Row
For j = 12 To Sheets("資料").Cells(i, 12).End(xlToRight).Column

For k = 2 To Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

If アイス(i, 3).Value = チョコ(k, 7).Value And アイス(i, j).Value = チョコ(k, 5).Value Then
Sheets("資料").Range("A" & i & ":D" & i).Interior.ColorIndex = 22
アイス(i, j).Interior.ColorIndex = 22

End If
Next k
Next j
Next i

回答No.6

real beatin（@realbeatin） · ベストアンサー

（回答No.5のつづきです。）

' ' /// 配列を変数に格納して処理する参考例
Sub Re9376751Wm()
Dim aryKey1　'　資料!Ｃ３以下【二次元配列】
Dim arySearchArea1　'　Sheet1!Ｇ２以下【二次元配列】
Dim aryKey2　'　Sheet1!Ｅ２以下【二次元配列】
Dim rMarkUpAreaAtoD As Range　'　資料!ＡＤ列【Range】
Dim rSerach2MarkUpAreaLto As Range　'　資料!Ｌ３:最終【Range】
Dim c As Range　'　資料!Ｌ:最終列　ループ用
Dim sKey1 As String, sKey2 As String　'　検索キー
Dim nLastRow As Long, nLastRow1 As Long, nLastCol As Long　'　最終行、最終列
Dim i As Long, j As Long
Dim blnPainted As Boolean　'　資料!ＡＤ列　塗潰し済フラグ

　　With Sheets("Sheet1")
　　　　nLastRow1 = .Cells(Rows.Count, "G").End(xlUp).Row　'　最終行、取得
　　　　arySearchArea1 = .Range("G2:G" & nLastRow1).Value　'　Sheet1!Ｇ２以下【二次元配列】変数に格納
　　　　aryKey2 = .Range("E2:E" & nLastRow1).Value　'　Sheet1!Ｅ２以下【二次元配列】変数に格納
　　End With

　　With Sheets("資料")
　　　　nLastRow = .Cells(Rows.Count, "C").End(xlUp).Row　'　最終行、取得
　　　　aryKey1 = .Range("C3:C" & nLastRow).Value　'　資料!Ｃ３以下【二次元配列】変数に格納
　　　　Set rMarkUpAreaAtoD = .Range("A3:D" & nLastRow)　'　資料!ＡＤ列【Range】変数にSet
　　　　With .Cells(3, "L").CurrentRegion　'　資料!Ｌ列より右に空の列が挟まれている場合は別の方法で
　　　　　　nLastCol = .Cells(.Cells.Count).Column　'　最終列　取得
　　　　End With
　　　　Set rSerach2MarkUpAreaLto = .Cells(3, "L").Resize(nLastRow - 2, nLastCol - 11)　'　資料!Ｌ３:最終【Range】】変数にSet
　　End With

　　Application.ScreenUpdating = False
　　For i = 1 To nLastRow - 2　'　資料!Ｃ３以下【二次元配列】をループ
　　　　sKey1 = aryKey1(i, 1)　'　資料!Ｃ列　検索キー1を文字列変数に格納
　　　　For j = 1 To nLastRow1 - 1　'　Sheet1!Ｇ２以下【二次元配列】Ｅ２以下【二次元配列】をループ
　　　　　　If arySearchArea1(j, 1) = sKey1 Then　'　Sheet1!Ｇ２以下【二次元配列】の要素がマッチしたら
　　　　　　　　sKey2 = aryKey2(j, 1)　'　Sheet1!Ｅ列　検索キー2を文字列変数に格納
　　　　　　　　blnPainted = False　'　資料!ＡＤ列　塗潰し済フラグを初期化
　　　　　　　　For Each c In rSerach2MarkUpAreaLto.Rows(i).Cells　'　当該行資料!Ｌ列より右のセルを総当たり
　　　　　　　　' ' 資料!Ｌ列より右 空セルより右にデータは無い前提であれば この下の行 イキ
'　　　　　　　　　　If c = "" Then Exit For
　　　　　　　　　　If c = sKey2 Then　'　資料!Ｌ列より右のセルの値がマッチしたら
　　　　　　　　　　　　c.Interior.ColorIndex = 22　'　セル塗潰し
　　　　　　　　　　　　If blnPainted = False Then　'　当該行で資料!ＡＤ列をまだ塗潰していなければ
　　　　　　　　　　　　　　rMarkUpAreaAtoD.Rows(i).Interior.ColorIndex = 22　'　資料!ＡＤ列当該行塗潰し
　　　　　　　　　　　　　　blnPainted = True　'　資料!ＡＤ列　塗潰し済フラグ
　　　　　　　　　　　　End If
　　　　　　　　　　' ' 資料!Ｌ列より右 各行 重複無し という前提なら この下の行 イキ
'　　　　　　　　　　　　Exit For
　　　　　　　　　　End If
　　　　　　　　Next
　　　　　　' ' Sheet1!Ｇ 重複無し という前提なら この下の行 イキ
'　　　　　　　　Exit For
　　　　　　End If
　　　　Next j
　　Next i
　　Application.ScreenUpdating = True

End Sub
' ' ///

お礼

realbeatinさん何度もご回答いただき、ありがとうございます！確かに、何度か別のデータでテストしてみて、これでは最終行と列のみを指定しているにすぎないことが分かりました…(゜o゜)配列のＶＢＡもご提供いただき、ありがとうございます！
ご提供いただいたＶＢＡ，まだ大まかにしか理解できていないのですが、理解できるまで何度か読ませていただこうと思います！
お二人の配列のＶＢＡを拝見して、少しずつではありますが、配列の入力方法について理解を深めることができました。本当にありがとうございます！

回答No.5

real beatin（@realbeatin）

こんにちは。

> 変数「アイス」と「チョコ」にそれぞれシート「資料」のデータと
> シート「Sheet1」のデータを格納したつもりなのですが、
、、、。
> アイス = Sheets("資料").Cells(Rows.Count, 1).End(xlUp).Row
> チョコ = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
と、この２行のことでしたら、どちらも、
　データを持つセル、の、最下の、行位置を指す数値（ひとつの数値）、
　= 最終行位置、を、それぞれの変数に格納しているだけなので、
セル範囲の値を配列として変数に格納する処理がなされていません。
変数「アイス」と「チョコ」には配列ではなくひとつの数値が格納されていますし、
配列を格納する為の変数、配列を格納する処理、
といった記述が見当たりません。
> 一体何が原因で処理が成功しないのか
この疑問に端的に応えるなら、配列を変数に格納することが理解できていない
ことが原因、ということになります。
"配列を使って"までは中級程度、"高速化"ともなると上級レベル、
知識、技術力、経験知などの総合力が高度に求められます。
質問者さんの課題としては、先を急ぎ過ぎているような感じもしますね。
目標を持ってトライする、ということでしたら手助けはしたいです。

> 内容は、シート「資料」のC列とシート「Sheet1」のG列の文字列が同じ
> かつ、シート「資料」のL列から最終列（そのときによって変化します）
> とシート「Sheet1」のE列の文字列が同じ場合、
> シート「資料」のA列～D列及びL列から最終列で文字列の一致したセルを
> 着色するというものです。
表現し尽くされてないようですし、こちらの理解も到っていないかも、ですが、
サンプルデータを作って、実際にマクロを１０通り書くうちに、
要求仕様については概ね理解できたつもりです。
例えば、
｜資料!Ｃ３以下のセルにある文字列を総当たりで、
｜　Sheet1!Ｇ２以下のセルを検索する。
｜　　マッチした場合、
｜　　　マッチした行のSheet1!Ｅ列にある文字列を
｜　　　資料!Ｌ:最終列＊最初の検索キーがある行にマッチするセルを検索する。
｜　　　　マッチした場合、
｜　　　　　資料!Ｌ:最終列のマッチしたセルと、
｜　　　　　マッチした行＊資料!ＡＤ列を、塗潰す。
といった具合に処理内容を言葉にして書いてみると、
　For i = 3 To Sheets("資料").Cells(Rows.Count, 1).End(xlUp).Row
　　For k = 2 To Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
　　　If アイス(i, 3).Value = チョコ(k, 7).Value Then
　　　　For j = 12 To Sheets("資料").Cells(i, 12).End(xlToRight).Column
　　　　　If アイス(i, j).Value = チョコ(k, 5).Value Then
　　　　　　' ここで処理
（Forループをネスト（入れ子に）する順番を変え、条件分岐を２つに分ける）
という風に、無駄なループをしないように分岐できることが
少しは解り易くなるのではないでしょうか。
> 大量のデータの処理を高速化するため
に最も基本的で重要なことは、無駄な処理を減らすことです。
その為には処理内容を（数通りは）言葉で表現し直す練習も大切です。
他に、
　Sheet1!Ｇ２以下のセルには 重複が ある/ない
　資料!Ｌ:最終列 各行のセルには 重複が ある/ない
　マッチングに関わるすべてのセル範囲について
　　空セルを挟まない一連らなりのデータで ある/ない
といった前提条件についても、一通り確認しておけば、
ループ処理を必要な分だけに短縮することも出来ます。
例えば、重複がない前提だとしたら、
ループを開始して１件めでマッチしたなら、
その後もループを続けるのは無駄ですよね。

気が付いた点を指摘しておきます。
> Dim アイス, チョコ As Long
どちらもLong型で宣言したい場合の話として、
　Dim アイス As Long, チョコ As Long
のように個別に型宣言を書く必要があります。
× Dim (アイス, チョコ) As Long
みたいなイメージで両方纏めて型宣言できるような勘違いを
している人、結構多いです。書く時も読む時も、ご注意を。
> Dim i As Integer, j As Integer, k As Integer
基本、現代のVBAで、ループ用の数値を宣言する時は、
Long型を使います。Integerには余計なオーバーヘッド（負荷）がかかる
ということと、Excelの行すべてをInteger型では扱い切れない、
ということと、２つの理由からです。
> For j = 12 To Sheets("資料").Cells(i, 12).End(xlToRight).Column
これでもうまくいく場合もありますが、
もし、Ｍ列から右にひとつもデータがないようなケースにあたると、
Cells(i, 12).End(xlToRight)は、ＸＦＤ列になりますから、
16372回、無駄なループをする羽目に合う事もあります。
　For j = 12 To Sheets("資料").Cells(i, Columns.Count).End(xlToLeft).Column
みたいに。
... .Cells(Rows.Count, 1).End(xlUp).Row 同様、
反対側から終端を探すことを基本にしておいた方が安心ですね。
Ｌ列から右には、どの行でも、最低２つ以上のデータがある、
というような特殊な保証があるのなら、xlToRightでも十分ですが、
こういう前提があったなら、きちんと言語化しておくようにした方がいいです。

参考までに、配列を変数に格納して処理する例を、ひとつのマクロとして
次の投稿であげておきます。
中身はまだ解らないこともあるでしょうけれど、
書き添えたコメントに照らしながら眺めて貰えれば、
どのような処理の流れになっているのか、とか、
前提条件として掌握しておくべきことの幾つか、について、
気が付く事もあるのではないかなぁ、と。

それではまた。

（次の投稿に続きます。）

回答No.4

SI299792

すみません。ミスがありました。
最初、Ｌ列までデータがあると思い込んでいました。
しかし、もっと後ろまであるのですね。他にも動作ミスが確認されました。
デバックしてからでないといけませんね。前の投稿は無視してください。
工夫次第で配列の使用量を減らせるのですが、プログラムが複雑になるのでやめました。

アイス・チョコは配列変数であり、セルではないので、
アイス(i, 3).Value
アイス(i, j).Interior.ColorIndex
といったことはできません。
'
Option Explicit
'
Sub Macro1()
'
　　Dim アイス As Variant
　　Dim チョコ As Variant
　　Dim i As Integer
　　Dim j As Integer
　　Dim k As Integer
　　Dim l As Long
'
　　l = Sheets("資料").[A1].SpecialCells(xlLastCell).Row
　　i = Sheets("資料").[A1].SpecialCells(xlLastCell).Column
　　アイス = Sheets("資料").[A1].Resize(l, i)
'
　　l = Sheets("Sheet1").[A1].SpecialCells(xlLastCell).Row
　　チョコ = Sheets("Sheet1").Range("A1:L" & l)
'
　　For i = 3 To Sheets("資料").Cells(Rows.Count, 1).End(xlUp).Row
　　　　For j = 12 To Sheets("資料").Cells(i, 12).End(xlToRight).Column
　　　　　　For k = 2 To Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
　　　　　　　　If アイス(i, 3) = チョコ(k, 7) And アイス(i, j) = チョコ(k, 5) Then
　　　　　　　　　　Sheets("資料").Range("A" & i & ":D" & i).Interior.ColorIndex = 22
　　　　　　　　　　Sheets("資料").Cells(i, j).Interior.ColorIndex = 22
　　　　　　　　End If
　　　　　　Next k
　　　　Next j
　　Next i
'
End Sub

お礼

ご回答いただき、ありがとうございます！[A1]はRangeA1のことを表すのですね！Resizeの使い方も、とても勉強になりました。ちなみにアイスとチョコはおいしそうなので入力しただけで、特に意味はありません…！(^o^)

回答No.3

SI299792

どうも、プログラムを見間違えていました。
間違いを正すために、急いで投稿します。プログラムを載せます。
但し、未テストなので、保証はできません。
'
Sub Macro1()
'
　　Dim アイス As Variant
　　Dim チョコ As Variant
　　Dim i As Integer
　　Dim j As Integer
　　Dim k As Integer
　　Dim l As Long
'
　　l = Sheets("資料").Cells(Rows.Count, 1).End(xlUp).Row
　　アイス = Sheets("資料").Range("A1:L" & l)
'
　　l = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
　　チョコ = Sheets("Sheet1").Range("A1:L" & l)
'
　　For i = 3 To Sheets("資料").Cells(Rows.Count, 1).End(xlUp).Row
　　　　For j = 12 To Sheets("資料").Cells(i, 12).End(xlToRight).Column
　　　　　　For k = 2 To Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
　　　　　　　　If アイス(i, 3) = チョコ(k, 7) And アイス(i, j) = チョコ(k, 5) Then
　　　　　　　　　　Sheets("資料").Range("A" & i & ":D" & i).Interior.ColorIndex = 22
　　　　　　　　　　Sheets("資料").Range(i, j).Interior.ColorIndex = 22
　　　　　　　　End If
　　　　　　Next k
　　　　Next j
　　Next i
'
End Sub

回答No.2

SI299792

Dim アイス, チョコ As Long
Dim i As Integer, j As Integer, k As Integer
この書き方はやめた方が良いです。asを付け忘れ、バグの元です。
長くなりますが、１つ１つDim を付けた方が良いです。

アイス = Sheets("資料").Cells(Rows.Count, 1).End(xlUp).Row
チョコ = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
これでは、アイス・チョコは最終行位置しか入らず、データは入りません。
'
　　Dim アイス As Variant
　　Dim チョコ As Variant
　　Dim i As Integer
　　Dim j As Integer
　　Dim k As Integer
　　Dim l As Long
'
　　l = Sheets("資料").Cells(Rows.Count, 1).End(xlUp).Row
　　アイス = Sheets("資料").Range("A1:L" & l)
　　l = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
　　チョコ = Sheets("Sheet1").Range("A1:L" & l)

配列にデータを入れるならこうです。
但し、これだけでは動きません。中身も間違っているので直す必要があります。
このプログラムには矛盾があります。
For j = 12 To Sheets("資料").Cells(i, 12).End(xlToRight).Column
だと、L 列から、L 列まで、つまり、L 列しか見ていません。正しいプログラムとは思えません。
配列を使わないプログラムが動いているなら、そちらを載せてください。

変数をアイス・チョコとしているのは、意味があるのですが？　判り難いです。

回答No.1

nan93850673

>どうやらデータを配列として格納できていないときに表示される

ではなく、配列ではない変数を配列の様に扱おうとしてエラーになっています

>Dim アイス, チョコ As Long

アイスは、Variant型の非配列変数、チョコはLong型の非配列変数で宣言してますよ。

お礼

以下の回答で気づいたのですが、「アイス」は何も宣言していないため、Variant型で宣言していることになっているのですね…(゜Д゜;)ご回答いただき、ありがとうございました！