VBA 経費管理８（清算完了）

清算完了

清算完了

清算完了ボタンの全体像

業務の流れとして、経費が発生して「経費申請書」を作成、清算を行います。
システムとしては進捗が「発行済」であることを確認して処理を進めます。
実際の清算が完了した時点で「清算完了」ボタンを押して申請内容を記録します。
進捗が再び「作成」となり、次の経費申請の登録が可能となります。

Sub AcReg_04() ‘清算完了

If Cells(9, 5).Value <> “発行済” Then
　　MsgBox (“発行処理を行ってください。”)
　　Exit Sub
End If

Application.EnableEvents = False

On Error GoTo jump1

‘実績最終行の取得
Set DTH(1) = Worksheets(“経費実績”).Range(“A1”).CurrentRegion
f = DTH(1).Rows.Count + 1

k = 0
‘ワークシート経費実績へ収入の登録
For i = 41 To 45
　　If Cells(i, 5).Value <> “” Then
　　　　DTH(1).Cells(f, 1).Value = Cells(i, 5).Value
　　　　DTH(1).Cells(f, 2).Value = Cells(9, 11).Value
　　　　DTH(1).Cells(f, 3).Value = Cells(i, 6).Value
　　　　DTH(1).Cells(f, 4).Value = Cells(i, 7).Value
　　　　DTH(1).Cells(f, 5).Value = Cells(i, 8).Value
　　　　DTH(1).Cells(f, 6).Value = Cells(i, 9).Value
　　　　DTH(1).Cells(f, 7).Value = Cells(i, 11).Value
　　　　DTH(1).Cells(f, 8).Value = Left(Cells(i, 6).Value, 6)
　　　　DTH(1).Cells(f, 9).Value = “清算済”
　　　　DTH(1).Cells(f, 10).Value = Cells(9, 9).Value
　　　　DTH(1).Cells(f, 11).Value = Mid(Date, 1, 4) & Mid(Date, 6, 2) & Mid(Date, 9, 2)
　　　　DTH(1).Cells(f, 12).Value = Cells(9, 7).Value
　　　　DTH(1).Cells(f, 13).Value = Cells(i, 10).Value

‘収入行手元金額計算
　　　　If f = 2 Then
　　　　　　DTH(1).Cells(f, 15).Value = 0 + DTH(1).Cells(f, 13).Value
　　　　Else
　　　　　　DTH(1).Cells(f, 15).Value = DTH(1).Cells(f – 1, 15).Value + DTH(1).Cells(f, 13).Value
　　　　End If
　　　　f = f + 1
　　　　k = k + 1
　　End If
Next

‘ワークシート経費実績へ支出の登録
For i = 13 To 37
　　If Cells(i, 5).Value <> “” Then
　　　　DTH(1).Cells(f, 1).Value = Cells(i, 5).Value
　　　　DTH(1).Cells(f, 2).Value = Cells(9, 11).Value
　　　　DTH(1).Cells(f, 3).Value = Cells(i, 6).Value
　　　　DTH(1).Cells(f, 4).Value = Cells(i, 7).Value
　　　　DTH(1).Cells(f, 5).Value = Cells(i, 8).Value
　　　　DTH(1).Cells(f, 6).Value = Cells(i, 9).Value
　　　　DTH(1).Cells(f, 7).Value = Cells(i, 11).Value
　　　　DTH(1).Cells(f, 8).Value = Left(Cells(i, 6).Value, 6)
　　　　DTH(1).Cells(f, 9).Value = “清算済”
　　　　DTH(1).Cells(f, 10).Value = Cells(9, 9).Value
　　　　DTH(1).Cells(f, 11).Value = Mid(Date, 1, 4) & Mid(Date, 6, 2) & Mid(Date, 9, 2)
　　　　DTH(1).Cells(f, 12).Value = Cells(9, 7).Value
　　　　DTH(1).Cells(f, 14).Value = Cells(i, 10).Value

‘支出行手元金額計算
　　　　If f = 2 Then
　　　　　　DTH(1).Cells(f, 15).Value = – DTH(1).Cells(f, 14).Value
　　　　Else
　　　　　　DTH(1).Cells(f, 15).Value = DTH(1).Cells(f – 1, 15).Value – DTH(1).Cells(f, 14).Value
　　　　End If
　　　　f = f + 1
　　　　k = k + 1
　　End If
Next

If k > 0 Then
　　MsgBox (k + “件、経費実績に転記されました。”)
Else
　　MsgBox (“登録するデータがありません。”)
End If

Clr_02 ‘リスト欄消去
Clr_01 ‘入力欄消去

Cells(9, 5).Value = “作成” ‘進捗状況セット

Application.EnableEvents = True

Exit Sub

