MENU
Excel Macro Laboratory
  1. ホーム
  2. ExcelVBA解説編
  3. 【VBA】10.2 [emalabo式] ワークブック・ワークシートのコーディングルールをマスターしよう！

【VBA】10.2 [emalabo式] ワークブック・ワークシートのコーディングルールをマスターしよう！

ExcelVBA解説編
目次
  1. ワークブック・ワークシートについて、ほとんど知られていない事実を公開！
  2. 今回、タイプA・タイプBで実装するExcelマクロの想定業務と仕様の解説
    1. 想定業務の解説
    2. 仕様の解説
    3. Excelファイルのイメージ
      1. 未受講者一覧シート作成マクロ
      2. eラーニング未受講者一覧ファイル
      3. 社員情報ファイル
  3. タイプAのソースコード
  4. タイプBのソースコード
  5. ソースコードの解説
    1. [emalabo式] XLOOKUP関数の使い方について
    2. [emalabo式] ワークブック変数・ワークシート変数のオブジェクトの割り当てのテクニック
  6. タイプAとタイプBの比較
  7. 今回の結果からの考察
  8. [emalabo式] ワークブック・ワークシートのコーディングルール

ワークブック・ワークシートについて、ほとんど知られていない事実を公開！

私の推測が正しければ、Excelマクロのプログラマーは次の2種類のいずれかの方法でワークブック・ワークシートを操作していると考えられます。

タイプA
（Activate推進派）		ワークブック・ワークシートに対して、ワークブック変数・ワークシート変数の宣言・割り当てをしない。
ワークブック・ワークシートを操作する際は、その時点でActiveなワークブック・ワークシートを確認し、Activeでなければそのワークブック・ワークシートをActivateした後で、ワークブック・ワークシートの操作を実行する。
タイプB
（Activate拒絶派）		全てのワークブック・ワークシートに対して、ワークブック変数・ワークシート変数の宣言・割り当てをする。
ワークブック・ワークシートを操作する際は、操作対象のワークブック・ワークシートをActivateするのはでなく、ワークブック変数・ワークシート変数に対して何らかの操作を実行する。

この2つのタイプに対する私の分析結果は以下の通りです。（あくまで、個人的見解です。）

タイプ名この方法を採用する理由多数派/少数派
タイプA全てのワークブック変数・ワークシート変数の宣言・割り当てをするよりは、
ワークブック・ワークシートを操作する都度Activateする方がマシ。		多数派
タイプBワークブック・ワークシートを操作する都度Activateするよりは、
全てのワークブック変数・ワークシート変数の宣言・割り当てをする方がマシ。		少数派

文章だけでは分かりづらいところもありますので、同一の処理を行うプロシージャをそれぞれのタイプの方法でコーディングしたものを以下に掲載し、比較していきます。

今回、タイプA・タイプBで実装するExcelマクロの想定業務と仕様の解説

想定業務の解説

社内で実施したeラーニングの未受講者に対して、個別に名指しでeラーニングの受講を促すメールを送信する業務で、eラーニングシステムから取得した未受講者の社員番号を元に、社内システムから取得した社員情報ファイルからメールアドレスと社員氏名を取得し、eラーニングの未受講者一覧シートを作成します。

仕様の解説

今回、以下の4つのExcelファイルが登場します。

ファイル名ファイル種別ファイルの解説
未受講者一覧シート作成マクロマクロファイルタイプA・タイプBのマクロを実装し、以下の
入力ファイルから出力ファイルを作成するマクロ
eラーニング未受講者一覧ファイル入力ファイルeラーニングシステムから未受講者の社員番号を
出力したファイル
社員情報ファイル入力ファイル社内システムから作成した社員の各種属性情報を
持っているファイル
eラーニング未受講者一覧ファイル出力ファイル入力ファイルの「eラーニング未受講者一覧ファイル」
に持っている社員番号からメールアドレスと社員氏名を
取得し、このファイルにそれらの情報を出力し、
入力ファイルとは別に出力ファイルを作成する。

