Create Monthly Business Report Format

はじめに

エクセルで業務報告書の書式を作って、毎日の報告書作成業務をおこなっている会社は多いと思います。
しかし、報告書作成業務はルーティンワークであり、できるだけ手間をかけたくないのではないでしょうか。
私の会社でも、時間を見つけては、手作業で書式のシートをコピーをして日付を記入していました。
それは時間のムダだと思ったので、マクロを作りました。
ボタンを押せば、自動的に1か月の営業日数分の報告書の書式を作ってくれるマクロです。
報告書の書式をコピーして1か月分を作るのですが、その書式は何でもかまいません。ファイルの中にあるシートをご自分の使っている書式と差し替えて、ご利用ください。

・動作は無保証です。
・動作確認は、Windows 10 + Excel 2016でおこなっています。
・ファイルはここからダウンロードしてください。
・「業務報告書」シートは、「bizocean」さんの「Excelで作るA41枚の業務日報」を使わせていただきました。
・「Holiday」シートの「祝日一覧」は「総務で使えるエクセル残業計算」さんの「番外編／１００年分の祝日リスト（山の日対応版）」を使わせていただきました。

1カ月分の業務報告書の書式を作成するマクロのソースコード

Option Explicit

Sub S_CreateMonthlyBusinessReportFormat()
    Application.ScreenUpdating = False

    On Error Resume Next
    
    Dim ProgramWS As Worksheet
    Set ProgramWS = Worksheets("プログラム")
    
    If ProgramWS Is Nothing Then
        MsgBox "「プログラム」ワークシートがありません", vbCritical
        Exit Sub
    End If
    
    On Error GoTo 0
    
    With ProgramWS
        If .Cells(1, 1).Value <> "" Then
            Dim myYear As Long: myYear = .Cells(1, 1).Value
        Else
            MsgBox "年を入力してください。", vbExclamation
            Exit Sub
        End If
        
        If .Cells(1, 3).Value <> "" Then
            Dim myMonth As Long: myMonth = .Cells(1, 3).Value
        Else
            MsgBox "月を入力してください。", vbExclamation
            Exit Sub
        End If
        
        Dim BusinessDays As Long: BusinessDays = .Cells(1, 6).Value
    
        Dim i As Long
        For i = 1 To BusinessDays
            Dim myDate As Date
            myDate = .Cells(i + 2, 2).Value
            
            Dim WS_Count() As Date
            ReDim Preserve WS_Count(1 To i)
            WS_Count(i) = myDate
        Next i
    End With
    
    On Error Resume Next
    
    Dim OriginalWS As Worksheet
    Set OriginalWS = Worksheets("原本")
    
    If OriginalWS Is Nothing Then
        MsgBox "「原本」ワークシートがありません", vbCritical
        Exit Sub
    End If
    
    On Error GoTo 0
    
    For i = BusinessDays To 1 Step -1
        OriginalWS.Copy After:=ProgramWS
        
        With ActiveSheet
            .Name = CStr(Format(WS_Count(i), "mm.dd"))
            .Cells(4, 2).Value = WS_Count(i)
        End With
    Next i
    
    Set OriginalWS = Nothing
    Set ProgramWS = Nothing
    
    Application.ScreenUpdating = True
    
    MsgBox "今月分シート一括作成終了！", vbInformation
End Sub

エクセルファイル・シートの構成

ブックやシート、セルを操作するプログラムでは、ユーザーがおこなう操作に対してエラー回避策をほどこすことが必要です。
ここでは「プログラムシート」にほどこしたエラー対策をご説明します。

まずは「年」を入力する「A1」セルです。
「データの入力規則」を使って、入力できる最小値を「1950」、最大値を「2050」にします。これは、使っている「祝日一覧」が「1950」年から「2050」年だからです。といっても、過去の業務報告書を作成することはないでしょうから、実質的には現在から「2050」年までということです。

次は「月」を入力する「C1」セルです。

ここでも、「データの入力規則」を使います。「リスト」の「元の値」を「1」から「12」にします。

ユーザーが入力するデータは「年」「月」だけです。あとは、ファイル上に用意しておく必要があります。

