Chr関数でAからZZまでの値を用意し配列に格納する
VBAには配列という機能があります。
配列を使える様になるとメリットが沢山あります。非常に便利な機能です。
- データの「一時置き場」が不要になる
- 作業速度が上がる
- エクセルブックの容量を軽くする事が出来る
この中から今回はデータの「一次置き場」を使って配列を勉強してみましょう。
データの「一時置き場」とは以下のようなケースです。
シートAを使ってシートCに演算結果を出力する際にデータの体裁を整える為に使うシートB
シートBは関数が敷き詰められている事が多くデータが重くなる原因の1つです。
要／不要で考えると「不要」なデータです。なんとかして無くしたいものです。
VBAの配列という機能を使えば一時置きシートは不要になります
今回はデータの「一時置き場」が不要になるというポイントをメインに配列を勉強する事にします。
データの「一時置き場」を作らずアルファベットのA～ZZの値を配列を使って用意してみましょう。
一緒にChr関数の使い方も紹介します。
以下で前回記事の事を書いてますが読まなくても問題ありません。
こちらをクリックしていただくと前回記事の振り返りをショートカット出来ます。
前回記事の概要
前回から続きで読んでいただいた方向けに少し説明をします。
「アルファベットのAからZまでを関数を使って並べる」という事例を見てもらいました。
実はそれでは足りなくて「AAからZZも欲しい」という事でした。
ワークシート関数エクセルのCHAR&ROW関数でAからZのアルファベットを並べる
出来るか出来ないかで言うと関数でも出来ます。ネット記事を見てもいろいろ書いてあります。
でも1列で全部書くことが出来る関数は無いです。
AからZまで作ってからAAからAZまで作って・・・を繰り返してZZまでというものが多い様です。
手作業で関数を駆使したら出来るので「関数でやった方がよい」という意見もあります。
しかし関数もVBAも両方できる私は「VBAの方が圧倒的に楽」という事を知っています。
今回は配列の紹介も兼ねてVBAを使って作業をしてみます。
VBAについて
VBAについては初心者様向けの学習シリーズを用意しています。
全部見ていただくと1クリックで複数セルの値にVLOOKUP関数を実行できる様になります。
コード：A～ZをVBAで用意する
前回記事ではA～Zまでを関数で用意する方法を紹介しました。
VBAでも同じことが出来ますので紹介しておきます。
AからZまでを用意するコードです。セルA1にA、セルA26にZが記入されるコードです。
Sub AZ()
'Chr関数の引数 ASCIIでAは65なので初期値は65
Dim n As Long: n = 65
'行を指定する変数
Dim r As Long
'変数rを使って行方向に値を記入する
For r = 1 To 26
Cells(r, 1) = Chr(n)
'nを1つ送ってChr関数の引数を1つ送る。そうするとAの次はBを持ってくるようになる
n = n + 1
Next
End SubVBAではChr関数を使います。
ワークシート関数CHARと少し文字が違いますが内容は同じです。
繰り返し処理を使って1行ずつセルを選択しています。
同時にChr関数の引数も1つずつ送る様にコードを書いています。
繰り返し処理についてはこちらをご覧ください。
ここからは配列を使ってA～ZZまでの値を用意していきます。
配列とは
配列はVBA側で記憶領域を確保してそこに情報を持つことができるという機能です。
好きなタイミングで情報をインプット／アウトプット出来ます。
勉強当初の私の疑問点
最初はなぜ配列を使うのか全く分かりませんでした。
理由はセルに情報があるのに何で配列に情報を取り込まないといけないのか？と思っていた為です。
加えて配列自体の考え方も難しく全然頭に入ってこなかったのを覚えています。
あえて難しい事をやる意味があるのか？と悩んでいました。
配列：どんな時に使うのか
大きく分けてこの２つの場面に対面したときに配列を使わないといけなくなります。
- セルではないところに情報を持ちたい時
- 手数の多すぎるコードを書いてマクロの実行が極めて遅くなってしまった時
今回は「セルではないところに情報を持ちたい」という事例に該当するので配列を使います。
「使い時」を深堀りする
配列はメモリ上に情報を格納できます。セルに情報を一次置きする必要もなくなります。
よって少ない作業範囲で仕事が進められます。
手数の多すぎるコード・・・については「データの持ち方が変わる」ので仕事が速くなります。
速くなりますでは足りなくて「ものすごく」速くなります。
2つの使い時＝配列のメリット
この2つの意味が分かった時に配列を勉強する理由が分かりました。メリットが非常に大きいという事です。
配列が分かる様になると普通にコードを書くのとは比較にならないぐらいデータの取り回しが綺麗になります。
加えて作業速度も上がります。上がりますでは足りず「ものすごく上がります」是非覚えてほしい機能です。
配列：考え方
具体的な説明に移ります。まずは画像をみてください。
分からない事もありますがひとまず情報の並びだけざっくり見てください。
これは1次元配列というものになります。
上記画像は記憶領域に用意された配列「E」にアルファベットを順に格納したものを見てもらっています。
ひとまずこの説明も一旦止めて今回は先に進みます。
情報の格納が完了するとローカルウインドウで見たときに上記のような状態となります。
表示が長すぎるので最初と最後だけ表示させています。
最初の画像の右上に配列Eの型はString型で1to702と書いてあります。
これは「1から702個の文字列を格納する領域」という事を表示させています。
なぜ702なのかというとAからZZまで文字列を用意すると702個の文字列が作成される為です。
以下画像をご覧ください。
配列E(1)には最初の値Aが入ります。最終的にはE(702)に”ZZ”が収納されます。
メモリ上に用意した702個の箱にAからZZの並びの文字列を作って配列に格納したものを見てもらいました。
これが配列を使用する際の要件として提示した「セルではないところに情報を持ちたい」という事です。
コード：AA～ZZ 配列を使って値を用意する
続いてAからZZまでの値を用意するコードの説明に入ります。
実際に配列を使っていきます。この記事では理屈を説明し次回記事でコードを準備します。
コードの建付けを決める
コードの検討に入ります。具体的にどうやって情報を準備したのか説明します。
私が考えたのは以下のような方法です。
E(1)からE(26)はAからZが入ります。
先程紹介したVBAのコードでAからZまでは用意出来ます。
セルに書いていた値を配列に格納します。E(１)から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)をループさせる
- 2桁目はAから始まりZになった時に1桁目が次のアルファベットに移行する
- 2桁目は1桁目がループしたらまたAからZのループを繰り返す
セルにアクセスせずこれが出来れば1列にAからZZまでアルファベットを並べることが出来ると考えました。
エクセルのシート上で作業を再現してみる
配列を用いた作業との比較の為にセルにデータを用意するつもりで検討してみます。
（今回はこの作業をやりたくないので配列を紹介しています）
セルの一部にAからZを２列書いておいてそれぞれをループさせて＆（アンパサンド）で繋ぎます。
この方がはるかに簡単です。&の使い方についてはこちらをご覧ください。
& は比較演算子です。記事は連結演算子の事を書いていますが後半で＆の解説をしています。
事例を使って分かりやすく説明していますので興味がある方はご覧ください。
この方法だと一旦セルに情報を書いておかないといけないですよね。
それが嫌で「セルじゃないところに情報をもちたい」時には配列を使うようにしています。
A列1行目のAを1桁目にしてB列をループ。
B列の各セルを参照時に1桁目と2桁目を＆で繋げばAA、ABのような文字を用意する事が出来ます。
B列でZまで到達したらA列をループさせてBにするというコードを書きます。
繰り返しになりますがこれでも出来ます。しかし画像の様に一旦セルに記号を書いておく必要があります。
まとめ
作業完了が最優先なのでセルに値を一時置きしてからという方法でも全然良いです。
でもどうせなら下準備とかなく全部VBAでやってみたくないですか。それだけです。
これができれば配列の使い時で解説した2つのメリットが発生します。コードを実行すると驚きますよ。
では実際にコードを書いていきます。続きは次回とさせていただきます。
