ExcelVBA（実践７）

前回の記事では演習問題5（ドット絵）、While文、プログラムの連携、演習問題6（複雑な数列の連携）、Sleep関数について学習しました
今回はまず演習問題7（アニメーション）に挑戦してもらい、その後でデバッグ、関数、セルの書式設定について学習しましょう
まずはExcelを開いて「開発」タブのリボン左端の「Visual Basic」ボタンをクリックし「Microsoft Visual Basic for Applications」画面を起動して、その画面の「挿入」タブの「標準モジュール」を選択してプログラミングの準備をしましょう

演習問題7（アニメーション）

これまで学習した知識を使って以下のプログラムを作ってみましょう
ルール1）以下の4つのプログラム（「マリオ1」～「マリオ4」）を準備する

マリオ1	マリオ2
マリオ3	マリオ4

ルール2）セルの行の高さと列の幅はそれぞれ以下の大きさに設定する
　行の高さ　Cells.RowHeight = 20 * 0.118
　列の幅　Cells.ColumnWidth = 20 * 0.75

ルール3）「マリオ（動画）」と文字が書かれたフォームコントロールボタンを用意し、ボタンに「アニメーション」というプログラム名を設定してクリックすると以下のアニメーションが表示されるようにする

上記のアニメーションの詳細は以下の通り
マリオ2Call　→　0.2秒Sleep　→　全消し　→　マリオ3Call　→　0.2秒Sleep　→　全消し　→
マリオ2Call　→　0.2秒Sleep　→　全消し　→　マリオ3Call　→　0.2秒Sleep　→　全消し　→
マリオ2Call　→　0.2秒Sleep　→　全消し　→　マリオ3Call　→　0.2秒Sleep　→　全消し　→
マリオ4Call　→　0.2秒Sleep　→　全消し　→
マリオ2Call　→　0.2秒Sleep　→　全消し　→　マリオ3Call　→　0.2秒Sleep　→　全消し　→
マリオ2Call　→　0.2秒Sleep　→　全消し　→　マリオ3Call　→　0.2秒Sleep　→　全消し　→
マリオ2Call　→　0.2秒Sleep　→　全消し　→　マリオ3Call　→　0.2秒Sleep　→　全消し　→
マリオ4Call　→　0.2秒Sleep　→　全消し　→
マリオ2Call　→　0.2秒Sleep　→　全消し　→　マリオ3Call　→　0.2秒Sleep　→　全消し　→
マリオ2Call　→　0.2秒Sleep　→　全消し　→　マリオ3Call　→　0.2秒Sleep　→　全消し　→
マリオ2Call　→　0.2秒Sleep　→　全消し　→　マリオ3Call　→　0.2秒Sleep　→　全消し　→
マリオ4Call　→　0.2秒Sleep　→　全消し　→
マリオ1をCall

演習問題7（アニメーション）ヒント: プログラム「マリオ1」～「マリオ3」の共通点

プログラム「マリオ1」～「マリオ3」の首の部分は全て共通（同じセルの位置と色）

演習問題7（アニメーション）解答

