マクロ（Excel VBA）のコードを書くときのちょっとした工夫、シート名はなるべく変数で指定する､アクティブなシートを切り替える

松井元（まついはじめ）

7年前

Excel でシートの数がたくさんあり､複数のシートを処理するようなマクロ（Excel VBA）のコードを記述することがあると思います。

「シート1」の数値を処理して、「シート2」の数値からマイナスして、さらに「シート3」の数値と掛け算して・・・
といった具合に、いくつものシートをまたいで処理をするとシートの指定を何回も行わなければなりません。

また、シートの指定を行うために一行一行のコードが長くなってしまいます。

今日は、Excel VBA で複数のシートの処理を行う場合におけるちょっとした工夫についてお話しします。

●シート名はなるべく変数で指定する
●アクティブなシートを切り替える

1､複数のシートの処理を行うコードの一例
2､後でシート名を変更したい場合があるのでシート名はなるべく変数で指定しておく
3､アクティブなシートを切り替えてコードを短くする
4､まとめ

1､複数のシートの処理を行うコードの一例

シート設計

さて、一例として2つのシート「操作画面」と「顧問先情報」がある場合を考えてみます。

以下の「操作画面」シートのクリーム色のセルに顧問先コードを入力して、青色の実行ボタンを押せばマクロが実行します。

マクロは「顧問先情報」から次の情報を読み込んで「操作画面」シートに書き出します。

・顧問先名
・住所
・代表取締役
・電話番号
・売上高
・売上原価
・原価率
・利益率

※原価率、利益率は Excel VBA で計算させます。

「顧問先情報」シートです↓

コード

標準モジュールに記述したコードを以下に示します。

マクロ名は「Test」です。このマクロが上記の「操作画面」シートの実行ボタンに組み込まれています。

Sub Test()

    Dim Code        '顧問先コード'
    Dim Name        '顧問先名'
    Dim Address     '住所'
    Dim President   '代表取締役'
    Dim Phone       '電話番号'

    Dim Uriage      '売上高'
    Dim Urigen      '売上原価'
    Dim Genkaritsu  '原価率'
    Dim Riekiritsu  '利益率'

    '顧問先コードの読取り'
    Code = Worksheets("操作画面").Cells(6, 5).Value

    '顧問先コードに該当する顧問先を検索して情報を読取り'
    For i = 1 To 200

        If Worksheets("顧問先情報").Cells(i + 1, 1) = Code Then

            Name = Worksheets("顧問先情報").Cells(i + 1, 2)
            Address = Worksheets("顧問先情報").Cells(i + 1, 3)
            President = Worksheets("顧問先情報").Cells(i + 1, 4)
            Phone = Worksheets("顧問先情報").Cells(i + 1, 5)
            Uriage = Worksheets("顧問先情報").Cells(i + 1, 6)
            Urigen = Worksheets("顧問先情報").Cells(i + 1, 7)

        Else
        End If

    Next i

    '該当する顧問先が無い場合の処理'
    If Name = "" Then

        MsgBox "該当する顧問先がありません。正しい顧問先コードを入力して下さい。"
        Exit Sub

    Else
    End If

    '原価率、利益率の計算'
    Genkaritsu = Application.RoundDown(Urigen / Uriage, 3)   '原価率'
    Riekiritsu = 1 - Genkaritsu     '利益率'

    '顧問先情報の書出し'
    Worksheets("操作画面").Cells(8, 5) = Name
    Worksheets("操作画面").Cells(9, 5) = Address
    Worksheets("操作画面").Cells(10, 5) = President
    Worksheets("操作画面").Cells(11, 5) = Phone
    Worksheets("操作画面").Cells(13, 5) = Uriage
    Worksheets("操作画面").Cells(14, 5) = Urigen
    Worksheets("操作画面").Cells(16, 5) = Genkaritsu
    Worksheets("操作画面").Cells(17, 5) = Riekiritsu

End Sub

Sub Test()

Dim Code '顧問先コード'

Dim Name '顧問先名'

Dim Address '住所'

Dim President '代表取締役'

Dim Phone '電話番号'

Dim Uriage '売上高'

Dim Urigen '売上原価'

Dim Genkaritsu '原価率'

Dim Riekiritsu '利益率'

'顧問先コードの読取り'

Code = Worksheets("操作画面").Cells(6, 5).Value

'顧問先コードに該当する顧問先を検索して情報を読取り'

