マクロ（Excel VBA）を使うときは後の作業を楽にするために行番号と列番号を変数で指定する､Excel シートに空白の行・列を入れておく

Excel VBA でのプログラミングを楽にするために工夫は欠かせません。

今日は、私が普段 VBA を使う前提で Excel シートを作るときに工夫していることを2つ紹介します。

●行番号と列番号を変数で指定する
●空白の行・列を入れておく

これらはシートの設計を変更した場合に VBA コードの変更点を極力少なくて済むようにするための工夫です。

1、行番号と列番号を変数で指定する
1. 列番号を数値で指定したコード
2. 列番号を変数で指定したコード
2、空白の行・列を入れておく
3、まとめ

1、行番号と列番号を変数で指定する

行番号と列番号は変数で指定しておいた方が良いケースがあります。

例えば、次の1年間の給料の一覧表があったとします。

月別の「総支給額」「社会保険料」「源泉所得税」「住民税」「差引支給額」の金額を表示しています。

F列の差引支給額は VBA によって計算するものとします。

＊単なる引き算なので実際には VBA を使うまどもなく四則演算だけで計算できますが、今日は題材として VBA を使いますね。

列番号を数値で指定したコード

次のコードで差引支給額を計算できます。

Sub Sashihiki_Shikyu()
    
    Dim Soushikyu           '総支給額'
    Dim Syaho               '社会保険料'
    Dim Gensen              '源泉所得税'
    Dim Jumin               '住民税'
    Dim Sashihiki_Shikyu    '差引支給額'
    
    For i = 1 To 12
        
        Soushikyu = Cells(i + 1, 2)       '「総支給額」読み込み'
        Syaho = Cells(i + 1, 3)           '「社会保険料」読み込み'
        Gensen = Cells(i + 1, 4)          '「源泉所得税」読み込み'
        Jumin = Cells(i + 1, 5)           '「住民税」読み込み'
        
        Sashihiki_Shikyu = Soushikyu - (Syaho + Gensen + Jumin)    '「差引支給額」の計算'
        
        Cells(i + 1, 6) = Sashihiki_Shikyu    '「差引支給額」の表示'
        
    Next i
    
End Sub

Sub Sashihiki_Shikyu()

Dim Soushikyu '総支給額'

Dim Syaho '社会保険料'

Dim Gensen '源泉所得税'

Dim Jumin '住民税'

Dim Sashihiki_Shikyu '差引支給額'

For i = 1 To 12

Soushikyu = Cells(i + 1, 2) '「総支給額」読み込み'

Syaho = Cells(i + 1, 3) '「社会保険料」読み込み'

Gensen = Cells(i + 1, 4) '「源泉所得税」読み込み'

Jumin = Cells(i + 1, 5) '「住民税」読み込み'

Sashihiki_Shikyu = Soushikyu - (Syaho + Gensen + Jumin) '「差引支給額」の計算'

Cells(i + 1, 6) = Sashihiki_Shikyu '「差引支給額」の表示'

Next i

End Sub

実行するとちゃんと計算できていることが確認できますね。

さて、ここでB列に新たに「支給日」を入力するようにしたい場合、列を挿入しなければなりません。

このようにすると先のコードで「総支給額」「社会保険料」「源泉所得税」「住民税」を読み込む列が1列ずつずれてしまうので、コードを修正しなければなりません。

具体的には、次のように列番号を1つずつずらします。

———————————————-

Soushikyu = Cells(i + 1, 2)
⇓
Soushikyu = Cells(i + 1, 3)

———————————————-
Syaho = Cells(i + 1, 3)

⇓
Syaho = Cells(i + 1, 4)

———————————————-
Gensen = Cells(i + 1, 4)

⇓
Gensen = Cells(i + 1, 5)

———————————————-
Jumin = Cells(i + 1, 5)
⇓
Jumin = Cells(i + 1, 6)

———————————————-

ここで1つ言えることは、変更箇所が多くて修正が面倒ということです。

この例ならまだ良いですが、もっと膨大なデータを扱わなければならない場合は容易ではありません。

列番号を変数で指定したコード

そこで、先のコードを最初から次のように作っておきます。