Declare PtrSafe Sub Sleep Lib _
"kernel32" (ByVal dwMilliseconds As Long)
Sub マリオ1()
    Range("F6:J6 , E7:M7").Interior.ColorIndex = 3 '帽子部分（ColorIndex = 3 赤色）
    Range("G13:G15 , J13:J14 , G15:J15 , F16 , H16:I16 , K16 , F17:L18 , E18:E19 , F19:G19 , J19:K19 , L18: L19 ").Interior.ColorIndex = 5 'ズボン部分（ColorIndex = 5 青色)
    Range("G16, J16").Interior.ColorIndex = 6 'ズボンのボタン部分（ColorIndex = 6 黄色）
    Range("E9:E10 , F11:I12 , J12:L12 , G9 , H8:I10 , J10 , K8:K9 , L9:M10 , N10").Interior.ColorIndex = 40 '顔の部分（ColorIndex = 40 肌色）
    Range("C16:D18 , E17 , L17 , M16:N18").Interior.ColorIndex = 40 '手の部分（ColorIndex = 40 肌色）
    Range("C21 , D20:F21 , K20:M21 , N21").Interior.ColorIndex = 53 'くつの部分（ColorIndex = 53 茶色）
    Range("C15 , D14:D15 , E13:E16 , F13:F15 , H13:I14 , K13 , K14:M15 , L16 , N15 ").Interior.ColorIndex = 3 '服の部分（ColorIndex = 3 赤色）
    Range("D9:D11 , E11 , E8:G8 , F9:F10 , G10 , J8:J9 , J11:M11 , K10 ").Interior.ColorIndex = 1 'かみとひげと目の部分（ColorIndex = 1 黒色）
End Sub
Sub マリオ2()
    Range("F6:J6 , E7:M7").Interior.ColorIndex = 3 '帽子部分（ColorIndex = 3 赤色）
    Range("G13:H15 , I14:I15 , J15:K15 , H16:K18 , I19:L19 , L17:L18 , G17:G18 , E16:F18 , E19 , D17:D19 , C18 ").Interior.ColorIndex = 5 'ズボン部分（ColorIndex = 5 青色)
    Range("G16").Interior.ColorIndex = 6 'ズボンのボタン部分（ColorIndex = 6 黄色）
    Range("E9:E10 , F11:I12 , J12:L12 , G9 , H8:I10 , J10 , K8:K9 , L9:M10 , N10").Interior.ColorIndex = 40 '顔の部分（ColorIndex = 40 肌色）
    Range("A14:B16 , C15 , M14:O14 , N15:O15").Interior.ColorIndex = 40 '手の部分（ColorIndex = 40 肌色）
    Range("B19:C20 , D20 , C21:E21 , M17:N19 , N16").Interior.ColorIndex = 53 'くつの部分（ColorIndex = 53 茶色）
    Range("C13:F14 , E15:F15 , I13:J13 , J14:L14 , L15:M15").Interior.ColorIndex = 3 '服の部分（ColorIndex = 3 赤色）
    Range("D9:D11 , E11 , E8:G8 , F9:F10 , G10 , J8:J9 , J11:M11 , K10 ").Interior.ColorIndex = 1 'かみとひげと目の部分（ColorIndex = 1 黒色）
End Sub
Sub マリオ3()
    Range("F6:J6 , E7:M7").Interior.ColorIndex = 3 '帽子部分（ColorIndex = 3 赤色）
    Range("I13 , E15 , E16:K17 , D17:D18 , E18:F18 , H18:J18").Interior.ColorIndex = 5 'ズボン部分（ColorIndex = 5 青色)
    Range("E9:E10 , F11:I12 , J12:L12 , G9 , H8:I10 , J10 , K8:K9 , L9:M10 , N10").Interior.ColorIndex = 40 '顔の部分（ColorIndex = 40 肌色）
    Range("D9:D11 , E11 , E8:G8 , F9:F10 , G10 , J8:J9 , J11:M11 , K10 ").Interior.ColorIndex = 1 'かみとひげと目の部分（ColorIndex = 1 黒色）
    Range("E9:E10 , F11:I12 , J12:L12 , G9 , H8:I10 , J10 , K8:K9 , L9:M10 , N10").Interior.ColorIndex = 40 '顔の部分（ColorIndex = 40 肌色）
    Range("D14:D15 , C15 , K14:L15 , L13 , M14").Interior.ColorIndex = 40 '手の部分（ColorIndex = 40 肌色）
    Range("C16:D16 , C17:C18 , B18:B19 , G19:I20 , J20").Interior.ColorIndex = 53 'くつの部分（ColorIndex = 53 茶色）
    Range("E13:H14 , F15:J15 , I14 , J13:J14").Interior.ColorIndex = 3 '服の部分（ColorIndex = 3 赤色）
