マクロ（Excel VBA）「Application.Caller」を使ってプロシージャを1つに統一する

マクロ（Excel VBA）で標準モジュールにコードを記述するに場合、プロシージャの数は少ない方が良いでしょう。

1つの標準モジュールの中にいくつものプロシージャがあるよりも、1つの方がすっきりします。

今日は、Application オブジェクトの Caller プロパティを使ってプロシージャを1つに統一することができる例を紹介します。

1、原価率、利益率、売上総利益を計算するシート
1. シート設計
2. プログラミングコード
2、「Application.Caller」の使い方
3、「Application.Caller」を使うメリット
4、まとめ

1、原価率、利益率、売上総利益を計算するシート

シート設計

次のシートを実例として使います。

「売上」と「売上原価」を数値で入力してから、原価率、利益率、売上総利益のいずれかを計算することができるシートです。

それぞれ次のように計算できます↓

●原価率＝売上原価 / 売上
●利益率＝（売上－売上原価）/ 売上
●売上総利益＝売上－売上原価

※マクロを使わなくても簡単に計算できる内容ですが、ここでは敢えて使いますね。

原価率、利益率、売上総利益で計算したい項目の実行ボタンを押せぼ、マクロが実行します。

「売上」と「売上原価」を数値で入力してから、「原価率計算」をクリックすれば、セル「E11」に原価率の計算結果が出力されます。

「利益率計算」をクリックすれば、セル「E11」に利益率の計算結果が出力されます。

「売上総利益計算」をクリックすれば、セル「E11」に売上総利益の計算結果が出力されます。

さて、これらの実行ボタンには1つのマクロ「Keisan」が登録されています。

3つのボタンに同じマクロが登録されているのに、出力される結果は異なることになります。

プログラミングコード

プログラミングコードは標準モジュールに以下のものを記述してあります。

Sub Keisan()
    
    '入力値'
    Dim Uriage  '売上'
    Dim Genka  '売上原価'
    
    '出力値'
    Dim Output  '原価率、利益率、売上総利益'
    
    '入力値読み込み'
    Uriage = Cells(8, 5)  '売上 読み込み'
    Genka = Cells(9, 5)  '売上原価 読み込み'
    
    '売上の入力値確認'
    If IsNumeric(Uriage) = False Or Uriage <= 0 Then
    
    
        MsgBox "「売上」には0より大きい数値を入力して下さい。"
        Cells(8, 5).ClearContents
        Cells(11, 5).ClearContents
        Exit Sub
        
    Else
    End If
    
    '売上原価の入力値確認'
    If IsNumeric(Genka) = False Or Genka <= 0 Then
    
        MsgBox "「売上原価」には0より大きい数値を入力して下さい。"
        Cells(9, 5).ClearContents
        Cells(11, 5).ClearContents
        Exit Sub
        
    Else
    End If
    
    
    Select Case Application.Caller
        
        Case "原価率計算"
        
            Output = Genka / Uriage '原価率＝売上原価／売上'
            Cells(11, 4) = "原価率"
            
            Cells(11, 5).NumberFormatLocal = "#,##0.##"
            Cells(11, 5) = Output
            
        Case "利益率計算"
        
            Output = (Uriage - Genka) / Uriage '利益率＝売上総利益／売上'
            Cells(11, 4) = "利益率"
            
            Cells(11, 5).NumberFormatLocal = "#,##0.##"
            Cells(11, 5) = Output
            
        Case "売上総利益計算"
        
            Output = Uriage - Genka '売上総利益＝売上ー売上原価'
            Cells(11, 4) = "売上総利益"
            
            Cells(11, 5).NumberFormatLocal = "#,###;""△"" #,###"
            Cells(11, 5) = Output
            
    End Select
    
End Sub

Sub Keisan()

'入力値'

Dim Uriage '売上'

Dim Genka '売上原価'

'出力値'

Dim Output '原価率、利益率、売上総利益'

'入力値読み込み'

Uriage = Cells(8, 5) '売上読み込み'

Genka = Cells(9, 5) '売上原価読み込み'

'売上の入力値確認'

If IsNumeric(Uriage) = False Or Uriage <= 0 Then

MsgBox "「売上」には0より大きい数値を入力して下さい。"

Cells(8, 5).ClearContents

Cells(11, 5).ClearContents

Exit Sub

Else

End If

'売上原価の入力値確認'

If IsNumeric(Genka) = False Or Genka <= 0 Then

MsgBox "「売上原価」には0より大きい数値を入力して下さい。"

Cells(9, 5).ClearContents

Cells(11, 5).ClearContents

Exit Sub

Else

End If

Select Case Application.Caller

Case "原価率計算"

Output = Genka / Uriage '原価率＝売上原価／売上'

Cells(11, 4) = "原価率"

Cells(11, 5).NumberFormatLocal = "#,##0.##"

Cells(11, 5) = Output

Case "利益率計算"

Output = (Uriage - Genka) / Uriage '利益率＝売上総利益／売上'

Cells(11, 4) = "利益率"

Cells(11, 5).NumberFormatLocal = "#,##0.##"

