こんにちは、じゅんぱ店長（@junpa33）です。
今回からは、「金種表作成エクセル完全版」の作成のためのＶＢＡコードの組み立てを行っていきます。
このソフトの中で使うエクセルシートは「設定」・「金種数量」・「印刷用」という名称にしていますが、
この記事では、最初に、「設定」シートの部分を説明します。
今回のキーポイントは「SUM」関数を使ってのVBAコード組み立てです。
この「設定」シートでは、
- 「金種数から合計金額を表示する」VBAの組み立てと
- 「合計金額から金種枚数を計算する」ための「相手先とその金額」データ入力欄
- 「各計算を実行するコントロールボタン」の設置
の設置作業を行います。
本題に入る前に、エクセルＢＯＯＫの準備をしておきます。
新しくエクセルを起動してください。
そのエクセルＢＯＯＫを「金種表作成.xlsm」として一度保存をしてください。
これで、下準備ＯＫです。
本題に入るその前に、あらためて先回の記事をチェックするにはこちら↓になります。
無料ＤＬ金種表作成エクセルソフト完全版！金額と枚数計算。かんたん取説
金種表作成関係の記事はこちらです。
コンテンツ
先ず全体としての金種表エクセルの形です
- 金額から金種数を算出する
- 金種数から合計金額を計算する
ことについて、
元の数量や金額の入力欄を作成します。
入力値をリセットするボタンを設置
１、２を計算実行するボタンを設置
金種の設定で、２０００円札を使うかどうかを選択できるようにする
計算結果のエクセルシート表示だけでなく、個別に結果をプリントアウトできるようにする
計算実行の日時日付を残せるように、結果のプリントアウトの時に印刷表示されるようにする
のようなことが出来るように組み立てて行きます。
今回作成する金種表エクセルの「設定」シートは
SUM関数を使ったエクセルＶＢＡ組み立て
エクセルVBAの操作で最初に行う事については、
こちらの「エクセルVBA 始めての起動。VBEの立ち上げ、保存と終了」が、参考になります。
「設定」シートの準備作業
「Sheet１」のシート名を「設定」に変更します。
「開発」タブ→「Visual Basic」より「標準Module」を挿入します。
準備は完了です。
「金種数から合計金額を表示する」入力欄を作ります
入力値をリセットして各セルサイズ、文字サイズを調整し項目を表示します。
VBAコードを組み立てるについて、まず、
流れとしては、まず最初に表示される（作業する）シート上のデータをクリアしセルサイズをデフォルトの状態に戻すところから始めます。
入力値をリセットするコード
各セルのフォーマットと値、セルサイズをデフォルトに戻します。
プロシージャー名を「設定CL」とします。Module1に記述します。
Sub 設定CL()
Worksheets(“設定”).Select
With Cells
.ClearFormats
.ClearContents
.UseStandardHeight = True
.UseStandardWidth = True
End With
End Sub
項目を表示しセルサイズフォントサイズとカラーをセットするコード
プロシージャー名を「設定作表」とします。
最初に先ほどの「設定CL」プロシージャーを呼び出します。
金種の数量入力欄を設置していきます。
フォントサイズやカラーは適宜お好みで設定してください。
Sub 設定作表()
設定CL
Worksheets(“設定”).Select
With ActiveSheet
.Rows.RowHeight = 21
.Columns.ColumnWidth = 18
End With
Rows(1).RowHeight = 33
Range(“A2”) = “相手先・項目”
Range(“B2”) = “金額”
Range(“E2”) = “金種”
With Range(“F1”)
.Value = “ここに項目を入力”
.Font.Size = 12
.Font.ColorIndex = 15
End With
Range(“F2”) = “数量”
Range(“G2”) = “金額”
Range(“E3”) = “10000”
Range(“E4”) = “5000”
Range(“E5”) = “2000”
Range(“E6”) = “1000”
Range(“E7”) = “500”
Range(“E8”) = “100”
Range(“E9”) = “50”
Range(“E10”) = “10”
Range(“E11”) = “5”
Range(“E12”) = “1”
Range(“E13”) = “合計”
With Range(“A2”, “G2”)
.Font.Size = 15
.Font.ColorIndex = 5
.Font.Bold = True
End With
With Range(“E3”, “E13”)
.Font.Size = 15
.Font.ColorIndex = 5
.Font.Bold = True
End With
End Sub
金種数量からSUM関数で合計の計算
各金種ごとに入力した数量から総合計までを計算するコードです。
合計計算は、今回「SUM関数」を使って計算します。
「WorksheetFunction.Sum」というように記述します。カッコ内は計算範囲になります。
Sub 金種合計()
For i = 3 To 12
Cells(i, 7) = Cells(i, 5) * Cells(i, 6)
Next i
Cells(13, 6) = WorksheetFunction.Sum(Range(Cells(3, 6), Cells(12, 6)))
Cells(13, 7) = WorksheetFunction.Sum(Range(Cells(3, 7), Cells(12, 7)))
With Range(“F3”, “G13”)
.Font.Size = 15
.Font.Bold = True
End With
Range(“G3:G13”).NumberFormatLocal = “\#,##0;\-#,##0”
End Sub
Module1に記述したコードの確認
Module1に記述したコードはこのようになります。
動作ボタンを設置します
「金種・数量表示」、「2000円札利用チェックボックス」、「リセットスタート」、「金種・合計表示」、「金種金額計算印刷」の動作ボタンを設置していきます。
動作ボタンは今までに何回か設置していますが、今回も同様になります。
「開発」タブ→「挿入」より「フォームコントロール」の「ボタン」をクリックし設置します。
「ボタン」のテキストを「金種・数量表示」としてフォントの変更は「コントロールの書式設定」から行います。
同様に順番に、各ボタンを設置してください。
動作ボタンとプロシージャーの紐づけは、「マクロの登録」から行うことが出来ます。
マクロ名欄からプロシージャーを選択します。
ここまでの作成したプロシージャーの紐づけは、
「始めにここをクリックします」には「設定作表」プロシージャーを
「金種・合計表示」には「金種合計」プロシージャーを選択します。
２０００円チェックボックスを設置します
ボタンの設置と同じ方法で、チェックボックスを設置します。Ｂ１セルに設置してください。
チェックしたときの動作でイベントを起こすようにします。
ＶＢＡコードの記述は、Sheet1（設定）のシートイベントとして行います。
チェックボックスに変化があったとき、それが「チェックされた」のであれば、
Ｂ１セルに「〇」を記述します。
というコードとします。
SUM関数を使ったVBAコード作成のまとめ
Module1に記述した中の設定シートでの作成はこれまでになります。
合計計算も「SUM」関数を使えば楽チンにコード化実行できます。
ループ計算で合計を出していくことも出来ますが、
SUM関数を利用した方が遥かに明確で簡単です。
エクセル関数でもVBAでも、SUM関数はベーシックなスタンダードな関数になっています。
次回からは、
Module2で「金種・数量表示」ボタンに対応したＶＢＡコードと
プリントアウトできるようにするＶＢＡコードを組み立てて行きます。
今回の記事はここまでとなります。
次の記事に進むのはこちら↓になります。
金種表作成エクセルＶＢＡの作り方②自動計算の金種数量表が作れる
エクセルVBAを使って業務効率を上げて行くのに、始めのうちに知っておきたいテーマを纏めてみました。
最後までお読みいただきありがとうございました。
