Excel VBAでプログラミング入門【実践編】

Excel VBAやプログラミングを学ぶ上で、

  • =
  • For
  • If

の3つのルールを理解することが重要という話をしました。

これを習っただけではあまりイメージが付きにくいと思うので、例題を1つ取り上げて解説します。

その例題は、過去に僕がこのブログで公開したExcelで4月始まりの年間カレンダーを作るというマクロ。

職場で掃除チェック表を1年に1回作っていたのですが、毎年曜日と日付がずれるので全部手直しするとなるとめんどくさいので自動化したものです。

ForとIfをほどほどに組み合わせて使っている他、目的も内容もわかりやすいので今回取り上げました。

オリジナルでは月間カレンダーも作成していましたが、この解説では年間カレンダーのみの作成を目指します。

スポンサーリンク

作成内容

サンプルデータはこちらからダウンロードできます。

既に完成したVBAが記述されていますので、まずは動作確認をしてみてください。

このように罫線と文字色など、12ヶ月分のカレンダーの様式があります。4月から始まり、4月カレンダーの上には今年の西暦、1月カレンダーの上には来年の西暦が入力されています。

これを2021年版にしようと思うと、4月1日が木曜日と1日ずつずれることになります。これを手直しするのは面倒なのでマクロで自動化してしまおうということです。

マクロの実行は右にある[実行]ボタンから行います。入力ボックスが表示され西暦を入力します。

すると一瞬でカレンダーが更新されます。

手入力だと数分かかる作業が、ファイルを開いてから10秒もかからず終わりました。

コードを書いてみよう

ではコードを書いていきましょう。完成コードは下記のようになります。

これをパートごとに分解して解説していきます。

コメント

前回の記事では説明しなかったことが冒頭でいきなり登場しますが、あまり神経質にならなくていい部分です。

この「’」で囲んだ部分はコメントといってプログラムの動作には影響が及びません。

このように作成・更新日を記録したり、各コードの補足説明などに使います。

また、コメントにしたところは動作に影響しないという特性を活かして、コードにコメントを付けて無効化するというコメントアウトというテクニックもあります。

ちなみに「’」で囲んでいますが、実際には行頭のみで大丈夫です。このブログのコードハイライトの都合上、囲んでおかないと次の行もコメント扱いになってしまうから囲んでいます。

変数宣言

「この変数はこの種類のデータを取り扱います」というのを「Dim 変数名 As データ型」という文法で宣言します。この変数宣言によってデータを適切に記憶し、変数を使った処理で矛盾が生じないようにします。

変数misoka、row、colはLong型というデータ型で使用することを宣言しています。Long型は整数-2,147,483,648~2,147,483,647まで扱えるデータ型です。misokaには1ヵ月の日数、rowは行番号、colは列番号を入れるために使用します。

変数thisYearとnextYearは文字列を扱うString型で宣言しています。thisYearとnextYearには今年の西暦と来年の西暦を入れるのですが、「あれ、文字列? 整数なんだしLong型で宣言しないの?」と思うかもしれませんがここはString型で宣言します。実はString型でないとできない処理があるからです。

データ型には他にもたくさんありますし、種類と使い方を覚えるのは面倒ですよね。そこで便利なのがVariant型。これで宣言すれば入れる値によってデータ型を自動的に変えてくれます。そして実は、変数宣言を行わなかった変数は自動的にVariant型になります。つまり変数宣言はなくても動きます。

ただ、変数宣言しないと意図しない処理になってしまうこともありますし、どんな変数を使っているのかをわかりやすくするためにも変数宣言をしておくことは大切です。

西暦を入力するInputBoxの表示

thisYearには今年の西暦を入れるのですが、これは作業する年によって変わります。

そこでユーザーがその場で入力できるようにInputBoxを使います。ここの入れた値がthisYearに格納されます。

ここでキャンセルボタンが押された場合はthisYearには「False」が格納されます。キャンセルするということはこの実行を中止するという意味なので、次にIf文で処理を分岐させます。

「もしthisYearがFalseの場合」という条件で分岐させます。Exit Subはこの処理を行っているSub、すなわちカレンダー作成()を終了させます。

Elseは指定していないので、thisYearにFalse以外の値が入っている場合はIf文の中身はスルーして次のコードへ進みます。

西暦のチェック

西暦は4桁で入れないと動作しないようにします。

Lenは文字数を調べる関数で、Len(thisYear)でthisYearの中に入っているデータの文字数を返します。