End Sub
Sub マリオ4()
    Range("F3:J3 , E4:M4").Interior.ColorIndex = 3 '帽子部分（ColorIndex = 3 赤色）
    Range("G10 , K10 , H11 , L11 , H12:L12 , E13 , G13:I13 , K13 , M13 , E14:M15 , E16:J16 , D17:G17").Interior.ColorIndex = 5 'ズボン部分（ColorIndex = 5 青色)
    Range("J13 , L13").Interior.ColorIndex = 6 'ズボンのボタン部分（ColorIndex = 6 黄色）
    Range("E6:E7 , G6 , F8:I9 , H5:I7 , J7 , J9:L9 , K5:K6 , L6:M7 , N7").Interior.ColorIndex = 40 '顔の部分（ColorIndex = 40 肌色）
    Range("D6:D8 , E8 , E5:G5 , F6:F7 , G7 , J5:J6 , K7 , J8:M8").Interior.ColorIndex = 1 'かみとひげと目の部分（ColorIndex = 1 黒色）
    Range("A12:B13 , B14 , C13 , M2:O3 , N4:O4").Interior.ColorIndex = 40 '手の部分（ColorIndex = 40 肌色）
    Range("B19 , B17:C18 , C16:D16 , D15 , N13:O15 , O11:O12").Interior.ColorIndex = 53 'くつの部分（ColorIndex = 53 茶色）
    Range("B11 , C10:F12 , F13 , G11:G12 , H10:J10 , I11:K11 , L10 , M9 , N8 , M5:O5 , N6:O6 , O7").Interior.ColorIndex = 3 '服の部分（ColorIndex = 3 赤色）
End Sub
Sub アニメーション()
    Cells.ColumnWidth = 20 * 0.118
    Cells.RowHeight = 20 * 0.75
    Dim i As Integer
    Dim j As Integer
    For i = 1 To 3
        For j = 1 To 3
            Call マリオ2
            Sleep 200
            Cells.Clear
            Call マリオ3
            Sleep 200
            Cells.Clear
        Next j
        Call マリオ4
        Sleep 200
        Cells.Clear
    Next i
    Call マリオ1
End Sub

デバッグ

不具合があるプログラムやその原因のことをバグと言いますが、バグを修正して正しいプログラムに直す作業のことを「デバッグ」と言います
「Microsoft Visual Basic for Applications」にはデバッグを支援する便利な機能があり、うまく使えるようになると開発が効率的に進められるようになります

まずは以下のプログラムを書いて実行してみましょう
変数aに初期値100を代入し、その後以下の処理を10回繰り返します
変数bに変数aを2乗した値を代入し、変数bの値をA列に順番に入力した後、変数aの値を10増やします

実行すると以下のようにエラーメッセージが表示されA列にはA1～A9までしか値が入力されていません

エラーメッセージに書かれている「実行時エラー6　オーバーフローしました。」の意味についてインターネットで検索してみると、以下のように「割り当て先の制限を超える割り当てを試行したときにオーバーフローが発生します」とのことが書かれています
（詳しくはMicrosoftのVBAに関する「オーバーフローしました (エラー 6)」のページをご覧ください）
要するに変数に値を代入する際に数値の上限を超えてしまったようです

「実行時エラー6　オーバーフローしました。」の意味はなんとなく分かりましたが、具体的にどこの数値が制限を超えてしまったのか良く分かりません
そんな時はエラーメッセージの「デバッグ」というボタンをクリックするとエラーの原因となる個所を黄色で示してくれます

どうやら変数bの値が上限を超えたことがエラー原因のようです
変数の値を具体的に見るには以下のように「表示」タブから「ウォッチウィンドウ」という画面を起動し、対象の変数をドラッグ＆ドロップにてセットし、ブレイクポイントを仕掛けた状態で実行すると、途中経過の変数の値を確認できます
なお、プログラムの処理を途中で一時中断する場合は▶（実行ボタン）の右の||（中断ボタン）、終了するには■（リセットボタン）をクリックします

