VBAクラスの作り方：列名の入力支援と列移動対応

公開日：2019-02-11　最終更新日：2022-06-29

VBAクラスの作り方：列名の入力支援と列移動対応

クラスを使う良さとして、入力支援が使えてコーディングが楽になるという利点があります、
列番号をクラスに持てば、列名が候補表示されて非常に便利です。
しかし、
これを実装するには、かなりの手間がかかります。
つまり、
クラス作成に手間をかけて、その後を楽にするということになります。

もちろん、
クラスを使う理由は入力支援だけではなく、むしろそれは結果としての付属的なものでしかありません。
列名で指定したいだけなら、
列挙体(Enum)

・Enumステートメント・列挙型（Enumステートメント）の使用例・列挙型（Enumステートメント）の活用について

や
構造体(Type)

・Typeステートメントの構文・ユーザー定義型の使い方・ユーザー定義型の使用例・ユーザー定義型の制限・最後に

で良いことになります。

あくまで、クラスを作る際に列名も持たせる場合ということになります。
クラスについての概要は、VBAのクラスとは（Class,Property,Get,Let,Set）

・オブジェクトとは・オブジェクト指向とは・カプセル化・オブジェクト指向とカプセル化とクラス・クラスの必要性と利点・一般的なクラスに関する説明・クラスの比喩的説明・クラスの使い方・クラスを体験してみる・クラスの使用例・クラス入門の最後に

VBAクラスの説明に入る前に、
入力支援（インテリセンス、メンバー一覧が候補表示）の状況と、
列挙体と構造体を使った場合の具体例を見てみます。

列挙体を使った列名表示の例

列名の表示だけなら、これで十分です。
問題点としては、列を入れ替えたり列挿入したりすると、列挙体の記述を変更しなければならない点です。

列の移動や入れ替えに対応する方法には名前定義を使う方法があります。
列タイトルに事前に名前定義しておくことで、実行時に名前定義をもとに列数を取得します。
Range("名前定義の名前").Column
これで取得した列数をVBAで使うことで、列移動や表の途中に列挿入されても正しい列位置で処理ができるようになります。

しかし、列挙体は数値定数で値の変更はできません。
名前定義で取得した列数は、そのまま使うか変数に入れるしかありません。
つまり、列挙体と名前定義の両方を同時に使う為には工夫が必要です。

例えば、
列挙体に合わせた配列を用意しその中に名前定義で取得した列数を入れる方法が考えられます。

Enum 列
　　列1 = 1
　　列2 = 2
　　・・・
　　列10 = 10
End Enum
・・・
Dim ary(1 To 10)
ary(列.列1) = Range("名前定義の名前").Column
Cells(i, ary(列.列1)) = ・・・

構造体を使った列名表示の例

Type 列
　列1 As Long
　列2 As Long
　・・・
　列10 As Long
End Type

この構造体に列数を事前に入れておきます。
col.列1 = Range("nm氏名").Column

その後は、列名で扱うことができるようになります。
Cells(i, col.列1) = ・・・

列位置変更がないのなら、列挙体が一番簡単で、
列位置変更に対応するなら、構造体＋名前定義が最も良い方法になると思います。

構造体は関連するデータを一つにまとめる機能であり、クラスに近い存在となります。
クラスは、関連するデータを一つにまとめ、さらに関連する操作まで組み込める機能だと理解すればよいでしょう。

もちろん、クラスはそんな単純なものではありませんし、もっと多くの機能があります。
しかし、とにかく最初は全体のイメージを持つことが重要ですので、
関連するデータやその操作を一つにまとめたものがクラスであり、
つまりは、構造体＋関連するプロシージャーを一つにまとめたもの、これくらいの認識でも構いません。。
従って、構造体を使ったことが無ければ、まずは構造体を少しでも理解してからクラスに入ったほうが理解がしやすいと思います。

クラスの各種機能については、今後VBAコードとともに徐々に解説していきます。

>クラスに関する基本のおさらい

オブジェクト指向とは

オブジェクト同士の相互の作用として、システムの動作をとらえる考え方であり、
対象物を部品を使って組み立てるように表現してプログラミングしていくものです。

