名簿作成マクロのスタイル(1)

Excelで名簿を扱うことが多い私。

これまで数多くの名簿を作成してきたなかで、最近だいぶスタイルが固まってきたので、一旦まとめておくことにした。

この先また考え方が変わるかも知れないが。

シートの役割に関するものはシートモジュールに書く

シートの役割を明確化する

そもそものExcelの使い方に関わる部分。多くの達人の皆さんが共通して〈シートごとに役割をハッキリさせよ〉的なことを述べておられると思う。

シートの役割をハッキリさせると、そのシートが請け負うべき機能もハッキリする。

その機能を実現するコードはそのシートのシートモジュールに書けば良いのだ。

たとえば、名簿作成の元になるデータを入れておくシートが必要だろう。

これは、〈１行１レコード・１列に１データ〉の原則に基づいて作るものだろう。

f:id:akashi_keirin:20200503073900j:plain

こんなふうに。

オブジェクト名をつける

私は、このような元データのシートには、

f:id:akashi_keirin:20200503073904j:plain

このように、オブジェクト名を「Sh01Data」として、シート名を「Data」とすることが多い。

オブジェクト名は、デフォルトだと「Sheet1」となっているが、このまま使っていると、大規模なプロジェクトでシート数が9を超えたときに、「プロジェクトエクスプローラー」上での並び順がイマイチになる。

f:id:akashi_keirin:20200503073907j:plain

このように実にうっとうしい並び順になってしまう。

さすがにシート数が99を超すことはないと思うので、シートモジュールのオブジェクト名は〈Shプラスゼロ埋め2桁〉を接頭辞にして命名するようにしている。

シートモジュールに書くコード

さて、では、このシートのシートモジュールには何を書くか。

このシートの役割は、

VBAにデータを提供する

ことである。それ以上でもそれ以下でもない。

その役割を考えたとき、大切なのは、、

データの位置が指し示しやすいこと

及び、、

データが取り出しやすいこと

であろう。

そのために、元データ用のシートモジュールには、だいたい次のコードを書くことが多い。

列番号を表す列挙体
表全体（項目ラベル含む）のRangeオブジェクトを返すプロパティ
表の正味のデータ部分（項目ラベルを含まない表全体）のRangeオブジェクトを返すプロパティ

こいつら。

実際のコード

先に挙げた

f:id:akashi_keirin:20200503073911j:plain

を例に、実際にシートモジュールに書くコードをお目にかけよう。

列番号を表す列挙体

表では、

1列目：名前
2列目：名前ふりがな
3列目：所属
4列目：卒業期
5列目：級
6列目：班
7列目：戦法
8列目：失格

となっている。

そこで、シートモジュールの宣言セクションに次のように列挙体を定義する。

Public Enum Sh01ColumnName
  sh01Name = 1
  sh01Phonetic
  sh01Prefecture
  sh01Generation
  sh01Grade
  sh01Class
  sh01Style
  sh01Unable
End Enum

ポイントは、列挙体の各要素に「sh01」という接頭辞をつけている点。

名簿作成作業の場合、元データから必要なデータだけを抽出して、表示用の別シートに転記する、という作業が重要になることが多い。

となると、転記先のシートでも同じように列番号を列挙体で定義する、という機会が生ずる。

そうなったときに、接頭辞をつけるようにすれば、同名被りをほとんど気にしなくてもよくなるのだ。

たとえば、Sh02View1というシートオブジェクトの1列目に名前、2列目に所属、3列目に戦法を転記するような場合なら、Sh02View1モジュールには、たとえば

Public Enum Sh02ColumnName
  sh02Name = 1
  sh02Prefecture
  sh02Style
End Enum

という列挙体を定義しておけばよい。

Sh01Dataオブジェクトのsh01Name列目のデータはSh02View1オブジェクトのsh02Name列目に転記するということになり、実にわかりやすい。

表全体（項目ラベル含む）のRangeオブジェクトを返すプロパティ

元データからデータを抽出するときには、[Range].AdvancedFilterメソッドを使うと便利。

[Range].AdvancedFilterメソッドを使う際には、[Range]オブジェクトが項目ラベルを持っていないとダメなので、表全体を手軽に取得できればコードが読みやすくなるだろう。

私は次のようなコードで表全体のRangeオブジェクトをシートオブジェクトのプロパティにして、取得しやすくしている。

Public Property Get WholeList() As Range
  Set WholeList = Me.Range("A1").CurrentRegion
End Property

こうしておくことで、項目ラベルを含む表全体を、

Sh01Data.WholeList

という式で他モジュールから参照することができて便利。

表の正味のデータ部分（項目ラベルを含まない表全体）のRangeオブジェクトを返すプロパティ

項目ラベルを含む表全体とは別に、項目ラベルを除いた表の正味のデータ部分も必要。Rangeオブジェクトの1行目がデータの1行目になるから。

[Range].Valueプロパティの返り値をVariant変数に突っ込んだときにできる2次元配列が1始まりの配列になる挙動とも実に相性がよい。

そこで、次のようなプロパティを別途設定する。

Public Property Get DataList() As Range
  Dim ret As Range
  Set ret = Me.Range("A1").CurrentRegion
  If ret.Rows.Count = 1 Then Exit Property
  With ret
    Set ret = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
  End With
  Set DataList = ret
End Property

OffsetプロパティとResizeプロパティを用いて、正味のデータ部分を取り出している。

まあ、元データについてはユーザが触ることは基本ないので、正味の元データ部分に、たとえば「選手データリスト」とでも名前を定義しておいて、

'Declarations Section'
Private Const RACER_DATA_LIST As String = "選手データリスト"

'Properties'
Public Property Get DataList() As Range
  Set DataList = Me.Range(RACER_DATA_LIST)
End Property

で十分だとは思う。もちろん、この場合、表の末尾にデータを追加した場合は範囲の名前定義を修正する必要があるが。

おわりに

シートの列番号と列挙体との相性はきわめてよいので、おすすめです。

つづく……かなあ？？？