Excel VBA 列挙型変数Enumについて(1)数字の定数を扱う方法あれこれ

はじめに

※今回の記事は、Excel VBAで、変数とかくらいはある程度分かる方を対象にします。

Excel VBAで、Enumとか列挙型変数とかいう言葉を聞いたことがあるだろうか。

たぶんガチ勢の人達でも、このEnumについては、イマイチ使い所が分からないとかいう方も多いと思う。

というのもExcel VBAでは様々な種類の変数とか定数を扱うことができるので、このEnumというのをわざわざ使おうという考えになかなか至れない、他の変数とかとの違いや特有のメリットを見出しにくいというのがあるからだ。

そこで、Enum特有のメリット等についての記述は次回の記事で扱うが、今回はまず、

Enumも変数も定数も何も使わないことのデメリット
Enumを使ってみようと言われたとき、まず思い浮かぶ疑問

から先に取り上げてみたい。

Excel VBA 列挙型変数Enumについて(2)Enum特有のメリット、運用方法など

2020年3月24日

大量の数値項目を扱うにあたって

Enumの使い道を一言で言うと、数字(定数)に名前を付けて分かりやすく管理する、ということになる。

なお、Enumとかは数字に名前を付けることができるわけだが、それに対し、名前が付けられていなくて単に1,2,3,・・・とベタで書かれた数字は「マジックナンバー」と呼ばれるので、以降もその表記を使う。

たとえば下図の表では、横に12の項目(列)があって結構多い。
実務では、これの何倍もの列がある表を扱うことも珍しくない。

で、そんな多数項目を持つ表で、この項目はこの処理・あの項目にはあの処理と、個々の処理を大量にマクロに書いていくことも多い。

私がEnumを使う場面は、専らこれだ。
大量の項目(列)を持つExcelシートにおいて、どの列が何の項目かというのを、名前を付けて分かりやすく扱いたいという場面が、Enumの最大の活躍の場だと思う。

ではそういうとき、何列目が何の項目かなんてことをマジックナンバーで書いていってたら、読んでて何のことかさっぱり分からない間の抜けたソースコードになってしまう。

間の抜けたソースコードの例。意味が分からないだろうし分かろうとしなくていい。

このソースコードでは、

