Chr関数でAからZZまでの値を用意し配列に格納する
VBAには配列という機能があります。非常に便利な機能です。
配列を使える様になるとメリットが沢山あります。具体的にメリットを3つ挙げてみます。
- データの「一時置き場」が不要になる
- 作業速度が格段に上がる
- エクセルブックの容量を軽くする事が出来る
この中から今回は1番のデータの「一次置き場」が不要になるにフォーカスを当てて配列を考えてみましょう。
データの「一時置き場」とは以下のようなケースです。
シートAを使ってシートCに演算結果を出力する際にデータの体裁を整える為に使うシートBがデータの一時置き場
シートBは関数が敷き詰められている事が多くデータが重くなる原因の1つです。
要／不要で考えると「不要」なデータです。なんとかして無くしたいものです。
VBAの配列という機能を使えば一時置きシートは不要になります
今回はデータの「一時置き場」が不要になるというポイントをメインに配列を勉強する事にします。
配列を使いデータの「一時置き場」を作らずアルファベットのA～Zの後にAA～ZZの値を用意してみましょう。
前回記事
前回から続きで読んでいただいた方向けに少し説明をします。
「アルファベットのAからZまでを関数を使って並べる」という事例を見てもらいました。
実はそれでは足りなくて「AAからZZも欲しい」という事でした。
ワークシート関数エクセルのCHAR&ROW関数でAからZのアルファベットを並べる
出来るか出来ないかで言うと関数でも出来ます。ネット記事を見てもいろいろ書いてあるので方法としては存在しています。
しかし一気にAからZZまでの値を用意することが出来る関数は無いです。
関数で値を用意するとなるとまずはAからZまでの値を作ることになります。
次にAAからAZまでを作り・・・を繰り返しZZまでの値を作るという方法を紹介したネット記事が多数を占めています。
このように手数をかければ何とかできるので「関数でやった方がよい」という意見も聞いたことがあるんですよ。
しかし結論としてはVBA一択です。関数もVBAも両方できる私は「VBAの方が圧倒的に楽」という事を知っています。
よって今回は配列の紹介も兼ねてVBAを使って作業をしてみる事にします。
VBAについて
VBAというのは言語の名前です。
Visual Basic for Applications（ビジュアルベーシック・フォー・アプリケーションズ、VBA）は、主にマイクロソフト製のMicrosoft Officeシリーズに搭載されているプログラミング言語である。
VBAで「できる事」ついてはこちらをご覧ください。初心者様向けの学習シリーズを用意しています。
全部見ていただくと1クリックで複数セルの値にVLOOKUP関数を実行できる様になります。
関連記事VBA-1ExcelVBAは何ができるのか？マクロとの違いを教えて
配列とは
配列はVBAの用意したメモリ空間の中で記憶領域を確保してそこに情報を持つことができるという機能です。
好きなタイミングで情報をインプット／アウトプット出来ます。
勉強当初の私の疑問点
シンプルに「なぜ配列を使うのか？」が全く分かりませんでした。
理由はセルに情報があるのに何で配列に情報を取り込まないといけないのか？と思っていたからです。
加えて配列自体の考え方も難しく全然頭に入ってこなかったので初心者の頃は勉強する気になりませんでした。
あえて難しい事をやる意味があるのか？と悩んでいました。
この後説明する使い時を見ていただくと配列のメリットが分かります。
使い時
大きく分けてこの２つの場面に対面したときに配列を使わないといけなくなります。
- セルではないところに情報を持ちたい時
- 手数の多すぎるコードを書いてマクロの実行が極めて遅くなってしまった時
今回は配列を用いてアルファベットのAからZZまでの文字列を用意するという作業を実行する予定です。
配列を使って値を用意するという事はセルに情報を用意するのではなくメモリ空間に情報を用意することになります。
よって今回は「セルではないところに情報を持ちたい」という事例に該当するので配列を使うことにしています。
「使い時」を深堀りする
配列はメモリ上に情報を格納できます。ワークシート上のセルに情報を一次置きする必要もなくなります。
よって少ない作業範囲で仕事が進められます。
手数の多すぎるコード・・・については配列を使うと「データの持ち方が変わる」ので仕事が速くなります。
速くなりますでは足りなくて「ものすごく」速くなります。理由はセルへのアプローチ回数が減るからです。
仕事が速くなる理由
配列を使うと仕事が速くなる理由を説明します。
マクロは一瞬で作業をしている様に感じますが毎度一瞬で作業をしているわけではないです。
裏では「実行速度」という概念があります。マクロでも手数の多い作業をすると実行速度は遅くなります。
この「手数の多い作業」は「オブジェクトを掴む回数が多い作業」と言い換えることができます。
もう少し具体的にすると「セルやワークシートを掴む回数が増えると」実行速度が遅くなります。
配列はメモリ空間に情報を用意し取り回すのでセルやワークシートを掴むのは最初と最後だけになり回数がグッと減ります。
よって配列を使うとセルを掴む回数が減るので速度に影響を及ぼす作業が減ります。結果実行速度が速くなります。
裏を返せば大容量のデータでも配列を使えば作業は一瞬だという事になります。
2つの使い時＝配列のメリット
この2つの意味が分かった時に配列を勉強する理由が分かりました。メリットが非常に大きいという事です。
配列が分かる様になると普通にコードを書くのとは比較にならないぐらいデータの取り回しが綺麗になります。
加えて作業速度も上がります。上がりますでは足りず「ものすごく上がります」是非覚えてほしい機能です。
考え方
ここで配列を少しだけ見てみましょう。具体的な説明に移ります。まずは画像をみてください。
この時点では分からない事もありますがひとまず情報の並びだけざっくり見てください。
これは一次元配列というものになります。
上記画像は記憶領域に用意された配列「E」にアルファベットを順に格納したものを見てもらっています。
ひとまず一次元配列の説明も一旦止めて先に進みます。
情報の格納が完了するとローカルウインドウで配列を見たときに上記のような状態となっています。
画像が2枚になっているのは表示が長すぎるので最初と最後だけ表示させたかった為です。ご了承ください。
画像の説明をします。最初の画像の右上に配列Eの型はString型で1to702と書いてあります。
これは「1から702個の文字列を格納する領域」という事を表示させています。
なぜ702なのかというとAからZZまで文字列を用意すると702個の文字列が作成される為です。以下画像をご覧ください。
配列E(1)には最初の値Aが入ります。最終的にはE(702)に”ZZ”が収納されます。
メモリ上に用意した702個の箱にAからZZの並びの文字列を作って配列に格納したものを見てもらいました。
事例を使って一次元配列を理解する
では実際に配列を使ってアルファベットのAからZZまでの文字列を生成して配列に格納してみましょう。
と言ってみたのですが少し段階を踏ませてください。今回の記事では以下内容までを紹介します。
- 配列を使わずにA～Zの値を用意する
- 配列を使ってAA～ZZの値を用意する方法を説明
理由は「いきなり配列は難しい」からです。よってまず配列を使わないコードを見てもらいます。
そのあとで一次元配列で文字列を生成する方法を見てもらいます。実際のコードは次の記事で用意することにします。
コード：配列を使わずにA～Zの値を用意する
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からZまでの値を用意する方法を見ていただきました。
コード：配列を使ってAA～ZZの値を用意する方法を説明
続いて配列を使ってAからZZまでの値を用意するコードの説明に入ります。
実際に配列を使っていきます。この記事では考え方を説明し次回記事でコードを準備します。
コードの建付けを決める
コードの検討に入ります。具体的にどうやって情報を準備したのか説明します。私が考えたのは以下のような方法です。
E(1)からE(26)はAからZが入ります。先程紹介したVBAのコードで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でやってみたくないですか。それだけです。
配列を使えるようになるとコードの中での値の取り回しが綺麗になるうえコードの実行速度が格段に上がります。
続きは次回とさせていただきます。コードを紹介していますので是非ご覧ください。
参考：関連記事
本シリーズはこちらの記事とも連携しています。