ユーザーフォーム(9)

前回までのおさらい

必要情報を考えて、それを元にユーザーフォームを作りました
今回は新たに「フレーム」というパーツを使用して入力するものが見分けやすくしました
UserForm_Initializeの時に未登録の従業員の欄(Label、TextBox)はVisible=Falseで隠しました
人件費の計算でTimeValue関数を使用して時間をシリアル値に変換。そこに24をかけて計算できる形に変換しました
それらを勤務した従業員数分足して人件費の計算が終了

という流れでしたね。

残りの情報を考えよう

残りは言うまでもなく「売上」と「仕入」ですね。

この「売上」と「仕入」から「人件費」を引いたものが「粗利」でしたね。

表の見出しの順番は左から「売上」→「仕入」→「人件費」→「粗利」ですね。

実際に表の入力欄の見出しの順番通りに配列にしまいましょう。

配列の準備と代入

といっても前回までのコードで既に配列は準備していました。

配列なのでArrayの頭をとって配列名は「arr」にしました。

今回は練習という事で配列の部屋数は4つに固定しております。

配列の中身は全て整数で大丈夫なのでLong型で宣言します。

では配列に格納していきましょう。

「売上」はTextBox1

「仕入」はTextBox2

「人件費」は前回までに計算しておいた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
    
    arr(0) = Me.Controls(t & 1).Value
    arr(1) = Me.Controls(t & 2).Value
    arr(2) = exp
    arr(3) = arr(0) - arr(1) - arr(2)
    
    
End Sub

と、まぁ今回はループも使わずに単純に配列に格納していってます。

セルに配列を一括で吐き出し

これは以前も触れた事がありますが、結局の所配列に必要情報を全て入れてセルに一括で吐き出し。

これはスピード的にストレスが少なく一番いいと思います。

一次元の配列はエクセルのセル的に考えると横方向に伸びています。ですので配列の部屋数分だけRangeで範囲を指定してarrをそのまま入れれば大丈夫です。

まずは入力しなければいけない日付(行)を探します。日付が入っているのはTextBox23ですね。

その行の6列～9列に配列を入れればいいわけですね。

    arr(0) = Me.Controls(t & 1).Value
    arr(1) = Me.Controls(t & 2).Value
    arr(2) = exp
    arr(3) = arr(0) - arr(1) - arr(2)
    
    i = Cells(Rows.Count, 5).End(xlUp).Row
    Do
        If IsDate(Cells(i, 5).Value) Then
            If CInt(Day(Cells(i, 5).Value)) = CInt(Me.TextBox23.Value) Then
                Range(Cells(i, 6), Cells(i, 9)) = arr
                MsgBox "入力完了"
                Exit Sub
            End If
        End If
        i = i - 1
        If i < 3 Then
            MsgBox "日付の指定が不正です。処理を中断します"
            Exit Sub
        End If
    Loop

今回は下から上に上がりつつ対象の日付を探しています。まずは見るセルの値が日付かどうかを判別して、そこからその日付の日とTextBox23の値を数値としてみた時に合致していれば

Range(Cells(i, 6), Cells(i, 9)) = arr

で貼り付けています。

もしも上に上がってもう日付のない3行目よりも上にいってしまったら日付の指定が正しくなかったとして処理を中断させています。

これで処理は完了です。

入力のしやすさを意識

入力のしやすさとは何かというと、ユーザーが数字を入力した時に最後にEnterを押すと思うんですよね。

そのEnterを押した時に入力しやすい順番にフォーカスが当たるのかという所に注目します。

現段階ではおそらくTextBoxを作成した順番にフォーカスがあたると思うんですよね。

という事でタブオーダーというものを使用してフォーカスがあたる順番を変更しましょう。

理想は
TextBox1>2>3>8>13>18>
4>9>14>19>
5>10>15>20>
6>11>16>21>
7>12>17>22>
CommandButton1>CommandButton2

の順番ですね。

TextBox3～22は従業員の時給・出勤時間・退勤時間・休憩時間の欄です。ちょっとわかりづらいですが整理すると

3→8→13→18が一人目

4→9→14→19が二人目

5→10→15→20が三人目

6→11→16→21が四人目

7→12→17→22が五人目

全て時給→出勤時間→退勤時間→休憩時間の順番です。Enterを押したらこの順番になる様に設定しましょう。という事ですね

タブオーダーを設定

順番を変えたいのはFrame2の人件費のTextBoxなのでFrame2を選択してからVBEの「表示」→「タブオーダー」でこの画面が開きます。

右側にある「上に移動」と「下に移動」でフォーカスをあてたい順番に並び替えればいいってわけです。一応ここではLabelにフォーカスがあたる事はないのでLabelは全て下に追いやってしまいます。

こんな感じですね。ちなみにShiftを押しながら複数選択とCtrlを押しながらで飛び飛びで選択も出来ますのでまとまって移動したいときに使用するといいでしょう。

ということでユーザーフォームについては今回で一旦終了です。

まとめ

必要情報を配列に格納しよう
一次元の配列は横方向なので一括でセルに吐き出し
ユーザーが入力しやすくするためにタブオーダーを設定しよう

では次回からはまたVBAの何かしら役に立つであろうものを取り上げていこうかと思います(まだ考えてない…)

<<ユーザーフォーム(8)　　自作関数(1)