マクロの勉強に！VBAの事例で変数と繰返しを習得しよう

「変数とは？」という説明はたくさんありますが、解説を読んで分かったような感じがしても、いざ PC の前でプログラムを書いてみようと思ってもなかなか書けないもの。今回は、プログラミングの要である「変数」と「繰り返し」を、実用的な事例をもとに解説してみました。

アイキャッチ画像は、麦さんによるイラストACからお借りしました。

今回の事例の概要とテストデータの準備

ネットショップでリピートオーダーを調べる

今回のプログラミングの事例は、ネットショップで「リピートオーダーの割合を知りたい」です。

例えば、去年の購入者リストは以下のとおりだったとします。
上村広明
下村憲志
星野波子
中井茂平
星優子
川田るり子
徳永芙美子
塚田真紀
松崎裕仁
加藤梨恵子
菊池酉蔵
浅野波子
上村和久
谷川元久
吉村麗奈
沼田智美
川本邦煕
永野耕介
長田隆吉
福井錦也

そして、こちらが、今年の購入者リスト。
上村広明
戸田美貴
星野波子
徳田則昭
星優子
木下真由美
徳永芙美子
永井恵志
松崎裕仁
古賀由雄
菊池酉蔵
川本邦煕
上村和久
片山大夢
吉村麗奈
長田隆吉
川本邦煕
杉原千津
長田隆吉
長岡玲

名前はすべて架空のものです。同性同名の方がいらっしゃったらゴメンナサイ。

ちなみに、プログラミングではテスト用に個人名が必要なことがよくあるのですが、架空の名前を量産するのって意外と大変です。
そこで、こちらのサイトを使わせていただきました。テストデータ・ジェネレータ架空の名前を4999件まで作成できます。

さて、去年の購入者は今年何人の方がリピート購入してくれたでしょうか？　テスト用なので去年と今年で20人ずつにしていますが、実際には 10,000人ぐらいの方が購入してくれるかもしれません。これを目視で調べるのは気が遠くなりそうですしミスも出そうです。

こんな時便利なのが Excel マクロです。

テストデータを Excel で作る

去年の購入者リストと今年の購入者リストを Sheet1、Sheet2 に貼り付けます。

去年の購入者リストのシート名は「OLD」にしました。

今年の購入者リストのシート名は「NEW」です。

とりあえずプログラムを書いて実行

では、プログラムを書いて実行してみましょう。

プログラムの勉強では、まずは動かしてみることが一番。動かさないで解説本を一生懸命読んでも頭に入ってきません。

一度動かしてみてから、コードを読み、ちょっと変えて動かしてみたり、分からないところを Google で検索してみたりすると理解が深まります。

Excel の「開発」タブから「Visual Basic」を起動する

「開発」タブが見つからない！という方はセットアップが必要です。以下の記事をどうぞ。

itkomagane.com

これから勉強する人のための Excel VBA（マクロ）超入門

プログラミングの勉強の入り口として一番のお勧めは Excel VBA です。これから Excel VBA を始める人のために、Excel プログラミングの基本と、VBA Editor を使ったデバッグの方法を解説します。プログラミングがリモートワークにお勧めな理由出社の必要が無くどこでも好きな場所で働けるリモートワークが盛り上がっていますね。これは、少子高齢化で働ける人が減っている中、子育てや障害などで家にいる人にも働いてもらいたいという国や企業の思惑と、技術革新やクラウドなどの環境が整ってきたことが理由に挙げられます。リモートワ...

「挿入」メニューから「標準モジュール」を追加します。

以下のコードを「標準モジュール」にコピペして、ツールバーの「実行」ボタンをクリックします。

