Timerを使って処理時間の計測と、ComboBox一括登録　【VBA在庫管理#17】

そろそろ股引(ももひき)の出番ですな、どうもoReです。

今回は、プログラムの処理時間を計測していきます。

Timer

・Timer　タァィマァ

Timerは、『計時装置』という意味みたいです。

Timerは、0時からの経過時間を秒数で取得できます。

処理開始時と、処理終了時の経過時間を変数に入れて、
終了時(変数)から開始時(変数)の経過時間を引いてあげれば、
処理に掛かった時間を計測できます。

それでは、部品登録フォームの初期化プログラムの処理時間を計測してみましょう。

　　↓こんな感じ

'ユーザーフォーム初期化
Private Sub UserForm_initialize()
　　Dim LastRow As Long, i As Long
　　Dim StTime As Double, SpTime As Double　'StTimeは開始時間　SpTimeは終了時間ね

　　StTime = Timer　'開始時間を入れるよ
　　ComboBox1.Clear
　　With Sheet3
　　　　LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
　　　　For i = 2 To LastRow
　　　　　　ComboBox1.AddItem .Cells(i, 1)
　　　　Next i
　　End With
　　SpTime = Timer　'終了時間を入れるよ
　　MsgBox SpTime - StTime　'終了時間-開始時間をメッセージに出すよ
End Sub

部品登録フォームを表示させてみてください。

メッセージ『0』(処理時間)と出ましたか?
※メーカー名3件登録の場合

ComboBoxの登録データを増やして計測

そうしたら、設定シートのメーカー名を増やしてみます。

・メーカー名　10000件登録

私のノートパソコンでは、約0.2秒になり大分もっさりします。

・メーカー名　100000件登録

約10秒となり、一瞬フリーズしたかと思うほどもっさりします。

ちなみに、デスクトップパソコンでは約5秒となった事から、
パソコンのスペックによって変わるみたいですね。

処理に時間がかかる理由

処理に時間がかかる理由として

　　↓

1つ1つセルのデータを取得してコンボボックスへ登録しているので、
処理に時間が掛かると思われます。

なので、一括でデータを取得してコンボボックスに登録してやれば、
処理に時間が掛からないはず。

ComboBox1.List登録

コンボボックスについてこう書きました。

・ComboBox　カァンボォゥボォクス

ComboBoxは、テキストボックスとリストボックスが合体した物です。

テキスト入力できるし、登録したデータをリストから選べるしで、
とても出来る子です。

【VBA在庫管理#13】より

コンボボックスのリストに一括で登録してあげればいけるはずです。

　　↓このように書き換えます。

'ユーザーフォーム初期化
Private Sub UserForm_initialize()
　　Dim LastRow As Long, i As Long
　　Dim StTime As Double, SpTime As Double

　　StTime = Timer
　　ComboBox1.Clear
　　With Sheet3
　　　　LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
　　　　If LastRow = 1 Then　'設定シートに1つも登録されていない場合
　　　　　　Exit Sub　'リストに『メーカー名登録』を表示さない
　　　　ElseIf LastRow = 2 Then　'設定シートに1つしか登録されていない場合
　　　　　　ComboBox1.AddItem .Range("A2")　'ComboBox.Listに1つのデータを登録できない為AddItemを使う
　　　　Else
　　　　　　ComboBox1.List = .Range("A2:A" & LastRow).Value　'コンボボックスのリストにセルA2～A列最終行までを登録するよ
　　　　End If
　　End With
　　SpTime = Timer
　　MsgBox SpTime - StTime
End Sub

それでは、メーカー名100000件登録の処理時間を計測してみます。

約10秒→約0.08秒まで改善されましたよ。

設定シートにメーカー名登録プログラム　変更

設定シートのA列にメーカー名を登録するプログラムにも、
For文を使用して書きました。

　　↓設定シートにメーカー名登録

'設定シートにメーカー名登録処理
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Dim LastRow As Long, i As Long

　　If KeyCode = 13 Then
　　　　If ComboBox1 = “” Then
　　　　　　Exit Sub
　　　　Else
　　　　　　With Sheet3
　　　　　　　　LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
　　　　　　　　For i = 2 To LastRow　‘2～設定シートのA列最終行数まで繰り返し処理
　　　　　　　　　　If .Cells(i, 1) = ComboBox1.Text Then　‘設定シートのA列のi行にコンボボックス1と同じメーカー名があった場合
　　　　　　　　　　　　Exit Sub　‘処理終了
　　　　　　　　　　Else　‘設定シートのA列のi行にコンボボックス1と同じメーカー名がなかった場合
　　　　　　　　　　　　If i = LastRow Then　‘iが終値になった場合(最終行まで同じメーカー名がなかった場合)
　　　　　　　　　　　　　　.Cells(LastRow + 1, 1) = ComboBox1.Text
　　　　　　　　　　　　　　MsgBox “登録しました。”
　　　　　　　　　　　　　　Call UserForm_Initialize　‘部品登録フォーム初期化(コンボボックス1再登録)
　　　　　　　　　　　　End If
　　　　　　　　　　End If
　　　　　　　　Next i
　　　　　　End With
　　　　End If
　　End If
End Sub

このプログラムは、メーカー名が100000件あっても処理に時間が掛からないのですが、
なんかわかりにくいので変更します。

　　↓このように変更しましょう。

'設定シートにメーカー名登録処理
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Dim LastRow As Long
　　Dim Knskcell As Range

　　If KeyCode = 13 Then
　　　　If ComboBox1 = “” Then
　　　　　　Exit Sub
　　　　Else
　　　　　　With Sheet3
　　　　　　　　Set Knskcell = .Columns("A").Find(what:= ComboBox1.Text, lookat:= xlWhole)　'設定シートA列からコンボボックス1と完全一致するセルを検索するよ
　　　　　　　　If Knskcell Is Nothing Then　'完全一致するセルがない場合
　　　　　　　　　　LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
　　　　　　　　　　.Cells(LastRow + 1, 1) = ComboBox1.Text
　　　　　　　　　　MsgBox “登録しました。”
　　　　　　　　　　Call UserForm_Initialize
　　　　　　　　End If
　　　　　　End With
　　　　End If
　　End If
End Sub

まとめ

次回　設定シートのメーカー名を削除するよ

メッセージボックス条件分岐でメーカー名の登録を削除する　【VBA在庫管理#18】

人肌恋しい、どうもoReです。 今回は、メッセージボックスで確認をとり処理を分岐させていきます。 MsgBox 通常メッセージボックスを表示させると、 『メッセージ』と『OK』ボタンが表示されますよね。 MsgBox "oReです...

oregramming.com

2019.11.14

ExcelVBA部品在庫管理【目次】へ戻る