見出し画像
Photo by jniimi

ExcelVBAで定数の代わりにEnum使う& Enumの自動作成

ななし

VBAコードを作成する際に、よく列数を数字（Long型）で指定することがありますが、列数のみ（例：1や５など）を記載しても実際のデータのどんなデータのある列を指定したのか分からず、データソースの列に変動が生じた場合にコードのメンテナンスに困りますよね。

例えば元々4列目にあったのが、5列目に変更になったとして、置換機能で変更したくても、その列を指す趣旨のみでしか「4」という数値がコード内で使用されていない保証がないからです。

変わりに定数（Const）を利用する方法もありますが、それも宣言する定数が多いとメンテナンスが大変です。

Const ENTRY_DATE = 1
Const USER_ID = 2
Const USERNAME = 3
Const SEX = 4
Const AGE = 5
Const TOTAL_MONEY = 6
Const BIRTHDAY = 7

例えば上記のように各列数を定数で宣言するとして、3列目と4列目の間に新しく列が挿入されることになったら、その後の定数で宣言していた数値をすべて変更する必要があります。
例で示しているぐらいの列数であれば良いのですが、列数が多ければ多いほど手作業での変更は大変ですよね。

そういうときには、列挙型変数(Enum)を使用するのがおすすめです。
先ほどの例をEnumに書き直すと、下記のようになります。

Enum zeユーザー情報
	entrydate = 1
	userid
	username
	sex
	age
	totalmoney
	birthday
End Enum

※定数の場合に半角大文字で書いていたのは、定数の大文字規則に従っていたためです。Enumではヘッダー（見出し）のある行の名称をそのまま使用して構いません。（半角英字になっていますが、日本語で大丈夫です。）

Enumは整数のみを取り扱うことができる定数の集合体のようなものです。列挙型の「＝値」部分は省略でき、その場合は、省略すれば連番になるように採番されます。
最初の要素を省略した場合は「0」が割り当てられます。
列数をコードに直書きする代わりに使用するので、通常は「0」スタートではなく「1」から始めます。そのため、最初の「=1」は省略せずに書きます。
こうしてEnumを定義すれば、列数を直書きしていた部分を、代わりに下記のように記載できます。

 MsgBox zeユーザー情報.age , "表示"    '5と表示される

「=1」以降は、記載を省略することで連番が振られているため、後日に行を挿入しなければいけなくなったケースでは単に間にその列のヘッダー名等を追加するだけで済みます。

Enum zeユーザー情報
	entrydate = 1
	userid
	username
    status      '追加
	sex
	age
	totalmoney
	birthday
End Enum

「username」の後に、「status」を挿入しました。以降は、自動的に連番が「sex=5」「age=6」・・・に変更されます。
コード自体には、「zeユーザー情報.age」という風に記載しているので、コード内を変更する必要はありません。

実際にEnumを使用する際は、モジュールの一番上（宣言エリア）に記載してください。同じモジュール内で共通の変数を宣言するときと同じです。
プロシージャの内部では宣言できません。

Enumの宣言箇所の例

このように、Enumは定数よりもメンテナンスに効力を発揮します。
ただし、大量にある宣言しないといけない場合に、Enumを手書きするのも大変ですよね。
また、列数を省略することで連番になることがEnumの魅力なので、コード内で使用する列数以外の宣言を省略すると下記のように穴抜けにしないといけなくなります。

Enum zeユーザー情報
    entrydate = 1
    username = 3
    age = 5
End Enum

これではEnumを使用するメリットがありません。
そこで、Excelのデータにあるヘッダーを範囲選択した状態にして、マクロを実行することで、Enumを自動的に作成するようにすると便利です。

