エクセルVBAでDateSerial関数で今月の日付を取得してワークシートに入れる方法

こんにちは～。「デザインもできるシステムエンジニア」のあつもり(@atumori17)です。今日も業務効率化のアイデアを発信していきますっ！

業務でエクセルを使っていると日付を入れたいケースがよくありますよね。例えばスケジュール表を作っているときなどです。

セルに1,2と入力して、2つのセルを選択してドラッグすれば簡単に入力することができます。ただし月末の日にちを調べなければならないし、曜日を入れたい場合などは少し手間がかかります。

そこで今回から日付や曜日を自動でエクセルシートに入力するツールを作っていきます。第1回目は「DateSerial関数で今月の日付を取得してワークシートに入れる方法」を紹介します。

1 今月の日付を取得してワークシートに入れるプログラムの作成
2 プログラムコードの解説
3 今月の日付を取得してワークシートに入れるプログラムの動作確認
4 まとめ

今月の日付を取得してワークシートに入れるプログラムの作成

ワークシートにボタンを追加する

「今月の日付を入力.xls」という名前のファイルを作りデスクトップに保存します。

ワークシートに起動ボタンを配置します。

作った起動ボタンに下記のようなプログラムコード書きます。書く場所はシートモジュールです。

Private Sub CommandButton1_Click()
    Load UserForm1
    UserForm1.Show vbModeless    
End Sub

Private Sub CommandButton1_Click()

Load UserForm1

UserForm1.Show vbModeless

End Sub

ユーザーフォームの作成

新たにユーザーフォームを作成し「日付入力」ボタンを配置します。

サンプルコードを貼り付ける

「日付入力」ボタンをダブルクリックしてコードウィンドウを開きます。「CommandButto1_Click()」の下に以下のコードを貼り付けます。

Private Sub CommandButton1_Click()

    Dim monthStart As Long  '月初の日付
    Dim monthEnd As Long    '月末の日付

    monthStart = 1
    monthEnd = Day(DateSerial(Year(Date), Month(Date) + 1, 0))

    Dim tmp As String

    'キャンセルが押された場合は処理を終了する
    On Error GoTo myError
    tmp = Application.InputBox("列または行を選択して下さい", "列か行の選択", Type:=8).Address
    On Error GoTo 0

    Range(tmp).Select
    
    Dim y As Long
    Dim x As Long
    Dim col As Long '選択した列番号
    Dim row As Long '選択した行番号
    
    '列が選択されていた場合
    If Selection.Address = Selection.EntireColumn.Address Then
        
            col = Selection.Column
            
            For y = monthStart To monthEnd
                Cells(y, col).Value = y
            Next
    
    Else
        '行が選択されていた場合
        If Selection.Address = Selection.EntireRow.Address Then
            
            row = Selection.row
    
            For x = monthStart To monthEnd
                Cells(row, x).Value = x
            Next
        End If
    End If
    
myError:

End Sub

Private Sub CommandButton1_Click()

Dim monthStart As Long '月初の日付

Dim monthEnd As Long '月末の日付

monthStart = 1

monthEnd = Day(DateSerial(Year(Date), Month(Date) + 1, 0))

Dim tmp As String

'キャンセルが押された場合は処理を終了する

On Error GoTo myError

tmp = Application.InputBox("列または行を選択して下さい", "列か行の選択", Type:=8).Address

On Error GoTo 0

Range(tmp).Select

Dim y As Long

Dim x As Long

Dim col As Long '選択した列番号

Dim row As Long '選択した行番号

'列が選択されていた場合

If Selection.Address = Selection.EntireColumn.Address Then

col = Selection.Column

For y = monthStart To monthEnd

Cells(y, col).Value = y

Else

'行が選択されていた場合

If Selection.Address = Selection.EntireRow.Address Then

row = Selection.row

For x = monthStart To monthEnd

Cells(row, x).Value = x

End If

myError:

End Sub

プログラムコードの解説

それではプログラムコードの解説をしていきます。重要なポイントに絞って説明していきます。InputBoxから列番号を取得する方法は過去の記事を参考にしてみてください。

エクセルVBAでセル内の文字列に含まれる改行を一括で削除する方法

ワークシートのセル内にある改行を消したいって思ったことありませんか？データを別のアプリケーションに取り込んだり、データベースに登録したりするときに改行が邪魔になることがあります。そんな時に改行を一括で削除できるツールがあったら便利ですよね。

