ExcelマクロVBA技術解説 | VBAクラスの作り方:列名の入力支援と列移動対応 | ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説



最終更新日:2019-02-12

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


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

つまり、
クラス作成に手間をかけて、その後を楽にするということになります。

もちろん、
クラスを使う理由は入力支援だけではなく、むしろそれは結果としての付属的なものでしかありません。
列名で指定したいだけなら、列挙体(Enum)構造体(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) こちらを参照してください。

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

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

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

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



名前定義の範囲をシートにしている理由は、
列タイトルは、他のシートの列タイトルと重複してしまうので、これを避けるためです。
名前にシート名を入れることでも対応できますが、
名前が長いとプロパティ名として扱うときに不便なので、なるべく短く済むようにするためです。
従って、同一シート内の列タイトルは重複しないようにしなければなりませんし、
さらに、適度な長さの適切な名称にしておく必要があります。
名前が適度な長さの適切な名称とは
クラスのプロパティ名としても使いますので、プロパティ名として適切なということになります。
つまり、入力支援で表示される名称としてふさわしい名称ということであり、
その規則は、VBAの変数名で許可される文字列ということです。
・英数字(全角半角)、漢字、記号はアンダーバー(_)だけ使える、空白(スペース)は使えない
・先頭には、数字やアンダーバー(_)は使えない、英文字か漢字で始める
・使えない予約語がある
・長さは半角で255文字まで
とはいえ、列タイトルとしては、なかなかそういうわけにもいかない(項目の説明的なタイトル等の)場合があると思います。
そのようなときは、名前定義に使う名称を別の行に設定しておくことで対応できます。

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

以上で、前準備としての名前定義の完了になりますが、
この準備は、先に書いた列挙体(Enum)構造体(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箇所で追加作業が必要です。)
今回の方法なら、一か所にまとまっているので、メンテ作業はしやすいので採用したということです。

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

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



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

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のクラスとは(Class,Property,Get,Let,Set)
VBAクラスの作り方:列名の入力支援と列移動対応
VBAクラスの作り方:列名のプロパティを自動作成する
VBAクラスの作り方:独自Rangeっぽいものを作ってみた
クラスを使って他ブックのイベントを補足する

新着記事 ・・・新着記事一覧を見る

Excelシートの複雑な計算式を解析するVBA|VBAサンプル集(2月18日)
VBAクラスの作り方:独自Rangeっぽいものを作ってみた|VBA技術解説(2月16日)
VBAクラスの作り方:列名のプロパティを自動作成する|VBA技術解説(2月14日)
VBAクラスの作り方:列名の入力支援と列移動対応|VBA技術解説(2月11日)
クラスを使って他ブックのイベントを補足する|VBA技術解説(2月6日)
Excelアドインの作成と登録について|VBA技術解説(2月3日)
参照設定、CreateObject、オブジェクト式の一覧|VBA技術解説(1月20日)
VBAでファイルを規定のアプリで開く方法|VBA技術解説(1月20日)
ドキュメントプロパティ(BuiltinDocumentProperties,CustomDocumentProperties)|VBA技術解説(1月19日)
他ブックへのリンクエラーを探し解除|VBAサンプル集(1月15日)

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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.変数とデータ型(Dim)|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.RangeとCellsの使い方|ExcelマクロVBA入門
6.マクロって何?VBAって何?|ExcelマクロVBA入門
7.繰り返し処理(For Next)|ExcelマクロVBA入門
8.とにかく書いて見よう(Sub,End Sub)|VBA入門
9.定数と型宣言文字(Const)|ExcelマクロVBA入門
10.ひらがな⇔カタカナの変換|エクセル基本操作



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

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


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





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

    本文下部へ

    ↑ PAGE TOP