Sub sb選択範囲よりEnum自動作成しクリップボードへ出力()
'用途：Excelのデータのヘッダーを選択してEnumをクリップボードに出力する
 
    Dim myMsg As String 'メッセージボックス用変数
    'enumの名前をmsgboxで入力
    Dim enumName As String
    enumName = InputBox("enumの名前を入力してください。", "enum作成")
   
    'enumの名前が空白の場合は終了
    If enumName = "" Then
        MsgBox "enumの名前が入力されませんでした。", vbExclamation, "enum作成"
        Exit Sub
    End If
   
    'データベースのヘッダーを範囲選択
    Dim rng As Range
    Set rng = Selection
   
    '列数と行数を取得
    Dim colNum  As Long
    Dim rowNum  As Long
    colNum = rng.Columns.Count
    rowNum = rng.Rows.Count

    '選択範囲の最左列を取得
    Dim startCol As Long
    startCol = rng.Column '選択範囲の左端の列番号
   
    'enumの値をヘッダーから取得
    Dim enumValue  As String
    Dim i          As Long
    Dim j          As Long
    Dim k          As Long: k = 1
    Dim isFirst    As Boolean '最初の要素かどうかを判定する変数
    enumValue = ""
    isFirst = True '最初の要素のフラグをTrueにする
   
    If rowNum > 1 Then GoTo Continue  '選択している行が2行の場合には処理中止
   
    For i = 1 To colNum
        For j = 1 To rowNum
            '空白のセルは無視
            If rng.Cells(j, i).Value <> "" Then
               
                '括弧（）などの記号は使用できないため、『_（アンダースコア）』に変換するようにコード改修
                '※閉じる方『）』は、空白に置換
                Dim cellValue As String
                cellValue = rng.Cells(j, i).Value
                cellValue = Replace(cellValue, "(", "_")
                cellValue = Replace(cellValue, ")", "")
                cellValue = Replace(cellValue, "（", "_")
                cellValue = Replace(cellValue, "）", "")
                cellValue = Replace(cellValue, "【", "_")
                cellValue = Replace(cellValue, "】", "")
                cellValue = Replace(cellValue, "/", "_")
                cellValue = Replace(cellValue, "・", "_")
                cellValue = Replace(cellValue, "･", "_")
                cellValue = Replace(cellValue, "-", "_")
               
                If isFirst Then '最初の要素の場合は、値の後に「=startCol」を追加する（startColは選択範囲の左端の列数）
                    enumValue = enumValue & vbTab & cellValue & " =" & startCol & vbNewLine
                    isFirst = False '最初の要素のフラグをFalseにする
                ElseIf i = colNum Then '最終列なら
                    enumValue = enumValue & vbTab & cellValue & vbNewLine
                    enumValue = enumValue & vbTab & "[_最終項目]" & vbTab & "'疑似的最終項目" & vbNewLine
                    enumValue = enumValue & vbTab & "Count = [_最終項目] - 1" & vbNewLine
                Else '最初の要素以外の場合
                    enumValue = enumValue & vbTab & cellValue & vbNewLine
                End If
                
            ElseIf rng.Cells(j, i).Value = "" Then '空白の場合はインテリセンスが働かないダミー項目名を追加
                enumValue = enumValue & vbTab & "[_dummy_" & k & "]" & vbNewLine
                k = k + 1
            End If
        Next j
    Next i
   
    'enumの値の末尾の改行を削除
    enumValue = Left(enumValue, Len(enumValue) - 2)
   
    'enumのコードを作成
    Dim enumCode As String
    enumCode = "Enum ze" & enumName & vbNewLine & enumValue & vbNewLine & "End Enum" & vbNewLine
   
    'enumのコードをクリップボードにセット
     sbクリップボードへ文字列セット enumCode
   
    myMsg = "クリップボードにEnumの出力が完了しました。" & vbCrLf + myMsg
    MsgBox myMsg, , "処理結果通知"
    Exit Sub
 
Continue:              'GoTo Continueの後はここから処理が行われる
    myMsg = "選択行が2行以上のため処理を中止しました。"
    MsgBox myMsg, , "処理結果通知"
   
End Sub

Public Sub sbクリップボードへ文字列セット(ByVal a_text As String)
'クリップボードへ文字列を送信
'必要な参照設定：Microsoft Forms 2.0 Object Library
    With CreateObject("Forms.TextBox.1")
      .MultiLine = True
      .Text = a_text
      .SelStart = 0
      .SelLength = .TextLength
      .Copy
    End With
End Sub

使い方は、Excelのデータのヘッダーを範囲選択→「sb選択範囲よりEnum自動作成しクリップボードへ出力」を実行→クリップボードにEnumが保存された状態になります。
そのため、Enumを使用したい標準モジュールの宣言エリア等でCTRL＋VでEnumのコードを貼り付けしてください。

実行結果は下記のようになります。

Enum zeユーザー情報
	entrydate = 1
	userid
	username
	sex
	age
	totalmoney
	birthday
	[_最終項目]	'疑似的最終項目
	Count = [_最終項目] - 1
End Enum

空きの項目列がある場合にもインテリセンスに出てこないように、[ ]で囲み、先頭を_（アンダースコア）にしています。また、Countで合計項目数が分かるようにしています。これらのアイディアは、りゅうりゅうさんのアイディアを拝借しています。
Enumの場合は、列が増えたりして間に挿入する必要が出てきてもメンテナンスが格段に楽になります。
ただし、Enumも数が多すぎてもが利用する際に困る（インテリセンスで選択するにしても選択肢が多くなりすぎる）ため、せいぜい数十レベルで利用するとよいと思います。

もしよろしければサポートをお願いします。今後の執筆のかてにします。