引き続き、「プログラム」シート上のセルの設定を説明していきましょう。

セル「B3」には「=IF(OR($A$1=””,$C$1=””),””,WORKDAY(EOMONTH(DATE($A$1,$C$1,1),-1),1,Holiday!$A$1:$A$2000))」という式を入力します。
この式の意味は、「A1」セルと「C1」セルが空白でない限り、月初第1営業日の日付を入力します、という意味です。

セル「B4」には「=IF(B3=””,””,IF(MONTH($B3)<>MONTH(WORKDAY($B3,1,Holiday!$A$1:$A$2010)+1),””,WORKDAY($B3,1,Holiday!$A$1:$A$2010)))」という式を入力します。
この式の意味は、上のセルが空白でない限り、日付が同月内であれば、上のセルの次の営業日を入力します、ということです。

この式をコピーして、「B5」セルから「B27」セルを選択して、「貼り付けオプション」で「数式」を選択して、貼り付けます。

次に、「F1」セルには「=IF(COUNTIF(B3:B27,”>0″)=0,””,COUNTIF(B3:B27,”>0″))」という式を入力します。
この式の意味は、「B3」セルから「B27」セルの中で、「0」以上の数値のセルの個数を計算して合計を出す、という意味です。
日付の値は必ず「0」以上の値を取りますから、日付の個数の合計を出すということになります。

「今月分シート一括作成ボタン」は、リボンの開発タブにある「ボタン（フォームコントロール）」や「コマンドボタン（ActiveXコントロール）」ではなく、リボンの「挿入」タブにある「図」->「図形」の中の「四角形（角度付き）」です。
こちらの図形を選択したのは、色を自由に変更できるからです。開発タブにあるボタンの場合は、色が自由に選択できません。

最後にこのシートを保護します。シート内のデータやボタンの図形を破壊されると、プログラムが動作しなくなるからです。リボンの「校閲」タブから「シートの保護」をクリックします。ちなみにこのシート保護の場合はパスワードをかけていないのですぐにパスワードをはずすことができます。

業務報告書のシート（原本・例）、祝日一覧のシートは、ウェブに公開されているものを使わせていただきました。

「S_CreateMonthlyBusinessReportFormat」プロシージャの解説

本プログラムはこのプロシージャ1つだけの小さなプログラムですから、気を使う点は、プログラムのロジックそのものよりもエラー処理です。このプログラムもエラー処理が多めになっています。

Application.ScreenUpdating = False

このプログラムは、シートを20枚以上ブックに追加するプログラムなので、画面のちらつきが気になります。ですので、「Application.ScreenUpdating」プロパティに「False」を設定して、画面描画を抑止して、画面のちらつきを抑えます。

On Error Resume Next

Dim ProgramWS As Worksheet
Set ProgramWS = Worksheets("プログラム")

If ProgramWS Is Nothing Then
    MsgBox "「プログラム」ワークシートがありません", vbCritical
    Exit Sub
End If

On Error GoTo 0

これは「プログラム」シートがない場合のエラー処理です。変数「ProgramWS」を宣言して「プログラム」シートを代入しています。
このとき、「プログラム」シートが存在しないとエラーが起こります。

このようにエラーメッセージが表示されます。ここで、このエラーメッセージを表示させずに、次の行の命令文を実行するための文が

On Error Resume Next
～～～～～～～～～～～
On Error GoTo 0

です。「On Error Resume Next」ステートメントは、エラーの発生した行の次の命令文を実行し、「On Error GoTo 0」ステートメントは「On Error Resume Next」の処理を終了し、以降の処理の中でエラーが発生したら、エラーメッセージを表示させる、という命令です。

この文、「On Error Resume Next」ステートメントのおかげで、次の「If ProgramWS Is Nothing Then」が実行されます。
「ProgramWS」が「Nothing」、つまり空っぽであるとき、「MsgBox」関数を表示し、プログラムを終了します。

以上の流れで、「プログラム」シートがない場合は何もせずにこのプログラムを終了します。

With ProgramWS
    If .Cells(1, 1).Value <> "" Then
        Dim myYear As Long: myYear = .Cells(1, 1).Value
    Else
        MsgBox "年を入力してください。", vbExclamation
        Exit Sub
    End If

