ExcelVBAの配列を使って任意の文字列を作り出す箱を用意する
 |
 |
 |
では配列を使ったコードを見ていきましょう。前回からの続きでExcelVBAの配列を使ったコードを用意して作業をしていきます。
今回配列を使います。配列を覚えると処理時間が劇的に速くなり大幅にやれる事が増えます。ぜひご覧ください。
前回の記事
前回は主に配列の考え方と今回実行するコードの構想について書いています。続けて見ていただくと分かりやすいと思います。
コード：構成
早速コードを紹介します。まずはコードの構成です。
コード：構成の1、2
続けて前半3ブロックまでのコードです。今回は配列を使うので画面遷移が無いです。ScreenUpdationgの効果は得られない為書かなくても良いのですがこれを説明するとまた話がそれるのでスルーしてください。というわけで今回は書いても書かなくてもどちらでも良いです。
まずは変数の定義です。変数定義ゾーンの一番最初に居る
というのが配列の定義です。他は今までの記事を見ていただいていれば大丈夫だと思うので説明しません。
配列を使う際は以下内容を決める必要があります。
配列を使う際の決まり
・種類を決める ・・・1次元配列か2次元配列かを決める。
・枠の宣言をする ・・・どれだけ枠をストックするのか。型も決める。
・情報を収納する ・・・前回記事のロジックで情報を収納。
・その後の処理に展開する ・・・ここではセルに書き出すという事になります。
Dim E(1to702) As String で配列の種類と枠の宣言に対応しています。配列のかっこ内の数値を添え字というのですが1次元配列を見てみると添え字が1つです。
E(1)
E(2)
E(3)
といった感じです。
宣言時は1と702をtoで繋いでいるのでかっこ内の数値としては2つある様に見えるのですが1つの添え字（値）が1から702まで変化しますよという事を定義していますので1つと数えます。
以下で添え字が2つの2次元配列を見てもらいます。実際見てみると違いが分かると思います。
2次元配列というのはコンマで区切られたうえで添え字が2つになります。配列Eを使ってたとえると・・・
E(1,1)
E(1,2)
E(1,3)
といった感じです。軸が2つ持てます。前回記事のマンションの様に例えてみるとEタワーマンションの1階の1号室、2号室、3号室という様に1つの階層の中でさらに階層を構築出来るようになります。ワークシートのセルと同じです。ってことはCellsで行列を表現するのに似てますね。これは後でヒントになるので頭の片隅に置いておきましょう。
次は枠の宣言です。いくつ枠を使うのかあらかじめ宣言します。配列は最初にいくつの枠を用意するのか決まっている場合と決まってない（決められない）場合があります。今回は終わりが分かっているので事前に添え字は(1to702)と宣言してしまいます。決まってない場合の説明は別の機会にします。
今回は1次元配列を使うという事が決まりました。添え字と一緒に型の宣言もします。アルファベットは文字なのでStringにしましょう。
これで配列を使う際の決まりである「種類を決める」と「枠の宣言をする」が完了しました。コードの構成の「変数の定義」ブロックが完了です。
次はコード構成の「AからZまでを準備する」というブロックです。ここで配列を使う際の決まりにありました「情報を収納する」という処理を行います。
For emt = 1 To 26
E(emt) = Chr(n)
n = n + 1
Next
For～Nextでemtという変数が1から26まで変化しますね。その中で関数Chrの引数を1つずつ変化させて配列に値を収納させています。関数Chrの説明は前々回の記事を参照ください。
E(1) = Chr(65) → A
E(2) = Chr(66) → B
E(3) = Chr(67) → C
・・・
という結果になります。配列でも基本的に左辺に右辺を代入という形は変わりません。これで簡単に配列に値をセットする事が来ます。ローカルウインドウを見ながらやっていくと分かりやすいと思います。
コード：構成の3、4
コード構成の3つ目「AAからZZを用意するための変数を準備」ブロックに入ります。
n = 65
nmr = 1
nはChr関数をAに戻す為に65をセットし直しています。nmrはアルファベットの1桁目を管理するのに使用する変数に最初の値として1をセットしています。
では後半のコードです。最初は「AAからZZまでを準備」ブロックです。
For emt = 27 To 702
E(emt) = E(nmr) & Chr(n)
n = n + 1
If emt Mod 26 = 0 Then
n = 65
nmr = nmr + 1
End If
Next
最初のFor～Next文は配列Eの添え字27から添え字702までをループするためのコードです。
そのあとは前回記事で説明した建付けを実現させるようにコードを書いてます。ちょっと違うのはループの中に If emt Mod 26 = 0 Then というIF文が居ますね。このコードがポイントです。これで1桁目をAからBに送る細工をしています。
今回はModという関数を使っています。「emtを26で割った時の余りがゼロの時は・・・」と聞いています。つまり変数emtが26の倍数の時だけTrueという事です。この時は必ずnが90を示すようにしています。つまり2桁目がZを示すときという事です。
2桁目がZだったら1桁目の添え字を1つ送る＋Chrの添え字を65に戻すというコードになってますので以下のようなアルファベットの並びが準備出来るようになります。
E(27) = E(1) & Chr(65) → A & A → AA
E(28) = E(1) & Chr(66) → A & B → AB
E(29) = E(1) & Chr(67) → A & C → AC
E(30) = E(1) & Chr(68) → A & D → AD
E(31) = E(1) & Chr(69) → A & E → AE
・・・
E(52) = E(1) & Chr(90) → A & Z → AZ
E(53) = E(2) & Chr(65) → B & A → BA
・・・
E(706) = E(26) & Chr(90) → Z& Z → ZZ
コード：構成の５
コード構成の最後は「AからZZまでをセルに書きだす」のブロックです。配列を使う際の決まりの中の最後の要素「その後の処理に展開する」という処理になります。
今回は1次元配列でしたね。この1次元配列を2次元配列に書き直すと以下のようになります。
E(1) → E(1,1)
E(2) → E(1,2)
E(3) → E(1,3)
・・・
という様に1次元配列の添え字は2次元配列に置き換えると2つめの添え字と同じになります。これをCellsに置き換えると1行目で（以下画像の赤枠）列方向に値が用意されていくことになりますよね。でもデータの貼付けは1列目固定で行方向（以下画像の青枠）に値を用意したいと思っています。
そんな時は「行/列の入れ替え」を使います。ワークシートでの作業時にセルの内容をコピーしてから貼り付けたい場所で右クリックすると貼付けのオプションが出てくると思います。
その時の左から4番目に出てくるアイコンと同じ作業をVBAでやってからセルに貼り付けることで行方向に値が並ぶように貼付けを実行する事が出来ます。
VBAで書くとTranspose関数になります。引数で配列のEを指定します WorksheetFunctionを忘れない様にしてください。
Range(Cells(1, 1), Cells(702, 1)) = WorksheetFunction.Transpose(E)
E(1,1) → E(1,1)
E(1,2) → E(2,1)
E(1,3) → E(3,1)
というように添え字が変化します。（実際は変化しませんが行列を入れ替えると上記の様に表現する事が出来るという事です）
これでセルA1からセルA702にAAからZZを用意する事が出来ました。
まとめ
配列を使うとセルにあらかじめ値を用意しておかなくても色んなことが出来ます。しっかりと「配列とは・・・」という説明はしてないのですが使ってみるとなんとなくわかってもらえると思います。
そういえば前回記事にもう一つの配列を使う条件として「手数の多すぎるコードを書いて処理が間に合わない時」というのがありましたね。
今回のように情報が少ないかつ簡単な時はまだ良いのですが数十万、数百万のセルをかまうというコードになるとCellsやRangeで書いていると処理が追い付かずフリーズしてしまいます。配列を使う事で毎回セルの値を参照しなくても良いので処理が高速化されてフリーズを防ぐ事が出来ます。
イメージですが図書館で本の場所を検索→本を取りに行く→本を読む→内容を理解するという人より図書館に入った瞬間に本の場所と内容を一瞬で全部暗記してしまう人が居たら（居ないけど・・・）ダントツで後者の方が速いですよね。
「VBAは遅い」とたまに言われますがコードが悪いだけで配列を使えばストレスなく高速に処理する事が出来ます。
どんなコードだと処理が遅くなってどうやって書けば速く処理出来るのかはまた別の機会に説明させていただきます。
他にも2次元配列の詳しい説明や枠の宣言時に収納する量が決まってない時のコード等々説明してない事がたくさんありますが今回はここまでとさせていただきます。
また質問があったら記事を書いていこうと思います。失礼しました。
|
|
|
