AからZZの文字列を作り配列に格納後セルに貼り付ける
ExcelVBAの配列（一次元配列）を使ってアルファベットのA～ZZまでの値を用意します。
配列を覚えると処理時間が劇的に速くなり色々なことができる様になります。
1回読んだだけでは理解できないです。何回か読んでみてください。
何回読んでも「全然分からない」時は問い合わせフォームから連絡をお願いします。
どうしても分からない時はお問い合わせより連絡をお願いします。可能な範囲で回答させていただきます。
関連記事
こちらの記事は全2回のシリーズものです。前回記事はこちらになります。
前回記事【一次元配列の考え方】マクロでアルファベットのAからZZを用意
事例を使って一次元配列を解説
ある事例を使って一次元配列を理解していただきます。
事例の詳細
この後紹介するコードでは「アルファベットのAからZZまでの文字列を用意してセルに配置」という作業をします。
冒頭でも紹介しました以下リンクの記事では主に配列の考え方と使い時、今回実行するコードの構想について書いています。
本記事を読む前に見ていただくとより理解が進みます。
コード
以下参照ください。
Sub AZZ()
'A～ZZまでをセルに出力するコード。ZのあとはAAになるという感じでZZまでを書き出す。
'最初に記号を全部配列の中に並べて最後にまとめてセルに出力するというコード。
'***************************************************************
'変数の定義
Dim E(1 To 702) As String 'Englishの先頭のE 配列の名前
Dim n As Long: n = 65 'numberの先頭のn ASCIIの番号をループさせるための変数
Dim emt As Long 'element(要素)の略 配列Eの要素番号
Dim nmr As Long 'number（番号）の略 記号が2桁になってからの1桁目を用意する為の変数
'***************************************************************
'まずは最初のAからZまでを配列にとる。
For emt = 1 To 26
'配列Eの1～26の部屋にA～Zを収納。
E(emt) = Chr(n)
n = n + 1
Next
'***************************************************************
'変数nをASCIIでAと対になっている65に戻す。加えて変数nmrに1をセットする。
'nmrは配列Eに収納される記号の1桁目を担当する。
n = 65
nmr = 1
'***************************************************************
'記号AA～ZZまでを収納する為のループを組む。
For emt = 27 To 702
'E(nmr)で記号の1桁目。「＆」でつないでChr(n)で記号の2桁目を用意。配列Eにセットする。
E(emt) = E(nmr) & Chr(n)
n = n + 1
'ここがポイント。変数emtを26で割って余りがゼロになる時にTrueの処理をさせる。
'これで毎回Zが来た時だけ変数を変えるという動きをさせている。
If emt Mod 26 = 0 Then
'変数(n)をASCIIでAと対になっている65に戻す。
'加えて変数nmrに1を足す事で次の記号を準備する。
n = 65
nmr = nmr + 1
End If
Next
'***************************************************************
'最後にセル出力｡Transpose関数で並びを変える｡
Range(Cells(1, 1), Cells(702, 1)) = WorksheetFunction.Transpose(E)
'***************************************************************
End Sub配列を使うにあたって
ある程度決まりがあるので事前にお知らせします。
例外もあるのですが基本はこの流れでコードを用意していただくと使いやすいと思います。
- 種類を決める ・・・1次元配列か2次元配列かを決める
- 枠の宣言をする ・・・値を収納する枠の大きさを決める
- 情報を格納する ・・・前回記事のロジックで値を格納する
- その後の処理に展開する ・・・ここではセルに書き出すという事になります
以下コードの構成についても上の「きまり」の範囲内でコードが構築されています。
コードの解説
では解説を進めていきます。
構成
全部で5つの要素で構成されています。
- 1変数の定義
使用する変数を宣言（定義）します
- 2AからZまでの文字列を準備
Chr関数と繰り返し処理を使ってAからZの文字列を配列に格納します
- 3AAからZZを用意する為の変数を準備
変数n、nmrに値を準備します
- 4AAからZZまでを準備
配列に格納された値と関数を組み合わせてAAからZZまでの文字列をつくり配列に格納します
- 5AからZZまでをセルに書き出す
配列に格納した文字列をセルの指定の場所に書き出します
以降解説をご覧ください。
1_変数の定義
まずは変数の定義です。変数定義ゾーンの一番最初に居る以下コードで配列の定義をしています。
Dim E(1 to 702) As String
Dim E(1to702) As String で配列の種類と枠の宣言に対応しています。
配列のかっこ内の数値を添え字というのですが1次元配列を見てみると添え字が1つです。
- E(1)
- E(2)
- E(3)
宣言時は1と702をtoで繋いでいるのでかっこ内の数値としては2つある様に見えます。
解釈としては1つの添え字（値）が1から702まで変化しますよという事を定義しています。
枠の宣言
次は枠の宣言です。いくつ枠を使うのか？枠の大きさをあらかじめ宣言します。
配列は最初にいくつの枠を用意するのか決まっている場合と決まってない（決められない）場合があります。
今回は終わりが分かっているので事前に添え字は(1to702)と宣言してしまいます。
今回は1次元配列を使うという事が決まりました。添え字と一緒に型の宣言もします。
アルファベットは文字なのでStringにしましょう。
2_AからZまでの文字列を準備
For～Nextステートメントでemtという変数が1から26まで変化します。
その中で関数Chrの引数を1つずつ変化させて配列に値を収納させています。
関数Chrの詳細は今シリーズの1つ目の記事を参照ください。
VBAではなくセルに書くワークシート関数の説明ですがVBAでも理屈は同じです。
- E(1) = Chr(65) → A
- E(2) = Chr(66) → B
- E(3) = Chr(67) → C
という結果になります。配列でも基本的に左辺に右辺を代入という形は変わりません。
これで簡単に配列に値をセットする事が来ます。
ローカルウインドウを見ながらやっていくと分かりやすいと思います。
3_AAからZZを用意する為の変数を準備
nはChr関数からの戻り値をAに戻す為に65をセットし直しています。
nmrはアルファベットの1桁目を管理するのに使用する変数に最初の値として1をセットしています。
4_AAからZZまでを準備
最初の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
5_AからZZまでをセルに書き出す
先にお伝えしておきますがこれはイメージです。
考え方としてこうすると分かりやすいという意味で読んでください。
実際に1次元配列は今から私の書く様に情報を持っているかは検証してないので分かりません。
1次元配列を2次元配列に書き直すと以下のようになります。
- E(1) → E(1,1)
- E(2) → E(1,2)
- E(3) → E(1,3)
という様に1次元配列の添え字は2次元配列に置き換えると2つめの添え字と同じになります。
配列の名前であるEをCellsに置き換えてコードを読んでみます。
すると1行目で（以下画像の赤枠）列方向に値が用意されていくことになりますよね。
この1次元配列の値をセルに貼り付けるとどうなるでしょうか。
以下画像の赤枠の様に行固定で列方向に値が貼り付いてしまいます。
でも今回のデータの貼付けは1列目固定で行方向（以下画像の青枠）に値を用意したいです。
そうしないと使いにくいですよね。
1行で702列まで情報が入っているより1列で702行まで情報が入っている方が断然扱いやすいです。
そんな時は「行/列の入れ替え」を使います。
ワークシートでの作業時にセルの内容をコピーしてから貼り付けたい場所で右クリックしてみましょう。
すると貼付けのオプションが出てくると思います。
その時の左から4番目に出てくるアイコンと同じ作業をVBAでやってからセルに貼り付けることにします。
これで列方向に用意された値を行方向に値が並ぶように貼付ける事が出来ます。
ショート動画を使って「行列を入れ替える」を説明しています。
1分程度の動画です。是非ご覧ください。
Wikipediaにも分かりやすい画像があります。リンク先をご覧ください。
リンク先を開くとすぐ右側に動く画像があります。ものすごく分かりやすいです。
転置の話に戻します。VBAで書くとTranspose関数になります。引数で配列のEを指定します。
関数を書く際は頭にWorksheetFunctionを忘れない様にしてください。
- E(1,1) → E(1,1)
- E(1,2) → E(2,1)
- E(1,3) → E(3,1)
というように添え字が変化すると思ってください。
先程もお伝えしましたがこれはあくまでイメージです。
（実際は変化しませんが行列を入れ替えると上記の様に表現する事が出来るという事です）
これでセルA1からセルA702にAAからZZを用意する事が出来ました。
まとめ
配列を使うとセルにあらかじめ値を用意しておかなくても色んなことが出来ます。
2次元配列
2次元配列のコードと配列を使ってないコードを用意して差が見れる様にした記事です。
VBEにコピペですぐ使える様にしてあります。
配列の作業速度を体感出来ます。是非お試しください。
連想配列
連想配列を説明する記事とコードを使って解説している記事を用意しています。
