エクセルVBAで家計簿作った。メモリ不足になったので、クラスモジュールでユーザーフォームのイベント処理を設定
エクセルＶＢＡで家計簿作ってみた。
プレイストアで無料でダウンロードできますが、
いまさらエクセルで家計簿を勉強ついでに作ってみました。
今回は、食費入力From部の話です。
作ってみて収穫があったこと
１ ユーザーフォームのコントロールを作成しすぎるとメモリ不足で動かなくなる（YouserFrom．Showでエラー）。
１３０品目ほどの入力項目を作成したら、「メモリ不足です」でエラー。
最初からコントロールを設定するのではなく、コードから動的に入力項目のコントロールを作成する設計に変更。
私のＰＣのメモリが４ＧＢなので、メモリが小さいです。
しかし、家計簿なので家庭用パソコン（メモリ４ＧＢ）でも動く設計でないといけません。
ちなみに、
旧_家計簿のファイルサイズ＝４１５７ＫＢ。
新_家計簿のファイルサイズ＝２５７ＫＢ。
明らかに、コードから動的にコントロールを作成した方がサイズが小さいです。
２ クラスモジュールで動的に作成したコントロールに、イベント処理を設定する。
Me.Controls.Add（コントロール） ’例：Me.Controls.Add("Forms.TextBox.1", "txt_" & Item & "_金額")
上記のコントロールを追加するコードは、Fromに記載します。重要なのはコードから動的に作ったコントロールに対してイベント処理を設定できることです。
実際に作成したのはこんな感じ（Initialize）↓↓
コンボボックスで「魚」を選択してみると↓↓のように、
コントロール作成（Changeイベント）。
アジ ～ ヤリイカまで、入力用のコントロールが作成されました。
このコントロールのイベント処理をクラスモジュールで実装します。
クラスモジュールのコード（オブジェクト名：EventClass）
チェックボックスが変更されたときの処理（Changeイベント）。
Private WithEvents Target_CheckBox As MSForms.CheckBox Public Sub SetCtrl_CheckBox(new_ctrl_CheckBox As MSForms.CheckBox) Set Target_CheckBox = new_ctrl_CheckBox End Sub 'イベント処理 Private Sub Target_CheckBox_Change() If Target_CheckBox.Value = True Then 'チェックボックスがオンのとき EditForm("txt_" & Target_CheckBox.Caption & "_数量").Value = 1 '最近買った値段を食材単価DBから引っ張る。 Lline = Worksheets("食材単価DB").Cells(Rows.Count, 1).End(xlUp).Row For k_DB = Lline To 2 Step -1 If Target_CheckBox.Caption = Worksheets("食材単価DB").Cells(k_DB, 4) Then '食品名で比較（カボチャなど） EditForm("txt_" & Target_CheckBox.Caption & "_金額").Value = Worksheets("食材単価DB").Cells(k_DB, 6) Exit For End If Next k_DB Else 'チェックボックスがオフのとき EditForm("txt_" & Target_CheckBox.Caption & "_数量").Value = 0 EditForm("txt_" & Target_CheckBox.Caption & "_金額").Value = 0 End If End Sub
つまり、何をするかというとチェックボックスがTrueになったら（チェックされたら）、数量のテキストボックスの値を１にする。
金額を単価DB（Sheet）から抽出する。
チェックボックスがFalseになったら、数量のテキストボックスの値を０にし、金額のテキストボックスの値を０にする。
入力を簡素化するための機能です。チェックいれたら数量は１以上という手間を省かせています。
YouserFrom上のコード（オブジェクト名：EditForm）
’行頭に記載---------------------------------------------------------------- Private Const MAX_CONTROL_NUMBER = 300 '300は適当な大きさ Private ctrl(1 To MAX_CONTROL_NUMBER) As New EventClass Private CheckCollection As Collection 'コレクションの宣言 ’行頭終了________________________________________________________________________ ’～ 省略 ～ 'イベントの登録 For Each ctrl In Me.Controls 'フォーム内のコントロールをループ If TypeName(ctrl) = "CheckBox" Then 'コントロールのタイプがチェックボックス Set obj = New EventClass 'インスタンスの生成 obj.SetCtrl_CheckBox ctrl 'コントロールをセット CheckCollection.Add obj 'コレクションへ追加 Set obj = Nothing 'インスタンス破棄 Next
以上までが、チェックボックスの部分です。
これで、チェックボックスにイベント処理を登録することができました。
後は、数量と金額のスピンボタンの数値増減処理のイベント（SpinUp、SpinDown）。
数量と金額のテキストボックスに数値以外が入った時のイベント処理（エラー処理）を実装します。
コード全体は↓↓。
YouserFrom上のコード（オブジェクト名：EditForm）
’行頭に記載---------------------------------------------------------------- Private Const MAX_CONTROL_NUMBER = 300 '300は適当な大きさ Private ctrl(1 To MAX_CONTROL_NUMBER) As New EventClass Private CheckCollection As Collection 'コレクションの宣言 ’行頭終了________________________________________________________________________ ’～ 省略 ～ 'イベントの登録 For Each ctrl In Me.Controls 'フォーム内のコントロールをループ If TypeName(ctrl) = "CheckBox" Then 'コントロールのタイプがチェックボックスだったら Set obj = New EventClass 'インスタンスの生成 obj.SetCtrl_CheckBox ctrl 'コントロールをセット CheckCollection.Add obj 'コレクションへ追加 Set obj = Nothing 'インスタンス破棄 ElseIf TypeName(ctrl) = "SpinButton" Then 'コントロールのタイプがスピンボタンだったら Spin_Type = ctrl.Name If Spin_Type Like "*数量" Then 'スピンボタンの名前が～数量だったら 'スピンアップを登録 Set obj = New EventClass 'インスタンスの生成 obj.SetCtrl_SpinButton_数量Up ctrl 'コントロールをセット CheckCollection.Add obj 'コレクションへ追加 Set obj = Nothing 'インスタンス破棄 'スピンダウンを登録 Set obj = New EventClass 'インスタンスの生成 obj.SetCtrl_SpinButton_数量Down ctrl 'コントロールをセット CheckCollection.Add obj 'コレクションへ追加 Set obj = Nothing 'インスタンス破棄 ElseIf Spin_Type Like "*金額" Then 'スピンアップを登録 Set obj = New EventClass 'インスタンスの生成 obj.SetCtrl_SpinButton_金額Up ctrl 'コントロールをセット CheckCollection.Add obj 'コレクションへ追加 Set obj = Nothing 'インスタンス破棄 'スピンダウンを登録 Set obj = New EventClass 'インスタンスの生成 obj.SetCtrl_SpinButton_金額Down ctrl 'コントロールをセット CheckCollection.Add obj 'コレクションへ追加 Set obj = Nothing 'インスタンス破棄 End If ElseIf TypeName(ctrl) = "TextBox" Then 'コントロールのタイプがテキストボックスだったら Text_Type = ctrl.Name If Text_Type Like "*数量" Then 'スピンボタンの名前が～数量だったら Set obj = New EventClass 'インスタンスの生成 obj.SetCtrl_TextBox_数量 ctrl 'コントロールをセット CheckCollection.Add obj 'コレクションへ追加 Set obj = Nothing 'インスタンス破棄 ElseIf Text_Type Like "*金額" Then 'スピンボタンの名前が～数量だったら Set obj = New EventClass 'インスタンスの生成 obj.SetCtrl_TextBox_金額 ctrl 'コントロールをセット CheckCollection.Add obj 'コレクションへ追加 Set obj = Nothing 'インスタンス破棄 End If End If Next
クラスモジュールのコード（オブジェクト名：EventClass）
’行頭に記載---------------------------------------------------------------- Private WithEvents Target_CheckBox As MSForms.CheckBox Private WithEvents Target_SpinButton_数量Up As MSForms.SpinButton Private WithEvents Target_SpinButton_数量Down As MSForms.SpinButton Private WithEvents Target_SpinButton_金額Up As MSForms.SpinButton Private WithEvents Target_SpinButton_金額Down As MSForms.SpinButton Private WithEvents Target_TextBox_数量 As MSForms.TextBox Private WithEvents Target_TextBox_金額 As MSForms.TextBox ’行頭終了________________________________________________________________________ Public Sub SetCtrl_CheckBox(new_ctrl_CheckBox As MSForms.CheckBox) Set Target_CheckBox = new_ctrl_CheckBox End Sub 'イベント処理 Private Sub Target_CheckBox_Change() If Target_CheckBox.Value = True Then 'チェックボックスがオンのとき EditForm("txt_" & Target_CheckBox.Caption & "_数量").Value = 1 '最近買った値段を食材単価DBから引っ張る。 Lline = Worksheets("食材単価DB").Cells(Rows.Count, 1).End(xlUp).Row For k_DB = Lline To 2 Step -1 If Target_CheckBox.Caption = Worksheets("食材単価DB").Cells(k_DB, 4) Then '食品名で比較（カボチャなど） EditForm("txt_" & Target_CheckBox.Caption & "_金額").Value = Worksheets("食材単価DB").Cells(k_DB, 6) Exit For End If Next k_DB Else 'チェックボックスがオフのとき EditForm("txt_" & Target_CheckBox.Caption & "_数量").Value = 0 EditForm("txt_" & Target_CheckBox.Caption & "_金額").Value = 0 End If End Sub Public Sub SetCtrl_SpinButton_数量Up(new_ctrl_SpinButton_Up As MSForms.SpinButton) Set Target_SpinButton_数量Up = new_ctrl_SpinButton_Up End Sub 'イベント処理 Private Sub Target_SpinButton_数量Up_SpinUp() 'クリックしたスピンボタンからNameを取得（Target_SpinButton_数量Up.Name）。 Expense_strig = Target_SpinButton_数量Up.Name '"_"に挟まれた、Item文字列を取得（Expense_strig）。 strat_string = InStr(Expense_strig, "_") + 1 end_string = InStr(5, Expense_strig, "_") - strat_string Expense_strig = Mid(Expense_strig, strat_string, end_string) 'テキストボックスのNameを作成し変数に格納。 Item_String = "txt_" & Expense_strig & "_数量" 'スピンアップ EditForm.Controls(Item_String).Object.Value = EditForm.Controls(Item_String).Object.Value + 1 End Sub Public Sub SetCtrl_SpinButton_数量Down(new_ctrl_SpinButton_Down As MSForms.SpinButton) Set Target_SpinButton_数量Down = new_ctrl_SpinButton_Down End Sub 'イベント処理 Private Sub Target_SpinButton_数量Down_SpinDown() 'クリックしたスピンボタンからNameを取得（Target_SpinButton_数量Down.Name）。 Expense_strig = Target_SpinButton_数量Down.Name '"_"に挟まれた、Item文字列を取得（Expense_strig）。 strat_string = InStr(Expense_strig, "_") + 1 end_string = InStr(5, Expense_strig, "_") - strat_string Expense_strig = Mid(Expense_strig, strat_string, end_string) 'テキストボックスのNameを作成し変数に格納。 Item_String = "txt_" & Expense_strig & "_数量" 'スピンダウン EditForm.Controls(Item_String).Object.Value = EditForm.Controls(Item_String).Object.Value - 1 End Sub Public Sub SetCtrl_SpinButton_金額Up(new_ctrl_SpinButton_Down As MSForms.SpinButton) Set Target_SpinButton_金額Up = new_ctrl_SpinButton_Down End Sub 'イベント処理 Private Sub Target_SpinButton_金額Up_SpinUp() 'クリックしたスピンボタンからNameを取得（Target_SpinButton_金額Up.Name）。 Expense_strig = Target_SpinButton_金額Up.Name '"_"に挟まれた、Item文字列を取得（Expense_strig）。 strat_string = InStr(Expense_strig, "_") + 1 end_string = InStr(5, Expense_strig, "_") - strat_string Expense_strig = Mid(Expense_strig, strat_string, end_string) 'テキストボックスのNameを作成し変数に格納。 Item_String = "txt_" & Expense_strig & "_金額" 'スピンアップ EditForm.Controls(Item_String).Object.Value = CInt(EditForm.Controls(Item_String).Object.Value) + CInt(EditForm.Controls("cmb_金額増減値").Object.Value) End Sub Public Sub SetCtrl_SpinButton_金額Down(new_ctrl_SpinButton_Down As MSForms.SpinButton) Set Target_SpinButton_金額Down = new_ctrl_SpinButton_Down End Sub 'イベント処理 Private Sub Target_SpinButton_金額Down_SpinDown() 'クリックしたスピンボタンからNameを取得（Target_SpinButton_金額Down.Name）。 Expense_strig = Target_SpinButton_金額Down.Name '"_"に挟まれた、Item文字列を取得（Expense_strig）。 strat_string = InStr(Expense_strig, "_") + 1 end_string = InStr(5, Expense_strig, "_") - strat_string Expense_strig = Mid(Expense_strig, strat_string, end_string) 'テキストボックスのNameを作成し変数に格納。 Item_String = "txt_" & Expense_strig & "_金額" 'スピンダウン EditForm.Controls(Item_String).Object.Value = CInt(EditForm.Controls(Item_String).Object.Value) - CInt(EditForm.Controls("cmb_金額増減値").Object.Value) End Sub Public Sub SetCtrl_TextBox_数量(new_ctrl_TextBox As MSForms.TextBox) Set Target_TextBox_数量 = new_ctrl_TextBox End Sub 'イベント処理 Private Sub Target_TextBox_数量_Change() If IsNumeric(Target_TextBox_数量.Value) = True Then Target_TextBox_数量.Value = StrConv(Target_TextBox_数量.Value, vbNarrow) Else MsgBox "数値で入力" Target_TextBox_数量.Value = 1 End If End Sub Public Sub SetCtrl_TextBox_金額(new_ctrl_TextBox As MSForms.TextBox) Set Target_TextBox_金額 = new_ctrl_TextBox End Sub 'イベント処理 Private Sub Target_TextBox_金額_Change() If IsNumeric(Target_TextBox_金額.Value) = True Then Target_TextBox_金額.Value = StrConv(Target_TextBox_金額.Value, vbNarrow) Else MsgBox "数値で入力" Target_TextBox_金額.Value = 0 End If End Sub
このモジュールを実装後の動作確認↓↓
以上になります。クラスモジュールって何に使うのだろうと思っていました。
結論、「同じ処理をさせるまとまりのこと」だと理解しました。
誰かの何かの参考になれば幸いです。
参考にしたサイト
