前回のおさらい
必要情報を考えてそれをユーザーフォームで入力スペースを作りました。
その際に新しく出てきた「フレーム」というものを使用して売上入力スペースと人件費入力スペースで分けたわけです。
ちなみに従業員数は変わる可能性があるので、エクセルのシート上にあるデータベースから参照して従業員が登録されていない(空白だった)場合は入力スペースをVisibleというプロパティを用いて非表示としたわけですね。
という事で出来上がった入力フォームがこちらですね。
では、このユーザーフォームを用いてセルに値を入力するプログラムを書いていきましょう。
情報を整理してみよう
- 入力日を参照して入力する行を割り出します
- 売上・仕入はそのままの状態で入力出来ます
- 人件費の計算をします
- 売上 – 仕入 – 人件費が粗利となります
と、簡単ですがこんな感じですね。
どのように入力するか考えてみよう
もちろんそのまま入力するのが簡単ですが、今回のケースでは隣接した一行に必要情報を入力するだけなので配列に一旦格納してから一括で吐き出してみましょう。
ひとまず先にCommandButton2のキャンセルボタンを押した時の「Unload Me」は書いておきましょう。
必要な変数を考えましょう
まず必要になるのは人件費を格納する変数。最終的に入力する値を格納する配列、これは今回4つの部屋で十分なので宣言の時点で部屋数も一緒に宣言してしまいます。
後はカウンタ変数ですかね。必要なものが出てきたら随時追加していきましょう。
こんな感じですね。
では実際に値を格納していきましょう。今回はちょっと複雑になる人件費の計算からやっていきましょう。
時間の計算
時間の計算は普通の計算と少し違います。
時給1000円でぴったり5時間働いた場合はもちろん5000円ですが、休憩時間が入ると考えると実働時間は実際は4時間30分とかになる場合があります。
考えてみると4時間と1時間の半分なので数値的には4.5になりますよね。
ではこの小数点以下は計算でどのように求めれば良いのでしょうか。
という事で今回登場するのは「TimeValue関数」というものです
TimeValue関数は引数に時間となるものを文字列で与えシリアル値として出力してくれます。そのシリアル値に1日の合計時間24を足すことにより時給計算に必要な数値(4時間30分なら4.5)を出力してくれます。
この値に時給をかける事により従業員の日給が計算されるわけですね。
ではやってみましょう。
ここで可読性を上げる為に変数を２つ追加しておきましょう。
従業員の実働時間のシリアル値から換算する時間の数値格納用の「times」という変数(シリアル値は小数点以下ありなのでDoubleで宣言)。
Me.Controlsで使用する”textbox”という文字列を格納する「t」という変数。
For ~Nextで回りつつ実働時間を一時的に格納してそこに時給をかけて人件費用の変数expに足していきましょう。
Private Sub CommandButton1_Click()
Dim exp As Long '人件費
Dim times As Double '従業員の実働時間一時格納
Dim t As String 'textbox
Dim arr(3) As Long '入力する全ての値を格納
Dim i As Long 'カウンタ変数
'人件費の計算
exp = 0
t = "textbox"
For i = 3 To 7
If Not Me.Controls(t & i).Value Like "" Then
If IsDate(Me.Controls(t & i + 5)) And IsDate(Me.Controls(t & i + 10)) And IsDate(Me.Controls(t & i + 15)) Then
times = TimeValue(Me.Controls(t & i + 10)) - TimeValue(Me.Controls(t & i + 5)) - TimeValue(Me.Controls(t & i + 15))
exp = exp + times * 24 * Me.Controls(t & i)
End If
End If
Next
End Sub
と、まぁかなり横長になりましたね。。。
改行したい所ですが、まだ改行やっていないので今回はそのまま載せます。
まずは時給用の変数expをリセットする為に0を代入します。
次に”textbox”と入力するのが面倒なので変数tに代入します。
時給の欄が入力されている=従業員が存在しているという事なので従業員の時給に使用しているTextBox3～7を回り時給欄が空白じゃなければ処理に入る分岐を書きます。
時給が存在していてもその日は休みの可能性がありますよね？という事で出勤・退勤・休憩時間全てが入力されていて尚且つ時間としての認識が出来る(Date型としての認識が出来る)状態かどうかをIsDate関数で見て出来るようなら計算を始めます。
まずは時間を計算する為には退勤時間 – 出勤時間 – 休憩時間で割り出せますね。出勤時間から退勤時間を引いてしまうと負の値になってしまうのでご注意ください。
時間の計算が出来たら時給用の変数expに時間*24*時給をどんどんと格納していきます。
ループが回り終わったら全従業員の合計人件費が出る。というわけですね。
という事で時間の計算で結構枠を使ってしまいましたので続きは次回にしていきましょう。
まとめ
- 時間の計算はTimeValue(時間となる文字列)を与えてシリアル値に変換
- 時間のシリアル値*24で計算しやすい形に変換
- 変換したら時給をかければ日給が割り出せる