If文の「<>」は「左辺と右辺が等しくない場合」、つまり≠(ノットイコール)の意味です。

ここのIf文は「もし変数thisYear内のデータの字数が4でない場合」という条件になります。この場合もExit Subで処理を中止します。

String型でないと判定できない

ここでthisYearをString型に指定した理由が1つ出てきます。

このLen関数は文字列の文字数を返す関数なので、整数を表すLong型だと4桁でなくても分岐に引っかかりません。

MsgBoxの使い方

ただ中止するとその理由がわからずユーザーは困ってしまいます。そこでMsgBoxで処理を中止する理由を教えてあげます。

MsgBox内に表示する文字列は「”」で囲みます。途中にある「vbCrLf」は改行のコードです。文字列と改行のコードを「&」で連結しています。

このMsgBoxはデバッグにも便利でして、プログラムを中断して変数の値がどうなっているかを確認するのにも使えます。

InputBoxでthisYearに値を格納した次の行に「MsgBox thisYear」と記述します。

こうするとthisYearのデータの中身が書かれたMsgBoxが表示されます。また、MsgBoxを表示すると一旦プログラムの流れが中断し、それはOKを押して消すまで続きます。

このような方法で変数の値を確認しながらコードを書いていくということもできますので覚えておきましょう。

今年の西暦から来年の西暦を計算

nextYearにはthisYearに1をプラスした値を入れます。thisYearが2021ならnextYearは2022です。

ここで気づいていただきたいのがthisYearとnextYearのデータ型。String型は文字列ですので本来であれば計算できません。「2021」は左から順番に2・0・2・1と文字が並んでいるだけと解釈すると、文字列「2021」に1をプラスすると「20211」になるのではないか……と思いきや普通に計算できてしまいます。

この辺はプログラミング言語によって柔軟に対応してくれる場合とそうでない場合があります。VBAは結構柔軟にやってくれます。逆に余計なお世話な場合もありますが。

文字列を数値に変える場合はVal関数、逆に数値を文字列に変える場合はStr関数という機能もあるので、もしデータ型で行き詰まったら思い出してください。

ループでカレンダーの中身をリセット

既にカレンダーには日付が入力されているのでこれをリセットします。

月や曜日はそのままでいいので、日付の部分だけを消していきます。セル番地を指定して範囲を特定します。

範囲としては、

  • 【A1】から【W14】
  • 【A18】から【W29】
  • 【A33】から【W44】
  • 【A48】から【W59】

の4範囲です。法則としては行番号が3→18→33→48、14→29→44→59とそれぞれ15ずつ増えているのがわかります。

これを使ってFor文を作ります。

カウンタとなる変数iの初期値は3で48になるまで繰り返します。ここで「Step 15」と書いていますが、何も書かなければ1ずつプラスするところを15ずつプラスするという意味になります。

つまりiは3→18→33→48と増えます。これはカレンダー4段分の先頭行を表します。

Rangeで左上のセルと右下のセルを指定することで範囲の操作が行えます。例えば「Range(“A3:W14”)」と書けば4~6月カレンダーの日付部分の範囲となります。

「”A3″」と直接入力する箇所は「”」で囲み、変数を使う場合は「”A” & i」と「&」で繋ぐルールでした。また右下の行番号は左上の行番号に11をプラスした値になります。これを書くと「”A” & i & “:W” & i + 11」となります。ややこしいですが、書いているうちに慣れてきます。

.ClearContentsは対象のRangeに入っている値をクリアします。引かれた罫線や書式はそのまま残ります。

iが3の処理を行ったら18、33、48と値を変えて処理を繰り返します。

西暦を入力

4月の上に今年の西暦、1月の上に来年の西暦を表示します。

入力されていても上書きされるので、日付のようにリセットする必要はありません。

4月1日の曜日を取得

4月1日の曜日が何曜日かわかれば、あとはそこから1日ずつ設定していけばOK。

そこでWeekday関数を使って最初の曜日を取得します。年月日を入れると日曜日から数えて1~7までの数字を返します。”2021/04/01″を入れた場合は木曜日なので5が返ってきます。

ちょうど1列目のカレンダーの各曜日の列番号と対応しているので、返ってきた値をそのまま変数colに格納します。

これで4月1日をセットする列が決まりました。

列を数字で表示するR1C1形式

列番号はA、B、C…とアルファベットで表示するのがデフォルトの設定ですが、先ほどの曜日と列番号の説明の画像では1、2、3…表示が変わっていたことに気付いたのではないかと思います。