Cells(r,

というやつに続けて、列番号をマジックナンバーで入力しまくっているが、どれが何の列番号なのか全く分からないはずだ。

Sub test()
    Dim r           As Long

    For r = 2 To 11
        Cells(r, 1).Value = r - 1
        Cells(r, 2).Value = Trim(Cells(r, 2).Value) 
        Cells(r, 3).Value = Trim(Cells(r, 3).Value) 
        Cells(r, 4).Value = StrConv(Cells(r, 4).Value, vbKatakana)
        Cells(r, 5).Value = StrConv(Cells(r, 5).Value, vbKatakana)
        Cells(r, 6).Value = Trim(Cells(r, 6).Value)
        Cells(r, 7).NumberFormatLocal = "yyyymmdd" 
        Cells(r, 8).Value = Left(Cells(r, 8).Value, 8) 
        Cells(r, 9).NumberFormatLocal = "@" 
        Cells(r, 10).Value = Trim(Cells(r, 10).Value)     
        Cells(r, 11).Value = Left(Cells(r, 8).Value, 11)   
        Cells(r, 12).NumberFormatLocal = "@" 
    Next r
End Sub

Sub test()

Dim r As Long

For r = 2 To 11

Cells(r, 1).Value = r - 1

Cells(r, 2).Value = Trim(Cells(r, 2).Value)

Cells(r, 3).Value = Trim(Cells(r, 3).Value)

Cells(r, 4).Value = StrConv(Cells(r, 4).Value, vbKatakana)

Cells(r, 5).Value = StrConv(Cells(r, 5).Value, vbKatakana)

Cells(r, 6).Value = Trim(Cells(r, 6).Value)

Cells(r, 7).NumberFormatLocal = "yyyymmdd"

Cells(r, 8).Value = Left(Cells(r, 8).Value, 8)

Cells(r, 9).NumberFormatLocal = "@"

Cells(r, 10).Value = Trim(Cells(r, 10).Value)

Cells(r, 11).Value = Left(Cells(r, 8).Value, 11)

Cells(r, 12).NumberFormatLocal = "@"

Next r

End Sub

間が抜けていると一言で言ったが、こういうExcelの列番号をマジックナンバーで表記することのデメリットをもう少し具体的に挙げよう。

マジックナンバー表記のデメリット

1. なんといってもマジックナンバーでは、その数字が何の項目に対応しているのか、全く意味不明。
2. 自分でフォームとして作るExcelシートは、試行錯誤の中で、列の構成を変えることがしょっちゅうだ。
  そういうとき、どの項目が何番目の列にあるかということを、マジックナンバーで変化に対応し管理しきれるものではない。
3. そして列の構成が変わったとき。マジックナンバーを複数箇所で使っている場合、それら全てを書き換えていかないといけない。

で、1,2,3,・・・というマジックナンバーではなく、読んで分かる言葉の名前を付けようというのがEnumの目的だが・・・。
そこで必ず疑問になるのが、単なるLong型とかの変数・定数じゃいけないの？ってことだ。

ある程度慣れてる人なんかはConstとか使って定数を書き、コメントなんかもソースコードに書き込みまくって分かりやすくしようとかする。

Const定数を使って改良した例

けっこう分かりやすくなったんじゃないだろうか。

Sub test()
    Const gyoNo      		As Long = 1  '行№
    Const sei        		As Long = 2  '姓
    Const mei        		As Long = 3  '名
    Const seiKana    		As Long = 4  '姓カナ
    Const meiKana    		As Long = 5  '名カナ
    Const sex        		As Long = 6  '性別
    Const birthday   		As Long = 7  '生年月日
    Const postalCode 		As Long = 8  '郵便番号
    Const prefecturesCode 	As Long = 9  '都道府県コード
    Const prefectures 		As Long = 10 '都道府県
    Const tel 				As Long = 11 '電話番号
    Const mailAddress 		As Long = 12 'メールアドレス

    Dim r           As Long

    For r = 2 To 11
        Cells(r, gyoNo).Value = r - 1                 '行№
        Cells(r, sei).Value = Trim(Cells(r, 2).Value) '姓
        Cells(r, mei).Value = Trim(Cells(r, 3).Value) '名
        Cells(r, seiKana).Value = StrConv(Cells(r, 4).Value, vbKatakana) '姓カナ
        Cells(r, meiKana).Value = StrConv(Cells(r, 5).Value, vbKatakana) '名カナ
        Cells(r, sex).Value = Trim(Cells(r, 6).Value) '性別
        Cells(r, birthday).NumberFormatLocal = "yyyymmdd" '生年月日
        Cells(r, postalCode).Value = Left(Cells(r, 8).Value, 8) '郵便番号
        Cells(r, prefecturesCode).NumberFormatLocal = "@"       '都道府県コード
        Cells(r, prefectures).Value = Trim(Cells(r, 10).Value)  '都道府県
        Cells(r, tel).Value = Left(Cells(r, 8).Value, 11)       '電話番号
        Cells(r, mailAddress).NumberFormatLocal = "@" 'メールアドレス
    Next r
End Sub

Sub test()

Const gyoNo As Long = 1 '行№

Const sei As Long = 2 '姓

Const mei As Long = 3 '名

Const seiKana As Long = 4 '姓カナ

Const meiKana As Long = 5 '名カナ

Const sex As Long = 6 '性別

Const birthday As Long = 7 '生年月日

Const postalCode As Long = 8 '郵便番号

Const prefecturesCode As Long = 9 '都道府県コード

Const prefectures As Long = 10 '都道府県

Const tel As Long = 11 '電話番号

Const mailAddress As Long = 12 'メールアドレス

Dim r As Long

For r = 2 To 11

Cells(r, gyoNo).Value = r - 1 '行№

Cells(r, sei).Value = Trim(Cells(r, 2).Value) '姓

Cells(r, mei).Value = Trim(Cells(r, 3).Value) '名

Cells(r, seiKana).Value = StrConv(Cells(r, 4).Value, vbKatakana) '姓カナ

Cells(r, meiKana).Value = StrConv(Cells(r, 5).Value, vbKatakana) '名カナ

Cells(r, sex).Value = Trim(Cells(r, 6).Value) '性別

Cells(r, birthday).NumberFormatLocal = "yyyymmdd" '生年月日

Cells(r, postalCode).Value = Left(Cells(r, 8).Value, 8) '郵便番号

Cells(r, prefecturesCode).NumberFormatLocal = "@" '都道府県コード

Cells(r, prefectures).Value = Trim(Cells(r, 10).Value) '都道府県

Cells(r, tel).Value = Left(Cells(r, 8).Value, 11) '電話番号

Cells(r, mailAddress).NumberFormatLocal = "@" 'メールアドレス

Next r

End Sub

Enumを使ってみたソースコード

で、これに対して、Enumを使う理由としてよく言われるのが、こういう数字に関する定数はグループ化して扱いたいというようなことだ。

上記のちょっと改良したソースコードでは

Const gyoNo             As Long = 1  '行№
Const sei               As Long = 2  '姓
Const mei               As Long = 3  '名
Const seiKana           As Long = 4  '姓カナ
Const meiKana           As Long = 5  '名カナ
Const sex               As Long = 6  '性別
Const birthday          As Long = 7  '生年月日
Const postalCode        As Long = 8  '郵便番号
Const prefecturesCode   As Long = 9  '都道府県コード
Const prefectures       As Long = 10 '都道府県
Const tel               As Long = 11 '電話番号
Const mailAddress       As Long = 12 'メールアドレス

Const gyoNo As Long = 1 '行№

Const sei As Long = 2 '姓

Const mei As Long = 3 '名

Const seiKana As Long = 4 '姓カナ

Const meiKana As Long = 5 '名カナ

Const sex As Long = 6 '性別

Const birthday As Long = 7 '生年月日

Const postalCode As Long = 8 '郵便番号

Const prefecturesCode As Long = 9 '都道府県コード

Const prefectures As Long = 10 '都道府県

Const tel As Long = 11 '電話番号

Const mailAddress As Long = 12 'メールアドレス

というように色々なConst定数を盛り込んだ。

でも、ソースコードが大量になって、他の多種多様な定数・変数が入り乱れるようになってくると、どうだろうか。

最初に定義したgyoNoとかpostalCodeとかのConst定数がいずれも、元の表の列番号を表す役割を共通して持つのだということは、これでは分かりにくい。

ということで、こういう数字の定数を共通のグループにまとめようということでEnumを使うと便利だといわれる。
例として次のような感じだ。

Enum eColumn '表の列番号を示すEnum
    gyoNo = 1       '行№
    sei             '姓
    mei             '名
    seiKana         '姓カナ
    meiKana         '名カナ
    sex             '性別
    birthday        '生年月日
    postalCode      '郵便番号
    prefecturesCode '都道府県コード
    prefectures     '都道府県
    tel             '電話番号
    mailAddress     'メールアドレス
End Enum
    
Sub enumTest()
    Dim r           As Long
 
    For r = 2 To 11
        Cells(r, eColumn.gyoNo).Value = r - 1                 '行№
        Cells(r, eColumn.sei).Value = Trim(Cells(r, 2).Value) '姓
        Cells(r, eColumn.mei).Value = Trim(Cells(r, 3).Value) '名
        Cells(r, eColumn.seiKana).Value = StrConv(Cells(r, 4).Value, vbKatakana) '姓カナ
        Cells(r, eColumn.meiKana).Value = StrConv(Cells(r, 5).Value, vbKatakana) '名カナ
        Cells(r, eColumn.sex).Value = Trim(Cells(r, 6).Value) '性別
        Cells(r, eColumn.birthday).NumberFormatLocal = "yyyymmdd" '生年月日
        Cells(r, eColumn.postalCode).Value = Left(Cells(r, 8).Value, 8) '郵便番号
        Cells(r, eColumn.prefecturesCode).NumberFormatLocal = "@"       '都道府県コード
        Cells(r, eColumn.prefectures).Value = Trim(Cells(r, 10).Value)  '都道府県
        Cells(r, eColumn.tel).Value = Left(Cells(r, 8).Value, 11)       '電話番号
        Cells(r, eColumn.mailAddress).NumberFormatLocal = "@" 'メールアドレス
    Next r
End Sub

Enum eColumn '表の列番号を示すEnum

gyoNo = 1 '行№

sei '姓

mei '名

seiKana '姓カナ

meiKana '名カナ

sex '性別

birthday '生年月日

postalCode '郵便番号

prefecturesCode '都道府県コード

prefectures '都道府県

tel '電話番号

mailAddress 'メールアドレス

End Enum

Sub enumTest()

Dim r As Long

For r = 2 To 11

Cells(r, eColumn.gyoNo).Value = r - 1 '行№

Cells(r, eColumn.sei).Value = Trim(Cells(r, 2).Value) '姓

Cells(r, eColumn.mei).Value = Trim(Cells(r, 3).Value) '名

Cells(r, eColumn.seiKana).Value = StrConv(Cells(r, 4).Value, vbKatakana) '姓カナ

Cells(r, eColumn.meiKana).Value = StrConv(Cells(r, 5).Value, vbKatakana) '名カナ

Cells(r, eColumn.sex).Value = Trim(Cells(r, 6).Value) '性別

Cells(r, eColumn.birthday).NumberFormatLocal = "yyyymmdd" '生年月日

Cells(r, eColumn.postalCode).Value = Left(Cells(r, 8).Value, 8) '郵便番号

Cells(r, eColumn.prefecturesCode).NumberFormatLocal = "@" '都道府県コード

Cells(r, eColumn.prefectures).Value = Trim(Cells(r, 10).Value) '都道府県

Cells(r, eColumn.tel).Value = Left(Cells(r, 8).Value, 11) '電話番号

Cells(r, eColumn.mailAddress).NumberFormatLocal = "@" 'メールアドレス

Next r

End Sub

このソースコードの細かい意味はまだ分からなくて良いが、eColumn というEnum変数を使って、列番号に関する各種定数をグループ化してる感じだ。

そしてこのグループ化をしたことによって、
eColumn.
まで手入力すると入力候補が自動表示されるので、定数を入力しやすくなる、ということがよく言われることだ。

↓の動画に示したけど、この例では「eco」まで入力した時点でショートカットキー
Ctrl + スペースキー
で入力補完により「eColumn」を完成させ、次にピリオド「.」を入力するとまた自動的に候補が表示されている。

で、私が長らく疑問に思ってたのが「でもこれと同じことだったらさ、Const定数でもできるんじゃね？無理してEnum使うことなくね？」ということだった。

前述のConst定数を用いたソースコードにおける諸々のConst定数について、先頭に共通して「column_」とか付けたものにして次のように変更すれば、定数のグループ化もできるし入力補完もけっこう使えるようになる。

Const column_gyoNo As Long = 1             '行№
Const column_sei As Long = 2               '姓
Const column_mei As Long = 3               '名
Const column_seiKana As Long = 4           '姓カナ
Const column_meiKana As Long = 5           '名カナ
Const column_sex As Long = 6               '性別
Const column_birthday As Long = 7          '生年月日
Const column_postalCode As Long = 8        '郵便番号
Const column_prefecturesCode As Long = 9   '都道府県コード
Const column_prefectures As Long = 10      '都道府県
Const column_tel As Long = 11              '電話番号
Const column_mailAddress As Long = 12      'メールアドレス