jump1:
MsgBox (“ERROR”)
Application.EnableEvents = True

End Sub

清算完了ボタンの詳細

経費実績転記準備

If Cells(9, 5).Value <> “発行済” Then
　　MsgBox (“発行処理を行ってください。”)
　　Exit Sub
End If

Application.EnableEvents = False

「発行」ボタンを押すことで印刷プレヴューが表示され、進捗が「発行済」となります。
清算完了処理は、進捗が「発行済」であることを確認します。
実務では「経費申請書」をもって清算が完了したのち、「清算完了」ボタンを押すこととなります。

On Error GoTo jump1
＜中略＞
jump1:
MsgBox (“ERROR”)

以下の処理でエラーが発生した場合、jump1:まで処理をとばし、メッセージを出します。

‘実績最終行の取得
Set DTH(1) = Worksheets(“経費実績”).Range(“A1”).CurrentRegion
f = DTH(1).Rows.Count + 1

転記するワークシート「経費実績」の最終行を取得します。

‘ワークシート経費実績へ収入の登録
For i = 41 To 45
　　If Cells(i, 5).Value <> “” Then
　　　　＜中略＞
　　End If
Next

‘ワークシート経費実績へ支出の登録
For i = 13 To 37
　　If Cells(i, 5).Value <> “” Then
　　　　＜中略＞
　　End If
Next

ワークシート「経費申請」収入欄、E列41行から45行目までデータが空白でない行を取得して転記処理へ移ります。
次に支出欄、E列13行から37行目までデータが空白でない行を取得して転記処理へ移ります。

k = 0
　　＜中略＞
k = k + 1
　　＜中略＞
k = k + 1
　　＜中略＞
If k > 0 Then
　　MsgBox (k + “件、経費実績に転記されました。”)
Else
　　MsgBox (“登録するデータがありません。”)
End If

はじめにkに0を代入して、記録データがあればkには1ずつ加算されます。
k = 0 であれば「経費実績」に記録するデータはないことになります。
K > 0であれば、転記する件数をメッセージで表示します。

経費実績転記

DTH(1).Cells(f, 1).Value = Cells(i, 5).Value
DTH(1).Cells(f, 2).Value = Cells(9, 11).Value
DTH(1).Cells(f, 3).Value = Cells(i, 6).Value
　　　　　　　　　　　・
　　　　　　　　　　　・
　　　　　　　　　　　・

転記はワークシート「経費申請」と「経費実績」の配列に合わせて記述します。
並びを揃えれば For … Next で簡素化できます。
以下に表にまとめます。
１項目ずつ地道に記述します。

‘収入行手元金額計算
If f = 2 Then
　　DTH(1).Cells(f, 15).Value = – DTH(1).Cells(f, 13).Value
Else
　　DTH(1).Cells(f, 15).Value = DTH(1).Cells(f – 1, 15).Value + DTH(1).Cells(f, 13).Value
End If

‘支出行手元金額計算
If f = 2 Then
　　DTH(1).Cells(f, 15).Value = – DTH(1).Cells(f, 13).Value
Else
　　DTH(1).Cells(f, 15).Value = DTH(1).Cells(f – 1, 15).Value + DTH(1).Cells(f, 13).Value
End If

fはワークシート「経費実績」の最終行 + 1 です。f = 2 のとき最初の行となり支出、収入金額がそのまま代入されます。
3行目以降は一つ前の行の手元金額から差し引きした金額を代入します。

後処理

Clr_02 ‘リスト欄消去
Clr_01 ‘入力欄、入力候補欄消去

Cells(9, 5).Value = “作成” ‘進捗状況セット

Application.EnableEvents = True

Exit Sub

jump1:
MsgBox (“ERROR”)
Application.EnableEvents = True

転記が終わり後処理へ移ります。
リスト欄と入力欄、入力候補欄を消去します。
進捗を「作成」に戻します。
Application.EnableEvents = Trueに戻します。

正常に転記が終了するとExit Subで処理を出ます。

途中エラーが発生した場合でもApplication.EnableEvents = Trueに戻すため、jump1:の後に重ねて記述しています。

エラーが発生した場合、ワークシート「経費実績」を確認して転記された内容を確認する必要があります。

Clr_02 ‘リスト欄消去

Sub Clr_02() ‘リスト欄クリア

Application.EnableEvents = False

　　Range(“E13:L37,E41:L45,I9,J48:J51,K9”).Select
　　Selection.ClearContents

Application.EnableEvents = True

End Sub

後処理で出てきたClr_02のコードです。
ワークシート「経費申請」の支出欄、収入欄、合計金額、申請日、伝票番号をクリアしています。

VBA 経費管理８（清算完了）は以上です。
次回は残り、削除ボタンと業務終了ボタンについて解説します。（作成中）