レイアウト変更に強いVBAコード

更新履歴

| 日付       |       内容 |
|:-----------------|------------------:|
| 2020-01-25             | Enum版を追加 |


# 概要
[VBAメモ：シートのレイアウト変更に伴うVBAコード修正を減らしたい。](https://qiita.com/EmikoKishi/items/a4b740fed5ab1a22e110) の記事を見て、自分のやり方を投下してみました。

# 本文
## 2パターン投下します
私の中で基本形だったものと、それを改良したものです。
2020-01-25追記： @jinoji さんにご紹介いただいたEnumのコードを追加しました。

## サンプル題材
題材として、`DBからエクスポートしてきた住所テーブルを、住所録っぽく書き換える処理`を用います。
なお題材の処理自体にはあまり力を入れてませんので、そちらへのツッコミは容赦願いたいです。

入力
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/282159/fd175a57-5578-bdbc-1b9c-9a524fd99874.png)

結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/282159/cd979b84-98df-7747-b5e0-8a22ffc92a8e.png)

## パターン1、基本形

### 方針
とにかくコード内でマジックナンバーになるような箇所すべて定数にします。
[全コードはこちら](https://github.com/kumanobori/misc/blob/master/20200121_col_row_definition/src/col_row_definition_demo.xlsm/part1.bas)

```vb
' --- シート定義
Const INPUT_SHEET_NAME = "INPUT"
Const OUTPUT_SHEET_NAME = "OUTPUT"

' --- 入力列定義
Const COL_IN_INDEX = 1
Const COL_IN_FAMILY_NAME = 2
Const COL_IN_LAST_NAME = 3
Const COL_IN_SEX = 4
Const COL_IN_ZIP1 = 5
Const COL_IN_ZIP2 = 6
Const COL_IN_PREFECTURE = 7
Const COL_IN_CITY = 8
Const COL_IN_TOWN = 9
Const COL_IN_BUILDING = 10
Const COL_IN_ADDRESSLIST_PROHIBITED = 11

' --- 入力行定義
Const ROW_IN_DATA_START = 2

' --- 出力列定義
Const COL_OUT_NAME = 1
Const COL_OUT_ZIP = 2
Const COL_OUT_ADDRESS = 3

' --- 出力行定義
Const ROW_OUT_DATA_START = 3
```

### 弱点
- 列が10個くらいならいいですが、Z列より先になると、列アルファベットを列番号に変換するのがひと手間です。
- 途中の列に何かを追加することになった時(たとえば入力のD列あたりに名前カナを入れるとか)となったとき、列番号をまとめて直す必要があり、これはけっこうな手間です。

## パターン2、改良版

### 方針
列番号を動的に設定します。

[全コードはこちら](https://github.com/kumanobori/misc/blob/master/20200121_col_row_definition/src/col_row_definition_demo.xlsm/part2.bas)

パターン1では定数だったところを、モジュール変数にします。
そして、COL_IN_ITEM_START という定数を設け、項目の開始列を指定します。

```vb
' --- 入力列定義
Const COL_IN_ITEM_START = 1
Dim COL_IN_INDEX As Long
Dim COL_IN_FAMILY_NAME As Long
Dim COL_IN_LAST_NAME As Long
Dim COL_IN_SEX As Long
Dim COL_IN_ZIP1 As Long
Dim COL_IN_ZIP2 As Long
Dim COL_IN_PREFECTURE As Long
Dim COL_IN_CITY As Long
Dim COL_IN_TOWN As Long
Dim COL_IN_BUILDING As Long
Dim COL_IN_ADDRESSLIST_PROHIBITED As Long
```

そして、列番号を増分しながら代入していきます。

```vb
    Dim i As Long: i = COL_IN_ITEM_START - 1
    i = i + 1: COL_IN_INDEX = i ' A列
    i = i + 1: COL_IN_FAMILY_NAME = i ' B列
    i = i + 1: COL_IN_LAST_NAME = i ' C列
    i = i + 1: COL_IN_SEX = i ' D列
    i = i + 1: COL_IN_ZIP1 = i ' E列
    i = i + 1: COL_IN_ZIP2 = i ' F列
    i = i + 1: COL_IN_PREFECTURE = i
    i = i + 1: COL_IN_CITY = i
    i = i + 1: COL_IN_TOWN = i
    i = i + 1: COL_IN_BUILDING = i
    i = i + 1: COL_IN_ADDRESSLIST_PROHIBITED = i
```

### 何が改善されるか
列の入れ替えや追加があったとき、列番号の代入箇所を編集するだけで済みます。
上から列順に並んでますので、実際の表を見ながら並び替えることができ、失敗もしにくいです。

### ちょっともやもやする点
事実上定数なのに、文法的には変数にしてしまっていますので、その点ちょっともやつきます。

## パターン3、Enum版
@jinoji さんにコメント頂いた、Enumを使ってみました。
[全コードはこちら](https://github.com/kumanobori/misc/blob/master/20200121_col_row_definition/src/col_row_definition_demo.xlsm/part3.bas)

こんな感じで定義すると、

```vb
' --- 入力列定義
Enum COL_IN
    Index = 1
    familyName ' 2 (値を指定しなければ、前の項目からインクリメントされた値になる)
    lastName ' 3
    sex ' 4
    zip1 ' (以下略)
    zip2
    prefecture
    city
    town
    building
    addresslistProhibited
End Enum
```

`Enum名.項目名` な感じで使えます。

```vb
    ' 住所
    Dim prefecture As String: prefecture = wsIn.Cells(rowNoIn, COL_IN.prefecture).Value
    Dim city As String: city = wsIn.Cells(rowNoIn, COL_IN.city).Value
    Dim town As String: town = wsIn.Cells(rowNoIn, COL_IN.town).Value
    Dim building As String: building = wsIn.Cells(rowNoIn, COL_IN.building).Value
```

これは書きやすくていいですね。
ただ難点があるとすれば、項目名が短くなりすぎるために、

```vb
Enum COL_IN
    Index = 1
```

のように、他の変数と綴りが同じになったものは、大文字小文字がひきずられてしまいます。
贅沢な悩みのような気もしますが・・・。
命名をうまく回さないと、項目名の統一性がなくなってしまうので、注意が要りそうです。

## 参考資料
[Enumステートメント（VBA) - Microsoft Dosc](https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/enum-statement)