「With ProgramWS」は、これから「ProgramWS」オブジェクトに対して、連続的に処理をおこないますよという文です。
「If .Cells(1, 1).Value <> “” Then」という文は、「A1」セルが空白でなければ、何か文字列などが入っていれば、という意味です。
「Dim myYear As Long: myYear = .Cells(1, 1).Value」は、2つの命令文を1行に記入しています。意味としては、「A1」セルの値を変数「myYear」に代入するという意味ですが、変数の宣言とデータ型の指定、初期値の代入を1行で済ませています。
「：（コロン）」は複数行の命令文をつなぐものですが、乱用するとかえってプログラムが読みにくくなります。私は、この「変数の宣言とデータ型の指定、初期値の代入」をまとめるときに限って、2つの文が十分に短いときは使っています。
内容的にまったく違う命令の場合や、2つの文がともに長い場合は、コロンを使うとかえって見づらくなるので、逆効果だと思います。

「Else」は、「If」文の条件でないときという意味ですので、ここでは「A1」セルが空白であるときという意味になりますが、そのとき、メッセージボックスを表示してプログラムを終了します。

If .Cells(1, 3).Value <> "" Then
    Dim myMonth As Long: myMonth = .Cells(1, 3).Value
Else
    MsgBox "月を入力してください。", vbExclamation
    Exit Sub
End If

上に説明した部分は、「A1」セルに関する処理でしたが、今度は「C1」セルに関する処理です。違いは「年」と「月」です。セルの値が入っているとどんな処理をするのか（変数に値を代入する）、セルの値が入っていないとどんな処理をするのか（メッセージボックスを表示して処理を終了する）という部分は同じです。

Dim BusinessDays As Long: BusinessDays = .Cells(1, 6).Value

「Businessdays」は営業日数を表す変数です。「F1」セルの値を代入します。

    Dim i As Long
    For i = 1 To BusinessDays
        Dim myDate As Date
        myDate = .Cells(i + 2, 2).Value
        
        Dim WS_Count() As Date
        ReDim Preserve WS_Count(1 To i)
        WS_Count(i) = myDate
    Next i
End With

この部分は、営業日にあたる日付を配列に取得する部分です。「For～Next」文のなかで「Dim」を用いて変数の宣言をしていますが、これは間違いではありません。
次のページに詳細な説明があります。大変参考になりますので、ぜひご覧ください。

VBA 変数宣言はIfやFor等のコードブロック中に書いても機能する – t-hom’s diary

変数「myDate」は、各営業日の日付を代入します。日付ですので「Date」型を用います。変数「i」は1営業日目から最終営業日までの間でループします。しかし、各営業日の日付は「i + 2」番目のセル、、1営業日目なら「B3」セル、つまり上から3番目のセル、2営業日目なら4番目のセル、・・・、「n」番目の営業日ならば「n + 2」番目のセルに格納されています。

したがって、変数「myDate」にはセル「Cells(i + 2, 2)」の値を代入します。

さて、変数「myDate」に格納された営業日の日付の値は、配列「WS_Count」に順番に格納されます。「WS_Count(1)」には1番目の営業日の日付の値が、「WS_Count(2)」には2番目の営業日の日付の値が、・・・、「WS_Count(n)」には「n」番目の営業日の日付の値が格納されます。ここでは次のようなコードも間違いではありません。

Dim i As Long
For i = 1 To BusinessDays
    Dim WS_Count() As Date
    ReDim Preserve WS_Count(1 To i)
    WS_Count(i) = .Cells(i + 2, 2).Value
Next i

一見、このほうがシンプルなコードでよさげに見えますが、逆にいうと、これでは説明不足なのです。いきなり、配列「WS_Count(i)」にセル「Cells(i + 2, 2)」の値を代入しても、後から見た場合、これが何の値を代入しているのかわかりづらいのです

ですので、いったん「myDate」という「日付」ということがわかりやすい変数に格納してやって、あらためて配列「WS_Count(i)」に格納しています。

On Error Resume Next

Dim OriginalWS As Worksheet
Set OriginalWS = Worksheets("原本")