Sub Hikaku()
    '「OLD」スプレッドシートの変数を宣言
    Dim OldCurrentRow As Long
    Dim OldLastRow As Long
    
    '「NEW」スプレッドシートの変数を宣言
    Dim NewCurrentRow As Long
    Dim NewLastRow As Long
    
    '比較する文字を入れるための変数を宣言
    Dim s As String
    
    '「OLD」スプレッドシートの最終行を取得
    OldLastRow = Worksheets("OLD").Cells(Rows.Count, 1).End(xlUp).row

    '「NEW」スプレッドシートの最終行を取得
    NewLastRow = Worksheets("NEW").Cells(Rows.Count, 1).End(xlUp).row

    '「OLD」スプレッドシートを基準にループ
    For OldCurrentRow = 1 To OldLastRow
        '「OLD」スプレッドシートの現在行の値を取得
        s = Worksheets("OLD").Cells(OldCurrentRow, 1)
        
        '「NEW」スプレッドシートを１行ずつチェックする
        For NewCurrentRow = 1 To NewLastRow
        
            '「NEW」スプレッドシートの値と「OLD」スプレッドシートを比較
            If s = Worksheets("NEW").Cells(NewCurrentRow, 1) Then
                
                '「NEW」スプレッドシートの値と「OLD」スプレッドシートの値が同じなら
                '「OLD」スプレッドシートのＢ列に「＊」を入れる
                Worksheets("OLD").Cells(OldCurrentRow, 2).Value = "＊"
                
                '「NEW」スプレッドシートの繰り返しから抜ける
                Exit For
            End If
        Next NewCurrentRow
    Next OldCurrentRow

End Sub

Sub Hikaku()

'「OLD」スプレッドシートの変数を宣言

Dim OldCurrentRow As Long

Dim OldLastRow As Long

'「NEW」スプレッドシートの変数を宣言

Dim NewCurrentRow As Long

Dim NewLastRow As Long

'比較する文字を入れるための変数を宣言

Dim s As String

'「OLD」スプレッドシートの最終行を取得

OldLastRow = Worksheets("OLD").Cells(Rows.Count, 1).End(xlUp).row

'「NEW」スプレッドシートの最終行を取得

NewLastRow = Worksheets("NEW").Cells(Rows.Count, 1).End(xlUp).row

'「OLD」スプレッドシートを基準にループ

For OldCurrentRow = 1 To OldLastRow

'「OLD」スプレッドシートの現在行の値を取得

s = Worksheets("OLD").Cells(OldCurrentRow, 1)

'「NEW」スプレッドシートを１行ずつチェックする

For NewCurrentRow = 1 To NewLastRow

'「NEW」スプレッドシートの値と「OLD」スプレッドシートを比較

If s = Worksheets("NEW").Cells(NewCurrentRow, 1) Then

'「NEW」スプレッドシートの値と「OLD」スプレッドシートの値が同じなら

'「OLD」スプレッドシートのＢ列に「＊」を入れる

Worksheets("OLD").Cells(OldCurrentRow, 2).Value = "＊"

'「NEW」スプレッドシートの繰り返しから抜ける

Exit For

End If

Next NewCurrentRow

Next OldCurrentRow

End Sub

プログラムを実行してシート名「OLD」を見ると、リピーターさん（OLD と NEW の両方に名前がある人）のB列に「＊」印が入ります。この「＊」を数えて人数（今回は20人）で割るとリピート率が出ます。
分かりやすいように、１個置きに「＊」が付くデータにしました。リピート率は50%になります。

プログラムを書く前の準備

まず、どういう手順でプログラムを書けば良いか考えてみる

もし手作業でチェックするとしたら、次のような手順になると思います。

１．「OLD」シートの１番目「上村広明」さんが、「NEW」シートにあるかどうか探します。

２．「NEW」シートの１番目は同じ「上村広明」さんなので、「OLD」シートに戻ってB列に「＊」を入れます。

３．「OLD」シートの２番目は「下村憲志」さんです。

４．「NEW」シートの１番目から「下村憲志」さんを探します。

５．１番目、２番目、３番目・・・と順番に見ていき、２０番目まで行ってもなかったので、「OLD」シートのB列は空白になります。

つまり、

「OLD」シートの１番目
　　　↓
「NEW」シートの１番目から２０番目まで探す
　　　↓
「OLD」シートの２番目
　　　↓
「NEW」シートの１番目から２０番目まで探す

という手順になるため、「NEW」シートの１行目から２０行目まで調べる作業を、「OLD」シートの行数分繰り返すことになります。

繰り返しのコードの種類

繰り返しのコードには２種類あります。「回数を指定して繰り返す」方法と「条件を指定して繰り返す」方法です。