Sub Sashihiki_Shikyu1()
    
    Dim Soushikyu           '総支給額'
    Dim Syaho               '社会保険料'
    Dim Gensen              '源泉所得税'
    Dim Jumin               '住民税'
    Dim Sashihiki_Shikyu    '差引支給額'
    Dim S_Line              '総支給額の列'
    
    S_Line = 2
    
    For i = 1 To 12
        
        Soushikyu = Cells(i + 1, S_Line)
        Syaho = Cells(i + 1, S_Line + 1)
        Gensen = Cells(i + 1, S_Line + 2)
        Jumin = Cells(i + 1, S_Line + 3)
        
        Sashihiki_Shikyu = Soushikyu - (Syaho + Gensen + Jumin)    '「差引支給額」の計算'
        
        Cells(i + 1, 6) = Sashihiki_Shikyu    '「差引支給額」の表示'
        
    Next i
    
End Sub

Sub Sashihiki_Shikyu1()

Dim Soushikyu '総支給額'

Dim Syaho '社会保険料'

Dim Gensen '源泉所得税'

Dim Jumin '住民税'

Dim Sashihiki_Shikyu '差引支給額'

Dim S_Line '総支給額の列'

S_Line = 2

For i = 1 To 12

Soushikyu = Cells(i + 1, S_Line)

Syaho = Cells(i + 1, S_Line + 1)

Gensen = Cells(i + 1, S_Line + 2)

Jumin = Cells(i + 1, S_Line + 3)

Sashihiki_Shikyu = Soushikyu - (Syaho + Gensen + Jumin) '「差引支給額」の計算'

Cells(i + 1, 6) = Sashihiki_Shikyu '「差引支給額」の表示'

Next i

End Sub

最初のコードと違う点は、「総支給額」「社会保険料」「源泉所得税」「住民税」を読み込む際の列番号の指定を変数を使っているということです。

S_Line = 2　の部分（10行目）で「総支給額」の列番号である2を変数「S_Line」に入力しておき、以後の各項目の読み込みの際の列番号の指定には変数「S_Line」を使っています。

最初のコードと異なる点は次の部分ですね。

———————————————-

Soushikyu = Cells(i + 1, 2)
⇓
Soushikyu = Cells(i + 1, S_Line)

———————————————-
Syaho = Cells(i + 1, 3)

⇓
Syaho = Cells(i + 1, S_Line + 1)

———————————————-
Gensen = Cells(i + 1, 4)

⇓
Gensen = Cells(i + 1, S_Line + 2)

———————————————-
Jumin = Cells(i + 1, 5)
⇓
Jumin = Cells(i + 1, S_Line + 3)

———————————————-

何が良いかというと、変数を使って列番号を指定しているため先ほどの「支給日」のように新たに列を挿入しなければならなくなった場合でも変数さえ変更しておけば、列番号の数値を1つ1つ変更する必要が無いということです。

変数の変更は「S_Line = 2」を「S_Line = 3」にすれば良いだけですね。

この例に限らずデータ量が多いほど、行番号、列番号を変数でしていしておくメリットは大きくなります。

2、空白の行・列を入れておく

さて、もう1つ VBA を使う前提の Excel シートにおいて役立つこととして「空白の行・列を入れておく」という考え方があります。

先ほどのこのシート⇃

行・列ともに空白を表の外に挿入しておくと、後々表の変更をしたい場合に便利です。

例えば、「支給日」を表示したい場合、空白の行・列が無いシートだと新たに列を挿入しなければなりませんが、空白の行・列をいれておけばその一部を使うことで対応できます。

K列を「支給日」に変更して、「月」を余白だったJ列に移すことができます。

そうすると、「総支給額」「社会保険料」「源泉所得税」「住民税」の列番号は変わらないので VBA コードに与える影響も無くなります。

（まぁ、列番号を変数で指定しておけばそもそも問題ありませんが）

3、まとめ

さて、VBA を使う前提で Excel シートを作るときに工夫していることを紹介しました。

これらの方法は日頃よく使います。

Excel で最初に作ったシートの中身を後で変更したくなるケースはたくさんあります。

VBA を使っている場合は、シートが変わるとコードも大幅に変更しなえればならなくなることがあるので、極力変更点を少なくするために工夫をすることは大切です。