Enumを定義したモジュールを自動生成するマクロ

↑この表を定数化して扱う場合、
　

' 列番号の定数化
Public Enum CNo売上データ
    品物 = 2
    価格
    個数
    売上
End Enum

' セルの指定サンプル
Cells(R, CNo売上データ.売上) = _
    Cells(R, CNo売上データ.価格) * Cells(R, CNo売上データ.個数)

このように、Enum(列挙型)の定数にして扱うと便利です。

Enum(列挙型)の詳しい説明はこちら

www.limecode.jp

このEnumですが、見出し名を縦に並べるだけで定義ができるため、
このコードはある程度自動生成することができます。
　

Debug.Print "    " & 見出し名

このように単純にDebug.Printを連打していくだけで、
イミディエイトウィンドウにEnumコードを生成できます。

この手法はこちらの記事で紹介しました。

さて生成したこのコードの置き場ですが、
「Df_シートレイアウト」のようなモジュールを作っておき、
そのモジュール内に対象の全シートのEnumを定義していくのが基本です。

ですがもう一つの手法として、各シートごとに専用のモジュールを作り、
そこにEnumをはじめとした各定数を定義する手法もあります。

この手法は「モジュール数がかなり増えてしまう」というデメリットがありますが、
メリットとしては

• 同じシートを複数のマクロで使いまわすときにモジュールのコピーで済む
• シート特有のSubやFunctionを一緒に記載しやすい
• そのブックがどのシートを使っているかモジュール構成を見るだけでわかる

あたりが挙げられます。

特に各シートで専用のSub/Functionがある場合は、
Enumが宣言セクション(最初のSub/Functionより上)にしか書けないため、
ひとつのモジュールに複数シートを書くと

シートAの定数
シートBの定数
シートCの定数
シートAの関数
シートBの関数
シートCの関数

のように混線してしまい、コピー等の移設が面倒になってしまいますからね。

いろいろなデータの親になっている大きなシートの場合は、
それ専用の標準モジュールを用意しておいた方がいい場面も多いです。

今回はこの「自動生成したEnumコードを搭載した標準モジュールを挿入するマクロ」を紹介します。

ソースコード

Sub 列見出しのEnum定数モジュールを挿入する()
    On Error Resume Next

    ' モジュールとEnumの本体名はシート名を使用
    Dim シート名 As String: シート名 = Replace変数用整形(ActiveSheet.Name)
    
    ' モジュールの追加
    Call 標準モジュールを挿入する(ActiveWorkbook, "Df_" & シート名)
    
    ' Public Const
    Call コードを最後尾に挿入する("' " & シート名, ActiveWorkbook, "Df_" & シート名)
    
    ' 選択エリアの第1行を見出しとして実行
    Dim Range見出し As Range
    Set Range見出し = Selection.Rows(1)

    ' フィルター付シートで単セル・単行から実行時は、オートフィルター見出しを使用
    If Range見出し.Cells.Count = 1 And ActiveSheet.AutoFilterMode = True Then
        Set Range見出し = ActiveSheet.AutoFilter.Range.Rows(1)
    End If

    ' Enum本体と初項を出力（ついでに第1行も）
    Call コードを最後尾に挿入する("Public Const R1st" & シート名 & " = " & Range見出し.Row + 1)
    Call コードを最後尾に挿入する("Public Enum CNo" & シート名)
    Call コードを最後尾に挿入する("    " & Replace変数用整形(Range見出し.Cells(1).Text) _
                              & " = " & Range見出し.Column)
    
    ' 見出しをループしてイミディエイトウインドウに出力
    If Range見出し.Cells.Count > 1 Then
        Dim i As Long
        For i = 2 To Range見出し.Cells.Count
            Call コードを最後尾に挿入する("    " & Replace変数用整形(Range見出し.Cells(i).Text))
        Next
    End If
    
    Call コードを最後尾に挿入する("End Enum")
End Sub

Private Function Replace変数用整形(元テキスト As String) As String

    ' (○○)を_○○に
    Replace変数用整形 = 元テキスト
    Replace変数用整形 = Replace(Replace変数用整形, "（", "_")
    Replace変数用整形 = Replace(Replace変数用整形, "(", "_")
    Replace変数用整形 = Replace(Replace変数用整形, ")", "")
    Replace変数用整形 = Replace(Replace変数用整形, "）", "")

    ' 改行とスペースは消す
    Replace変数用整形 = Replace(Replace変数用整形, vbLf, "")
    Replace変数用整形 = Replace(Replace変数用整形, " ", "")
    Replace変数用整形 = Replace(Replace変数用整形, "　", "")
    
    ' 数値始まりはとりあえず置換しやすい様に"XX"を接頭
    If IsNumeric(Left(Replace変数用整形, 1)) Then
        Replace変数用整形 = "XX" & Replace変数用整形
    End If
    
End Function

' モジュールの挿入
Sub 標準モジュールを挿入する(wb挿入ブック As Workbook, モジュール名 As String)
    wb挿入ブック.VBProject.VBComponents.Add(1).Name = モジュール名
End Sub

' コードの挿入
Sub コードを最後尾に挿入する(挿入コード As String, Optional wb指定ブック As Workbook, Optional 指定モジュール名 As String)
    
    ' 連続実行時に引数を省略できるようStatic変数に直前の引数を記憶
    Static Staticwb挿入ブック As Workbook
    Static Static挿入モジュール名 As String
    
    If Not wb指定ブック Is Nothing Then Set Staticwb挿入ブック = wb指定ブック
    If 指定モジュール名 <> "" Then: Static挿入モジュール名 = 指定モジュール名
    
    With Staticwb挿入ブック.VBProject.VBComponents(Static挿入モジュール名).CodeModule
        .InsertLines .CountOfLines + 1, 挿入コード
    End With
End Sub

　
このコードを実行すると「Df_シート名」というモジュールが挿入され、
そのモジュール内に生成したEnumコードが記述されます。

専用の関数などを書く場合はそのモジュールに追記できますし、
なんなら上記の自動生成コードで生成させることもできます。

皆さんのライブラリ仕様に合わせてカスタマイズしてみてください。