上記のウォッチウィンドウを見ると変数aが190の時、変数bがaの2乗（a * a = 36100）を代入する行でエラーになっているのが分かります
改めてインターネットでVBAの整数型（Integer）について調べると-32768～32767の範囲の数値が対象となっているため、36100は上限の32767を超えているのでエラーとなっていることが分かりました
（詳しくはMicrosoftのVBAに関する「整数データ型」のページをご覧ください）

エラーの原因はわかりましたがこのような上限を超える大きい数字を扱うにはどうしたらよいでしょう
以下のMicrosoftのVBAに関する「データ型の概要」のページを見るとLong型（-2147483648 〜 2147483647）やLongLong型（-9223372036854775808 ～ 9223372036854775807）の変数にすることでより大きな数値を扱えるとのことですので、先ほどのプログラムの変数bをLong型にして再度実行してみましょう

意外なことに変数bをLong型に変更したにも関わらず同じエラーが再び出てしまいました
原因となった b = a * a の右辺をマウスでドラッグすると「a * a = <オーバーフローしました。>」と表示され、左辺bをドラッグすると「b = 32400」と表示されています
変数aは190でInteger型の範囲内（-32768～32767）ですが、右辺のa * aでエラーになったようなので、変数bへ代入する前の a * a の計算結果もInteger型として扱われていることが分かります
そこで変数aもLong型にして再度 実行してみましょう

変数aと変数bのどちらもLong型とすることでバグが解消され、A10セルに36100が入力されました
このようにプログラムを実行してバグがあった場合は、エラーメッセージ内容をインターネットで調べたり、「ウォッチウィンドウ」やブレイクポイントを活用して変数の値の途中経過を確認したり、変数をマウスでドラッグして状態を確認したりすることでデバッグを行うことが出来ました
プログラミングを上達するために、このように自分でデバッグが出来るようになることはとても重要ですので慣れるまで難しいかも知れませんが、出来るだけすぐに人に聞いたり諦めたりせずに自分で解決しようと努力するようにしましょう

上記のプログラムと同じ内容を以下に記載します

Sub デバッグ()
    Dim i As Integer
    Dim a As Long: a = 100
    Dim b As Long
    For i = 1 To 10
        b = a * a
        Cells(i, 1).Value = b
        a = a + 10
    Next i
End Sub

関数

VBAのプログラムの中でSUM関数やAVERAGE関数などExcelの数式の関数を使うことが出来ます
（Excelの数式の関数が分からない人は以前のSUM関数やAVERAGE関数、MAX関数、MIN関数の記事を参照してください）
Excelの数式の関数を使う場合は以下の構文となります

Application.WorksheetFunction.関数名(引数)

少し長くて書くのが大変ですが、Excelの数式の関数をプログラムから呼び出すことで出来ることが増えるので非常に便利です
（詳しくはMicrosoftのVBAに関する「WorksheetFunction オブジェクト (Excel)」のページをご覧ください）
例えば以下のような成績表があったとします

空白のセルE2～I2にそれぞれ合計、平均、平均（切り下げ）、最高得点、最低得点の数式の関数（SUM、AVERAGE、ROUNDDOWN、MAX、MIN）を入力して以下のように成績表を完成させるプログラムを作成しましょう

なお、入力するセルの指定方法はRange（”列番号のアルファベット&行番号”）.Valueまたは
Cells（行番号 , 列番号）.Valueのどちらでも構いません
また、関数の引数は通常、セルの範囲を指定するのでRange(“列番号のアルファベット＆行番号”)を用いる方が適していますが、Cells（行番号 , 列番号）で1つずつセルを指定しても問題はありません
なお、RangeとCellsを併用することも可能ですが、プログラムが見にくくなるので推奨はしません