オブジェクト指向における三大要素

・カプセル化 (encapsulation)
・継承 (inheritance)
・多態性 (polymorphism)

VBAでは、継承も多態性も使えない。（インターフェースだけ使える）
従って、カプセル化だけを意識すれば良いのです。

カプセル化

関連するデータやその操作を一つ（カプセル）にまとめることを言います。
つまり、関連するものを一つのカプセルにまとめ、
そのカプセルをコピーしつつ使いまわすというイメージになります。

カプセル化の重要な要素として、「隠蔽」があります。
必要なもののみ外部に公開し、不必要なものは非公開として隠蔽します。
隠蔽が正しくできないと、
カプセルの中身をあちこちから何時の間にか変更されたりして困ることになります。

>全体の機能と構成

サンプルして使うシート

シート名：

顧客マスタ

フィールド：

顧客ID
氏名
氏名（カナ）
性別
生年月日
年齢
郵便番号
都道府県
電話番号
Email
備考

クラスで、列名の入力支援と列移動対応するための手順

・シートに名前定義を設定
・クラスを新規挿入
・クラスに列数取得のプロシージャーと対象シートを受け取るプロパティを作成
・クラスに列名のプロパティを作成
・クラスの完成コード

以下で、順に説明します。

>シートに名前定義を設定

シートの列タイトルに名前定義を設定します。
上記サンプルシートでは1行目になります。


  
    
      '表の列タイトルのセルにタイトル文字列より名前定義設定

      Sub SetTitleName()

      　　Const strPre As String = "nm" '名前定義のプリフィックス

      　　Dim ws As Worksheet '対象シート

      　　Dim NameRow As Long '列タイトル行

      　　Dim strName As String

      　　Dim i As Long

      　　

      　　Set ws = Worksheets("顧客マスタ")

      　　NameRow = 1

      　　With ws

      　　　　For i = 1 To .Cells(NameRow, .Columns.Count).End(xlToLeft).Column

      　　　　　　If .Cells(NameRow, i) <> "" Then

      　　　　　　　　strName = editName(.Cells(NameRow, i))

      　　　　　　　　.Names.Add Name:=strPre & strName, _

      　　　　　　　　　　　　　　RefersTo:="=" & .Cells(NameRow, i).Address

      　　　　　　End If

      　　　　Next

      　　End With

      End Sub


      '記号は_に変換：名前定義とプロパティ名に使うので

      Private Function editName(ByVal strName As String) As String

      　　'記号の一覧は使用しそうな記号を適当に記載

      　　'名前定義とプロパティ名に使えない記号は_に置換

      　　Const cnsSymbol As String = "!""#$%&'()=-~^|\`@{[+;*:}]<,>.?/\ "

      　　Dim strSymbol As String

      　　

      　　'CrLfは消去

      　　strName = Replace(Replace(strName, vbCr, ""), vbLf, "")

      　　'空白（スペース）は消去

      　　strName = Replace(Replace(strName, " ", ""), "　",
      "")

      　　'全角記号も対象

      　　strSymbol = cnsSymbol & StrConv(cnsSymbol, vbWide)

      　　

      　　'記号を_に置換

      　　Dim i As Integer

      　　For i = 1 To Len(strSymbol)

      　　　　strName = Replace(strName, Mid(strSymbol, i, 1), "_")

      　　Next

      　　If Right(strName, 1) = "_" Then

      　　　　strName = Left(strName, Len(strName) - 1)

      　　End If

      　　editName = strName

      End Function

列タイトル行のA列から最終列までの空白セルを除くセルに名前定義を設定しています。
名前定義の詳細については、名前定義（Names） こちらを参照してください。

・Namesコレクション・Nameオブジェクト・RangeオブジェクトのNameプロパティ・名前定義を使ったRangeの書き方・シートコピー時は名前定義に注意・名前定義の実践例

列タイトルには、各種の記号が使われている場合があるので、
名前定義に使う名前（後でプロパティ名としても使います）にするために、
_(アンダーバー)以外の記号を_(アンダーバー)に置換します。
ただし、そもそも列タイトルに色々な記号を使うことはあまりお勧めしません。
改行コードやスペースは削除しています。