VBAを使う場合、このR1C1形式に慣れた方がいいです。これからループで日付をセットする列番号を設定していくのですが、アルファベットだと足し算ができません。列番号を数値で表現できなくてはいけません。

これはVBA上だけであって、Excelの見た目はアルファベットのままでもいいのですが、パッと見てどの列かがわかりにくいので設定を変えましょう。

Alt + T → Oキーでオプションを開き、数式メニューから「R1C1 参照形式を使用する」にチェックを入れます。

1ヶ月の日数を調べる

ここから肝となる部分です。ここを理解できたらループと分岐は完璧でしょう。

まずどういう動作が必要なのかをまとめます。

  1. 各カレンダーの右上の「○月」よりセットする日数を確定
  2. スタートは「○月」のセルの2行下、4月1日の曜日の列からスタート
  3. 1日ずつセットする列を右に移動し、土曜日まで進んだら日曜日の列へ戻り2行下に移動
  4. 1ヶ月分入力したら右のカレンダーへ移動、1段分終わったら次の段へ移動

かなりやることが多いので、まずは1つ目の行程から進めましょう。

まず、各カレンダー右上にある「○月」というセルの値を取得するには、1段目は1行目、2段目は16行目、3段目は31行目、4段目は46行目を参照します。「For i = 1 To 46 Step 15」はこれを意味します。

1段に3つカレンダーが並んでいますが、1つ目は7列目、2つ目は15列目、3つ目は23列目に「○月」というセルがあります。「For j = 7 To 23 Step 8」はこれを意味します。

各ループを表にすると以下の通り。

ij対象セル
17Cells(1, 7)4月
115Cells(1, 15)5月
123Cells(1, 23)6月
167Cells(16, 7)7月
1615Cells(16, 15)8月
1623Cells(16, 23)9月
317Cells(31, 7)10月
3115Cells(31, 15)11月
3123Cells(31, 23)12月
467Cells(46, 7)1月
4615Cells(46, 15)2月
4623Cells(46, 23)3月

Select Caseで分岐

Cells(i, j)の値によって日数を判定するのですが、ここでの分岐はIfではなくSelect Caseを使います。

「Select Case ●●」で、●●によって分岐すると前提し、「Case ▲▲」と記すことで値が▲▲の場合の処理を記します。条件は「,」で複数指定することができます。

「西向く士(さむらい)小の月」ということで、2月以外の小の月(4、6、9、11月)であれば30、2月であれば28か29、それ以外であれば31を変数misokaに入れます。

閏年の判定方法

2月は閏年であれば29日、平年は28日です。閏年の判定方法ですが、

  • 400で割り切れれば閏年
  • 400で割り切れず、100で割り切れれば平年
  • 100で割り切れず、4で割り切れれば閏年
  • 4で割り切れなければ平年

という条件をIf文で組んでいます。

1ヶ月分の日付を入力

あとは日付を入力していくループです。先ほどのコードに「1ヶ月分の日付を入力」というコメント以下のコードを加えます。

最初にセットする行はどのカレンダーも「○月」の2行下なので、変数rowに変数i+2を入れます。rowはセットする行番号です。

そして3つ目のFor文です。1から変数misokaの値まで繰り返します。要するに1日から始まり1ヶ月分の日数分だけ繰り返します。

colにはあらかじめ4月1日の列番号を格納していますので、「Cells(row, col) = k」で指定の行・列番号のセルに日付を入れます。

入れ終わったらcolとjが同じかどうかを確認します。jとは「○月」のセルがあった列ですが、同時に土曜日の列でもあります。つまり土曜日までいったら日曜日の列に戻って2行下へ移動させます。土曜日でなければcolに1を足して次の曜日に進みます。

そしてkがmisoka、すなわち1ヶ月の最終日まで達したら次のカレンダーへ移動します。jが23というのは各段の3つ目のカレンダーということで、colを16列分左に戻します。各段の1つ目か2つ目であればcolを8列分右にずらします。この時、colは最後にセットした曜日の列を基準に動くので、カレンダーは移ってもセットする曜日は直前に入力した次の列となります。

jが23に達したらjのループを抜けて、iのループが進んで1段下に移動します。というのを4段分繰り返して1年分の日付が入力されます。

デバッグで確認しよう

説明は以上になります。

