AからZZまで英語文字列を並べてみよう VBAの配列を使います
 |
 |
 |
前回の記事ではAからZまで並べて英語をみましたがさらに「AAからZZまで欲しい」って事でしたね。
関数でも出来るんですよ。ネット記事を見てもいろいろ書いてあります。
でも1列で全部書くことが出来る関数はなかなか無くてAからZまで作ってからAAからAZまで作って・・・を繰り返してZZまでというものが多い様です。
- 関数をたくさん書きたくない
- とにかく手数を減らしたい
- 再現性が欲しい
- 1列でAからZZまで書きたい
- 関数よりVBAのが好き
という事もありVBAで書いてみます。配列も使いたかったのでちょうど良いと思って今回記事を書いてみます。
前回記事からの続き
VBAの書き方についてはシリーズで書いた記事がありますのでそちらを読んでいただいてからの方が理解が早いと思います。
早速コードの検討に入りましょう。
コード：A～Z
では始めます。まずはAからZまでを用意するコードです。セルA1にA、セルA26にZが記入されるコードです。
VBAではChrという関数を使います。ワークシート関数CHARと少し文字が違いますが内容は同じです。
ポイントとしてはFor～Nextでは行を進める変数を用意しループ内でASCIIコードを進めるコードを書いているという事ですね。
コード：AA～ZZ 配列を使い最後にセルに書き込みます
AからZまでを用意するコードに続いてAからZZまでを用意するコードの説明に入ります。1番大きなポイントである配列を使います。難しいので2回に分けて説明させていただきます。今回は大まかに理屈を説明して次回でコードを準備します。
配列はVBA側で記憶領域を確保してそこに情報を収納するという機能です。一旦必要な情報を先に揃えましょうという時に使うコードです。
先？ただ情報を収納するだけ？何でそんなことするの？って思いませんか。
このあたりでいろんなネット記事や本ではまず「配列とは・・・」から書いてますが私はイマイチ分からなかったので今回はコードを説明して動きを確認しながら「配列とは・・・」をやろうと思います。多分その方が分かると思ったからです。
では話を戻します。私は配列の勉強をした時に何で配列を使うのかが全く分かりませんでした。セルに情報は居るからそれを使えばいいのに何で先に情報を全部取り込まないといけないの？と思っていました。
配列：どんな時に使うのか
というわけでまずはどんな時に配列を使わないといけないのかを提示します。私が疑問に思った「何で配列を使わないといけないのか」についても分かると思います。
- セルじゃないところに情報を持ちたい時
- 手数の多すぎるコードを書いて処理が間に合わない時
大きく分けてこの２つの場面に対面したときに配列を使わないといけなくなります。
今回は「セルじゃないところに情報を持ちたい」という事例なので配列を選択しました。
配列：考え方
「ん？」、「なに言ってんの？」ってなっていると思いますのでまず画像を見てもらってから説明しますね。どうやってこの状態が作られたかは後で説明しますので画像の並びだけざっくり見てください。
長すぎるので2つにして最初と最後だけ表示させています。これはVBEのローカルウインドウの一部を見てもらっています。
Eというのは適当に付けた配列の名前です。先程配列はVBA側で用意された記憶領域だと書きましたね。上記画像は記憶領域に用意された配列Eにアルファベットを順に収納したものを見てもらっています。配列Eの型はString型で1to702と書いてあります。1から702までという事です。
かっこ内の702という数値はAからZZまで702個の情報が発生するので先に収納する場所を確保していると思ってください。建物に例えるとEタワーマンションは702階あり各階には部屋が1つずつあります。１階にAさん、２階にBさん・・・702階にZZさんという感じでしょうか。「各階に1部屋で702階？」ってあまり無い構造ですがまずはイメージとしてとらえてください。
これは1次元配列というものになります。ひとまずこの説明も一旦止めて今回は先に進みます。
情報の収納が完了するとローカルウインドウで見たときに上記のような状態となります。最終的にはE(702)には"ZZ"が収納されているという状態です。
セルに書き出す前にExcelの中でAからZZの並びを作って配列に収納したものを見てもらいました。これが配列を使用する際の要件として提示した「セルじゃないところに情報を持ちたい」という事です。
確認ですがこの作業はVBAの記憶領域内で情報を作っているのでExcelのセルにはまだ1回もアクセスしてません。こうすることでセルにAからZの情報が無くてもAからZZの文字を準備する事が出来ます。あとはセルに貼るだけです。
コードの建付けを決める
実際にコードの検討に入ります
では具体的にどうやって情報を準備したのかを説明します。
私が考えたのは以下のような方法です。先に説明した様にE(1)からE(26)はAからZになるはずなのでE(27)からは・・・
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つ。
- 1桁目と2桁目のループを一旦別々に考える。
- 1桁目と2桁目を＆でつないで配列に収納する。
- 1桁目はE(1)からE(26)をループさせる。AからZという事ですね。
- 2桁目はAから始まりZになった時に1桁目が次のアルファベットに移行する。
- 2桁目は1桁目がループしたらまたAからZのループを繰り返す。
セルにアクセスせずこれが出来れば1列にAからZZまで規則的にアルファベットを並べることが出来るのではと考えました。
セルの一部にAからZを２列書いておいてそれぞれをループさせて＆で繋いでも出来ますしこの方がはるかに簡単です。ただし一旦セルに情報を書いておかないといけないですよね。それが嫌で「セルじゃないところに情報をもちたい」時には配列を使うようにしています。
イメージしやすいようにセルの情報を使う場合の画面を用意してみました。A列B列ともにＡからＺまでアルファベットが並んでいます。
A列1行目のAを1桁目にしてB列をループ。B列の各セルを参照時に1桁目と2桁目を＆で繋げばAA、ABのような文字を用意する事が出来ます。B列でZまで到達したらA列をループさせてBにするというコードを書けばこれでも出来るのですが画像にある様に一旦セルに記号を書いておく必要があります。
まとめ
セルに値を置いてからという方法でも全然良いのですがどうせなら下準備とかなく全部VBAでやりたくないですか。それだけです。
では実際にコードを考えましょう。続きは次回とさせていただきます。
|
|
|