Function editName が記号置換の関数になります。
ここで作成する名称は、名前定義と、クラスのプロパティ名としても使いますので、
本来なら、_(アンダーバー)以外の記号は全て外したいところですが、
ここでの本旨から外れるので、簡易的にキーボードから簡単に入力できる記号だけの判定にしています。
記号を完全に排除する場合は、
文字種（ひらがな、全半角カタカナ、半角英大文字等々）の判定

マクロVBAで、文字の種類を判定する具体的なコードになります、書き方はいろいろありますし、なにより、文字を何の種類に入れるかは決まりがありません。カタカナって、どこからどこまで、カナ記号は含むのこのような細部については、使用する時々に合わせて微調整が必要になります。

このあたりを参考に文字判定を組み込んでください。

名前のプリフィックスは名前定義として分かり易くしているだけであり、必須ではありません。

上記コードでは、シートに対して
シート.Names.Add
としているので、名前の適用範囲はそのシートに限定されます。

名前定義の範囲をシートにしている理由は、
列タイトルは、他のシートの列タイトルと重複してしまうので、これを避けるためです。
名前にシート名を入れることでも対応できますが、
名前が長いとプロパティ名として扱うときに不便なので、なるべく短く済むようにするためです。
従って、同一シート内の列タイトルは重複しないようにしなければなりませんし、
さらに、適度な長さの適切な名称にしておく必要があります。

名前が適度な長さの適切な名称とは
クラスのプロパティ名としても使いますので、プロパティ名として適切なということになります。
つまり、入力支援で表示される名称としてふさわしい名称ということであり、
その規則は、VBAの変数名で許可される文字列ということです。
・英数字（全角半角）、漢字、記号はアンダーバー(_)だけ使える、空白（スペース）は使えない
・先頭には、数字やアンダーバー(_)は使えない、英文字か漢字で始める
・使えない予約語がある
・長さは半角で255文字まで

とはいえ、列タイトルとしては、なかなかそういうわけにもいかない（項目の説明的なタイトル等の）場合があると思います。
そのようなときは、名前定義に使う名称を別の行に設定しておくことで対応できます。

上記の表であれば、先頭に2行追加して、
1行目に名前定義で使う名称
2行目は空行・・・これが無いとエクセルのデータベース機能が使えなくなります。
この2行を非表示にしておけば見た目は元のままです。
この場合は、上記VBAコードの
NameRow = 1 を NameRow = 3 に変更することになります。

以上で、前準備としての名前定義の完了になりますが、
この準備は、先に書いた
列挙体(Enum)

・Enumステートメント・列挙型（Enumステートメント）の使用例・列挙型（Enumステートメント）の活用について

や
構造体(Type)

・Typeステートメントの構文・ユーザー定義型の使い方・ユーザー定義型の使用例・ユーザー定義型の制限・最後に

での列移動対応の時も同様になります。

>クラスを新規挿入

Class1が作成されます。
次々に挿入していくと、2,3,と連番で作成されます。

オブジェクト名：Class1
これを変更します。
オブジェクト名：clsColumns

名前なので、命名規則の範囲内なら何でも良いのですが、
プリフィックスとして「cls」「class」等を付けておくことをお勧めします。
使うときに、As やNew の後で、「cl」と入力すると候補一覧が出て簡単に指定できるようになります。

作成したクラスをダブルクリック等で開くと、
Option Explicitだけが書かれた白紙が出てきます、標準モジュール作成時と同じですね。

>クラスに列数取得のプロシージャーと対象シートを受け取るプロパティを作成

名前定義の列数を取得するのは簡単です。
シート.Range("名前定義の名前").Column
これで取得できます。

シートと名前定義の名前が必要なので、
対象のシートを受け取るプロパティと、これを受け取って列数を返す関数（Function）を作成します。
また、名前が無いとエラーになるので、エラー処理も入れておきます。


  
    
      Private ws As Worksheet


      Public Property Set Worksheet(ByVal argWs As Worksheet)

      　　Set ws = argWs

      End Property


      Private Function getColumn(ByVal argName As String) As Long

      　　On Error Resume Next

      　　getColumn = ws.Range(argName).Column

      　　If Err Then

      　　　　MsgBox "名前定義不正：" & argName

      　　　　End

      　　End If

      End Function