文章で説明するとなかなか理解が難しいかもしれませんが、前回紹介したブレークポイントによるデバッグで動きを見てみるとわかりやすいですよ。

どこにブレークポイントを設定したらいいかわからない人は、とりあえず「Next k」にブレークポイントを設定すると日付がどんどんセットされていく様子が確認できます。

ブログでアイデア帳を作成しよう

僕はこのプログラムを入門にぴったりな簡単なものと思っていますが、初学者の人にとっては頭から湯気が出そうなほど難しいものに感じたかもしれません。

ただ、場数を踏んでいくうちに確実にレベルアップしていきます。コードの引き出しも増えてきて、ネットで検索しながらある程度思い通りのプログラムを作れるようになっていきます。

いくつか作っていくと「この前使ったコードを使いまわせそうだな」とか「あれ、この場合はどう書けばいいんだっけ」となることがしばしば。その時に過去のファイルを掘り起こしたり検索し直したりすると非効率的です。

そんな時にアイデア帳を作っておけばすぐに思い出せなくても引き出せますし、参考にしたサイトが見つからなかったり閉鎖してしまってどうすればいいのかわからなくなったという事態を防ぐことができます。

メモアプリや手書きのノートでも悪くはありませんが、僕はブログの利用をおすすめします。インターネットに繋がっていればどの端末からでも見られますし、検索もできるので目的のコードを探しやすいです。そしてコピペでコードを使うこともできるので便利です。かく言うこのブログもプログラミングのアイデア帳として活用しています。

ブログをやるならWordPressがおすすめ

どんなブログでもOKなのですが、僕はWordPressをおすすめします。

お金を払ってサーバーやドメインを用意するところから始まるのでハードルは高いですが、逆に言えば自分で好きに使えるサーバーとドメインが大したことない費用と作業で手に入るのだから、この経験はしておくべきだと思います。ブログとしてだけでなく、ファイルのアップローダーとしても使えるので、成果物を上げておいて必要時にダウンロードするということも可能です。

WordPressをサーバーにインストールして使うという点は難しそうに見えますが、記事を投稿するだけなら専門知識はいりません。Urvanov Syntax Highlighterというプラグインをいれるとコードを見やすくハイライトしてくれるのでおすすめです。

テンプレートテーマを選んでインストールするだけで自分好みのデザインにすることができます。もしアレンジしたければHTML、CSS、JavaSciprt、PHPを勉強しましょう。WordPressのブログを運営することでプログラミング言語の幅も広がります。

WordPressでブログを作ってみたい人におすすめのサーバーはこのブログでも使っているスターサーバーです。WordPressブログ1つだけを運用するのであればライトプランが月330円から使えます。長期的に運用するのであれば月220円になりますし、複数のWordPressをインストールしたい場合はスタンダードプランに上げる必要はありますが、それでも440~660円とかなり安いです。

お金をかけたくなかったり、WordPressの環境を構築するのが面倒くさい人は無料のブログサービスを使いましょう。僕が昔使っていたはてなブログはコードのハイライト機能が備わっていて使いやすかったです。ファイルは画像しかアップできずファイルのアップローダーとしては使えないので、必要であれば別のサービスを使いましょう。

別の言語も勉強しよう

Excel VBAを使えばExcelの業務は何でも自動化できますが、逆にExcel以外は役に立ちません。つぶしの効く言語ではありません。

そこで他の言語にも挑戦してみましょう。もちろん「何を作りたいのか」という目的を明確にしましょう。その時は過去に自分が作った成果物を、別の言語で再現するというのがおすすめです。

実際に僕は今回例題にあげたカレンダー作成を、GASという言語で再現したことがあります。GASはGoogle スプレッドシートなどGoogleが提供しているアプリで使える言語で、VBAのGoogle版みたいなものです。

一度自分で作ったものを別の言語で作ると、同じ動作でも言語によって書き方の違いが明らかになってきます。逆に言えば、「こんにちは」を「ハロー」と言うのか「ニイハオ」と各国の言葉が違うだけで、プログラミングで実現できることに大きな差はないことがわかります。

特に前回の記事で説明した「=」「For」「If」の考え方はどの言語にも共通する部分が多く、これらを各言語でどう表すのかがわかれば、なんとなくコードを書けてしまいます。

3編に分けてお送りしました解説記事もこれにて終わりです。プログラミングの敷居が下がり、わからないことを調べるための最低限の知識を身につけていただけたのであれば幸いです。

コメント

タイトルとURLをコピーしました