【Rangeでセルを指定する場合】

【Cellsでセルを指定する場合】

上記のプログラムと同じ内容を以下に記載します

【Rangeでセルを指定する場合】

Sub 関数()
    Range("E2").Value = Application.WorksheetFunction.Sum(Range("B2:D2")) '合計
    Range("F2").Value = Application.WorksheetFunction.Average(Range("B2:D2")) '平均
    Range("G2").Value = Application.WorksheetFunction.RoundDown(Range("F2"), 0)  '平均（切り下げ）
    Range("H2").Value = Application.WorksheetFunction.Max(Range("B2:D2")) '最高得点
    Range("I2").Value = Application.WorksheetFunction.Min(Range("B2:D2")) '最低得点
End Sub

【Cellsでセルを指定する場合】

Sub 関数()
    Cells(2, 5).Value = Application.WorksheetFunction.Sum(Cells(2, 2), Cells(2, 3), Cells(2, 4)) '合計
    Cells(2, 6).Value = Application.WorksheetFunction.Average(Cells(2, 2), Cells(2, 3), Cells(2, 4)) '平均
    Cells(2, 7).Value = Application.WorksheetFunction.RoundDown(Cells(2, 6), 0)   '平均（切り下げ）
    Cells(2, 8).Value = Application.WorksheetFunction.Max(Cells(2, 2), Cells(2, 3), Cells(2, 4)) '最高得点
    Cells(2, 9).Value = Application.WorksheetFunction.Min(Cells(2, 2), Cells(2, 3), Cells(2, 4)) '最低得点
End Sub

セルの書式設定

以前、Excelのセルの書式設定に関する記事の中で各セルには「書式」と呼ばれるデータの形式があることをご説明しました（セルの書式設定がよくわからない方は上記を参照してください）
今回はセルの書式設定を変更するプログラムを学習しましょう
セルの書式設定は何も変更していないと表示形式の分類が「標準」になっています

この「標準」は数字のゼロで始まる値を入力すると勝手にゼロが捨てられてしまう仕様になっています
しかし電話番号などゼロで始まる数字をセルに入力したい場合もあります
その場合は表示形式の分類を「文字列」にすることでゼロで始まる値を入力することが出来ます

なお、ゼロで始まる値を入力するとセルの左上に「エラーインジケーター」と言う緑のマークが表示されますが、これはExcelがセルの値について「内容が間違っている可能性がある」と判断して自動的に表示される警告マークなので、特に問題なければ気にせずそのままにしても良いですし、警告マークをクリックして「エラーを無視する」と選択するとマークを消すことも出来ます
（「エラーインジケーター」については以前のRANK関数の記事で説明してますので、詳細についてはそちらを参照してください）

セルの書式設定の表示形式の分類をプログラムから変更する場合は以下の構文となります

Cells.NumberFormatLocal = “表示形式の分類ごとの表記”

表示形式の分類ごとの表記は分類の種別ごとに異なっており以下の表のように記述します
ちなみにこのプログラムはすべてのセルを一括で変更します
個別のセルごとに設定したい場合はCellsの部分をCells(行番号 , 列番号)またはRange(“列番号のアルファベット&行番号”)と指定します
（あまり役立つ内容が書かれていませんが興味のある方はMicrosoftのVBAに関する「Range.NumberFormatLocal プロパティ (Excel)」のページもご覧ください）

