配列の活用

 2.Dim文からみた配列の種類

変数を配列として扱う場合、その変数が配列であることをどこかで記述しなければいけません。

そしてその記述（Dim文での宣言など）によってそのあとの扱い方が大きく変わってきます。

(1) 配列として宣言しない  ---　Dim  AR1

　ただの変数、またはVariant変数としてDim文で宣言します。VBEで　Option Explicit（変数宣言の強制）

　が設定されていなければ、Dimの記述さえ必要ありません。（おすすめできませんが）

　1.の例のようにセルの一部をその変数に代入するか、別の配列を代入することで、その変数が配列化します。

　この配列の要素数は後から変更することはできません。

(2)配列であることだけ宣言する　---　Dim AR1()

　(1)のようにセルの一部をその変数に代入するか、別の配列を代入することで、そのインデックスの数が決まります。

　しかし、(1)も(2)も、何もせず、直接その配列の要素に何かを代入はできません。（AR1(1,1) = 8 などはできない）

　直接代入するためには、配列の要素数をあとから宣言する必要があります。

　それがRedim 文でRedim AR1(2,5) などの書き方をします。そうすればAR1(0～2,0～5）に数字や文字を代入できます。

　Redim文は何回でも異なる要素数で宣言できますが、再宣言することにより、それ以前に配列に代入されていた

　ものは消えてしまいます。消えないようにする方法は後程述べますが曲者です。

(3)配列とその要素数を宣言する　---　Dim AR1(2,3)

    配列の宣言としてはもっともまっとうな方法ですが、配列の要素数は後から変更はできず

　少し使いにくいです

 3.インデックスの範囲（取得と活用）

　　インデックスの最初の数は基本的には0です。Dim AR1(2,3)　 の場合　使える変数は

       AR1(0,0),AR1(0,1),AR1(0,2),AR1(0,3),AR1(1,0),AR1(1,1),AR1(1,2),AR1(1,3),AR1(2,0),AR1(2,1),AR1(2,2),AR1(2,3)

       の12になります。

　　ところが、(1)のようにセルの値を代入したときは初期値が1になります。

　　また2.-(2) のように要素数を決めずに配列宣言をした場合で、他の配列を代入した場合は、その『他の配列』

　　に左右されます。

　　またモジュールの宣言セクションに　Option Base 1 とか宣言すると、配列のインデックスの初期値は1になります。　

 とてもわかりにくいです。

そのためかVBAには配列インデックスの最小数と最大数を求める関数が用意されています。

su1 = LBound(AR1, 1)  　配列ar1 の（1次）インデックスの最小数を求める  

su3 =ULBound(AR1, 1)  　配列ar1 の（1次）インデックスの最大数を求める  

この関数を使用して配列の要素を過不足なく処理すると同時に、シートに貼り戻す時にResize関数を用いて

正確に張り付けることができます。

 Range("A2").Resize(su3, 2) = AR1　　

ここのケースではResize 命令のsu3には　50000が入っています。

Range（”A2”）はそこを起点として　縦方向に　49999　横方向に　1　足した範囲まで拡張されるのです。

Resize命令は　Resize(1,1) →　範囲は何も変わらない　

　　　　　　　Resize(3,2) → セルの行範囲を2広げる、セルの列範囲を1広げるという効果があります。

これらの関数を使って1.のTEST3Bを　行数が何行であっても対応できる式に変更したものが下記のプロシージャです。

Sub TEST3C()

Dim AR1

Dim su1,  su3,  Max1 As Long

Dim i, ruikei As Long

Dim starttime, endtime As Double

starttime = Timer

Max1 = Cells(Rows.Count, "A").End(xlUp).Row

AR1 = Range("A2:B" & Max1)      'セルの一部を配列に入れる

su1 = LBound(AR1, 1)     '配列インデックスの最小数を求める

su3 = UBound(AR1, 1)    '配列インデックスの最大数を求める

For i = su1 To su3      '配列の最小インデックスから最大インデックスまでループさせる

ruikei = ruikei + AR1(i, 1)

AR1(i, 2) = ruikei

Next

 Range("A2").Resize(su3, 2) = AR1 '配列をセルに書き戻す

endtime = Timer

Range("D1") = endtime - starttime

End Sub

 4.連想配列

　　配列の処理は早いですが、配列の表から特定の情報を取り出したり、書き込んだりするのはとても面倒です。

　　具体的には配列を対象にVlookup関数もMatch関数も使えないし、Sortも使えない、重複削除も使えない。

　　たとえば　1001という社員コードから社員名と部署を取り出してシートに書き込む場合、社員情報のあるシート

　　に対してMatch関数で社員コードからその社員情報が何行目に書かれているか取得することができますが

　　配列に入っているときは　1行ずつ　社員コードは1001か？と社員の数だけ繰り返してチェックしなければいけない。

　　どの社員コードが何行目にあるか探す関数がないからです。

　　これを回避するために連想配列を使います。

　　連想配列の機能は配列を参照したり、配列に集計したりするための外付けのツールです。

　　これがまたデバックしにくいです。VlookupはExcel2016以降だいぶ高速にないましたし、集計するためには

　　ピポッドやSumifs関数もあるのであまりお勧めはできないです。

　(1)連想配列使用の準備（①②どちらでも構いません）

　　①VBEのツール⇒参照設定から　Microsoft　Scripting Runtime にチェックをいれる

　　　Dim 文で連想配列項目の使用を宣言する　

　　　Dim Dic_1　As New Scipting Dictionary

 　　②Dim文とSet文で連想配列の宣言をする

             Dim dic_1 As Object

 　　　Set dic_1 = CreateObject("scripting.dictionary")　　

連想配列はないかあるかわからないものを参照してはいけないのです。ではどうするかというと、赤い部分を

下記のように書き換えます。vlookupなどの関数と違って、参照して登録があるかどうか事前にわかります。

『参照したらある』と判っているものしか参照してはいけないのです。

Public Sub test01()

Dim i ,j As Long

Dim dic_1 As Object

Set dic_1 = CreateObject("scripting.dictionary")

For i = 1 To 4

       dic_1.Add Trim(Sheet4.Cells(i, 1).Value), Sheet4.Cells(i, 2)    'B列を連想配列の　itemにセットする

Next

 If dic_1.Exists("山田") = False Then

 j = 0

 Else

  j = dic_1("山田")　　'連想配列のキー"山田" でそのitem (データのB列目を取得して表示）

 MsgBox j

 End If

 Range("D1:D5") = Application.WorksheetFunction.Transpose(dic_1.Keys)

Range("E1:E5") = Application.WorksheetFunction.Transpose(dic_1.Items)

 End Sub

(4)デバック中の連想配列の出力

連想配列の中身はとても参照しにくいです（dc_1(1,1)またはdic_1.keys(1) のような参照ができない）

思うように動かないときにはマクロの中で連想配列を for eachを使用して全出力するなどします。

デバックに困ったら下記文をどこかに挿入して、一度連想配列の中身を出力します。

Dim AR

Dim S As Long

S = 1

For Each AR In dic_1

        Cells(S, 9).Value = AR

        Cells(S, 10).Value = dic_1.Item(AR)

        S = S + 1

     Next 

あるいは　下記のように出力します

Range("G2:J2") = dic_1.keys

Range("G3:J3") = dic_1.items

横方向でなく、縦方向に出力するならば

Range("G2:G5") = Application.WorksheetFunction.Transpose(dic_1.keys)

Range("H2:H5") = Application.WorksheetFunction.Transpose(dic_1.items)

(6)動的配列（Redimしても中身が消えない方法）

Redim するときに Preserve を挟むと、中身が消えずに配列の範囲を広げることができます。

ただし、拡張できるのは複数の次元を使用している場合、その最終次元だけです。

ReDim Preserve AR1(3, 7)　　←　3　の部分は変更できない

配列をシートに書き戻す時には（）の中は行、列になりますので、列方向にしか拡張できないことになります。

データは行方向に拡張される事が多いので、これは不便です。

動的配列を活用したいときは、わざと（列、行）にデータを配列に入れて、シートに書き込む直前で

行列変換をします。

Range("F2").Resize(7, 3) = Application.WorksheetFunction.Transpose(AR1)

ただしTransposeで変換できる数は限界があり65,536です。何かと制約が多いです。