ゆんの業務改善ブログ

①生産性向上 ②業務改善 ③自動化　について情報発信しています。VBAプログラムは本当の初心者から他のアプリケーションを呼び出して使う上級者的な使い方まで幅広いレベルで解説していきます。

トップ > VBAの勉強 > VBA Dateserial関数とFormat関数を使って日付を自在に操る

VBA Dateserial関数とFormat関数を使って日付を自在に操る

VBAの勉強

このブログの記事一覧にもどる

今回はDateserial関数を使って日付を自由に操ってみたいと思います。

この記事は初級～中級です。
レベルについてはExcel VBAの実力（レベル）を定義してみる　初心者～三段をご参照ください。

目次

DateSerial関数の書き方

DateSerial関数の文法は下記の通りです。

DateSerial関数の書き方
DateSerial関数の書き方

試しに実行してみます。

Sub DateSerial関数を使う()

    Debug.Print DateSerial(2019, 11, 19)

End Sub

実行結果は下記の通り。

DateSerialに数値をベタ打ちした結果
DateSerialに数値をベタ打ちした結果

これだけ見ると、数値を「/」で連結しただけのように見えますね。しかし、Dateserial関数は非常に強力な関数です。

DateSerial関数のメリット

計算値を入れられる

DateSerial関数の最大のメリットは年、月、日の値に計算値を入れられることです。日付の計算が足し算引き算で求めることができると言うのは非常にありがたいことです。具体例を見ます。

Sub DateSerial関数のメリット1()

    Debug.Print DateSerial(2019, 11, 19 + 3)

End Sub

3日後を計算するなら日付の所に3を足せば良いです。3ヶ月後なら月の所に3を足せばいいですし、3年後なら年の所に3を足せば良いです。そして、閏年やその月が何日あるかを正確に反映した数値を表示してくれます。

3年後のきょうまで何日あるか計算して、3年後のきょうが何曜日か計算してみましょう。

Sub DateSerial関数のメリット2()
    
    Dim 三年後のきょう As Date
    
    三年後のきょう = DateSerial(2019 + 3, 11, 19)
    
    Debug.Print Format(Weekday(三年後のきょう), "DDDD")
    Debug.Print 三年後のきょう - Date & "日"

End Sub

実行結果は下記の通りです。

実行結果
三年後のきょうの日付と何日あるかを計算した

Weekday関数は日曜日を1、月曜日を2、・・・土曜日を7と曜日を数値で返す関数です。その数値をFormat関数の第二引数を”DDDD”にする事によって、英語で「Saturday」と表示させました。このように、DateSerial関数は日付の表現の仕方を変更することができるFormat関数を組み合わせることにより、表現の幅を広げることができます。

0やマイナスが使える

日付の所の数値が1なら１日ですよね。0にするとどうなるでしょうか。

Sub DateSerial関数のメリット3()
    
    Debug.Print DateSerial(2019, 11, 0)
    Debug.Print DateSerial(2019, 12, 0)
    Debug.Print DateSerial(2020, 0, 0)
    
End Sub

実行結果
月や日の値に0を入れた結果

DateSerial(2019, 11, 0)の結果が、2019/10/31となりました。DateSerial(2019, 11, 1)が2019/11/1なので、そうきたか！って感じですね。Debug.Print DateSerial(2019, 12, 0)は2019年11月の末日が返ります。

DateSerial(2020, 0, 0)も2019/11/30が返りました。2020年0月ということは2019年12月と言うこと。その0日は2019年11月末日なので、こちらも2019/11/30 が返りました。

もちろんマイナスの数字も入れることができます。○ヶ月前も取得できるんですね。便利。これらをうまく組み合わせると前月1日から前月末までのデータの集計と言った事も可能です。

これらを踏まえて、以下、実務的なサンプルコードを示します。

DateSerial関数とFormat関数を組み合わせた実務的なサンプルコード

DateSerial関数が強力な関数である事が実感できたでしょうか。ここからはFormat関数と組み合わせて実務的なサンプルコードを示したいと思います。

今回やりたいこと：前月1日と前月末の日付を8桁の数値で取得する

日々、更新されるデータであれば、本日の日付をつけたファイル名が付けられていることがあります。例えば「販売実績20191119.xlsx」のような感じです。日付をYYYYMMDDの8保存しておくのはよく見る例ですね。このように日付を8桁にしてファイル名として保存しておくことによって、「最新のファイルを開く」や「今月のファイルをすべて開く」と言った作業もしやすくなるのでおすすめです。

今回は「販売実績20191119.xlsx」の様なファイル名の月初から月末までのデータを全て取得する為に、前月の1日と前月末の日付を8桁で表した数値を取得します。

それでは実務的なサンプルコードです。

Sub DateSerial関数とFormat関数を組み合わせた実践的なコード()
    
    Dim 前月year As String
    Dim 前月month As String
    Dim 前月1日day As String
    Dim 前月末day As String
    
    前月year = Year(DateSerial(Year(Date), Month(Date) - 1, Day(Date)))
    前月month = Format(Month(DateSerial(Year(Date), Month(Date) - 1, Day(Date))), "00")
    前月1日day = Format(Day(DateSerial(Year(Date), Month(Date) - 1, 1)), "00")
    前月末day = Format(Day(DateSerial(Year(Date), Month(Date), 0)), "00")
    
    Debug.Print "前月1日は"; 前月year & 前月month & 前月1日day
    Debug.Print "前月末日は"; 前月year & 前月month & 前月末day
    
End Sub

実行結果
前月1日と前月末日の日付が8桁で取得できた

無事に取得できました。簡単に解説します。

まず、日付を数値の8桁で表示する為にFormat関数の第二引数を”00”とすることによって月や日の値が一桁の場合に0で埋めて2桁にするようにしました。

さらに年、月、日のそれぞれの値を格納する変数をString型で宣言しました。こうしないと、せっかくFormat関数で0埋めをしても、変数に入れた瞬間に0埋めしていない普通の数値の値になってしまうためです。

年、月、日のそれぞれの値を求めるところは複雑に見えますが、よく見るとそれほど複雑ではありません。一番内側にDateSerial関数があって、そこで前月の年月日をYYYY/MM/DDで取得しています。そのYYYY/MM/DDに対してYear関数、Month関数、Day関数でそれぞれ、年の値、月の値、日の値を求めています。最後に0埋めをしています。

あとは単に結合するだけですね。

これで「販売実績20191001」のような日付をファイル名につけることができるようになりました。もし、過去の実績を一つのフォルダにまとめて保存しているのであれば、フォルダ内をループで検索して、ファイル名から「販売実績」を除いた8桁の数値部分の大小を比較する事によって、○月のファイル全部を取得する、と言った事も可能ですね！

＜お知らせ＞
Excel VBAでクラスやオブジェクトの概念と使い方を丁寧に解説し、ワンランク上の実力を目指すガイドを書きました。この本で本物の実力を身に付けて一皮むけてみませんか？
books.rakuten.co.jp