回数を指定して繰り返すForNext

次の例は Excel VBA で「回数を指定して繰り返す」方法です。

For i = 1 to 5
    '処理
Next i

For i = 1 to 5

'処理

Next i

１．「For」から始まって「Next」で終わります
２．「For」と「Next」の間に繰り返したい処理を書きます
３．「For」の右横にある「i = 1 to 5」は、「i が 1から始まって、i が 5」になるまで繰り返すという意味になります

終了条件を指定して繰り返すDoLoop

「条件を指定して繰り返す」方法は、「Do Loop」を使います。

i = 0

Do
'処理

i = i + 1
if i = 5 then
    exit do
end if

Loop

i = 0

'処理

i = i + 1

if i = 5 then

exit do

end if

Loop

１．「Do」から始まって「Loop」で終わります
２．「Do」と「Loop」の間に繰り返したい処理を書きます
３．繰り返しの中で「i」の数を「1」ずつ増やしていき、i が「5」になったら繰り返しを終了します

お勧めは ForNext

「For 〜 Next」も「Do 〜 Loop」も一連の処理を繰り返すという点は同じですが、「Do 〜 Loop」の方がやや複雑になります。終わる条件が正しく書かれていないと無限ループになってしまったり、想定どおりに動かないということが起こります。

ですから、回数が指定できるのであれば、できる限り「For 〜 Next」を使った方が、より安全なプログラムを作ることができます。

変数

「繰り返しのコード」のところにある「i」は変数と呼ばれるものです。「変数」は「変な数」ではなく、中身を「変えられる」という意味で「変数」と呼びます。「入れ物」とか「箱」とかに例えられることが多いです。

Excel VBA では、プログラムの初めに「この変数を使います」という宣言をします。具体的には次のように書きます。

Dim i As Long

1	Dim i As Long

最初に付く「Dim」は、VBA 特有のもので、変数を宣言するときのマークのようなものです。変数を宣言するよ！と Excel に教えているわけです。

Javascript では「Var」、PHP では「$」など、プログラミング言語によって変数の宣言の方法は異なります。

変数の名前

「Dim」の後ろの「i」は変数名になります。

変数の名前は何でも良いのですが、「予約語」と言ってプログラムで使う命令は変数として宣言することはできません。例えば「Dim」とか「As」は変数名にすることはできません。

変数は何を表しているのか一目で分かる名前にすると分かりやすいプログラムを書くことができます。

変数の型

名前の後ろには「型」を指定します。ここでは「Long」型を指定しました。Long は桁数の多い数字を入れるための型になります。Excel に限らず、他のプログラミング言語でも見かけます。桁数の少ない数字を入れる型には「Integer」があります。

今回のテストデータの顧客リストは20人なので「Integer」でも間に合うのですが、Excel の行数を対象にするときは数万件が対象になる可能性があるため「Long」を使っています。

他にも文字を入れるための「String」という型があります。

変数の使い方

変数に値を入れるときは「=」を使います。次のコードは、「＝」の右と左が等しいというわけではなく、「右側の値を左側の変数（i）に入れる」という意味になります。

i = i + 1

i = i + 1

上の例は、i に 1 を足して i に入れるというプログラムです。繰り返す間に、i が 1 ずつ増えてくプログラムになります。

プログラムの解説

それでは、実際にプログラムを書いてみましょう。

プログラムに名前をつける

まず１行目はプログラムの名前になります。Excel マクロは、Sub か Function ではじめます。今回は Sub にしました。通常、後ろの（　）には引き数を入れますが、今回は何も無しです。

Sub Hikaku()

1	Sub Hikaku()

「Enter」キーを押すと、自動で最後に「End Sub」が追加されますので、「Sub」から「End Sub」の間にプログラムを書いていきます。

変数を宣言する

今回は次の５つの変数を使います。

'「OLD」スプレッドシートの変数を宣言
Dim OldCurrentRow As Long 
Dim OldLastRow As Long

 '「NEW」スプレッドシートの変数を宣言
Dim NewCurrentRow As Long
Dim NewLastRow As Long

'比較する文字を入れるための変数を宣言
Dim s As String

'「OLD」スプレッドシートの変数を宣言

