カテゴリー
Microsoft

【Excel VBA】指定したブックのシートの列の値を 1 からの連番に置換した配列を生成するクラスコード

ポイント

  • 対象範囲セルが 1 セルのみの場合、Range.Value は配列にならない。よってこの場合だけ別に処理をする。

VBA コード

CellSequencer.cls
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
''' <summary>
''' 指定したブックのシートの列の値を 1 からの連番に置換した配列を生成するクラスです。
''' </summary>
 
Option Explicit
 
' 対象範囲 Range.Value の 2 次元配列
Private mvntValues As Variant
Private mlngRow As Long
 
''' <summary>
''' 初期化処理を実行します。
''' </summary>
''' <param name="strBook">対象ブック名</param>
''' <param name="strSheet">対象シート名</param>
''' <param name="strRange">対象範囲名</param>
''' <param name="lngRow">連番を開始する Range 内の行番号</param>
Public Sub Init( _
    ByVal strBook As String, _
    ByVal strSheet As String, _
    ByVal strRange As String, _
    ByVal lngRow As Long)
 
    ' 変数をセット
    mvntValues = Workbooks(strBook).Sheets(strSheet).range(strRange).Value
    mlngRow = lngRow
End Sub
 
''' <summary>
''' 対象列の要素を 1 からの連番に置換した 2 次元配列を返します。
''' </summary>
''' <returns>要素変換済みの 2 次元配列</returns>
Public Function SequencedValues() As Variant
    ' Range.Value 配列のコピーを用意し、元の配列はそのままの形で残す。
    Dim vntCopied As Variant: vntCopied = mvntValues
    ' 対象が 1 セルのみの場合
    If Not IsArray(mvntValues) Then
        SequencedValues = vntCopied
        If mlngRow <= 1 Then
            SequencedValues = 1
        End If
        Exit Function
    End If
 
    Dim count As Long: count = 1
    Dim i As Long
    Dim j As Long
    For i = LBound(mvntValues, 1) To UBound(mvntValues, 1)
        For j = LBound(mvntValues, 2) To UBound(mvntValues, 2)
            ' 連番を設定
            vntCopied(i, j) = count
        Next j
    Next i
 
    SequencedValues = vntCopied
End Function

確認用コード。A1:B4 までを対象とし、この範囲の 2 行目から (つまり 2 〜 4 行目) 連番をつけていきます。

ThisWorkbook.cls
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Option Explicit
  
Public Sub Test()
    ' 対象定義
    Dim book As String: book = "Book.xlsm"
    Dim sheet As String: sheet = "Sheet1"
    Dim range As String: range = "A1:A" & Cells(Rows.count, 1).End(xlUp).row
  
    ' クラス生成
    Dim cs As CellSequencer: Set cs = New CellSequencer
    Call cs.Init(book, sheet, range, 2)
  
    ' 実行
     Workbooks(book).Sheets(sheet).range(range) = cs.SequencedValues
End Sub

確認用エクセル。

  • ブック名: Book1.xlsm
  • シート名: Sheet1
No カラム1
a1 b1
a2 b2
a3 b3

おわりに

No 列には本来数字が入ってほしいのに、全然異なった値が入っておりました＞＜。

そのような列がいくつもありましたし、何度も入れ替える必要がございましたので、もういっそのこと VBA コードとして用意いたしました。

クラスにするか、モジュールにするか、実は少し迷いました。

結局クラスにしたのですけれども、クラスにすることで、ちょっと違う関数を作ってちょっと違う結果を得ることが簡単にできるようになるメリットがあると存じます。

最後に、参考ページです。ありがとう存じます！

以上です。

コメントを残す