【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する


calendar

photo credit: dlkautz Turning the Corner via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

初心者向けエクセルVBAのシリーズとして請求データから請求書を作るマクロの作り方についてお伝えしています。

前回の記事はこちら。

【初心者向けエクセルVBA】入力ダイアログを表示するInputBoxメソッドの使い方
初心者向けエクセルVBAで請求データから請求書を作成するシリーズです。今回はInputBoxメソッドを使った入力ダイアログの使い方です。引数や引数名について、またInputBox関数との違いについても解説します。

InputBoxメソッドを使って、入力ダイアログ集計対象の年月を入力してもらうという処理を追加しました。

それで、対象の年月が変わるということは、「請求日」や「お支払い期限」も変わるということですよね。

ということで、今回はエクセルVBAで日付データから月末日または翌月末日を自動算出する方法についてお伝えします。

なお、この記事は以下のYouTube動画と連動していますので、合わせてご覧ください。

【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する

では、行ってみましょう!

前回のおさらいとお題

前回作成したプログラムはこちらです。

Sub 請求書作成()

'初期処理
wsTemplate.Rows("21:50").Hidden = False '隠れているかもしれない行を再表示
wsTemplate.Range("A21:C50").ClearContents

'メイン処理
Dim rowsData As Long '行数カウント用の変数
rowsData = wsData.Cells(Rows.Count, 1).End(xlUp).Row '最後の行数を取得

Dim dayCutoff As Date
dayCutoff = Application.InputBox("年月を入力してください", "対象年月を入力", Format(Date, "yyyy/mm"))

Dim i As Long, k As Long
k = 21
For i = 2 To rowsData
    Dim deliDate As Date
    deliDate = wsData.Cells(i, 1).Value
    If Year(deliDate) = Year(dayCutoff) And Month(deliDate) = Month(dayCutoff) Then
        wsData.Range(wsData.Cells(i, 2), wsData.Cells(i, 4)).Copy wsTemplate.Cells(k, 1)
        k = k + 1
    End If
Next i

'終了処理
wsTemplate.Rows(k & ":50").Hidden = True  'データがない行を隠す
wsTemplate.Range("A18").Value = "ご請求金額:" & Format(wsTemplate.Range("D54").Value, "#,##0") & " 円"

wsTemplate.Select
wsTemplate.Range("A21").Select

End Sub

以下の「請求データ」シート(wsData)から

請求データに納品日の列が追加された

入力ダイアログで指定した年月のデータのみを、隣の「請求書ひな形」シート(wsTemplate)に転記するというものです。

請求日とお支払期限も変更したい

しかし、現時点では「請求日」と「お支払期限」が、ベタ打ちなので、都度変更をする必要があります。

例えば入力ダイアログで「2017/12」として、2017年12月のデータを集めたとしても、以下のようになってしまうのです。

エクセルVBAで請求日とお支払期限を変更する必要がある

この課題を今回解決していきますね。

DateSerial関数で日付を生成する

VBAでは年、月、日をそれぞれ整数で与えることで日付を生成するDateSerial関数という関数があり、今回はこれを使います。

書式はこちらです。

DateSerial(年, 月, 日)

例えば、イミディエイトウィンドウで他のコマンドを入力して Enter してみましょう。

? DateSerial(2018, 2, 5)

すると以下のように、日付が生成されて出力されます。

エクセルVBAのDateSerial関数で日付を生成する

月末日の日付データを生成する

しかし、今回は月末日が欲しいので、「日」として指定すべき整数が、月によっては31だったり、30だったり、はたまた28だったりするわけです。

実はこれを解決する裏技があります。

「日」の値を、以下のように「0」とするのです。これで指定した年月の「前月の」月末日を日付データとして取得できます。

DateSerial(年, 月, 0)

つまり、以下のようにイミディエイトウィンドウで実行すると

? DateSerial(2018, 2, 0)

以下のように、2018年1月の月末日が出力されるわけです。

エクセルVBAのDateSerial関数で月末日を取得する

請求日とお支払期限を日付として生成する

今回、ユーザーが入力ダイアログで入力してくれた日付はdayCutoffという変数に格納されます。

それで、請求日とお支払期限は以下のように求めることができます(月末締め翌月末払いの場合)。

wsTemplate.Range("D15").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 1, 0) '請求日
wsTemplate.Range("D16").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 2, 0) 'お支払期限

この命令を27行目の後あたりに挿入して実行すると、以下のようにバッチリ請求日とお支払期限を自動算出することができます。

エクセルVBAで請求日とお支払期限を自動で算出した