For i = 1 To 200

If Worksheets("顧問先情報").Cells(i + 1, 1) = Code Then

Name = Worksheets("顧問先情報").Cells(i + 1, 2)

Address = Worksheets("顧問先情報").Cells(i + 1, 3)

President = Worksheets("顧問先情報").Cells(i + 1, 4)

Phone = Worksheets("顧問先情報").Cells(i + 1, 5)

Uriage = Worksheets("顧問先情報").Cells(i + 1, 6)

Urigen = Worksheets("顧問先情報").Cells(i + 1, 7)

Else

End If

Next i

'該当する顧問先が無い場合の処理'

If Name = "" Then

MsgBox "該当する顧問先がありません。正しい顧問先コードを入力して下さい。"

Exit Sub

Else

End If

'原価率、利益率の計算'

Genkaritsu = Application.RoundDown(Urigen / Uriage, 3) '原価率'

Riekiritsu = 1 - Genkaritsu '利益率'

'顧問先情報の書出し'

Worksheets("操作画面").Cells(8, 5) = Name

Worksheets("操作画面").Cells(9, 5) = Address

Worksheets("操作画面").Cells(10, 5) = President

Worksheets("操作画面").Cells(11, 5) = Phone

Worksheets("操作画面").Cells(13, 5) = Uriage

Worksheets("操作画面").Cells(14, 5) = Urigen

Worksheets("操作画面").Cells(16, 5) = Genkaritsu

Worksheets("操作画面").Cells(17, 5) = Riekiritsu

End Sub

実行結果

試しに「操作画面」シートで顧問先コード「1005」を入力して実行してみます。

すると顧問先の情報が出力されます。

ちゃんと出てますね。

この情報は「顧問先情報」シートから該当するデータを読み込んだものです。

2､後でシート名を変更したい場合があるのでシート名はなるべく変数で指定しておく

さて、個々のコード全ての詳細な説明は割愛させて頂きますが、今日お伝えしたいところの１つ目について。

上記のコードでは、シート名をいちいち名前で指定しています。

このように↓

Name = Worksheets(“顧問先情報”).Cells(i + 1, 2)

Worksheets(“操作画面”).Cells(8, 5) = Name

その場合、後でシート名を変更したくなったときに1つ1つシート名を変更しなければなりません。

シート名を全て書き換えるのは面倒です（コピペでも）。

そのような場合に備えて、以下のコードのようにシート名はなるべく変数で指定するようにしています。

※やっていることは上記コードと同じです。

 Sub Test()

    Dim Code        '顧問先コード'
    Dim Name        '顧問先名'
    Dim Address     '住所'
    Dim President   '代表取締役'
    Dim Phone       '電話番号'

    Dim Uriage      '売上高'
    Dim Urigen      '売上原価'
    Dim Genkaritsu  '原価率'
    Dim Riekiritsu  '利益率'

    Dim Sheet_Name_1  'シート名'
    Dim Sheet_Name_2   'シート名'

    Sheet_Name_1 = "操作画面"
    Sheet_Name_2 = "顧問先情報"

    '顧問先コードの読取り'
    Code = Worksheets(Sheet_Name_1).Cells(6, 5).Value

    '顧問先コードに該当する顧問先を検索して情報を読取り'
    For i = 1 To 200

        If Worksheets(Sheet_Name_2).Cells(i + 1, 1) = Code Then

            Name = Worksheets(Sheet_Name_2).Cells(i + 1, 2)
            Address = Worksheets(Sheet_Name_2).Cells(i + 1, 3)
            President = Worksheets(Sheet_Name_2).Cells(i + 1, 4)
            Phone = Worksheets(Sheet_Name_2).Cells(i + 1, 5)
            Uriage = Worksheets(Sheet_Name_2).Cells(i + 1, 6)
            Urigen = Worksheets(Sheet_Name_2).Cells(i + 1, 7)

        Else
        End If

    Next i

    '該当する顧問先が無い場合の処理'
    If Name = "" Then

        MsgBox "該当する顧問先がありません。正しい顧問先コードを入力して下さい。"
        Exit Sub

    Else
    End If

    '原価率、利益率の計算'
    Genkaritsu = Application.RoundDown(Urigen / Uriage, 3)   '原価率'
    Riekiritsu = 1 - Genkaritsu     '利益率'

    '顧問先情報の書出し'
    Worksheets(Sheet_Name_1).Cells(8, 5) = Name
    Worksheets(Sheet_Name_1).Cells(9, 5) = Address
    Worksheets(Sheet_Name_1).Cells(10, 5) = President
    Worksheets(Sheet_Name_1).Cells(11, 5) = Phone
    Worksheets(Sheet_Name_1).Cells(13, 5) = Uriage
    Worksheets(Sheet_Name_1).Cells(14, 5) = Urigen
    Worksheets(Sheet_Name_1).Cells(16, 5) = Genkaritsu
    Worksheets(Sheet_Name_1).Cells(17, 5) = Riekiritsu