クラス作成ではカプセル化を意識します。
そして、カプセル化の重要な要素として隠蔽があります。
むやみに内部を公開しないということです。
つまり基本的にはPrivateで作成し、外部に公開が必要なもののみPublicにします。

外部とやり取りするための出入り口が公開(Public)のプロパティとメソッドになります。

対象のシートを受け取るプロパティが、
Public Property Set Worksheet(ByVal argWs As Worksheet)
引数でシートのオブジェクトを受け取ります。
これはSetだけなので、書き込み専用プロパティ（入口専用、値を受け取るだけ）ということです。

名前定義の列数を取得し数値で返す関数が、
Private Function getColumn(ByVal argName As String) As Long
引数で名前定義の名前を受け取ります。

これで外部からこのクラスが使用可能になります。

Dim cls As New clsColumns
このように1行で書いても構いません。
違いはVBA内部処理の違いなので、特に気にする必要はありません。

クラスの中で、公開(Public)されているものだけが入力支援に表示されます。
つまり、それ以外は隠蔽されていて、外部からは扱えなくなっています。

ここまでで、後は名前定義の名前によって列数を返すプロパティを作れば良い状態になりました。

>クラスに列名のプロパティを作成

名前定義の名前を受け取り、列数を返すプロパティを作ります。


  
    
      Public Property Get 顧客ID() As Long

      　　Static c As Long

      　　If c <> 0 Then 顧客ID = c: Exit Property

      　　c = getColumn("nm顧客ID")

      　　顧客ID = c

      End Property

サンプルシートではA列の列数を返すプロパティが、
Public Property Get 顧客ID() As Long
これはA列なら1を返します。
これはGetだけなので、読み取り専用プロパティ（出口専用、値を返すだけ）ということです。

Static変数について

通常の変数は、プロシージャーやプロパティの終了とともに消えてしまいますが、Static変数は値を保持しています。
次回呼ばれたときに、前回の値が入ったままの状態ということです。

このプロパティにアクセスするたびに、getColumnを呼びRangeを参照してしまうと、とても処理速度が遅くなります。
そこで、ここではStatic変数に値を入れることで、最初の1回だけgetColumnを呼び出すようにしています。

これは、以下のようにすることもできます。
Privateなモジュールレベル変数として定義、全列の列数を変数に格納するメソッドを実行し変数に値を入れておく。
このように作成することも可能です、むしろそのほうが一般的かもしれません。
列数が多く、多くの列をVBAであつかうのであれば、そのほうが良いでしょう。


  
    
      Private p顧客ID As Long

      Private p氏名 As Long

      '・・・


      Public Property Set Worksheet(ByVal argWs As Worksheet)

      　　Set ws = argWs

      End Property


      Public Property Get 顧客ID() As Long

      　　顧客ID = p顧客ID

      End Property


      Public Property Get 氏名() As Long

      　　氏名 = p氏名

      End Property


      Public Sub Init()

      　　p顧客ID = getColumn("nm顧客ID")

      　　p氏名 = getColumn("nm氏名")

      End Sub


      '・・・

今回は、汎用的なものとして、VBAで扱う列がどの程度か不明なので、
実際にその列を扱うときに取得するようにしたということです。
また、
変数を使った方法では、一つのフィールドに対する記述が3箇所に分散しています。
これは、メンテナンスするときに面倒な作業となります。
（変更なら置換すれば良いのですが、追加の場合には3箇所で追加作業が必要です。）
今回の方法なら、一か所にまとまっているので、メンテ作業はしやすいので採用したということです。

ただし、パフォーマンス（速度やメモリ消費）に関しては、今回は無視して作成しています。
差異があるとしても、かなり僅かな差異なので、かなり大量の繰り返し処理をしなければ体感できる差異はないと思います
そのような必要性があるときは、個別に対応策を考える必要があります。

