金種表作成ソフトのコード組み立てです。今回はSUM関数を使って、金種数から合計金額を計算し、さらにコード実行ボタンを設置します。「設定」シートでの作業となります。
こんにちは、じゅんぱ店長（@junpa33）です。
今回からは、「金種表作成ソフト」の作成のためのVBAコードの組み立てを行っていきます。
このソフトの中で使うエクセルシートは「設定」・「金種数量」・「印刷用」という名称にしていますが、
この記事では、最初に、「設定」シートの部分を説明します。
今回のキーポイントは「SUM」関数を使ってのVBAコード組み立てです。
この「設定」シートでは、
- 「金種数から合計金額を表示する」VBAの組み立てと
- 「合計金額から金種枚数を計算する」ための「相手先とその金額」データ入力欄
- 「各計算を実行するコントロールボタン」の設置
の設置作業を行います。
本題に入る前に、エクセルBOOKの準備をしておきます。
新しくエクセルを起動してください。
そのエクセルBOOKを「金種表作成.xlsm」として一度保存をしてください。
これで、下準備OKです。
金種表作成ソフトの記事編成
- 金種表作成ソフトの使い方とダウンロード
コンテンツ
今回の金種表作成作業はSUM関数を使う
- 金額から金種数を算出する
- 金種数から合計金額を計算する
ことについて、SUM関数を利用して作成していきます。
- 元の数量や金額の入力欄を作成します。
- 入力値をリセットするボタンを設置
- ①、②を計算実行するボタンを設置
- 金種の設定で、２０００円札を使うかどうかを選択できるようにする
- 計算結果のエクセルシート表示だけでなく、個別に結果をプリントアウトできるようにする
- 計算実行の日時日付を残せるように、結果のプリントアウトの時に印刷表示されるようにする
のようなことが出来るように組み立てて行きます。
金種表作成ソフトの完成形「設定」シート
今回の作業で、このように「設定」シートを作っていきます。
SUM関数を使ったエクセルVBA組み立て
エクセルVBAの立ち上げに少々不安がある方は、こちらを参考にしてください。
VBA初めての起動。VBEの立ち上げ、保存と終了
「設定」シートの準備作業
「Sheet１」のシート名を「設定」に変更します。
「開発」タブ→「Visual Basic」より「標準Module」を挿入します。
準備は完了です。
「金種数から合計金額を表示する」入力欄を作る
入力値をリセットして各セルサイズ、文字サイズを調整し項目を表示します。
VBAコードを組み立てるについて、まず最初に表示される（作業する）シート上のデータをクリアしセルサイズをデフォルトの状態に戻すところから始めます。
シートクリアーを目的のメソッド別にVBA最速理解
各セルのフォーマットと値、セルサイズをデフォルトに戻します。
プロシージャー名を「設定CL」とします。Module1に記述します。
Option Explicit
Sub 設定CL()
Worksheets("設定").Select
With Cells
.ClearFormats
.ClearContents
.UseStandardHeight = True
.UseStandardWidth = True
End With
End Sub
部品化プロシージャーでCallステートメントは必須
With～End Withの使い方。VBAコードを簡潔に記述する
「フォント」の操作を最速理解する エクセルVBA
プロシージャー名を「設定作表」とします。Module1に続けて記述します。
最初に先ほどの「設定CL」プロシージャーを呼び出します。
金種の数量入力欄を設置していきます。
フォントサイズやカラーは適宜お好みで設定してください。
Sub 設定作表()
Call Module1.設定CL
With Worksheets("設定")
.Rows.RowHeight = 21
.Columns.ColumnWidth = 18
.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 With
End Sub
金種数量からSUM関数で合計の計算
各金種ごとに入力した数量から総合計までを計算するコードです。
合計計算は、今回「SUM関数」を使って計算します。
「WorksheetFunction.Sum」というように記述します。カッコ内は計算範囲になります。
エクセルVBA！For～Nextのループと入れ子構造をVBA最速理解
SUM関数で合計計算！実務で使えるVBAコード作成
「表示形式」をVBAコード的に最速理解する
Sub 金種合計()
Dim i As Long
Worksheets("設定").Select
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
動作ボタンを設置します
「金種・数量表示」、「2000円札利用チェックボックス」、「リセットスタート」、「金種・合計表示」、「金種金額計算印刷」の動作ボタンを設置していきます。
動作ボタンは今までに何回か設置していますが、今回も同様になります。
「開発」タブ→「挿入」より「フォームコントロール」の「ボタン」をクリックし設置します。
「ボタン」のテキストを「金種・数量表示」としてフォントの変更は「コントロールの書式設定」から行います。
同様に順番に、各ボタンを設置してください。
動作ボタンとプロシージャーの紐づけは、「マクロの登録」から行うことが出来ます。
マクロ名欄からプロシージャーを選択します。
ここまでの作成したプロシージャーの紐づけは、
「始めにここをクリックします」ボタンには「設定作表」プロシージャーを
「金種・合計表示」ボタンには「金種合計」プロシージャーを選択します。
２０００円チェックボックスの設置
ボタンの設置と同じ方法で、チェックボックスを設置します。B1セルに設置してください。
チェックしたときの動作でイベントを起こすようにします。
VBAコードの記述は、Sheet1（設定）のシートイベントとして行います。（シートモジュールに記述）
チェックボックスに変化があったとき、それが「チェックされた」のであれば、
B1セルに「〇」を記述します。
B1セルに文字が入力されても、チェックボックスの下敷きになっているので、前面表示されません。
というコードとします。
Option Explicit
Private Sub CheckBox1_Change()
If CheckBox1.Value = True Then
Range("B1").Value = "〇"
ElseIf CheckBox1.Value = False Then
Range("B1").Value = ""
End If
Range("B1").HorizontalAlignment = xlCenter
End Sub
SUM関数を使ったVBAコード作成のまとめ
Module1に記述した中の設定シートでの作成はこれまでになります。
合計計算も「SUM」関数を使えば楽チンにコード化実行できます。
ループ計算で合計を出していくことも出来ますが、
SUM関数を利用した方が遥かに明確で簡単です。
エクセル関数でもVBAでも、SUM関数はベーシックなスタンダードな関数になっています。
次回からは、
Module2で「金種・数量表示」ボタンに対応したVBAコードと
プリントアウトできるようにするVBAコードを組み立てて行きます。
エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です
今回の記事はここまでです。 最後までご覧いただき有難うございました。