基本的な処理の流れは以下の通りです。

  1. 2つの入力ファイルをOpenする。
  2. eラーニング未受講者一覧ファイルのA列の社員番号をキーにして、XLOOKUPで社員情報ファイルからメールアドレスと社員氏名を取得し、eラーニング未受講者一覧ファイルのB列にメールアドレス、C列に社員氏名を出力する。
  3. 上記の2.で作成したeラーニング未受講者一覧ファイルを出力フォルダに格納する。
  4. 2つの入力ファイルを、保存せずにCloseする

Excelファイルのイメージ

今回作成する各Excelファイルのイメージは以下の通りです。

未受講者一覧シート作成マクロ

eラーニング未受講者一覧マクロ

eラーニング未受講者一覧ファイル

eラーニング未受講者一覧ファイル

社員情報ファイル

社員情報ファイル

タイプAのソースコード

Option Explicit

Sub make_elearningunadministeredlist()

    Dim path_unadministeredlist As String
    Dim path_employeelist As String
    Dim path_outputfile As String
    Dim num_row As Long
    
    Application.ScreenUpdating = False

    If Right(ThisWorkbook.Worksheets(1).Range("D3"), 1) <> "\" Then
        path_unadministeredlist = ThisWorkbook.Worksheets(1).Range("D3") & "\" & ThisWorkbook.Worksheets(1).Range("C3")
    Else
        path_unadministeredlist = ThisWorkbook.Worksheets(1).Range("D3") & ThisWorkbook.Worksheets(1).Range("C3")
    End If
    
    If Right(ThisWorkbook.Worksheets(1).Range("D4"), 1) <> "\" Then
        path_employeelist = ThisWorkbook.Worksheets(1).Range("D4") & "\" & ThisWorkbook.Worksheets(1).Range("C4")
    Else
        path_employeelist = ThisWorkbook.Worksheets(1).Range("D4") & ThisWorkbook.Worksheets(1).Range("C4")
    End If
    
    If Right(ThisWorkbook.Worksheets(1).Range("D5"), 1) <> "\" Then
        path_outputfile = ThisWorkbook.Worksheets(1).Range("D5") & "\" & ThisWorkbook.Worksheets(1).Range("C5")
    Else
        path_outputfile = ThisWorkbook.Worksheets(1).Range("D5") & ThisWorkbook.Worksheets(1).Range("C5")
    End If
    
    Workbooks.Open Filename:=path_unadministeredlist
    
    Workbooks.Open Filename:=path_employeelist
    
    Workbooks("eラーニング未受講者一覧ファイル.xlsx").Worksheets(1).Activate
    
    For num_row = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        ActiveSheet.Cells(num_row, 2) = WorksheetFunction.XLookup(ActiveSheet.Cells(num_row, 1), Workbooks("社員情報ファイル.xlsx").Worksheets(1).Columns(1), Workbooks("社員情報ファイル.xlsx").Worksheets(1).Columns(4), vbNullString)
    
        ActiveSheet.Cells(num_row, 3) = WorksheetFunction.XLookup(ActiveSheet.Cells(num_row, 1), Workbooks("社員情報ファイル.xlsx").Worksheets(1).Columns(1), Workbooks("社員情報ファイル.xlsx").Worksheets(1).Columns(2), vbNullString)
    Next num_row
    
    ActiveWorkbook.SaveAs Filename:=path_outputfile
    
    Workbooks("eラーニング未受講者一覧ファイル.xlsx").Close SaveChanges:=False
    Workbooks("社員情報ファイル.xlsx").Close SaveChanges:=True
    
    MsgBox "eラーニング未受講者一覧の作成が完了しました。"
    
    ThisWorkbook.Worksheets(1).Activate
    
    ThisWorkbook.Save
    
    Application.ScreenUpdating = True
    
End Sub
VBA

タイプBのソースコード

Option Explicit