End Sub

Sub Test()

Dim Code '顧問先コード'

Dim Name '顧問先名'

Dim Address '住所'

Dim President '代表取締役'

Dim Phone '電話番号'

Dim Uriage '売上高'

Dim Urigen '売上原価'

Dim Genkaritsu '原価率'

Dim Riekiritsu '利益率'

Dim Sheet_Name_1 'シート名'

Dim Sheet_Name_2 'シート名'

Sheet_Name_1 = "操作画面"

Sheet_Name_2 = "顧問先情報"

'顧問先コードの読取り'

Code = Worksheets(Sheet_Name_1).Cells(6, 5).Value

'顧問先コードに該当する顧問先を検索して情報を読取り'

For i = 1 To 200

If Worksheets(Sheet_Name_2).Cells(i + 1, 1) = Code Then

Name = Worksheets(Sheet_Name_2).Cells(i + 1, 2)

Address = Worksheets(Sheet_Name_2).Cells(i + 1, 3)

President = Worksheets(Sheet_Name_2).Cells(i + 1, 4)

Phone = Worksheets(Sheet_Name_2).Cells(i + 1, 5)

Uriage = Worksheets(Sheet_Name_2).Cells(i + 1, 6)

Urigen = Worksheets(Sheet_Name_2).Cells(i + 1, 7)

Else

End If

Next i

'該当する顧問先が無い場合の処理'

If Name = "" Then

MsgBox "該当する顧問先がありません。正しい顧問先コードを入力して下さい。"

Exit Sub

Else

End If

'原価率、利益率の計算'

Genkaritsu = Application.RoundDown(Urigen / Uriage, 3) '原価率'

Riekiritsu = 1 - Genkaritsu '利益率'

'顧問先情報の書出し'

Worksheets(Sheet_Name_1).Cells(8, 5) = Name

Worksheets(Sheet_Name_1).Cells(9, 5) = Address

Worksheets(Sheet_Name_1).Cells(10, 5) = President

Worksheets(Sheet_Name_1).Cells(11, 5) = Phone

Worksheets(Sheet_Name_1).Cells(13, 5) = Uriage

Worksheets(Sheet_Name_1).Cells(14, 5) = Urigen

Worksheets(Sheet_Name_1).Cells(16, 5) = Genkaritsu

Worksheets(Sheet_Name_1).Cells(17, 5) = Riekiritsu

End Sub

最初にシート名を表す変数

「Sheet_Name_1」
「Sheet_Name_2」

を定義しています。

Dim Sheet_Name_1
Dim Sheet_Name_2

そしてすぐにシート名を決めています。

Sheet_Name_1 = “操作画面”
Sheet_Name_2 = “顧問先情報”

その後のシートの指定は､全て変数を使って行っています。

このようにです↓

Name = Worksheets(Sheet_Name_1).Cells(i + 1, 2)

Worksheets(Sheet_Name_2).Cells(8, 5) = Name

こうしておけば、後でシート名を変更したくなったときには、変数に入力する名前を変えるだけで対応できるので楽です。

3､アクティブなシートを切り替えてコードを短くする

また、上記のシートを変数を使って指定する方法に加えて、アクティブなシートを切り替えるのも有効です。

※アクティブというのは操作対象になっていることを意味します。

分かりやすく言えばそのシートを開いている状態のことです。

Worksheets(Sheet_Name_1).Activate

と記述すれば、「Sheet_Name_1」シートがアクティブになり

Worksheets(Sheet_Name_2).Activate

と記述すれば、「Sheet_Name_2」シートがアクティブになります。

シートをアクティブにした後に、そのシート内の数値を読み込むときにはシート名の指定は不要になります。

Name = Worksheets(Sheet_Name_2).Cells(i + 1, 2)

と記述していた部分は

Worksheets(Sheet_Name_2).Activate