If OriginalWS Is Nothing Then
    MsgBox "「原本」ワークシートがありません", vbCritical
    Exit Sub
End If

On Error GoTo 0

ここでは、変数「ProgramWS」に対しておこなった処理と同じ処理をおこなっています。変数「OriginalWS」に対して「原本」シートを代入しています。

For i = BusinessDays To 1 Step -1
    OriginalWS.Copy After:=ProgramWS
    
    With ActiveSheet
        .Name = CStr(Format(WS_Count(i), "mm.dd"))
        .Cells(4, 2).Value = WS_Count(i)
    End With
Next i

ここでようやく、シートのコピーをおこなっています。「OriginalWS」オブジェクトに格納した「原本」シートを、「ProgramWS」オブジェクトに格納した「プログラム」シートの直後（すぐ右側）にコピーしています。
コピーされたシートはアクティブになっていますので、アクティブシートの名前を日付に変更し、「D2」セルにその日付を代入します。

ここで工夫したのは、「For～Next」文を回す順番です。

通常の順番だと「For～Next」文は「For i = 1 To Businessdays」となり大変わかりやすいです。しかし、問題点はこの「For～Next」文のわかりやすさと引き換えに、その後の処理はわかりにくくなる、ということです。
アクティブシートの後ろ（右側）にシートをコピーするのですから、場合分けが必要です。1枚目は「ProgramWS」の後に、2枚目以降は「ActiveSheet」の後にコピーするということになり、もっとシンプルなやり方があるのに、無駄な処理が増えます。
そして、もう1つの問題点は最終的にアクティブになるシートは何かということです。上のやり方ですと、最終的にアクティブになるシートは「最終営業日のシート」です。ダウンロードできるファイルの場合でいうと、「2018/9/28」のシートになります。
その何が問題かというと、実際に使うためには「2018/9/3」のシートをいちいち選択し直さなければならない、ということです。しかも、それはコードの書き方によって省くことのできる手間だというのにです。

以上の2つの問題点を解決するために「For～Next」文に「Step -1」という増分値をマイナスにできる表現を入れています。
それによって、挿入するすべてのシートは「OriginalWS」の直後（右側）に挿入すればいいことになります。先に挿入されたシートはだんだんと右側にずれていきます。最終的に「OriginalWS」の右側は第1営業日の名前、「09.03」という名前のシートとなり、そこから右へ順番に日付順のシートが存在することになります。
シートのコピーのコードもシンプルになり、最終的に選択されるシートは第1営業日のシートになり、実際の利用上の利便性も増しています。

「For～Next」文の増分値をマイナスにする表現「Step -1」については、次のページが大変参考になります。

For～Next文で増分値をマイナスにするとイイ実例：いちばんやさしいExcel VBAの教本

このページの筆者は有名なインストラクタで、最近ではExcel VBAの本も書かれました。また、一般向けのセミナーも開催なさっています。私も受講しましたが、大変役に立ちました。
その講座がなかったら、私もオブジェクトブラウザの有用性や、平易でなくとも正確な理解の大切さに気付くことはなかったと思います。
また、次のような名著に出会うこともなかったと思います。

そこが知りたい！Excel VBA プロの技

VBA書籍マニアの間では知られているこの書籍ですが、内容の難易度では日本一難しいVBA書籍でしょう。VBAから他の言語にステップアップすることを考えている方には必須の書籍だと思います。

    Set OriginalWS = Nothing
    Set ProgramWS = Nothing
    
    Application.ScreenUpdating = True
    
    MsgBox "今月分シート一括作成終了！", vbInformation
End Sub

最後は、オブジェクト変数に「Nothing」を代入し、「Application.ScreenUpdating」プロパティに「True」を代入し、メッセージボックスを表示してプログラムを終了します。

おわりに

今回はシンプルですが、実用性の高い＝実際に使われる可能性の高いプログラムを説明しました。
ダウンロードできるファイルには標準モジュール「mdl01_Main」には今回説明したプログラムを、標準モジュール「mdl02_Temp」には今回のプログラムからエラー処理を除いたプログラムを収録しました。ぜひご活用ください。