Cells(11, 5) = Output

Case "売上総利益計算"

Output = Uriage - Genka '売上総利益＝売上ー売上原価'

Cells(11, 4) = "売上総利益"

Cells(11, 5).NumberFormatLocal = "#,###;""△"" #,###"

Cells(11, 5) = Output

End Select

End Sub

2、「Application.Caller」の使い方

今回のプログラミングコードのポイントは「Application.Caller」です。

Application オブジェクトの Caller プロパティの値によって処理を分岐させているのです。

Application オブジェクトとは Excelアプリケーション全体を表し、Caller プロパティとはVBA を呼び出した方法についての情報を返します。

Select Case ステートメントを使って、「Application.Caller」を複数の条件に分けています。

    Select Case Application.Caller
        
        Case "原価率計算"
        
            Output = Genka / Uriage '原価率＝売上原価／売上'
            Cells(11, 4) = "原価率"
            
            Cells(11, 5).NumberFormatLocal = "#,##0.##"
            Cells(11, 5) = Output
            
        Case "利益率計算"
        
            Output = (Uriage - Genka) / Uriage '利益率＝売上総利益／売上'
            Cells(11, 4) = "利益率"
            
            Cells(11, 5).NumberFormatLocal = "#,##0.##"
            Cells(11, 5) = Output
            
        Case "売上総利益計算"
        
            Output = Uriage - Genka '売上総利益＝売上ー売上原価'
            Cells(11, 4) = "売上総利益"
            
            Cells(11, 5).NumberFormatLocal = "#,###;""△"" #,###"
            Cells(11, 5) = Output
            
    End Select

Select Case Application.Caller

Case "原価率計算"

Output = Genka / Uriage '原価率＝売上原価／売上'

Cells(11, 4) = "原価率"

Cells(11, 5).NumberFormatLocal = "#,##0.##"

Cells(11, 5) = Output

Case "利益率計算"

Output = (Uriage - Genka) / Uriage '利益率＝売上総利益／売上'

Cells(11, 4) = "利益率"

Cells(11, 5).NumberFormatLocal = "#,##0.##"

Cells(11, 5) = Output

Case "売上総利益計算"

Output = Uriage - Genka '売上総利益＝売上ー売上原価'

Cells(11, 4) = "売上総利益"

Cells(11, 5).NumberFormatLocal = "#,###;""△"" #,###"

Cells(11, 5) = Output

End Select

実行ボタンの名前を、それぞれ

・原価率計算
・利益率計算
・売上総利益計算

としています。

図形を選択すると左上に、その図形の名前が表示されます。

ここで名前を変更することもできます。

VBA が「原価率計算」から呼び出された場合、原価率の計算を行い出力します。

「利益率計算」「売上総利益計算」も同様です。

3、「Application.Caller」を使うメリット

さて、今回「Application.Caller」を使うことによるメリットは冒頭にも書いたように、プロシージャを1つに統一できることです。

もし仮に「Application.Caller」を使わなかったら、実行ボタンそれぞれに異なるマクロを登録しなければならなくなります。

その場合1つのモジュールの中に次のように3つのプロシージャを作成することになります。

Sub Genkaritsu()

    '入力値
    Dim Uriage  '売上'
    Dim Genka  '売上原価'

    '出力値
    Dim Output  '原価率'

    Uriage = Cells(8, 5)  '売上 読み込み'
    Genka = Cells(9, 5)  '売上原価 読み込み'

    '売上の入力値確認'
    If IsNumeric(Uriage) = False Or Uriage <= 0 Then

        MsgBox "「売上」には0より大きい数値を入力して下さい。"
        Cells(8, 5).ClearContents
        Cells(11, 5).ClearContents
        Exit Sub

    Else
    End If

    '売上原価の入力値確認'
    If IsNumeric(Genka) = False Or Genka <= 0 Then

        MsgBox "「売上原価」には0より大きい数値を入力して下さい。"
        Cells(9, 5).ClearContents
        Cells(11, 5).ClearContents
        Exit Sub

    Else
    End If


    Output = Genka / Uriage '原価率＝売上原価／売上'
    Cells(11, 4) = "原価率"

    Cells(11, 5).NumberFormatLocal = "#,##0.##"
    Cells(11, 5) = Output

End Sub
Sub Riekiritsu()

    '入力値
    Dim Uriage  '売上'
    Dim Genka  '売上原価'

    '出力値
    Dim Output  '利益率'

    Uriage = Cells(8, 5)  '売上 読み込み'
    Genka = Cells(9, 5)  '売上原価 読み込み'

    '売上の入力値確認'
    If IsNumeric(Uriage) = False Or Uriage <= 0 Then

        MsgBox "「売上」には0より大きい数値を入力して下さい。"
        Cells(8, 5).ClearContents
        Cells(11, 5).ClearContents
        Exit Sub

    Else
    End If

    '売上原価の入力値確認'
    If IsNumeric(Genka) = False Or Genka <= 0 Then

        MsgBox "「売上原価」には0より大きい数値を入力して下さい。"
        Cells(9, 5).ClearContents
        Cells(11, 5).ClearContents
        Exit Sub

    Else
    End If


    Output = (Uriage - Genka) / Uriage '利益率＝売上総利益／売上'
    Cells(11, 4) = "利益率"

    Cells(11, 5).NumberFormatLocal = "#,##0.##"
    Cells(11, 5) = Output