asatte.biz

2017-06-24 08:36

プログラム全体の流れは以下のようになります。

今月の月末の日にちを取得する
処理対象の列番号または行番号を取得する
行か列どちらが選択されたかを調べる
セルに日付を入力する

DateSerial関数で月末の日付を取得する

DateSerial(year,month,day)

DateSerialは引数に指定した年、月、日に対応する日付を返す関数で、バリアント型 (内部処理形式 Date の Variant) の値を返します。例えば、

myDate =DateSerial(2017,10,29)とした場合、myDateには2017/10/29が入ります。

このDateSerial関数なんですが便利な使い方があります。それは引数dayに0を指定すると、先月の月末の日付を取得できるところです。つまり

myDate =DateSerial(2017,10,０)とした場合、myDateには先月の月末である2017/9/30が入ります。

ただし注意したいのが取得するのは先月の月末であるという点です。今回は今月の月末を取得したいので引数monthを+1します。

Date関数で現在の日付を取得する

Date関数はシステムの日付を取得します。システムの日付なのでパソコンの時刻が狂っていると間違った日付を取得してしまう点に注意してください。

Date

現在の日付から年、月、日を取得する

Year関数、Month関数、Day関数は指定した日付から年、月、日をバリアント型で返します。

Year(日付),Month(日付),Day(日付)

今回の場合、DateSerial関数の引数にYear関数、Month関数で取得した年、月の値を入れています。DateSerial関数が返す値は年月日ですから、最後にDay関数で日付だけの値にして、変数monthEndに入れています。

全体のコードでは7行目になります。

        monthEnd = Day(DateSerial(Year(Date), Month(Date) + 1, 0))

1	monthEnd = Day(DateSerial(Year(Date), Month(Date) + 1, 0))

EntireColumnプロパティで列かどうかを調べる

EntireColumnプロパティを使うと指定したセルの列全体を取得することができます。

オブジェクト.EntireColumn

このことを利用して選択されたオブジェクトが列かどうかを調べることができます。

次のようにして選択されたオブジェクトの番地が列であれば、If以下の処理を実行するようにしています。全体のコードでは23行目になります。

If Selection.Address = Selection.EntireColumn.Address Then

1	If Selection.Address = Selection.EntireColumn.Address Then

次に列でなかった場合に今度は行であるかを調べるためにEntireRowプロパティを使用します。

オブジェクト.EntireRow

全体のコードでは33行目になります。

If Selection.Address = Selection.EntireRow.Address Then

1	If Selection.Address = Selection.EntireRow.Address Then

選択されたオブジェクトが列か行かがわかったら、日付を入れます。開始日の1から月末の日付までループします。

For y = monthStart To monthEnd
　　Cells(y, col).Value = y
Next

For y = monthStart To monthEnd

　　Cells(y, col).Value = y

選択されたオブジェクトが列でも行でもない場合、つまりセルが選択された場合は日付を位列処理は実行しません。

プログラムコードの解説は以上です。

今月の日付を取得してワークシートに入れるプログラムの動作確認

それでは実際に出来上がったプログラムを動かしてみましょう。

ワークシートの起動ボタンを押します。

日付入力ボタンをクリックして、列を選択しOKボタンをクリックします。

今月の日付が入力されました。

行を選択した場合はこんな感じになります。

まとめ

いかがでしたか？今回の内容をまとめます。

DateSerial関数を使うと月末の日付を取得できる
Date関数で現在の日付を取得できる
EntireColumnプロパティで列かどうかを調べることができる

以上で「DateSerial関数で今月の日付を取得してワークシートに入れる方法」でした。おつかれさまでした～。

その使い方だと損してます！

会社員、個人事業主のかたへ。

無料で登録できるAmazonビジネスアカウントを知ってますか？

何と言っても便利なのが請求書払いによる後払いができるということ。

また、承認ルールを作成すれば、業務上必要な物だけに購入を制限することができます。

さらに多くの商品にビジネスアカウント価格が設定されており、個人アカウントより安く購入できます。

このように個人アカウントと比べてビジネスアカウントはお得な機能や特典がついています。

無料で登録できるので、会社員、個人事業主なら使ったほうが得なのは言うまでもありません。

Amazonビジネスの無料登録はこちら