列数の定数宣言の自動生成、自動変更

Excel で横に長い表、つまり大量に列があるファイルを扱う際に
次のことがとても面倒になります。

列の修正がはいった時に列数を修正すること

これを改善するために
列挙型の定数を自動生成するプロシージャを作ってみました。
 

例えば、下記のような表があったとして


A B C D E
1
2 学籍番号 クラス 名前
3 102 A 田中太郎
4 103 B 鈴木太郎
5


 

CreateConstantDecreation(Range("B2:D2")) を実行すると
下記のような列挙型の定数が自動的にコードの頭に
追加されるような挙動をします。

Enum C
   学籍番号 = 2
   クラス = 3
   名前 = 4
End Enum

 
列の変更があっても
このプロシージャを実行するだけで
定数の更新が自動で行われます。

コードは下記の通り。

Option Explicit
Enum C
   学籍番号 = 2
   クラス = 3
   名前 = 4
End Enum



Public Const FIRST_LINE = "#####ここまで宣言部######"




Sub CallCreateConstantDecreation()
  Call CreateConstantDecreation(sheet1.Range("B2:D2"))
End Sub


Sub CreateConstantDecreation( _
 ByVal r As Range _
)
 With Application.VBE.ActiveCodePane.CodeModule

   ' ①FIRST_LINE が定義されている行数を取得
   Dim LastDecreationLine As Integer
   For LastDecreationLine = 1 To .CountOfLines
       If Not InStr(FIRST_LINE, .Lines(LastDecreationLine, 1)) = 0 Then GoTo END_FOR
   Next LastDecreationLine
END_FOR:


   ' ②FIRST_LINE が定義されている行よりも上をすべて削除
   .DeleteLines 2, LastDecreationLine - 2


    ' ③指定された範囲を定数として宣言を順次記述
    Dim DecreationLine As Integer: DecreationLine = 2

    .InsertLines DecreationLine, "Enum C"
    DecreationLine = DecreationLine + 1

    Dim C As Range
    For Each C In r
      .InsertLines DecreationLine, vbTab & C.Value & "=" & C.Column
      DecreationLine = DecreationLine + 1
    Next C

    .InsertLines DecreationLine, "End Enum"

  End With
End Sub

 
参考にしたサイト
Office TANAKA - VBAでVBEを操作する[CodeModuleのメソッド]
 

Remove all ads