End Sub
Sub Uriagesourieki()

    '入力値
    Dim Uriage  '売上'
    Dim Genka  '売上原価'

    '出力値
    Dim Output  '売上総利益'

    Uriage = Cells(8, 5)  '売上 読み込み'
    Genka = Cells(9, 5)  '売上原価 読み込み'

    '売上の入力値確認'
    If IsNumeric(Uriage) = False Or Uriage <= 0 Then

        MsgBox "「売上」には0より大きい数値を入力して下さい。"
        Cells(8, 5).ClearContents
        Cells(11, 5).ClearContents
        Exit Sub

    Else
    End If

    '売上原価の入力値確認'
    If IsNumeric(Genka) = False Or Genka <= 0 Then

        MsgBox "「売上原価」には0より大きい数値を入力して下さい。"
        Cells(9, 5).ClearContents
        Cells(11, 5).ClearContents
        Exit Sub

    Else
    End If


    Output = Uriage - Genka '売上総利益＝売上ー売上原価'
    Cells(11, 4) = "売上総利益"

    Cells(11, 5).NumberFormatLocal = "#,###;""△"" #,###"
    Cells(11, 5) = Output

End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

Sub Genkaritsu()

'入力値

Dim Uriage '売上'

Dim Genka '売上原価'

'出力値

Dim Output '原価率'

Uriage = Cells(8, 5) '売上読み込み'

Genka = Cells(9, 5) '売上原価読み込み'

'売上の入力値確認'

If IsNumeric(Uriage) = False Or Uriage <= 0 Then

MsgBox "「売上」には0より大きい数値を入力して下さい。"

Cells(8, 5).ClearContents

Cells(11, 5).ClearContents

Exit Sub

Else

End If

'売上原価の入力値確認'

If IsNumeric(Genka) = False Or Genka <= 0 Then

MsgBox "「売上原価」には0より大きい数値を入力して下さい。"

Cells(9, 5).ClearContents

Cells(11, 5).ClearContents

Exit Sub

Else

End If

Output = Genka / Uriage '原価率＝売上原価／売上'

Cells(11, 4) = "原価率"

Cells(11, 5).NumberFormatLocal = "#,##0.##"

Cells(11, 5) = Output

End Sub

Sub Riekiritsu()

'入力値

Dim Uriage '売上'

Dim Genka '売上原価'

'出力値

Dim Output '利益率'

Uriage = Cells(8, 5) '売上読み込み'

Genka = Cells(9, 5) '売上原価読み込み'

'売上の入力値確認'

If IsNumeric(Uriage) = False Or Uriage <= 0 Then

MsgBox "「売上」には0より大きい数値を入力して下さい。"

Cells(8, 5).ClearContents

Cells(11, 5).ClearContents

Exit Sub

Else

End If

'売上原価の入力値確認'

If IsNumeric(Genka) = False Or Genka <= 0 Then

MsgBox "「売上原価」には0より大きい数値を入力して下さい。"

Cells(9, 5).ClearContents

Cells(11, 5).ClearContents

Exit Sub

Else

End If

Output = (Uriage - Genka) / Uriage '利益率＝売上総利益／売上'

Cells(11, 4) = "利益率"

Cells(11, 5).NumberFormatLocal = "#,##0.##"

Cells(11, 5) = Output

End Sub

Sub Uriagesourieki()

'入力値

Dim Uriage '売上'

Dim Genka '売上原価'

'出力値

Dim Output '売上総利益'

Uriage = Cells(8, 5) '売上読み込み'

Genka = Cells(9, 5) '売上原価読み込み'

'売上の入力値確認'

If IsNumeric(Uriage) = False Or Uriage <= 0 Then

MsgBox "「売上」には0より大きい数値を入力して下さい。"

Cells(8, 5).ClearContents

Cells(11, 5).ClearContents

Exit Sub

Else

End If

'売上原価の入力値確認'

If IsNumeric(Genka) = False Or Genka <= 0 Then

MsgBox "「売上原価」には0より大きい数値を入力して下さい。"

Cells(9, 5).ClearContents

Cells(11, 5).ClearContents

Exit Sub

Else

End If

Output = Uriage - Genka '売上総利益＝売上ー売上原価'

Cells(11, 4) = "売上総利益"

Cells(11, 5).NumberFormatLocal = "#,###;""△"" #,###"

Cells(11, 5) = Output

End Sub

全てのプロシージャで同じ内容を記述しなければならない部分があり、無駄になります。

Select Case ステートメントを使って「Application.Caller」で条件分岐する方が、無駄に重複するコードを書かなくてもよいのですっきりします。

4、まとめ

「Application.Caller」を使えばプロシージャを1つに統一できるケースについて説明しました。

プログラミングコードの無駄を省き、できるだけ簡素にできる工夫は必要です。