Excel VBA でHello, World! の次のステップにおすすめなのが、百ます計算です。
今回は、百ます計算のプログラムの作り方を通じて、少しずつ機能を追加したりデバッグしてプログラムの動作を確認したりする方法について解説します。
VBA が初めての人は、まず、下記記事を読んで初期設定を行いましょう
始めてのExcel VBA
1.なぜ百ます計算なのか
百ます計算のプログラムには、コンパクトな中プログラムの中に順接、分岐、反復という構造化プログラミング1の基本となる要素が含まれているため、プログラミングの基本的な考え方を学ぶのには最適です。
2.Excel シートをVBA で読み書きしてみる。
まず初めに問題を生成する分を作ります。
-
「開発」タブの「挿入」から「ボタン (フォームコントロール)」を選択し、シート上にドラッグします。
-
「マクロの登録」ダイアログが表示されるので、マクロ名(M):「ボタン作成_Click」として、「新規作成(N)」をクリックします。
-
作成したボタンの名前を「作成」に変更します。
デザインモードならそのまま編集するか、右クリックで「テキストの編集(X)」をクリックするとボタンのキャプションを編集できます。
-
セルに文字を出力してみる
VBA からシートのセルに値を出力してみます。
Sub ボタン作成_Click()
'現在のシート
Dim sheet As Worksheet
Set sheet = Application.ActiveSheet
'セルのRange
Dim range As range
Set range = sheet.Cells
'セルに値をセット
range(4, 2).Value = "Hello, world!"
End Sub
3.問題を作る
「作成」ボタンがクリックされたら、縦と横に10個ずつ数値を表示して問題を表示してみます。
3.1.そのまま作ってみる
このように単純並べてしまうと書くのが大変だし、修正するときも間違えやすくなってしまうのでNG です。
ダメな例
Sub ボタン作成_Click()
'現在のシート
Dim sheet As Worksheet
Set sheet = Application.ActiveSheet
'セルのRange
Dim range As range
Set range = sheet.Cells
'縦
range(4, 2).Value = 1
range(5, 2).Value = 2
range(6, 2).Value = 3
range(7, 2).Value = 4
range(8, 2).Value = 5
range(9, 2).Value = 6
range(10, 2).Value = 7
range(11, 2).Value = 8
range(12, 2).Value = 9
range(13, 2).Value = 10
'横
range(3, 3).Value = 10
range(3, 4).Value = 11
range(3, 5).Value = 12
range(3, 6).Value = 13
range(3, 7).Value = 14
range(3, 8).Value = 15
range(3, 9).Value = 16
range(3, 10).Value = 17
range(3, 11).Value = 18
range(3, 12).Value = 19
End Sub
3.2.ループ処理で繰り返し処理をまとめる
このような指定された回数だけ繰り返したいときはFor～Next を使います。
'For～Nextの基本構文
'ループ変数には、特にこだわりなければLong型の変数を指定します。
For [ループ変数] = [初期値] To [終了値]
'<この間に書かれた行が繰り返されます>
Next
また、シートのセルにアクセスするために使用するsheet.Cells の部分は後の処理でも同じ処理を使うので、別のメソッドに切り出して呼べるようにしておきます。
Sub ボタン作成_Click()
'セルのRange
Dim CellRange As Range
Set CellRange = GetActiveRange
Dim 行Index As Long
'縦
For 行Index = 0 To 9
CellRange(行Index + 4, 2).Value = 行Index + 1
Next
'横
Dim 列Index As Long
For 列Index = 0 To 9
CellRange(3, 列Index + 3).Value = 列Index + 10
Next
End Sub
Private Function GetActiveRange() As Range
'現在のシート
Dim sheet As Worksheet
Set sheet = Application.ActiveSheet
'セルのRange
Set GetActiveRange = sheet.Cells
End Function
3.3.マジックナンバーを定数にする
マジックナンバーというのは、プログラムの中に書かれている2や3、10などといった具体的な数値です。
プログラムの中に直接値を埋め込んでしまうと、複数の箇所に同じ値が存在した場合、値を変更するときにどちらも変更しなければならないのか、それとも全く違う役割でたまたま値が同じだけなのかが区別できなくなってしまい、メンテナンスするのが難しくなってしまいます。
そういうときは、値に名前を付けて定義しておき、その名前を使うことでプログラムを分かりやすくすることができます。
定数の宣言
'Private：この定数が使える範囲はこのファイル内だけ(Friendや、Publicは必要になったら使いましょう)
'Const：定数宣言のキーワード
Private Const 定数名 As [型名] = [値]
Option Explicit
Private Const 行数 As Long = 10
Private Const 列数 As Long = 10
Private Const 問題行 As Long = 3
Private Const 問題列 As Long = 2
Sub ボタン作成_Click()
'セルのRange
Dim CellRange As Range
Set CellRange = GetActiveRange
Dim 行Index As Long
'縦
For 行Index = 0 To 行数 - 1
CellRange(問題行 + 行Index + 1, 問題列).Value = 行Index + 1
Next
'横
Dim 列Index As Long
For 列Index = 0 To 列数 - 1
CellRange(問題行, 問題列 + 列Index + 1).Value = 列Index + 10
Next
End Sub
Private Function GetActiveRange() As Range
'現在のシート
Dim sheet As Worksheet
Set sheet = Application.ActiveSheet
'セルのRange
Set GetActiveRange = sheet.Cells
End Function
4.毎回違う問題を作る
次は、ボタンをクリックするたびに、毎回違う問題になるようにしてみます。
ランダムな値(乱数)を発生させるには、Rnd() を使用します。
Rnd() 呼ばれるたびに0～1未満の値を返すのでこの値を使うことで毎回違う値をセットすることができます。
Rnd() を使うときは、初めにRandomize() で、Rnd()がシード値を設定してから使用するようにしましょう。
もし、Randomize() を呼ばずにRnd() を呼ぶと、毎回同じ順番で乱数が返ってきます。
Sub ボタン作成_Click()
'Rndで使用する乱数表のシード値を変更して毎回違う乱数が生成されるようにします。
Call Randomize
'セルのRange
Dim CellRange As Range
Set CellRange = GetActiveRange
Dim 行Index As Long
'縦
For 行Index = 0 To 行数 - 1
CellRange(問題行 + 行Index + 1, 問題列).value = Get乱数
Next
'横
Dim 列Index As Long
For 列Index = 0 To 列数 - 1
CellRange(問題行, 問題列 + 列Index + 1).value = Get乱数 + 10
Next
End Sub
Private Function GetActiveRange() As Range
'現在のシート
Dim sheet As Worksheet
Set sheet = Application.ActiveSheet
'セルのRange
Set GetActiveRange = sheet.Cells
End Function
Private Function Get乱数() As Long
Dim value As Single
'Rnd関数で0～1未満の乱数を生成し、それを10倍して整数部を取り出してランダムな0～9の値を生成します
value = Rnd() * 10
Get乱数 = CLng(value)
End Function
5.ステップ実行でプログラムの動作を1行ずつ確かめてみる
ブレークポイントを設置して、プログラムの動作を1行ずつ確認してみましょう。
5.1.プログラムを1ステップずつ実行する
F8でステップイン、Shift+F8でステップオーバーです。
ステップインは、現在のステップで他のメソッドを呼んでいる場合、その中のステップに移動します。
ステップオーバーは、中の処理では止まらず現在のメソッドの次のステップに移動します。
5.2.変数の中身を表示する
ウォッチウィンドウで変数の値を確認することができます。
変数を右クリックして、「ウォッチ式の追加(A)...」をクリックします。
ウォッチ式の追加ダイアログで、式に確認したい変数や条件が指定されていることを確認し「OK」をクリックします。
(ウォッチの種類を変更すると、変数の値を監視して自動的に処理をそこで止めてくれるように設定することもできます。)
その状態で次のステップへ処理を進めていくと、ウォッチウィンドウで変数の現在値を確認することができます。
6.採点機能を付ける
シートに「採点」ボタンを追加して、入力された値(回答)と計算した値(正解)を比べ、一致した数を数えて正解数を数えます。
デバッグ方法の一つとして、ここでは、Debug.Print 文で途中の状態を出力しています。
このようにすることで、途中の処理の状態をイミディエイトウィンドウに出力することができます。
Sub ボタン採点_Click()
Dim CellRange As Range
Set CellRange = GetActiveRange
Dim 行Index As Long
Dim 列Index As Long
Dim 得点 As Long
'縦
For 行Index = 0 To 行数 - 1
'横
For 列Index = 0 To 列数 - 1
Dim 正解 As Long
正解 = CellRange(問題行 + 行Index + 1, 問題列).value + CellRange(問題行, 問題列 + 列Index + 1).value
Dim 解答 As Long
解答 = CellRange(問題行 + 行Index + 1, 問題列 + 列Index + 1).value
Debug.Print 行Index & ":" & 列Index & vbTab & " 解答=" & 解答 & " 正解=" & 正解
If 解答 = 正解 Then
得点 = 得点 + 1
End If
Next
Next
Debug.Print "得点:" & 得点 & "点"
Call MsgBox("得点は、" & 得点 & "点でした。", vbInformation Or vbOKOnly, "百ます計算")
End Sub
実際に試してみると分かりますが、確認するために毎回100個答えを入れるのは中々面倒です。
なので、簡単に確認できるよう自動解答ボタンを作ってしまいましょう(笑)
採点処理との違いは、正解を解答と比較するか、解答欄にセットするかの違いだけです。
自動解答ボタンの処理
Sub ボタン自動解答_Click()
Dim CellRange As Range
Set CellRange = GetActiveRange
Dim 行Index As Long
Dim 列Index As Long
Dim 得点 As Long
'縦
For 行Index = 0 To 行数 - 1
'横
For 列Index = 0 To 列数 - 1
Dim 正解 As Long
正解 = CellRange(問題行 + 行Index + 1, 問題列).value + CellRange(問題行, 問題列 + 列Index + 1).value
Dim 解答 As Long
CellRange(問題行 + 行Index + 1, 問題列 + 列Index + 1).value = 正解
Next
Next
End Sub
7.間違った回答の色を変える
間違った回答があったらそこを赤字に変えて間違えたところが分かるようにしてあげましょう。
セルの文字色を変えるには、対象セルのFont.Color プロパティに色をセットします。
Sub ボタン採点_Click()
Dim CellRange As Range
Set CellRange = GetActiveRange
Dim 行Index As Long
Dim 列Index As Long
Dim 得点 As Long
'縦
For 行Index = 0 To 行数 - 1
'横
For 列Index = 0 To 列数 - 1
Dim 正解 As Long
正解 = CellRange(問題行 + 行Index + 1, 問題列).value + CellRange(問題行, 問題列 + 列Index + 1).value
Dim 解答Cell As Range
Dim 解答 As Long
Set 解答Cell = CellRange(問題行 + 行Index + 1, 問題列 + 列Index + 1)
解答 = 解答Cell.value
Debug.Print 行Index & ":" & 列Index & vbTab & " 解答=" & 解答 & " 正解=" & 正解
If 解答 = 正解 Then
得点 = 得点 + 1
解答Cell.Font.Color = vbBlack
Else
解答Cell.Font.Color = vbRed
End If
Next
Next
Debug.Print "得点:" & 得点 & "点"
Call MsgBox("得点は、" & 得点 & "点でした。", vbInformation Or vbOKOnly, "百ます計算")
End Sub
8.クリア機能を付ける
次の問題を生成するとき、回答をクリアします。
クリアする処理は他からも呼べるようにしておき、クリアボタンを押さずに「作成」ボタンが押されても大丈夫なように、作成のときも呼ぶようにします。
Sub ボタン作成_Click()
'Rndで使用する乱数表のシード値を変更して毎回違う乱数が生成されるようにします。
Call Randomize
'解答エリアをクリアします。
Call クリア
'セルのRange
Dim CellRange As Range
Set CellRange = GetActiveRange
Dim 行Index As Long
'縦
For 行Index = 0 To 行数 - 1
CellRange(問題行 + 行Index + 1, 問題列).value = Get乱数
Next
'横
Dim 列Index As Long
For 列Index = 0 To 列数 - 1
CellRange(問題行, 問題列 + 列Index + 1).value = Get乱数 + 10
Next
End Sub
Sub ボタンクリア_Click()
Call クリア
End Sub
Private Sub クリア()
Dim CellRange As Range
Set CellRange = GetActiveRange(問題行, 問題列).CurrentRegion.Offset(1, 1)
Call CellRange.Clear
Call CellRange.ClearFormats
End Sub
9.点数によってメッセージを変える
採点するとき、得点に応じたメッセージを表示してみます。
こういうときに便利なのがSelect Case です。
Select Case を使うと1つの変数の値に足して比較するよう処理をスッキリと書くことができます。
Case で指定されている条件に合致すると、その中の処理が行われた後End Select へ飛ぶので、後から条件を追加したりするときも、その部分だけを修正だけで済みます。
どの条件に合致しない場合は、Case Else にある処理が実行されます。
もし、Case Elseに入ることがないなら、Case Elseのところにエラーメッセージを表示する処理を入れておくとミスをしたときにすぐに気付くことができるので、Case Else は必ず入れるようにしましょう。
Sub ボタン採点_Click()
Dim CellRange As Range
Set CellRange = GetActiveRange
Dim 行Index As Long
Dim 列Index As Long
Dim 得点 As Long
'縦
For 行Index = 0 To 行数 - 1
'横
For 列Index = 0 To 列数 - 1
Dim 正解 As Long
正解 = CellRange(問題行 + 行Index + 1, 問題列).value + CellRange(問題行, 問題列 + 列Index + 1).value
Dim 解答Cell As Range
Dim 解答 As Long
Set 解答Cell = CellRange(問題行 + 行Index + 1, 問題列 + 列Index + 1)
解答 = 解答Cell.value
Debug.Print 行Index & ":" & 列Index & vbTab & " 解答=" & 解答 & " 正解=" & 正解
If 解答 = 正解 Then
得点 = 得点 + 1
解答Cell.Font.Color = vbBlack
Else
解答Cell.Font.Color = vbRed
End If
Next
Next
Debug.Print "得点:" & 得点 & "点"
'得点に応じたメッセージをセットします。
Dim メッセージ As String
Select Case 得点
Case 100 '得点が100の場合
メッセージ = "パーフェクト！！"
Case Is >= 95 '得点が95以上の場合
メッセージ = "スゴイ！！"
Case Is >= 80
メッセージ = "よくできました。"
Case Is >= 60
メッセージ = "あと一歩です。"
Case Is >= 30
メッセージ = "頑張りましょう。"
Case Else
メッセージ = "もう一度やってみましょう。"
End Select
Call MsgBox("得点は、" & 得点 & "点でした。" & vbCrLf & メッセージ, vbInformation Or vbOKOnly, "百ます計算")
End Sub
10.全体ソース
出来上がったソースを載せておきます。
全体ソース
Option Explicit
Private Const 行数 As Long = 10
Private Const 列数 As Long = 10
Private Const 問題行 As Long = 3
Private Const 問題列 As Long = 2
Sub ボタン作成_Click()
'Rndで使用する乱数表のシード値を変更して毎回違う乱数が生成されるようにします。
Call Randomize
'解答エリアをクリアします。
Call クリア
'セルのRange
Dim CellRange As Range
Set CellRange = GetActiveRange
Dim 行Index As Long
'縦
For 行Index = 0 To 行数 - 1
CellRange(問題行 + 行Index + 1, 問題列).value = Get乱数
Next
'横
Dim 列Index As Long
For 列Index = 0 To 列数 - 1
CellRange(問題行, 問題列 + 列Index + 1).value = Get乱数 + 10
Next
End Sub
Sub ボタン採点_Click()
Dim CellRange As Range
Set CellRange = GetActiveRange
Dim 行Index As Long
Dim 列Index As Long
Dim 得点 As Long
'縦
For 行Index = 0 To 行数 - 1
'横
For 列Index = 0 To 列数 - 1
Dim 正解 As Long
正解 = CellRange(問題行 + 行Index + 1, 問題列).value + CellRange(問題行, 問題列 + 列Index + 1).value
Dim 解答Cell As Range
Dim 解答 As Long
Set 解答Cell = CellRange(問題行 + 行Index + 1, 問題列 + 列Index + 1)
解答 = 解答Cell.value
Debug.Print 行Index & ":" & 列Index & vbTab & " 解答=" & 解答 & " 正解=" & 正解
If 解答 = 正解 Then
得点 = 得点 + 1
解答Cell.Font.Color = vbBlack
Else
解答Cell.Font.Color = vbRed
End If
Next
Next
Debug.Print "得点:" & 得点 & "点"
'得点に応じたメッセージをセットします。
Dim メッセージ As String
Select Case 得点
Case 100 '得点が100の場合
メッセージ = "パーフェクト！！"
Case Is >= 95 '得点が95以上の場合
メッセージ = "スゴイ！！"
Case Is >= 80
メッセージ = "よくできました。"
Case Is >= 60
メッセージ = "あと一歩です。"
Case Is >= 30
メッセージ = "頑張りましょう。"
Case Else
メッセージ = "もう一度やってみましょう。"
End Select
Call MsgBox("得点は、" & 得点 & "点でした。" & vbCrLf & メッセージ, vbInformation Or vbOKOnly, "百ます計算")
End Sub
Sub ボタン自動解答_Click()
Dim CellRange As Range
Set CellRange = GetActiveRange
Dim 行Index As Long
Dim 列Index As Long
Dim 得点 As Long
'縦
For 行Index = 0 To 行数 - 1
'横
For 列Index = 0 To 列数 - 1
Dim 正解 As Long
正解 = CellRange(問題行 + 行Index + 1, 問題列).value + CellRange(問題行, 問題列 + 列Index + 1).value
Dim 解答 As Long
CellRange(問題行 + 行Index + 1, 問題列 + 列Index + 1).value = 正解
Next
Next
End Sub
Sub ボタンクリア_Click()
Call クリア
End Sub
Private Function GetActiveRange() As Range
'現在のシート
Dim sheet As Worksheet
Set sheet = Application.ActiveSheet
'セルのRange
Set GetActiveRange = sheet.Cells
End Function
Private Function Get乱数() As Long
Dim value As Single
'Rnd関数で0～1未満の乱数を生成し、それを10倍して整数部を取り出してランダムな0～9の値を生成します
value = Rnd() * 10
Get乱数 = CLng(value)
End Function
Private Sub クリア()
Dim CellRange As Range
Set CellRange = GetActiveRange(問題行, 問題列).CurrentRegion.Offset(1, 1)
Call CellRange.Clear
Call CellRange.ClearFormats
End Sub
11.まとめ
百ます計算はシンプルなので、基本的な使い方を練習するのにピッタリです。
自分で仕様からどんな処理をすればいいかを考えて実装する。
これを繰り返していけば、いろいろなものが作れるようになります。
今回紹介していない部分としては、値に数値以外が入力された場合、エラーが発生します。
想定外の状態になった場合の処理を例外処理と言いますが、作った本人以外が使う実際のプログラムでは例外処理は必須の仕組みです。
VBA の場合、On Error Goto ～ という仕組みで、処理中にエラーが発生したときに、予め用意された処理へジャンプさせることができます。
On Error Goto を使った処理の書き方は下記記事で解説しているので、応用編としてエラーが発生した時の処理を実装してみましょう。
始めてのExcel VBA
どんなときにどんなエラーが発生しうるのか、そのとき、どのような振る舞いをするのが適切なのかを考える癖をつけると、プログラムの完成度がぐっと上がります。
Hello,World! から本格的なプログラムへ進むその最初の一歩として、百ます計算はいかがでしょうか。
12.補足
12.1.代入するときのSetって何？
値を変数に代入するとき、Set が付く場合と付かない場合があります。
大まかに説明すると、Set は、変数の中に入っている値そのものではなく、変数の場所(参照)をセットすると意味になります。
Set で代入された変数を操作するということは、その元にある変数を操作するのと同じ意味になります。
'現在のシート
Dim sheet As Worksheet
Set sheet = Application.ActiveSheet
'セルのRange
Dim CellRange As Range
Set CellRange = sheet.Cells
CellRange(1, 1).Value = "Hello, World!"
'CellRange は、SheetのCellsを示しているので
'CellRange(1, 1).Value = "Hello, World!"
'は
'sheet.Cells(1, 1).Value = "Hello, World!"
'と同じ意味
12.2.文字の連結の仕方
& や + を使うと複数の文字列を1つの文字列に連結することができます。
+ を使う場合、数値として変換できるかによって動きが変わるため、特別な理由が無ければ文字の時は& 、加算するときは+と使い分けましょう。
Debug.Print "A" + "B" '→AB
Debug.Print "1" + "2" '→3
Debug.Print "A" & "B" '→AB
Debug.Print "1" & "2" '→12
12.3.色の使い方
今回は、vbRedや、vbBlackといったデフォルトで存在している定数を指定しましたが、RGB関数で光の三原色の割合を指定すると1677万色の中から好きな色を指定できます。
'RGB関数を使用して赤色を指定した例(&Hはその数値が16進数であることを示す)
'R:赤(Red), G:緑(Green), B:青(Blue)の割合を0(&H0)～255(&HFF)で指定する。
解答Cell.Font.Color = RGB(&HFF, &H0, &H0)