１. 本マクロの概要
｜
└１-１.このマクロで何ができるか
｜
└１-２.マクロ起動時の画面遷移
２. ソースコード掲載
３. ソースコード概要説明
１-１.このマクロで何ができるか
このマクロは、以下の具体的な作業時に使われることを想定しています。
■コピー機とプリンターを使った印刷業務において、コピー機の印刷使用金額に対してプリンターでは何回まで印刷できるかを算出する。
マクロ起動時にユーザーに求める操作は以下の通りです。
■５つのセルを選択
・１回印刷分の紙代が記入されているセルを選択
コピー機
プリンター
・ １回印刷分 のインク代が記入されているセルを選択
コピー機
プリンター
・コピー機の印刷回数が記入されているセルを選択
１-２.マクロ起動時の画面遷移
マクロ起動時の画面遷移は以下の通りで、６段階となっております。
２. ソースコード掲載
マクロのソースコードは、以下の通りです。マクロの登録方法は、本ブログ内で紹介しておりますので、こちらの「３.ExcelにおけるVBAマクロの登録と利用方法」 をご確認ください。
Sub コピー機の利用金額に対するプリンターの使用回数閾値算出()
'コピー機は、参照セル3つと、計算用数字変数5つを用意
Dim cell_paper_price_for_copy As Range 'コピー機の紙代記入セル
Dim cell_ink_price_for_copy As Range 'コピー機のインク代記入セル
Dim cell_count_of_doing_copy As Range 'コピー機の使用回数記入セル
Dim paper_price_for_copy As Double 'コピー機の紙代
Dim ink_price_for_copy As Double 'コピー機のインク代
Dim price_of_copy As Double 'コピー機1回あたりの金額
Dim count_of_doing_copy As Long 'コピー機の使用回数
Dim all_of_price_of_copy As Double 'コピー機使用の合計金額
'プリンターは、参照セル2つと、計算用数字変数3つを用意
Dim cell_paper_price_for_printer As Range 'プリンターの紙代記入セル
Dim cell_ink_price_for_printer As Range 'プリンターのインク代記入セル
Dim paper_price_for_printer As Double 'プリンターの紙代
Dim ink_price_for_printer As Double 'プリンターのインク代
Dim price_of_print As Double 'プリンター1回あたりの金額
Dim count_of_doing_print As Long 'コピー機の金額に対するプリンターの使用閾値
On Error Resume Next
'InputBoxを表示して、ユーザーにコピー機の紙代セルを選択してもらう
Set cell_paper_price_for_copy = Application.InputBox("コピー機の紙代のセルを選択してください" & vbLf & _
"操作を中断する場合は【キャンセル】をクリックしてください", _
Type:=8, _
Title:="コピー機の紙代が記入されているセルを選択")
'「キャンセル」がクリックされた場合は処理終了 以降同じ個所はコメントなし
If cell_paper_price_for_copy Is Nothing Then Exit Sub
'InputBoxを表示して、ユーザーにコピー機のインク代セルを選択してもらう
Set cell_ink_price_for_copy = Application.InputBox("コピー機のインク代のセルを選択してください" & vbLf & _
"操作を中断する場合は【キャンセル】をクリックしてください", _
Type:=8, _
Title:="コピー機のインク代が記入されているセルを選択")
If cell_ink_price_for_copy Is Nothing Then Exit Sub
'InputBoxを表示して、ユーザーにコピー機の使用回数セルを選択してもらう
Set cell_count_of_doing_copy = Application.InputBox("コピー機の使用回数のセルを選択してください" & vbLf & _
"操作を中断する場合は【キャンセル】をクリックしてください", _
Type:=8, _
Title:="コピー機の使用回数が記入されているセルを選択")
If cell_count_of_doing_copy Is Nothing Then Exit Sub
'InputBoxを表示して、ユーザーにプリンターの紙代セル範囲を入力してもらう
Set cell_paper_price_for_printer = Application.InputBox("プリンターの紙代のセルを選択してください" & vbLf & _
"操作を中断する場合は【キャンセル】をクリックしてください", _
Type:=8, _
Title:="プリンターの紙代が記入されているセルを選択")
If cell_paper_price_for_printer Is Nothing Then Exit Sub
'InputBoxを表示して、ユーザーにプリンターのインク代セル範囲を入力してもらう
Set cell_ink_price_for_printer = Application.InputBox("プリンターのインク代のセルを選択してください" & vbLf & _
"操作を中断する場合は【キャンセル】をクリックしてください", _
Type:=8, _
Title:="プリンターのインク代が記入されているセルを選択")
If cell_ink_price_for_printer Is Nothing Then Exit Sub
'コピー機の1回あたりの金額と合計金額算出
paper_price_for_copy = cell_paper_price_for_copy.Value 'コピー機の紙代セルから紙代を読み取り
ink_price_for_copy = cell_ink_price_for_copy.Value 'コピー機のインク代セルからインク代を読み取り
price_of_copy = paper_price_for_copy + ink_price_for_copy '読み取った紙代とインク代から1回あたりの金額算出
count_of_doing_copy = cell_count_of_doing_copy.Value 'コピー機の使用回数セルから使用回数読み取り
all_of_price_of_copy = price_of_copy * count_of_doing_copy 'コピー機の1回あたりの金額×使用回数で合計金額算出
'プリンターの1回あたりの金額算出
paper_price_for_printer = cell_paper_price_for_printer 'プリンターの紙代セルから紙代を読み取り
ink_price_for_printer = cell_ink_price_for_printer 'プリンターのインク代セルからインク代を読み取ろ
price_of_print = paper_price_for_printer + ink_price_for_printer '紙代とインク代から1回あたりの金額を算出
'コピー機の合計金額に対するプリンターの使用回数閾値の算出
count_of_doing_print = all_of_price_of_copy / price_of_print
'結果をメッセージボックスで表示
MsgBox Prompt:="【コピー機】" & vbLf & _
"紙代は " & paper_price_for_copy & " 円" & vbLf & _
"インク代は " & ink_price_for_copy & " 円" & vbLf & _
"1回あたりの金額は " & price_of_copy & " 円" & vbLf & _
"使用回数は " & count_of_doing_copy & " 回" & vbLf & _
vbLf & _
"合計金額は " & all_of_price_of_copy & " 円" & vbLf & _
vbLf & _
"【プリンター】" & vbLf & _
"紙代は " & paper_price_for_printer & " 円" & vbLf & _
"インク代は " & ink_price_for_printer & " 円" & vbLf & _
"1回あたりの金額は " & price_of_print & " 円" & vbLf & _
vbLf & _
"コピー機の使用回数 " & count_of_doing_copy & "回 に対して、" & vbLf & _
"プリンターは " & count_of_doing_print & "回 まで使用可能", _
Title:="コピー機使用の合計金額に対するプリンターの使用可能回数"
On Error GoTo 0
End Sub
３.ソースコード概要説明
以下、ソースコードに記載されている内容がどのようなものを意味しているかを、ソースコードの上から順に紹介します。
ソースコード内の” ‘ (シングルクォーテーション)”から始まる文章は「コメント」扱いになり、ソースコードとしては読み込まれません。そのソースコード部分では何をしているかの注釈付けを行っています。
変数宣言
'コピー機は、参照セル3つと、計算用数字変数5つを用意
Dim cell_paper_price_for_copy As Range 'コピー機の紙代記入セル
Dim cell_ink_price_for_copy As Range 'コピー機のインク代記入セル
Dim cell_count_of_doing_copy As Range 'コピー機の使用回数記入セル
Dim paper_price_for_copy As Double 'コピー機の紙代
Dim ink_price_for_copy As Double 'コピー機のインク代
Dim price_of_copy As Double 'コピー機1回あたりの金額
Dim count_of_doing_copy As Long 'コピー機の使用回数
Dim all_of_price_of_copy As Double 'コピー機使用の合計金額
'プリンターは、参照セル2つと、計算用数字変数3つを用意
Dim cell_paper_price_for_printer As Range 'プリンターの紙代記入セル
Dim cell_ink_price_for_printer As Range 'プリンターのインク代記入セル
Dim paper_price_for_printer As Double 'プリンターの紙代
Dim ink_price_for_printer As Double 'プリンターのインク代
Dim price_of_print As Double 'プリンター1回あたりの金額
Dim count_of_doing_print As Long 'コピー機の金額に対するプリンターの使用閾値
コピー機に関する情報読みだしと、プリンターに関する情報読み出しのために、変数を宣言しています。
Dim…As Rangeは、ユーザーに指定されるセルの場所を覚えておくためのRange型変数です。それぞれのRange型変数の変数名(DimからAs Rangeの間の部分)は、頭に”cell_”を付けていますが、call_以下の変数名が同じ変数もそれぞれ用意しており、それにはセルの入力値を格納します。
それらのセットのほかに、コピー機/プリンターの１回当たりの金額の算出値をそれぞれ記憶しておく変数、最終的に求めたい対象であるコピー機の金額に対するプリンターの使用回数の算出値を格納する変数を用意しています。
ユーザーにセルを選択してもらうための処理
'InputBoxを表示して、ユーザーにコピー機の紙代セルを選択してもらう
Set cell_paper_price_for_copy = Application.InputBox("コピー機の紙代のセルを選択してください" & vbLf & _
"操作を中断する場合は【キャンセル】をクリックしてください", _
Type:=8, _
Title:="コピー機の紙代が記入されているセルを選択")
'「キャンセル」がクリックされた場合は処理終了 以降同じ個所はコメントなし
If cell_paper_price_for_copy Is Nothing Then Exit Sub
上記のようなソースコードの塊が５か所ありますが、これらはユーザーにセルを選択してもらうためのものです。Set Range型変数 = Application.InputBox (…)がそれにあたる部分です。
Application.InputBox (…) 内の記述について説明します。初めの「コピー機の…」は、表示されるポップアップ内の文章を設定しています。その中の”& vbLf &”は、ポップアップ内の改行を設定しています。
「Type:８」は、ユーザーから指定されたものが”セル範囲型”であることを指定しています。
「Title:…」は、ポップアップ上部の帯に表示される文章を設定しています。
「 If cell_paper_price_for_copy Is Nothing Then Exit Sub 」は、ユーザーがセルの指定をせずにOKボタンを押した場合に、マクロが終了するようにしております。
コピー機の1回当たりの金額の算出と合計金額の算出
'コピー機の1回あたりの金額と合計金額算出
paper_price_for_copy = cell_paper_price_for_copy.Value 'コピー機の紙代セルから紙代を読み取り
ink_price_for_copy = cell_ink_price_for_copy.Value 'コピー機のインク代セルからインク代を読み取り
price_of_copy = paper_price_for_copy + ink_price_for_copy '読み取った紙代とインク代から1回あたりの金額算出
count_of_doing_copy = cell_count_of_doing_copy.Value 'コピー機の使用回数セルから使用回数読み取り
all_of_price_of_copy = price_of_copy * count_of_doing_copy 'コピー機の1回あたりの金額×使用回数で合計金額算出「 paper_price_for_copy = cell_paper_price_for_copy.Value 」部分で、ユーザーに選択してもらった”cell_”のセル内の入力値を、マクロ内の変数に格納しています。これにより、セルの入力値をマクロ内で使用することができます。
読み取った値から、コピー機の１回当たりの金額×コピー機の使用回数を計算し、コピー機で使用する合計金額を算出して「 all_of_price_of_copy 」変数に格納しています。
プリンターの1回当たりの金額の算出
paper_price_for_printer = cell_paper_price_for_printer 'プリンターの紙代セルから紙代を読み取り
ink_price_for_printer = cell_ink_price_for_printer 'プリンターのインク代セルからインク代を読み取ろ
price_of_print = paper_price_for_printer + ink_price_for_printer '紙代とインク代から1回あたりの金額を算出 先程のと同じ要領で、プリンターの１回当たりの合計金額を算出しています。
算出した結果は、「 price_of_print 」に格納しております。
結果をメッセージボックスで表示
MsgBox Prompt:="【コピー機】" & vbLf & _
"紙代は " & paper_price_for_copy & " 円" & vbLf & _
"インク代は " & ink_price_for_copy & " 円" & vbLf & _
"1回あたりの金額は " & price_of_copy & " 円" & vbLf & _
"使用回数は " & count_of_doing_copy & " 回" & vbLf & _
vbLf & _
"合計金額は " & all_of_price_of_copy & " 円" & vbLf & _
vbLf & _
"【プリンター】" & vbLf & _
"紙代は " & paper_price_for_printer & " 円" & vbLf & _
"インク代は " & ink_price_for_printer & " 円" & vbLf & _
"1回あたりの金額は " & price_of_print & " 円" & vbLf & _
vbLf & _
"コピー機の使用回数 " & count_of_doing_copy & "回 に対して、" & vbLf & _
"プリンターは " & count_of_doing_print & "回 まで使用可能", _
Title:="コピー機使用の合計金額に対するプリンターの使用可能回数"
「MsgBox」から始まると、ポップアップを表示する処理になります。
「Prompt:=」で始まる部分は、ポップアップ内に表示される文章を設定しています。
「Title:=」で始まる部分は、ポップアップ上部の帯に表示される文章を設定しています。
Prompt:= 内における「& 変数名&」の部分は、この処理を呼び出した時点の「変数名の変数」に格納されている数値が表示されます。ユーザーが選択したセルから読み取った数値や、その算出結果を格納している数値がここで表示されるように設定しています。
VBAマクロのソースコード一覧はこちらの「 ２.実践編_VBAマクロのソースコード掲載 」をご覧ください。