入力支援にクラス名が表示されるのが確認できます。


  
    
      Sub sample()

      　　Dim cls As clsColumns

      　　Set cls = New clsColumns

      　　Set cls.Worksheet = ActiveSheet

      　　MsgBox cls.顧客ID

      End Sub

サンプルシートであれば、1が表示されます。

上記では、顧客IDだけ入れましたが、
顧客ID以外の全てのフィールド（列タイトル）のプロパティを作成します。


  
    
      Public Property Get 氏名() As Long

      　　Static c As Long

      　　If c <> 0 Then 氏名 = c: Exit Property

      　　c = getColumn("nm氏名")

      　　氏名 = c

      End Property


      Public Property Get 氏名_カナ() As Long

      　　Static c As Long

      　　If c <> 0 Then 氏名_カナ = c: Exit Property

      　　c = getColumn("nm氏名_カナ")

      　　氏名_カナ = c

      End Property


      Public Property Get 性別() As Long

      　　Static c As Long

      　　If c <> 0 Then 性別 = c: Exit Property

      　　c = getColumn("nm性別")

      　　性別 = c

      End Property


      Public Property Get 生年月日() As Long

      　　Static c As Long

      　　If c <> 0 Then 生年月日 = c: Exit Property

      　　c = getColumn("nm生年月日")

      　　生年月日 = c

      End Property


      Public Property Get 年齢() As Long

      　　Static c As Long

      　　If c <> 0 Then 年齢 = c: Exit Property

      　　c = getColumn("nm年齢")

      　　年齢 = c

      End Property


      Public Property Get 郵便番号() As Long

      　　Static c As Long

      　　If c <> 0 Then 郵便番号 = c: Exit Property

      　　c = getColumn("nm郵便番号")

      　　郵便番号 = c

      End Property


      Public Property Get 都道府県() As Long

      　　Static c As Long

      　　If c <> 0 Then 都道府県 = c: Exit Property

      　　c = getColumn("nm都道府県")

      　　都道府県 = c

      End Property


      Public Property Get 電話番号() As Long

      　　Static c As Long

      　　If c <> 0 Then 電話番号 = c: Exit Property

      　　c = getColumn("nm電話番号")

      　　電話番号 = c

      End Property


      Public Property Get Email() As Long

      　　Static c As Long

      　　If c <> 0 Then Email = c: Exit Property

      　　c = getColumn("nmEmail")

      　　Email = c

      End Property


      Public Property Get 備考() As Long

      　　Static c As Long

      　　If c <> 0 Then 備考 = c: Exit Property

      　　c = getColumn("nm備考")

      　　備考 = c

      End Property

これで、全ての列名が入力支援に表示されます。

数字が返されるので、Cellsの中でもどこでも、その数値が必要な場所に書くことができます。
（どこでもといっても、定数値を書かなければならない場所には書けませんが）

クラスの完成コード