分類	プログラム
標準	Cells.NumberFormatLocal = “G/標準”
数値	Cells.NumberFormatLocal = “0_ “
通貨	Cells.NumberFormatLocal = “#,##0;-#,##0”
合計	Cells.NumberFormatLocal = “_ * #,##0_ ;_ * -#,##0_ ;_ * “-“_ ;_ @_ “
日付	Cells.NumberFormatLocal = “yyyy/m/d”
時刻	Cells.NumberFormatLocal = “[$-F400]h:mm:ss AM/PM”
パーセンテージ	Cells.NumberFormatLocal = “0% “
分数	Cells.NumberFormatLocal = “# ?/? “
指数	Cells.NumberFormatLocal = “0.E+00 “
文字列	Cells.NumberFormatLocal = “@”
その他	Cells.NumberFormatLocal = “[<=999]000;[<=9999]000-00;000-0000”

では、実際にセルの書式設定の表示形式の分類を「文字列」に変更して携帯電話の番号を入力するプログラムを作成しましょう

上記は最初にセルの書式設定の表示形式の分類を「文字列」に変更し、A1のセルに「携帯電話番号」、A2のセルに「09012345678」という文字を入力しています
実行すると以下のように正しく入力出来ていることが確認出来ます

では、今度は以下のように携帯電話番号の値を整数型（LongLong型）の変数に代入して、その変数の値をA2のセルへ入力するとどうなるでしょう（11桁の整数を扱うためには範囲の広いLongLong型にする必要があります）

実行してA2のセルの値が「9.01E+09」と表示された場合はA列のセル幅を広げててください
なおE+09は10の9乗という意味で、詳しくは以前のランダムな数の記事で説明していますのでそちらを参照してください

セルの書式設定の表示形式の分類を「文字列」に変更したにも関わらずA2のセルの携帯電話番号の先頭のゼロが消されてしまっています
これは変数numberが整数型（LongLong型）のため、InputBox関数で値を入力した時点で先頭のゼロが消されてしまっていたのです（数学では整数の先頭がゼロで始まることがない為です）
A2のセルの先頭にゼロを付けるには「 0 」という文字をアンド記号（&）を使って変数numberと繋げることで表示できます

上記のプログラムと同じ内容を以下に記載します

Sub セルの書式設定()
    Cells.NumberFormatLocal = "@"
    Cells(1, 1).Value = "携帯電話番号"
    Dim number As LongLong
    number = InputBox("携帯電話番号を入力してください")
    Cells(2, 1).Value = 0 & number
End Sub

最後にもう少し難しい話をして終わりにしようと思います
携帯電話番号を以下のようにA2セルに通信事業者番号、B2セルに加入者番号（上4桁）、C2セルに加入者番号（下4桁）と分けて入力するプログラムを考えましょう

以下のプログラムの7行目以降は、まず整数型（LongLong型）の変数xへInputBox関数にて携帯電話番号（09012345678）を代入し、変数xを100000000で割って小数切り下げた値（90）と先頭のゼロを繋げた値をA2セルへ入力します
次に、変数xを100000000で割った余りに対してさらに10000で割って小数切り下げた値（1234）をB2セルへ入力します
最後に変数xを10000で割った余り（5678）をC2セルへ入力しています

言葉では伝わりにくいので計算式のイメージを以下に示します

上記のプログラムと同じ内容を以下に記載します

Sub セルの書式2()
    Cells.NumberFormatLocal = "@"
    Cells(1, 1).Value = "通信事業者番号"
    Cells(1, 2).Value = "加入者番号（上4桁）"
    Cells(1, 3).Value = "加入者番号（下4桁）"
    Dim x As LongLong
    x = InputBox("携帯電話番号を入力してください")
    Cells(2, 1).Value = 0 & Application.WorksheetFunction.RoundDown(x / 100000000, 0)
    Cells(2, 2).Value = Application.WorksheetFunction.RoundDown((x Mod 100000000) / 10000, 0)
    Cells(2, 3).Value = x Mod 10000
End Sub

さて、次回はExcelVBAの最後の演習問題8（hit＆blow）に挑戦してもらいます
これまで学習した知識をたくさん使う難しい内容になりますが、すぐに諦めて解答を見たりせず、自分で考えてみましょう

<<1つ前の記事　　1つ後の記事＞＞