最近のエクセルの売れ筋の本を見ると、やたらVBAの使い方に関する本が目立ちます。仕事の効率化や自動化が求められ、ビジネスマンに関心が高い項目なんでしょう。しかし、書店に並んでいる本を見てみると覚えることが多すぎて、最後まで行き着くのか心配になる本がほとんどです。忙しいビジネスマンにとってそれは、買ってはやめ、また他の本を買ってはやめの繰り返しになっているのではないか？と思うのです。自動化が目的なら、思い切って覚えることを削ぎ落として、短時間で習得して、とりあえず実行しなきゃ。ということで、これだけ知っていれば、VBAは充分だという内容を、基本編と応用編に分けてお伝えします。
準備
覚えることを削ぎ落とすと言っても、最低限の基本操作は覚えなきゃなりません。画面を開く方法とマクロを書く準備です。まずは、VBEの画面を開きましょう。
VBEの表示
Excel2007、Excel2010の場合
リボンの「開発」→「Visual Basic」
Excel2003の場合
「ツール」→「マクロ」→「Visual Basic Editor」
どのバージョンでも、ショートカットは、Ctrl + F11 です。
左に表示されている部分は、プロジェクトウインドウと言います。この状態ではシートだけが表示されています。まだ、マクロを書く場所がありません。次はモジュールを作成してマクロを書く画面を用意します。
標準モジュールの表示
VBEの画面から、次の操作でモジュールを作成します。
メニューの「挿入」→「標準モジュール」 ・・・ Alt + I + M
または、プロジェクト内を右クリックで「挿入」→「標準モジュール」
で、以下のようになります。
この右の白地部分が、マクロを書く場所になります。そして、左側に、「標準モジュール」が追加され、その下に、「Module1」が追加されています。
つまり、標準モジュールのModule1にマクロを書くのです。
ちなみに、この「Module1」を消す場合は、
「Module1」を右クリック→「Module1の解放」
以上が、マクロを書く場所の作成と削除になります。
基本編
ポイントは３つ
- 変数の宣言
- 繰り返しの命令
- if構文
この３つは別々のことではなく、組み合わせて実行します。それぞれをテーマに説明するのではなく、実際に活用するイメージを持つために、サンプルを例に説明していきます。この後の流れは、エクセル画面とVBEの画面を切り替えながら操作していくことになります。煩雑になるので、完成したマクロのプログラムは手元に置いて進めた方がわかりやすいと思います。そして、エクセルはPCで操作すると思いますが、このサイト（教科書）はスマホやタブレットで表示して、学習を進めることをお勧めします。
完成したマクロのPDF →program_01
サンプルエクセル →sample_01
↑この完成したマクロのPDFとサンプルエクセルとをダウンロードしてください。PDFは印刷して手元に置いて参照してください。
sample_01の内容
用意されたエクセルシートは”加工data”,”生data”,”area table”,”category table”,”size table”の５つです。生dataは、データベースから抽出されたデータと仮定します。これには、エリアコード、カテゴリーコード、サイズコードはあるものの、エリア名、カテゴリー名、サイズ名など名前が無いものとします。これから作成するマクロは、生データはそのままにして、”加工データ”シートに生データを転記し、同時にコードに名前を結びつけます。
マクロを使わず手作業をイメージするなら、”生data”シートの生データをコピーし、”加工 data”シートに貼り付け、コードの後ろに列を挿入し、その列にVLOOKUPで各テーブルから名前を持ってくる…という作業になります。
マクロの作成
それでは、マクロを作成しましょう。
sub 転記 と入力しEnterを押すと自動的に
Sub 転記() と「転記」の後に（）が追加されて、末尾に
End Subと表示されます。※subと転記の間には必ず半角スペースを入れて下さい。
この”Sub 転記()”,”End Sub”間に、マクロを記入します。ちなみに、小文字で入力しても、プログラミングとして正しければ、自動的に大文字に変換されます。
（Sub***()〜End Subの一区切りをサブプロシージャと言います。サブプロシージャの名前は自由です。但し、数字で始まる名前はエラーになりますので注意！）
次に、毎回最新のデータを更新するために、”加工data”のシートをクリアします。このプログラムは覚える必要はありません。マクロの記録を実行してみましょう。
エクセルの画面に戻り、開発タブのマクロの記録をクリックしてください。
”加工data”のシートタブをクリック。
左隅をクリックして全体を反転させます。Deleteキーで消去すればOKです。
最後にセルA1を選んで、マクロの記録を終了してください。
VBE(Visual Basic Editor)を開いて、標準モジュールに現れた”Module2”を開くと、今実行したプロセスがプログラムとして記録されています。
赤枠の部分をコピーして、”Module1”のサブプロシージャ”転記”に貼付ければOK。ちなみに緑色の文字は、プログラムではありませんので実行には必要ありません。（シングルクォーテーションマーク「′」以降は、プログラムとして認識しないので、自分で解るように解説を加えておくと便利です。）
マクロの記録を利用すると、勝手にプログラムを書いてくれるので、参考にする機会は結構あります。色を付けたり、線を引いたり…「こうしたいなぁ」と思ったときには、マクロの記録を利用してみましょう。
※貼付けたら、Module2は不要なので削除しておいてください。（Module2の上で右クリック→削除）ちなみに、エクスポートするかどうか確認のメッセージボックスが表示されます。「いいえ」を選択してください。（エクスポートする機会はほとんどありません。他のBOOKで同様のマクロを活用したい場合に使えるかもしれませんが、コピペでも問題ありません。）
さぁ、いよいよ変数です。今までは準備。これからが本番です。
変数の宣言
サブプロシージャ「転記」は、データを転記する作業です。手作業だとそのままコピペして、名前の列をそれぞれのコードの後ろに挿入することでしょう。あるいは、マクロの記録で手作業を再現すれば、記録した動きを毎回同じように実行してくれます。ここでは、練習のために敢えて変数を使います。
まず、変数の宣言を行います。
赤線の部分を書き加えてください。
「Dim a As Integer」は 「“a”は変数ですよ！」という宣言になります。これは、暗記してください。決まり事です。
「a=1」は「”a”は１です。」という意味です。
これは、後に書き加えるプログラムの準備です。この変数を操作してプログラムを実行します。
繰り返しの命令
「Do Until〜Loop」について解説します。
赤線の部分を書き加えてください。
この「Do Until Sheets”生data”.Cells(a,1)=“”〜Loop」の意味は、
シート名「生data」のa行1列のセルが空白になるまで実行してください。そして、空白になったらLoopを抜けてください。
という意味です。
※Cellsの後ろの( )内は「行」/「列」の順番で表記します。
sample1.xlsを例にすると、シート”生data”の１行目〜２９５行目までは空白ではありませんからDoとLoopの間を繰り返します。「シート”生data”の２９６行目になったらLoopを抜けてください。」ということになります。
いかがですか？VBEの画面とエクセルの画面を見比べる作業が増えてませんか？できるだけ負担を減らすために、印刷した「program_01」を参照しながら進めてくださいね。
では、”生data”シートの生データを、”加工 data”シートに貼り付け、コードの後ろに各テーブルから名前を持ってくる準備を行います。
赤枠の部分を書き加えてください。１行目の
Sheets(“加工data”).Cells(a,1)=Sheets(“生data”).Cells(a,1)
の意味は、「”加工 data”シートのa行1列のセルに”生data”シートのa行1列のセルの値を持ってきてください」という意味です。a=1と宣言していましたので、「”加工 data”シートのセルA1に”生data”シートのセルA1の値を持ってきてください」ということになります。２行目も同じです。３行目のプログラムは、１列飛ばして「”加工 data”シートのa行4列のセルに”生data”シートのa行3列のセルの値を持ってくるように」命令しています。これは、コードの後ろに名前を入れるペースを空けるためです。４行目、５行目も同様です。エクセルのシート間を見比べるとその意味が解るでしょう。
最後の
a=a+1
は、「次の行に移ってください」という意味です。a=1でしたから１つ加えたら2ですね。Do〜Loopの間は繰り返されるので、１行ずつプログラムが実行されて行く訳です。そしてa=296になったとき、つまり「”生data”シートの296行1列のセルの値が空白になったとき」Loopを抜けて「End Sub」プログラムを終了します。これで、転記の作業は終了です。
マクロの実行
プログラムを実行してみましょう。
マクロの表示を選択すると、
「転記」のマクロが表示されます。
「転記」を実行してください。
”加工 data”シートに名前を入れる列を空けたデータが転記されます。
以上が「転記」のマクロです。次に、各テーブルから名前を持ってくる作業を行います。
データの紐付け
作成した”加工 data”シートをご覧ください。２列目の”area No.”の右の列に”area table”シートの”エリア名”を紐付ける作業を行います。手作業で行うなら、関数VLOOKUPで簡単にできる作業ですが、area/category/sizeの３列に式を入れることを考えると、瞬時にはできません。毎日の定型業務ならワンクリックで済ませたいものです。
まず、プログラムのイメージを共有しましょう。
ここでは、まず、”加工 data”シートの１行目の３列目（C列）に”area名”と書き込みます。２行目からは”加工 data”シートのB列のコードと一致するコードを”area table”シートのA列から探し出します。そして一致したとき、”加工 data”シートのC列に”area table”シートのB列の値を表示させます。では、マクロを書いてみましょう。
サブプロシージャ名を「area名」として、”加工 data”シートの１行目の３列目（C列）に”area名”と表示させる命令を書きます。
上記のように、セルを指定して、=”area名”とするだけです。２行目からは、変数を使います。
”加工 data”シートの２行目のB列のコードと”area table”シートのA列のコードが一致するまで、”area table”シートのA列を縦に探していきます。つまり、１行ずつ移動して探す訳です。サブプロシージャの「転記」では、２つのシートで１行ずつ連動していたので変数は１つでした。今回は、”加工 data”シートのある行のB列のコードを”area table”シートのA列から１行ずつ移動しながら探すので、変数が２つ必要になります。よって、
“加工data”シートの変数をa、”area table”シートの変数をbとします。“加工data”シートのデータは２行目から始まるので a=2 とします。
“加工data”シート側の動きは、データが空白になるまで実行されればいいので、
と書きます。
今、”a”は２行目です。“加工data”シートの２行目B列のコード「１」と”area table”シートの”b”行目A列のコードが一致するまで１行ずつ照合します。プログラムで表現すると、
となります。解説すると、
b=２は、「”area table”シートの２行目からスタートしてください」という意味です。
Do Until Sheets(“加工data”).Cells(a, 2) = Sheets(“area table”).Cells(b, 1)は、“加工data”シートの”a”行目B列のコードと”area table”シートの”b”行目A列のコードが一致することを表しています。一致するまで b=b+1 を繰り返す訳です。ここで注意するのは、コードが一致しない場合を想定するか？しないか？です。不一致の心配があるなら、
Or Sheets(“area table”).Cells(b, 1)=“”
のプログラムは必ず加えましょう。新しいエリアや商品により、元データに新しいコードが発生し、そのコードがテーブルに存在しない場合は、延々と探し続けることになるからです。
つまり、「一致したらLoopを抜ける、もしくは”area table”シートのA列が空白になったらLoopを抜ける」というプログラムになります。
こうしておけば、不一致のコードが発生した場合でも、探し続けることなく処理を続行します。不一致の部分は空白のままとなります。
ここで、プログラムの改行について触れておきます。
プログラムの改行には決まりがあります。and や or でプログラムを繋いでいて、見易くするために改行するときは、スペース+アンダーバーを入れてください。
***(プログラム)*** and _(改行) , ***(プログラム)*** or _(改行)
マクロが長くなって一読できないときにしばしば用いますので、覚えておいてください。
さぁ、紐付けの最終段階です。
if構文
or を使って、一致した場合と不一致の場合と２つの条件を指定しました。次の命令は、「一致したら値を転記し、不一致なら何もしない」という内容です。if構文を使います。
If (A) then (B) else (C) End if
(A)が正なら(B),偽なら(C) を実行せよ…という命令です。この場合、
Sheets(“area table”).Cells(b, 1)=“”が正なら何もしない。Sheets(“area table”).Cells(b, 1)=“”が偽ならSheets(“加工data”).Cells(a, 3) = Sheets(“area table”).Cells(b, 2)を実行する。
となります。
これで、”加工 data”シートの最初の行が完了し、次の行（a=a+1）に移ります。LoopによりDo Until Sheets(“加工data”).Cells(a,1)=“”に戻って、且つ、b=2にリセットされて作業を繰り返す訳です。そして、Sheets(“加工data”).Cells(a,1)=“”になった時点でプログラム終了となります。
以上で、area名の紐付けは終了です。category名、size名についても同様に、それぞれのサブプロシージャを作ってください。コピペでサブプローシージャ名と列の値を変更すれば簡単に作成できます。program_01…PDFを参照して間違いがないかチェックしてください。
出来上がったマクロを一つずつ実行してくだい。問題なければ、完成です。
最後に一度に実行する命令を加えます。以下のように、サブプロシージャを作成してください。
こうしておくと、マクロの実行で”main1”を選ぶだけで全て実行されます。そして、「MsgBox”完了”」により、マクロ終了時に「完了」と表示されます。
以上で、基本編は終了です。応用編では、クロス集計を通して、基本編で使った
３つの構文
・変数の宣言
・繰り返しの命令
・if構文
を、更に活用してみます。