Sub make_elearningunadministeredlist()

    Dim ws_macro As Worksheet
    Dim ws_work As Worksheet
    Dim wb_unadministeredlist As Workbook
    Dim ws_unadministeredlist As Worksheet
    Dim path_unadministeredlist As String
    Dim wb_employeelist As Workbook
    Dim ws_employeelist As Worksheet
    Dim path_employeelist As String
    Dim path_outputfile As String
    Dim num_row As Long
    
    Application.ScreenUpdating = False

    Set ws_macro = ThisWorkbook.Worksheets("eラーニング未受講者一覧作成マクロ")
    Set ws_work = ThisWorkbook.Worksheets("work")
    
    If Right(ws_macro.Range("D3"), 1) <> "\" Then
        path_unadministeredlist = ws_macro.Range("D3") & "\" & ws_macro.Range("C3")
    Else
        path_unadministeredlist = ws_macro.Range("D3") & ws_macro.Range("C3")
    End If
    
    If Right(ws_macro.Range("D4"), 1) <> "\" Then
        path_employeelist = ws_macro.Range("D4") & "\" & ws_macro.Range("C4")
    Else
        path_employeelist = ws_macro.Range("D4") & ws_macro.Range("C4")
    End If
    
    If Right(ws_macro.Range("D5"), 1) <> "\" Then
        path_outputfile = ws_macro.Range("D5") & "\" & ws_macro.Range("C5")
    Else
        path_outputfile = ws_macro.Range("D5") & ws_macro.Range("C5")
    End If
    
    Workbooks.Open Filename:=path_unadministeredlist
    Set wb_unadministeredlist = ActiveWorkbook
    Set ws_unadministeredlist = ActiveWorkbook.Worksheets(1)
        
    Workbooks.Open Filename:=path_employeelist
    Set wb_employeelist = ActiveWorkbook
    Set ws_employeelist = ActiveWorkbook.Worksheets(1)
    
    For num_row = 2 To ws_unadministeredlist.Cells(Rows.Count, 1).End(xlUp).Row
        ws_unadministeredlist.Cells(num_row, 2) = WorksheetFunction.XLookup(ws_unadministeredlist.Cells(num_row, 1), ws_employeelist.Columns(1), ws_employeelist.Columns(4), vbNullString)
    
        ws_unadministeredlist.Cells(num_row, 3) = WorksheetFunction.XLookup(ws_unadministeredlist.Cells(num_row, 1), ws_employeelist.Columns(1), ws_employeelist.Columns(2), vbNullString)
    Next num_row
    
    wb_unadministeredlist.SaveAs Filename:=path_outputfile
    
    wb_unadministeredlist.Close SaveChanges:=False
    wb_employeelist.Close SaveChanges:=True
    
    MsgBox "eラーニング未受講者一覧の作成が完了しました。"
    
    ws_macro.Activate
    
    ThisWorkbook.Save
    
    Application.ScreenUpdating = True
    
End Sub
VBA

ソースコードの解説

[emalabo式] XLOOKUP関数の使い方について

    For num_row = 2 To ws_unadministeredlist.Cells(Rows.Count, 1).End(xlUp).Row
        ws_unadministeredlist.Cells(num_row, 2) = WorksheetFunction.XLookup(ws_unadministeredlist.Cells(num_row, 1), ws_employeelist.Columns(1), ws_employeelist.Columns(4), vbNullString)
    
        ws_unadministeredlist.Cells(num_row, 3) = WorksheetFunction.XLookup(ws_unadministeredlist.Cells(num_row, 1), ws_employeelist.Columns(1), ws_employeelist.Columns(2), vbNullString)
    Next num_row
VBA

タイプAの36タイプ行目～40行目、タイプBの47行目～51行目のソースコードでXLOOKUP関数を使用しています。
XLOOKUP関数は近年公開された関数で、VLOOKUP関数の使いづらい点を修正したものですが、このXLOOKUP関数の使い方については、以下の記事をご参照ください。

emalabo.com
【VBA】15.2 XLOOKUP関数を使ってみよう！ XLOOKUP関数を使ったExcelVBAの活用法を解説します。VLOOKUP関数から格段に向上した利便性の解説、他のサイトでは見ることのできない「emalabo式コーディング」を通して、…