Dim OldCurrentRow As Long

Dim OldLastRow As Long

'「NEW」スプレッドシートの変数を宣言

Dim NewCurrentRow As Long

Dim NewLastRow As Long

'比較する文字を入れるための変数を宣言

Dim s As String

OldCurrentRowは、「OLD」シートの現在行を入れるための変数で、OldLastRowは「OLD」シートの名前の入っている最後の行を入れるための変数です。

同じように、「NEW」シート用の NewCurrentRow と NewLastRow も宣言します。

変数 s は「OLD」スプレッドシートと「NEW」スプレッドシートの名前を比較するために使います。

宣言した変数に値をセットします。

「OLD」スプレッドシートと「NEW」スプレッドシートの最後の行を取得して、それぞれ行数を変数にセットします。

Worksheets(“OLD”).Cells(Rows.Count, 1).End(xlUp) は、Excel 特有の書き方で、「OLD」シートの最終行（End）からデータの入っている最後の行（xlUP）にカーソルを動かす動作をプログラムにした書き方です。

'「OLD」スプレッドシートの最終行を取得
OldLastRow = Worksheets("OLD").Cells(Rows.Count, 1).End(xlUp).row

'「NEW」スプレッドシートの最終行を取得
NewLastRow = Worksheets("NEW").Cells(Rows.Count, 1).End(xlUp).row

'「OLD」スプレッドシートの最終行を取得

OldLastRow = Worksheets("OLD").Cells(Rows.Count, 1).End(xlUp).row

'「NEW」スプレッドシートの最終行を取得

NewLastRow = Worksheets("NEW").Cells(Rows.Count, 1).End(xlUp).row

繰り返しのコードを書く

いよいよメインのプログラムになります。

'「OLD」スプレッドシートを基準にループ
For OldCurrentRow = 1 To OldLastRow

    '「OLD」スプレッドシートの現在行の値を取得
    s = Worksheets("OLD").Cells(OldCurrentRow, 1)

     '「NEW」スプレッドシートを１行ずつチェックする
    For NewCurrentRow = 1 To NewLastRow

        '「NEW」スプレッドシートの値と「OLD」スプレッドシートを比較
        If s = Worksheets("NEW").Cells(NewCurrentRow, 1) Then

            '「NEW」スプレッドシートの値と「OLD」スプレッドシートの値が同じなら
            '「OLD」スプレッドシートのＢ列に「＊」を入れる
            Worksheets("OLD").Cells(OldCurrentRow, 2).Value = "＊" 

            '「NEW」スプレッドシートの繰り返しから抜ける
            Exit For
        End If
    Next NewCurrentRow    '「NEW」スプレッドシートの繰り返しはここまで

Next OldCurrentRow

'「OLD」スプレッドシートを基準にループ

For OldCurrentRow = 1 To OldLastRow

'「OLD」スプレッドシートの現在行の値を取得

s = Worksheets("OLD").Cells(OldCurrentRow, 1)

'「NEW」スプレッドシートを１行ずつチェックする

For NewCurrentRow = 1 To NewLastRow

'「NEW」スプレッドシートの値と「OLD」スプレッドシートを比較

If s = Worksheets("NEW").Cells(NewCurrentRow, 1) Then

'「NEW」スプレッドシートの値と「OLD」スプレッドシートの値が同じなら

'「OLD」スプレッドシートのＢ列に「＊」を入れる

Worksheets("OLD").Cells(OldCurrentRow, 2).Value = "＊"

'「NEW」スプレッドシートの繰り返しから抜ける

Exit For

End If

Next NewCurrentRow '「NEW」スプレッドシートの繰り返しはここまで

Next OldCurrentRow

１．外側に「OLD」スプレッドシートを基準にした繰り返しのコードがあります
２．「OLD」スプレッドシートの一つ目の名前を変数「s」に保管します
３．その後「NEW」スプレッドシートの１行目から一つずつ「s」と比較して、同じ名前があったら隣のセルに「＊」を入力します
４．同じ名前があったので、「NEW」スプレッドシートは終わりにして、「OLD」スプレッドシートに戻ります
５．「OLD」スプレッドシートの最後のお客様になるまで、１から４を繰り返します