以下が、今回のクラスの完成VBAコードです。


  
    
      Option Explicit


      Private ws As Worksheet


      Public Property Set Worksheet(ByVal argWs As Worksheet)

      　　Set ws = argWs

      End Property


      Public Property Get 顧客ID() As Long

      　　Static c As Long

      　　If c <> 0 Then 顧客ID = c: Exit Property

      　　c = getColumn("nm顧客ID")

      　　顧客ID = c

      End Property


      Public Property Get 氏名() As Long

      　　Static c As Long

      　　If c <> 0 Then 氏名 = c: Exit Property

      　　c = getColumn("nm氏名")

      　　氏名 = c

      End Property


      Public Property Get 氏名_カナ() As Long

      　　Static c As Long

      　　If c <> 0 Then 氏名_カナ = c: Exit Property

      　　c = getColumn("nm氏名_カナ")

      　　氏名_カナ = c

      End Property


      Public Property Get 性別() As Long

      　　Static c As Long

      　　If c <> 0 Then 性別 = c: Exit Property

      　　c = getColumn("nm性別")

      　　性別 = c

      End Property


      Public Property Get 生年月日() As Long

      　　Static c As Long

      　　If c <> 0 Then 生年月日 = c: Exit Property

      　　c = getColumn("nm生年月日")

      　　生年月日 = c

      End Property


      Public Property Get 年齢() As Long

      　　Static c As Long

      　　If c <> 0 Then 年齢 = c: Exit Property

      　　c = getColumn("nm年齢")

      　　年齢 = c

      End Property


      Public Property Get 郵便番号() As Long

      　　Static c As Long

      　　If c <> 0 Then 郵便番号 = c: Exit Property

      　　c = getColumn("nm郵便番号")

      　　郵便番号 = c

      End Property


      Public Property Get 都道府県() As Long

      　　Static c As Long

      　　If c <> 0 Then 都道府県 = c: Exit Property

      　　c = getColumn("nm都道府県")

      　　都道府県 = c

      End Property


      Public Property Get 電話番号() As Long

      　　Static c As Long

      　　If c <> 0 Then 電話番号 = c: Exit Property

      　　c = getColumn("nm電話番号")

      　　電話番号 = c

      End Property


      Public Property Get Email() As Long

      　　Static c As Long

      　　If c <> 0 Then Email = c: Exit Property

      　　c = getColumn("nmEmail")

      　　Email = c

      End Property


      Public Property Get 備考() As Long

      　　Static c As Long

      　　If c <> 0 Then 備考 = c: Exit Property

      　　c = getColumn("nm備考")

      　　備考 = c

      End Property


      Private Function getColumn(ByVal argName As String) As Long

      　　On Error Resume Next

      　　getColumn = ws.Range(argName).Column

      　　If Err Then

      　　　　MsgBox "名前定義不正：" & argName

      　　　　End

      　　End If

      End Function

最後に

今回作成したクラスですと、先に解説した構造体(Type)との違いがはっきりしません。
といいますか、結果としての機能は同じです。
一つの標準モジュールに構造体とFunctionを入れれば同じことになります。

これだけなら、どちらが良いということはありません。
他に作成するクラスがないのなら、構造体で良いでしょうし、
他のクラスを作るのなら、ついでにクラスが良いのではないかというくらいでしかありません。

今回作成したクラスは、表のフィールド数が多いと、やたら長くなるというのが欠点です。
フィールド数分のプロパティが必要なので、これを全部タイピングするのは時間がかかります。
（VBAで扱わない列のプロパティは作る必要はありませんが）
だからと言って、
プロパティの代わりに列名の変数を用意し、変数をそのまま直接公開(Public)するというのは、
正しくカプセル化ができていないクラスということになりますし、
それなら構造体を使ったほうが良いので、クラス化した意味が半減してしまいます。
変数を公開してしまうと、外部からその変数の値が変更可能になってしまいます。
これは極めて危険であり、カプセル化できていないということになります。

プロパティの記述は単純作業ですが（なので）大変です。
そこで次回は、
この面倒なプロパティの記述を自動で作成するマクロVBAを紹介する予定です。

同じテーマ「VBAクラス入門」の記事

VBAクラス入門：クラスとは?オブジェクト指向とは?
クラスを使った全ブック（他ブック）のイベント補足
VBAクラスの作り方：列名の入力支援と列移動対応
VBAクラスの作り方：列名のプロパティを自動作成する
 VBAクラスの作り方：独自Rangeっぽいものを作ってみた
 クラスとイベントとマルチプロセス並列処理
 クラスとCallByNameとポリモーフィズム(多態性)
オートフィルターを退避回復するVBAクラス
 オートフィルター退避回復クラスを複数シート対応させるVBAクラス
 コレクション（Collection）の並べ替え（Sort）に対応するクラス
 VBAクラスのAttributeについて（既定メンバーとFor Each）

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.繰り返し処理（For Next)｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.メッセージボックス(MsgBox関数)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.ブック・シートの選択（Select,Activate）｜VBA入門
10.条件分岐（Select Case）｜VBA入門

ホーム
マクロVBA応用編
VBAクラス入門
VBAクラスの作り方：列名の入力支援と列移動対応

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBA技術解説
VBAクラスの作り方：列名の入力支援と列移動対応