[emalabo式] ワークブック変数・ワークシート変数のオブジェクトの割り当てのテクニック

    Workbooks.Open Filename:=path_unadministeredlist
    Set wb_unadministeredlist = ActiveWorkbook
    Set ws_unadministeredlist = ActiveWorkbook.Worksheets(1)
VBA

タイプBのソースコードの39行目では、eラーニング未受講者一覧ファイルをOpenし、その直後でwb_unadministeredlistとws_unadministeredlistに対するオブジェクトの割り当てを、ActiveWorkbookを使用して行っています。
これは、ExcelVBAではExcelファイルをOpenした直後のActiveWorkbookは、自動的にOpenしたExcelファイルに設定される、という特性を利用したものです。

タイプAとタイプBの比較

タイプAとタイプBを比較すると、以下のことが言えると思います。

  • タイプA：55行に対し、タイプB：66行と、タイプAの方がソースコードは短いことが分かります。
    これは、タイプAはワークブック変数、ワークシート変数の宣言を行う必要がない、という点が大きく影響しているものと考えられます。

  • タイプAに対し、タイプBの方が全体的に行の文字数が少ないことが分かります。
    これは、タイプAはActiveでないワークブック、ワークシートの記述が冗長にならざるを得ないためと考えられます。

今回の結果からの考察

今回、同じ仕様のExcelマクロをタイプAとタイプBの両方でコーディングしてみましたが、その結果から様々なケースを想定し、考察してみると、以下のことが言えると考えております。

タイプA
タイプB
  • 頻繁にActiveなワークブック・ワークシートを変更する必要のあるExcelマクロであった場合は、ソースコードが長くなり、Activeなワークブック・ワークシートを把握する手間が増えると考えられる。
  • Activeでないワークブック、ワークシートの記述が冗長になるため、引数が多く、引数の値にActiveでないワークブック、ワークシートを設定する必要がある場合は、行の文字数が長くなる傾向があると考えられる。
  • Excelマクロの仕様によっては、タイプAと比較して行数が多くなる場合がある。
  • その一方で、タイプAのように仕様によって行数や行の文字数が大幅に変動する要素が少ないため、行数や行の文字数の変動幅が小さいと考えられる。

[emalabo式] ワークブック・ワークシートのコーディングルール

ワークブック・ワークシートのタイプAとタイプBのコーディング方法については、明らかな優劣が判明したわけではなく、また個人的な好みもあると思いますので、最終判断は皆さん自身に委ねるしかありませんが、emalabo式のコーディングルールとしては、行数や行の文字数の変動幅が小さいと考えられるタイプBを採用することといたします。

次の記事

emalabo.com
【VBA】10.3 [emalabo式] ループ処理を作成する際のコーディングルールをマスターしよう！ ExcelVBAでのループ処理のコーディングについて、2つの方法を実際に想定されるケースについて実際にコーディングを行い、具体的に比較を行って、emalabo式のループ処理のコ…
ExcelVBA解説編

この記事を書いた人

overdoserのアバター overdoser

某IT企業に勤務。

2年間の休職を経て、一から独学でExcel VBAを習得し、半年でExcel VBA エキスパート資格のスタンダードに一発合格。

社内の非効率的な作業を効率化するExcelマクロの開発を、全行程まるっと一人で請け負い、これまでに約100個のExcelマクロを開発し、現在も増産中。

目次
  1. ワークブック・ワークシートについて、ほとんど知られていない事実を公開！
  2. 今回、タイプA・タイプBで実装するExcelマクロの想定業務と仕様の解説
    1. 想定業務の解説
    2. 仕様の解説
    3. Excelファイルのイメージ
      1. 未受講者一覧シート作成マクロ
      2. eラーニング未受講者一覧ファイル
      3. 社員情報ファイル
  3. タイプAのソースコード
  4. タイプBのソースコード
  5. ソースコードの解説
    1. [emalabo式] XLOOKUP関数の使い方について
    2. [emalabo式] ワークブック変数・ワークシート変数のオブジェクトの割り当てのテクニック
  6. タイプAとタイプBの比較
  7. 今回の結果からの考察
  8. [emalabo式] ワークブック・ワークシートのコーディングルール