お知らせこの記事は初心者用マクロ学習講座｜勉強範囲を限定＆12本の動画で早く効果を実感の関連記事です
一瞬で月末を取得するマクロを作ろう
シリーズ最後の記事になります。タイトルのとおり月末を一瞬で取得するのがメインテーマですが今まで勉強してきたことを出して勉強の総括をしましょう。
勉強はこれで終わりではないです。復習だけにならない様にここでも新しい要素を盛り込んでいきます。
- 最終行を取得する
- 月末を取得する
最終行の取得は色々なところで使えるコードです。仕事の終点を認識する事が出来るのでループの終点等に使う事が出来ます。
月末は仕事をやっている方は気になる日付です。締め日等何らかの区切りであることが多い為です。月末などの「特定日の取得」については非常に要望が多いのでここで紹介する事にします。
以上２点に加え今まで勉強してきたことを盛り込んだ動画を作りましたのでご覧ください。その後に要点を解説していきます。
変数、繰り返し処理、条件分岐などを盛り込んだ動画になっています
記事の難易度
３です。このシリーズでは最高に難しい内容になっています。
オンラインミーティング
この辺りまで来ると勉強出来る人が限られてきます。
裏を返せばここまで辿り着いた方ならもう一人で勉強出来ると思いますが配列などさらに難しい要素を勉強する為には分かる人に聞くのが近道です。ご検討ください。
前回記事
前の記事ワークシート関数の使い方VLOOKUPメソッド使用時はエラー対策が必須
この記事を読むとわかる事
実務ですぐに使える様な事が勉強できます。特に３番のForNextステートメントとIfステートメントを組み合わせて使えるようになると複雑な事ができる様になりますよ。
- リストの最終行が取得できる様になります
- n月の月末が取得できる様になります
- ForNextとIfを同時に使えるようになります
マクロ
２分動画
複数の日付に対して月末を取得する事が出来るコードを紹介しています
確認してもらいたい事
コードが複雑になりますので再生速度を調整して見てください。今回はコードやコメントのほかにコードの書き方を見ていただきたいです。
- ForNextステートメントやIfステートメントの書き方
- 文末のNextやEnd Ifを書いてから中にコードを書くようにしています
- インデントの使い方
- どこまでがループや条件分岐の対象なのか見た目で分かるようにしています
動画内のコードを解説
少し複雑ですのでいつも以上に丁寧に解説していきます。
素材の紹介
Excelには以下のような情報が並んでいます。
続いて動画内で使用しているコードです。VBEを展開し標準モジュールを挿入後以下情報を貼り付けていただくとそのまま使えるコードになっています。
シート側にも上記画像と同じ情報を用意してからコードを実行してみましょう。
コードはWクリックで選択出来ます。その後「Ctrl+C」でコピー、「Ctrl+V」で貼付けです。
Sub 月末を求める()
Dim r As Long
For r = 3 To Cells(Rows.Count, 2).End(xlUp).Row
If Not Cells(r, 3) = "" Then
Cells(r, 4) = DateSerial(Year(Cells(r, 3)), Month(Cells(r, 3)) + 1, 1) - 1
End If
Next
Range(Cells(2, 2), Cells(r - 1, 5)).Borders.LineStyle = xlContinuous
Range("B2").AutoFilter
End Sub
最終行の取得
最初の頃は最終行の取得方法はコードを暗記してください。
Cells(Rows.Count, 2).End(xlUp).Row というコードの中で2という数字が居ますね。ここは2列目の2です。つまりB列の最終行を数えるというコードです。
2列目を数えた理由は「最後まで値が居る」列だからです。世の中のリストの大半は一番左の列は番号などリストの外枠を決める情報であることが多いです。
よってリストの一番左にあるB列を数える為に2にしました。リストがC列から始まっていれば数字は3になります。エクセルの中で表の位置を変える事は少ないので定数で記入しています。
これだけ分かればあとは暗記して使えますね。・・・なんて言うと解説記事の意味がなくなってしまうのですがこのコードは難しいです。
割り切って暗記するか必要に応じて以降の解説を読んでください。ForNextステートメントとIfステートメントの話まで読み飛ばしても良いです。
コードを3分割する
解説に入ります。まずはコードを3つに分けます。
- Cells(Rows.Count,2)
- Cellsプロパティなのでどこかのセルを指定します
- Rows.Countプロパティで行を全部カウント（数える）します
- .End(xlup)
- 領域の端にあるRangeオブジェクトを返します
- 探索方法は引数にあるxlupです
- .Row
- 行番号を返します
- ここで返ってくる行番号はLong型です 変数もLong型で構えるのが良いです
この3つです。
Cells（Rows.Count,2)
ここで求めているセルはCells(1048576,2)です。Range(“B1048576”)です。理由を説明します。
CellsプロパティはRangeオブジェクトを返す役割があります。Cells(1,1)はRange(“A1”)です。このことからCells(Rows.count,2)を考えるとB列のどこかのセルという事になります。
Rows.Countは「行を数える」です。文字通りB列にある行を全部数えています。
どの行を数えても行の数は一緒なのですが次のコードとの関連を考えてリストの最終行を取得したい列の行を数える必要があります。
数えると1,048,576になります。これが「Excelの底」です。以下画像をご覧ください。
ではこれをCells～のコードに当てはめましょう。Cells(1048576,2)になります。Range(“B1048576”)が返ってくることになります。
.End(Xlup)
1つ前のコードでRangeオブジェクトが指定されました。Rangeオブジェクトに続くEndプロパティについて考えます。
このプロパティもRangeを返すので結論から申し上げますとどこかのセルを指定します。ここでセルB16を選択しています。
Range(“B1048576”)からXlup（1行目に戻る方向）で終端のセルを取得するというのがEndプロパティの役割です。これでセルB16が特定されます。
よって「最終行を取得する」というより「データの端を取得する」という方が合っているのかもしれません。
.Row
ここに至るまでのコードはずっとセルの特定をしています。最後に.Rowプロパティで行番号を特定します。先程指定したセルB16の「16」だけ取り出すというのがこのプロパティのお仕事です。
この16を変数rに代入する事で「rは16まで変化する」というコードが成立します。
ForNextステートメントとIfステートメントの組み合わせ
ここではForNext、Ifの両ステートメントが目立つのですが２つの機能をうまく組み合わせているのは変数です。
変数と各ステートメントが連動して動く事で複数のセルに対して仕事ができる環境を作っています。
変数
変数「r」をLong型で定義します。理由はForNextステートメントで使う変数とIfステートメントで使うCellsプロパティの引数は数値だからです。
変数についてはこちらの記事をご覧ください。
ForNextステートメント
ForNextステートメントは指定列の最終行までループを繰り返します。
そのループ内にIfステートメントを組み込む事で各セルに対し順番にアプローチできる環境を作っています。
変数rはループを構成する変数でもありながらセルの行を指定する変数になっています。これでループが進むと行も1つずつ下に降りていくというコードが作り出されています。
ループの設定方法と抜け方
ForNextステートメントを紹介した記事で説明していない事がありますので紹介しておきます。ループの設定方法と抜け方について書いておきます。
ループの設定方法
まずは設定方法です。ここではFor～句で「変数rは3から最終行の行番号16まで変化する」という様に設定しました。
このことからFor～句ではループの変化量や方法を設定するパートだという事が言えます。
もう少し情報を加えます。以下コードはFor～句の最後に「Step 2」というコードが付いています。これは変数rを3、５、７～という様に値を2ずつ変化させる事ができるコードです。
For r = 3 To Cells(Rows.Count, 2).End(xlUp).Row Step 2
If Not Cells(r, 3) = "" Then
Cells(r, 4) = DateSerial(Year(Cells(r, 3)), Month(Cells(r, 3)) + 1, 1) - 1
End If
Next
ここでも言える事はやはりFor～句がループの変化量や方法を管理しているという事です。Step２の話はここで終了です。「こんなことも出来ますよ」という話題でした。
ループの抜け方
続いてループの抜け方の話です。Next句が担当しています。変数がFor～句で指定した条件の範囲内かどうかを都度見ています。
今回の事例をもとに具体的に書くと「変数rが3から16の範囲内であればループに入り変数が17になった時にはループから抜ける」様にコードを監視しています。
画像に情報をまとめてみました。Nextがステートメント内のコードの動きをコントロールをしている事が分かりますね。
Ifステートメント
If、Then、End if を使う型です。Elseは必要に応じて使う事になりますので無くても大丈夫です。ここまでは以前紹介した内容と同じですが今回はIfの後に「Not」が居ます。
If Not Cells(r, 3) = "" Then
Cells(r, 4) = DateSerial(Year(Cells(r, 3)), Month(Cells(r, 3)) + 1, 1) - 1
End If
「Not」はセルに直書きする関数と同じように条件を否定する効果があります。ここではCells(r,3)=””を否定する条件になるので「指定のセルが空欄ではない時」という条件が設定されます。
月末の取得
結論から申し上げますと「検索対象の日付の翌月を取得してから一日戻る」という方法で月末を取得しています。
考え方
月末というのは人が決めて区切ったものです。当たり前ですが日付は連続で続いています。という事はn月末日はn+1月の初日と隣り合わせだという事ですね。
次に「なぜそんな方法で月末を取得するのか？」について解説します。「比較的簡単な方法だから」というのが主な理由です。
月毎に末日は違いますよね。加えてうるう年なんていうイレギュラーな日付もあります。これを規則的に取得しようとすると別にリストを作って都度照合・・・という事になり大変です。
代わって月初はどうでしょうか？どの月も1日ですよね。ここで規則性を発見する事ができました。毎月1日から始まるのでまずはn+1月の初日を取得し1日戻ればn月の月末になりますよね。
これを使えば月末を求める事ができます。あとはどうやってコードに落とし込むのかを考えていきましょう。
コード
メインはDateSerial関数です。マイクロソフトさんのリンクを付けておきました。
DateSerial(year, month, day)
https://learn.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/dateserial-function
引数として年、月、日を与えると日付として返してくれるという関数ですね。n月の月末が欲しいのでこのように関数を組んでみましょう。
- 年、月はC列の情報を使う（前提条件としてセル内の情報が日付の書き方であること）
- 月に1を足す（これでn月＋1月にしている）
- 日はデフォルトで1にする（月の初日が欲しいから）
- DateSerial関数の引数のかっこを閉じてから1を引く
これでn月の月末が取得できました。あとはD列に書き入れるだけです。
コードの書き方
動画の前にもお知らせしましたが書き方を見てほしいなと思っています。
- ForNextステートメント、Ifステートメント共に最後のNext、End Iを先に書く
- インデントを使う
細かい事ですが重要なので解説していきます。
Next、End Ifを先に書く
ステートメントの大枠を書いた後にさらに間にコードを書くようにしています。理由はNext、End Ifが無いとエラーになる為です。
例えば以下のようなコードがあるとします。上からコードを書いていくとEndIfやNextを書く際にどのFor～句に対するNextなのかが良く分からなくなります。
だから先に書いておくことで書き忘れを防止します。For～句とNextの組み合わせが1つずつにならないとエラーになるので気を付けてください。
For s = 1 To 10
For t = 1 To 2
If Cells(2, 2) = Cells(s, t) Then
Cells(s, t) = "同じ"
End If
Next
Next
インデントを使う
見易さの向上を目的にインデント（字下げ）を行いましょう。動画の中ではコードを選択して一括で字下げしています。コードを選択後Tabキーを押すと字下げが出来ます。
インデントを使わないコードを見ていただきます。これは直ぐ上にあるコードと同じ事が書いてあるのですがインデントが無いと見辛いです。
For s = 1 To 10
For t = 1 To 2
If Cells(2, 2) = Cells(s, t) Then
Cells(s, t) = "同じ"
End If
Next
Next
End IfやNextがどこにかかっているのか分かりにくいです。自分で分からないという事は他人が読むとさらに分かりません。「見易さを考える」のも勉強の中に含めましょう。
まとめ
最後なので詰め込んだ感があります。1回読むだけでは難しいので動画も見ながら何回か読み返して下さい。
ここまで出来れば初心者卒業です。おそらく今なら書店に行って本を読んだら分かる様になっているはずです。試してみてください。
- リストの最終行の取得方法
- 日付から月末を取得する方法
- ForNextとIfステートメントの組み合わせ方
マクロ
あとがき
これで「VBA歴7年のオンライン講師が25分の動画でマクロを解説」シリーズは終了となります。
今回の記事は私がオンラインミーティングで複数の方と勉強してきた中で足りないなと感じたところを補う為に用意しました。
足りないと感じたのは「予習、復習をせずにオンラインミーティングだけは参加される」という人に助け船を出せなかった事です。
もちろんしっかりやっていただく方が大半でこちらとしては嬉しい限りでした。ありがとうございました。
しかし何人かの受講者様からは「オンラインミーティング時は分かるけど翌日自分でやってみるとうまくいかない」というコメントもいただいています。
でも続けないとさらに分からなくなるので・・・という気持ちもありオンラインミーティングだけは出続けようと考えた方も居る様でした。
もちろんそれは「分かったつもりだった」という事なのでその人の責任なのですが復習してなんとかVBA習得に結びつけることが出来るかどうかは私の責任でもあると思っています。
うまくいってない方にさらに話を聞いてみると「予習、復習をしない」という事が分かってきました。
という事は予習復習をサラっとできる様になれば勉強が続くのではないかと考えました。そこで動画を用意するに至ったという次第です。
動画は意図的に短く作っています。動画だけでは全部は理解出来ないようにしていますがやろうとしている事は伝わるような構成にしたつもりです。
そこをミーティングや記事で補うような活動にする事でより多くの人が勉強できるのではないかと考えました。
まだ完全ではないのでこれから色々な方とミーティングする中で記事を見直しより良いものにしていく活動を続けていきます。
不明点や質問がある方はオンラインミーティングを活用ください。お疲れ様でした。
リンクの提供をいただきました
本シリーズで勉強を終えた方はこちらのブログを紹介させていただきます。
配列やユーザーフォームなどより実務で活躍する為に必要な機能を学ぶことができます。