としてSheet_Name_2」シートをアクティブにした後に

Name = Cells(i + 1, 2)

とワークシートを指定せずに記述すればよくなります。

アクティブなシートを切り替えたコードを以下に示します。

※やっていることは上記コードと同じです。

 Sub Test()

    Dim Code        '顧問先コード'
    Dim Name        '顧問先名'
    Dim Address     '住所'
    Dim President   '代表取締役'
    Dim Phone       '電話番号'

    Dim Uriage      '売上高'
    Dim Urigen      '売上原価'
    Dim Genkaritsu  '原価率'
    Dim Riekiritsu  '利益率'

    Dim Sheet_Name_1  'シート名'
    Dim Sheet_Name_2   'シート名'

    Sheet_Name_1 = "操作画面"
    Sheet_Name_2 = "顧問先情報"

    '顧問先コードの読取り'
    Code = Worksheets(Sheet_Name_1).Cells(6, 5).Value

    '顧問先コードに該当する顧問先を検索して情報を読取り'
    Worksheets(Sheet_Name_2).Activate

    For i = 1 To 200

        If Cells(i + 1, 1) = Code Then

            Name = Cells(i + 1, 2)
            Address = Cells(i + 1, 3)
            President = Cells(i + 1, 4)
            Phone = Cells(i + 1, 5)
            Uriage = Cells(i + 1, 6)
            Urigen = Cells(i + 1, 7)

        Else
        End If

    Next i

    '該当する顧問先が無い場合の処理'
    If Name = "" Then

        MsgBox "該当する顧問先がありません。正しい顧問先コードを入力して下さい。"
        Exit Sub

    Else
    End If

    '原価率、利益率の計算'
    Genkaritsu = Application.RoundDown(Urigen / Uriage, 3)   '原価率'
    Riekiritsu = 1 - Genkaritsu     '利益率'

    '顧問先情報の書出し'
    Worksheets(Sheet_Name_1).Activate

    Cells(8, 5) = Name
    Cells(9, 5) = Address
    Cells(10, 5) = President
    Cells(11, 5) = Phone
    Cells(13, 5) = Uriage
    Cells(14, 5) = Urigen
    Cells(16, 5) = Genkaritsu
    Cells(17, 5) = Riekiritsu

End Sub

Sub Test()

Dim Code '顧問先コード'

Dim Name '顧問先名'

Dim Address '住所'

Dim President '代表取締役'

Dim Phone '電話番号'

Dim Uriage '売上高'

Dim Urigen '売上原価'

Dim Genkaritsu '原価率'

Dim Riekiritsu '利益率'

Dim Sheet_Name_1 'シート名'

Dim Sheet_Name_2 'シート名'

Sheet_Name_1 = "操作画面"

Sheet_Name_2 = "顧問先情報"

'顧問先コードの読取り'

Code = Worksheets(Sheet_Name_1).Cells(6, 5).Value

'顧問先コードに該当する顧問先を検索して情報を読取り'

Worksheets(Sheet_Name_2).Activate

For i = 1 To 200

If Cells(i + 1, 1) = Code Then

Name = Cells(i + 1, 2)

Address = Cells(i + 1, 3)

President = Cells(i + 1, 4)

Phone = Cells(i + 1, 5)

Uriage = Cells(i + 1, 6)

Urigen = Cells(i + 1, 7)

Else

End If

Next i

'該当する顧問先が無い場合の処理'

If Name = "" Then

MsgBox "該当する顧問先がありません。正しい顧問先コードを入力して下さい。"

Exit Sub

Else

End If

'原価率、利益率の計算'

Genkaritsu = Application.RoundDown(Urigen / Uriage, 3) '原価率'

Riekiritsu = 1 - Genkaritsu '利益率'

'顧問先情報の書出し'

Worksheets(Sheet_Name_1).Activate

Cells(8, 5) = Name

Cells(9, 5) = Address

Cells(10, 5) = President

Cells(11, 5) = Phone

Cells(13, 5) = Uriage

Cells(14, 5) = Urigen

Cells(16, 5) = Genkaritsu

Cells(17, 5) = Riekiritsu

End Sub

セルを指定する際にワークシートの指定は不要になるのでコードが短くなりスッキリします。

4､まとめ

Excel VBA のコードはできる限りスッキリと書きたいものです。

また、変更があったときの作業の負担も抑えたいです。

上記のようなやり方も、そのための一例として参考にして頂ければと思います。