エクセルで日付を扱うことが多いと思いますが、日付の計算って面倒だと思いませんか？
今日の日付から今月1日や月末日が一瞬で自動計算されて表示されたら便利だと思いませんか？
そこでDateSerial関数を使って自動計算させることで作業時間の短縮しましょう。
DateSerial(Year,Month,Day)で自由に日付を作成
このDateSerial関数を使う事により、年・月・日をそれぞれ別々に指定することができて、日付を作成してくれます。
DateSerial関数で設定できる年・月・日は下記の通りで、1つの項目でも範囲外の整数が指定されるとエラーになります。
| Year | 年を表し、整数で100～9999まで可能 |
| Month | 月を表し、整数で1～12まで可能 |
| Day | 日を表し、整数で1～31まで可能 |
実際にはこのような使い方はしませんが、コマンドボタン1に2001年1月1日という日付を作成して表示したいならば・・・
Private Sub CommandButton1_Click()
UserForm1.CommandButton1.Caption = DateSerial(2001, 1, 1)
End Sub
このように年・月・日を整数で入れれば完成します。
今日の日付から今月1日を取得する
先ほど説明したことをそのまま正直に受け取れば年・月・日は整数なので数字を入力を思ってしまいますが、言い方を変えると小数点にならなければいいのです。
という事で計算された値が整数ならば使用可能になるので、今日の日付を表すDate関数を使うことが可能になりますので、下記のようにDate関数を使えば今日の日付から今月1日を取得するのは簡単に出来上がります。
Private Sub CommandButton1_Click()
UserForm1.CommandButton1.Caption = DateSerial(Year(Date), Month(Date), 1)
End Sub
- 年・・・Year(Date)で今日の日付から年の部分を取得
- 月・・・Month(Date)で今日の日付から月の部分を取得
- 日・・・1日を取得したいので数字に「1」を記述
これで今月1日が今日の日付から自動的に取得できるようになります。
今日の日付から今月末の日付を取得する
今度は月末日を取得しますが今月1日のように月末日は月によって30日や31日、さらに2月は28日だったり、4年に1回うるう年があり29日になります。
そうすると1日のように日の部分を数字で記述することはできません。
そこで発想をちょっと変えて、今月末ではなく「来月1日の前日」というように考えれば、30日でも31日でもどちらでも関係なくなります。
ではVBAコードで記述するにはこの日本語のままVBAコードで記述すれば今月末日が取得できます。
Private Sub CommandButton1_Click()
UserForm1.CommandButton1.Caption = DateSerial(Year(Date), Month(Date)+1, 1)-1
End Sub
- 年・・・Year(Date)で今日の日付から年の部分を取得
- 月・・・Month(Date)で今日の日付から月の部分を取得して「+１」する
- 日・・・1日を取得したいので数字に「1」を記述
- -1・・・今月+１で来月の1日が取得されているので、「-1」で前日にする
これで、日本語的にちょっとややこしいですが、このブログを書いている今日の日付が2021年2月6日なので、DateSerial関数で2021年・2月+1で3月・1日の-1するので前日は2021年2月28日という事になり、見事今日の日付から今月月末日が取得できました。
指定した値から日付を作成
先ほどまでは今日の日付Dateを使用しましたが、任意の数字を組み合わせる為にセルの値から日付を作成することや、リストボックスやコンボボックスで選択されたリストの値から日付を作成することの方が多いと思いますので、この方法も使えるようにマスターしておきましょう。
指定したセルの値から日付を作成する
DateSerialの年・月・日をそれぞれセルの値を取得して日付を作成する方法で、ここでは下記のようにセルの値を指定し、出来上がった日付をセルD1に記載します。
- 年・・・セルA1
- 月・・・セルB1
- 日・・・セルC1
- 日付・・セルD1
Private Sub CommandButton1_Click()
Cells(1, 4) = DateSerial(Cells(1, 1), Cells(1, 2), Cells(1, 3))
End Sub
リストの選択値から日付を作成
上記画像のカレンダーのようにコンボボックスで選択された年・月から日付を作成する方法が一番実用的だと思いますが、この方法は慣れればさほど難しくないのですがVBA覚えたての頃はできませんでした。
その為にリストで選択した値を一旦セルへ記載してから再度取得する方法で対応していましたが、変数として格納するという方法を理解できるようになれば、この方法の方が簡単ですよ。
Private Sub CommandButton3_Click()
y = ComboBox1.Value
m = ComboBox2.Value
UserForm1.CommandButton1.Caption = DateSerial(y, m, 1)
UserForm1.CommandButton2.Caption = DateSerial(y, m + 1, 1) – 1
End Sub
- コマンドボタン3がクリックされたら
- 変数「y」はコンボボックス1の値を取得する
- 変数「m」はコンボボックス2の値を取得する
- ユーザーフォーム1のコマンドボタン1の表示文字はy年m月1日で日付表示する
- ユーザーフォーム2のコマンドボタン2の表示文字はy年m+1月1日の前日で日付表示する
- マクロ記録終了
このようにVBAで今月1日や月末が取得できるようになれば、自作カレンダーフォームを作る事もできるようになり、入力作業がはかどりますよ。