まとめ

以上、エクセルVBAで日付データから月末日と翌月末日を自動算出する方法についてお伝えしました。

まとめのコードはこちらです。

Sub 請求書作成()

'初期処理
wsTemplate.Rows("21:50").Hidden = False '隠れているかもしれない行を再表示
wsTemplate.Range("A21:C50").ClearContents

'メイン処理
Dim rowsData As Long '行数カウント用の変数
rowsData = wsData.Cells(Rows.Count, 1).End(xlUp).Row '最後の行数を取得

Dim dayCutoff As Date
dayCutoff = Application.InputBox("年月を入力してください", "対象年月を入力", Format(Date, "yyyy/mm"))

Dim i As Long, k As Long
k = 21
For i = 2 To rowsData
    Dim deliDate As Date
    deliDate = wsData.Cells(i, 1).Value
    If Year(deliDate) = Year(dayCutoff) And Month(deliDate) = Month(dayCutoff) Then
        wsData.Range(wsData.Cells(i, 2), wsData.Cells(i, 4)).Copy wsTemplate.Cells(k, 1)
        k = k + 1
    End If
Next i

'終了処理
wsTemplate.Rows(k & ":50").Hidden = True  'データがない行を隠す
wsTemplate.Range("A18").Value = "ご請求金額:" & Format(wsTemplate.Range("D54").Value, "#,##0") & " 円"
wsTemplate.Range("D15").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 1, 0) '請求日
wsTemplate.Range("D16").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 2, 0) 'お支払期限

wsTemplate.Select
wsTemplate.Range("A21").Select

End Sub

日付を生成するDateSerial関数の使い方と、「日」を0にすることで前月の月末日を求める方法がポイントでしたね。

ぜひ、マスターくださいませ。

次回ですが、取引先ごとに別シートに請求書を作成する方法についてお伝えしていきます。

【初心者向けエクセルVBA】ワークシートをコピーする方法とそのシート名を変更する方法
初心者向けにエクセルVBAで請求データ一覧から請求書を自動で作成する方法をお伝えしています。今回はワークシートをコピーする方法、Activesheetの使い方、シート名を変更する方法についてお伝えします。

どうぞお楽しみに!

連載目次:データ一覧から請求書を自動で作る

お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。
  1. 【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
  2. 【初心者向けエクセルVBA】ワークシートをオブジェクト名で取り扱う方法
  3. 【初心者向けエクセルVBA】For~Next文で簡潔にプログラムを書く
  4. 【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
  5. 【初心者向けエクセルVBA】文字列の連結&Format関数での書式変更
  6. 【初心者向けエクセルVBA】セル範囲を一気にまとめてコピーする方法
  7. 【初心者向けエクセルVBA】ワークシートのデータのある範囲だけをピッタリ取得する方法
  8. 【初心者向けエクセルVBA】セル範囲の平行移動をする方法・リサイズをする方法
  9. 【初心者向けエクセルVBA】日付データから年・月・日を取り出す
  10. 【初心者向けエクセルVBA】If~Thenを使った条件分岐の超入門
  11. 【初心者向けエクセルVBA】For~Next文でセル範囲を一行ずつ移動させる
  12. 【初心者向けエクセルVBA】セル範囲のクリア~ClearContentsメソッドとClearメソッド
  13. 【初心者向けエクセルVBA】ワークシート・セルを選択する方法の色々について
  14. 【初心者向けエクセルVBA】入力ダイアログを表示するInputBoxメソッドの使い方
  15. 【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する
  16. 【初心者向けエクセルVBA】ワークシートをコピーする方法とそのシート名を変更する方法
  17. 【初心者向けエクセルVBA】オブジェクトを変数にセットして取り扱う方法
  18. 【初心者向けエクセルVBA】Openメソッドで新たなブックを開く方法
  19. 【初心者向けエクセルVBA】現在マクロを書いているブックのフォルダパスを取得する
  20. 【初心者向けエクセルVBA】開いたブックとそのワークシートをオブジェクト変数にセットする
  21. 【初心者向けエクセルVBA】ワークブックを別名で保存して閉じる方法
  22. 【初心者向けエクセルVBA】取引先別に請求書を作成するマクロを作る

コメント

  1. icyblue より:

    こんにちは。
    初心者向けVBA講座をいつも見させて頂いています。要点がまとまっていて凄く分かりやすくスムーズに進められます。
    市販の初心者向けの書籍も読んだりしたのですが、いちいち事細かに説明があって、まどろっこしく感じたり、逆に大事なことが分かりにくく感じていました。

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