えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

トップ > ExcelVBAクイズ > VBAクイズ プログラムの誤りを指摘せよ！

VBAクイズ プログラムの誤りを指摘せよ！

ExcelVBAクイズ VBA

先日、Twitterにて #VBAクイズ を出題しました。このクイズの解答について解説していきます。

趣旨の説明

今回のクイズは、筆者こと @KotorinChunChun が見かけた「コンパイルエラーにも、実行時エラーにもならないが、意図したとおりに動かないよくある間違い」を元に作成しました。


実際のプログラミングでは、

  • タイプミス
  • ポカミス
  • 勘違い
  • コピー修正漏れ
  • 置換間違い
  • ネットからコピーしてきたプログラムが間違っていた

などなど、様々な要因で間違いが発生しますよね。


何かエラーが出れば原因の特定は容易になりますが、中には本題のようにコードを丁寧に目で追いかけないと発見できないものもあります。

こういったものは、ある程度の技能や経験で目と心を鍛えないと発見は困難です。

そういう意味では、教科書を読むだけでは身につかない、極めて実践的なVBAクイズが出題できました。


VBAクイズ

下図のプログラムには、作者が意図しないであろう誤りが多数含まれています。

・このプログラムを実行した結果、画面に表示される値

・誤っていると思われる箇所

をお答え下さい。

Twitter関係ない読者の方のために、Googleフォームで回答出来るようにしておきます。

https://forms.gle/xeHYc2iGpxbrfMb88

※頂いた回答は特に使う予定はありません。回答が増えてきたら集計を発表しても面白いかなと考えてはいますが。


このクイズで求められていること

このクイズで求められること・・・それは、プログラマの意図を読み取ることです。

小規模なプログラムなので、何もコメントを入れていません。

何をやりたいのかも、コードを読まないと分かりません。

出題ミスで若干（かなり？）わかりづらくなってしまいましたが、実際の現場でもプログラムで書かれていることには、何らかの意味・意図があります。

「なんで、こんな面倒なことしてるんだ？」

「なんで、危険な書き方してるんだ？」

という影には

「そうしたほうが都合が良かった」

「能力不足でこうなってしまった」

という意図があります。


問題文の一行一行全てに対して疑ってかかってみて下さい。


ここから先は回答になりますので、問題を解くまでは見ないでくださいね～
















VBAクイズの解答

修正すべき点は、次の6項目です。

  • 1.Error をErrと間違えている
  • 2.繰り返し文の開始値が不適切
  • 3.合計するデータ型が適切ではない
  • 4.コピペミス：MsgBoxの引数が間違っている
  • 5.出題ミス：無意味なStop
  • 6.エラー処理の本当の目的が達せられていない


これら全てについて、順番に解説していきます。


1.Error をErrと間違えている

色が違うので、慣れている人ならすぐに気がついたかもしれません。

Errと書かれていた部分は、正しくはErrorです。


そうすると、On オブジェクト GoTo ラベルというあまり見かけない記述になりますが、驚いたことにエラーにはならないのです。

筆者自身、今回の調査で初めて知りましたが、Onから始まるものはOn Error ステートメントの他に、On...GoSub、On...Gotoステートメント というものがあるそうです。

従って、On Err GoTo labelは後者のステートメントとして解釈されています。


なお、Errというのは直前のエラーを記憶するための Err オブジェクト というグローバルな変数に当たるため、Option Explicit宣言の有無に関わらず未定義エラーにはなりません。

さらに、ErrオブジェクトのデフォルトプロパティはErr.Numberのため、コンパイラには整数として解釈されます。

実際にはErr.Numberはエラーが出ていないときは0を示すので、On 0 GoTo labelが実行されたことになります。


では、On 0 GoTo labelは何が起こるのかというと・・・

0 に等しい On...GoSub または On...GoTo の次のステートメントに制御が移動します。

「何も実行されない」「エラートラップも始まらない」ということになります。


つまり、このようになります。

※上部コメントがイミディエイトウィンドウの出力結果

'0
'jump1
Sub Test_OnNumberGotoLabel0()
    On 0 GoTo label1
    Debug.Print "0"
label1:
    Debug.Print "jump1"
End Sub


ついでなので、他のパターンも試してみました。


この記法は、古き時代の条件付きジャンプに使えるようです。

'jump1
'jump2
Sub Test_OnNumberGotoLabel1()
    On 1 GoTo label1
    Debug.Print "0"
label1:
    Debug.Print "jump1"
label2:
    Debug.Print "jump2"
End Sub

'jump2
Sub Test_OnNumberGotoLabel2()
    On 2 GoTo label1, label2
    Debug.Print "0"
label1:
    Debug.Print "jump1"
label2:
    Debug.Print "jump2"
End Sub


ラベル指定が少なくて、存在しないインデックスへのジャンプは無視されるようです。

リスト内の項目数より大きい On...GoSub または On...GoTo の次のステートメントに制御が移動します。

'0
'jump1
'jump2
Sub Test_OnNumberGotoLabel3()
    On 3 GoTo label1, label2
    Debug.Print "0"
label1:
    Debug.Print "jump1"
label2:
    Debug.Print "jump2"
End Sub


負の値 エラーが発生します。

'True (マイナス1)の指定はエラーとなる
'プロシージャの呼び出し、または引数が不正です。
Sub Test_OnNumberGotoLabelTrue()
    On True GoTo label1
    Debug.Print "0"
label1:
    Debug.Print "jump1"
End Sub


今の時代、使うことはなさそうですが、こんな機能のせいでエラー検知できなくなっていたとは恐ろしいですね！


2.繰り返し文の開始値が不適切

次のような書き方は手抜きです。

For i = 1 To UBound(arr)

どのような状況でも、

For i = LBound(arr) To UBound(arr)

と書きましょう。


Excel VBAにおいてRange.Valueで取得した二次元配列を扱う事が多く、配列の要素番号が1に慣れるとやりがちの失敗です。

VBAが嫌われる原因の一つでもありますが、配列の開始インデックスが物事にとってバラバラです。

構造上、全てを統一することもできません。


せっかくなので、一度、何が0で何が1か、何が変動するのか整理しておきましょう。


常にゼロから始まる書き方

'0 to 2
arr = Split("abc,def,ghi",",")

'0 to 4
arr = VBA.Array(1,2,3,4,5)

'0 to ?
Sub Func(ParamArray arr())
End Sub

'ディクショナリのメンバ配列のインデックス
'0 to ?
Dim dic As New Dictionary
arr = dic.Keys


常にイチから始まる書き方

'Excel.Application のメンバ関数・プロパティ全てに該当
'1 to 5, 1 to 5
arr = Range("A1:E5")
'1 to 5
arr = Evaluate("{1,2,3,4,5}")
arr = [{1,2,3,4,5}]

'Excel.Application のワークシート関数全てに該当
'1 to ???[, 1 to ???]
arr = WorksheetFunction.Transpose(arr)

'配列じゃないけどコレクションのインデックス
'1～?
Dim obj As New Collection


Option Baseによって変動する書き方

モジュール先頭のOption Base 0|1によって、開始インデックス省略時の既定値を変化させることができます。（省略時は0が既定値）

'? to ?+4
arr = Array(1,2,3,4,5)

'開始値の省略時はUBoundが決定される。要素の個数ではない。
'? to 5
Dim arr(5)   
ReDim arr(5)


範囲を明示する書き方

dim arr(0 to 4)
dim arr(1 to 5)
dim arr(100 to 123)

面白いことにDim、Redimでは開始インデックスは、ゼロ・イチだけではありません。

しかしOption Baseではゼロ・イチしか選べません。

※ゼロイチ以外が必要になったことはないですが。


ちなみに、先日フォロワーの皆さんにアンケートを取ったところ、ご飯🍚を食べたい方が一位でした。


3.合計するデータ型が適切ではない

変数名sumから予想できるように、期待しているのは「数値の合計の算出」です。

しかし、このプログラムを実行すると、（配列先頭要素の2はループ外なので）"143"となります。

原因は文字列型の連結が起こっているからなのですが、いくつもの条件を満たしたときのみ起こる現象なので、解決方法がたくさんあります。

全部やっても良いし、一つしかやらなくてもOKです。


修正案1.入力値の型を明示する

Array()で作成した配列はVariant()型となりますが、配列の要素の型は個別に決定されます。

たとえば、今回は文字列型で登録していました。ですから

    arr = Array("2", "1", "4", "3")

を

    arr = Array(2, 1, 4, 3)

とすれば、数値型として格納されます。


修正案2.初期値を設定する

sumは変数の初期値を代入しないまま計算に使われていました。

従って

Dim sum

を

Dim sum
sum = 0

とすれば、解消されます。


なぜかと言うと、VBAにおいて

両辺が文字列型の場合のみ「+」演算子は「＆」演算子の代替として機能する

という仕様だからです。

1 + 1 = 2
"1" + 1 = 2
1 + "1" = 2
"1" + "1" = "11"
-"1" + "1" = 0
"1" + -"1" = 0
"1" - "1" = 0


初期値が指定されていないVariant型変数は、Emptyという特殊な状態になっています。

Empty + "1"を検証すると

?TypeName(Empty + "1")
String

のように、計算結果は文字列型になります。


だから、初めて以下の式を実行した時、sumにはarr(i)の型(=文字列型)が代入されます。

sum = sum + arr(i)


arr()の内容は

arr = Array("2", "1", "4", "3")

のように、全て文字列型です。

ループ2週目以降では、文字列 + 文字列という計算を行っているため、文字列の連結として扱われます。

従って、数値としての計算結果ではなく、文字列が連結された結果が出力されることになります。


これについては、togetter - VBAの「＋演算子」の挙動が怖すぎる で過去に出題しましたので、よろしければ御覧ください。

togetter.com


修正案3.出力変数の型を明示する

案2では初期値に0を代入するように言いましたが、そもそも変数の型を整数型にしておけば問題有りません。

出題プログラムのように変数の宣言で、As ～～を省略するとVariantとなります。なので

Dim sum' As Variantと同等

を

Dim sum As Long

とすれば、sumの初期値は0になりますし、sumに代入しようとした値は暗黙的な変換により「整数型」として格納されます。


※VBAでの整数型は余程の理由がない限りはLongを使いましょう。詳細は VBAで最速の整数型を調べてみた を御覧ください。

www.excel-chunchun.com


修正案4.代入時に要素の型をキャストする

sum側ではなく、arrの要素の方を強制的に数値に変換するという方法もあります。

sum = sum + arr(i)

を

sum = sum + CLng(arr(i))

とすれば良いです。


初回の計算の途中式をすべて可視化すると

sum = sum + CLng(arr(i))
sum = Empty + CLng("1")
sum = Empty + 1
sum = 1

となりますから、前述のVBAの仕様に則って、数値 + 数値 = 数値の合計という計算が行われるようになります。


4.コピペミス：MsgBoxの引数が間違っている

'↓これはOK
Debug.Print "Error : ", Err.Number, Err.Description

'↓これはおかしい
MsgBox "Error : ", Err.Number, Err.Description

コピペミスって言いましたが、出題のための意図的な間違いですよ。

実務でコードを書いていると、この手のミスを頻繁にやらかします。


そもそも、MsgBox関数の仕様は次のようになっています。

MsgBox (prompt, [ buttons, ] [ title, ] [ helpfile, context ])

引数の型は順番に、String、VbMsgBoxStyle（数値）、Stringです。

偶然にも「型は合っている」んです。だからコンパイルエラーも実行時エラーも出ません。

実務ではこんなことがよくあります。


本題のソースコードの入力データではエラーを再現できないので、ちょっと改変します。

例えば、入力データを数値化できない値aにしてテストするとこうなります。

明らかに、意図した結果になっていませんね。


せめてこうしましょう。

MsgBox "Error : " & Err.Number & " " & Err.Description


理想としてはこういう感じにしましょう。※お好みでどうぞ。

MsgBox "Error : " & Err.Number & " " & Err.Description, vbCritical, "[アプリ名] [関数名]"

Ctrl+Breakでデバッグモードに入れるので、関数名などは出力しなくても簡易デバッグには困らないと思います。


ついでに、Debug.Printのほうはカンマ区切り引数のように書いてあるのに、良い感じに文字列連結されていいる理由は、VBAがそういう仕様だからです。

以前、「イミディエイトウィンドウの真の使い方」なんて大仰なタイトルの記事を書いているので、こちらを御覧ください。

www.excel-chunchun.com


5.出題ミス：無意味なStopがある

当初のTwiterでのクイズでは、無意味なStopステートメントが記載されてました。

申し訳ございませんが、これは私の出題ミスです。


訂正するとしたら

Stop

は

Exit Sub

とするべきでした。


仮にStopを続行した場合、プロシージャから抜けずに続行するため、エラー処理ルーチン

label:
    Debug.Print "Error : ", Err.Number, Err.Description
    MsgBox "Error : ", Err.Number, Err.Description

を実行してしまいます。

正常な実行結果でもエラーが出てたら大問題ですね。


なお、「プロジェクトのロック」を行っている場合はStopステートメントが無視されるため、致命的なバグとなりえます。

これについては、以前書いた「VBAのStopとDebug.Assertステートメントの違い」を御覧ください。

www.excel-chunchun.com


6.エラー処理の本当の目的が達せられていない

出題時に、土壇場で欲を出してMsgBoxを追加したせいで、意図が読みづらくなってしまいましたが、On Error Goto labelでエラートラップを開始したのには意味がありました。

入力データの方も、ひとまず動くことを証明するために

arr = Array("2", "1", "4", "3")

としましたが、本当は

arr = Array("2", "1", "a", "3")

のような入力データを想定していました。


だからこそのエラートラップです。


当初、ExcelのSUM関数のように、エラー値をスルーする仕組みにして、エラーログを出力させるつもりでした。

今では影くらいしかありません。

後出しズルいって言われると思います（笑）


出題者として希望していた答えは

label:
    Debug.Print "Error : " & arr(i), Err.Number, Err.Description
End Sub

を

label:
    Debug.Print "Error : " & arr(i), Err.Number, Err.Description
    Resume Next
End Sub

とすることでした。


問題を取り除いた完成コード

以上を踏まえて問題点を取り除いた完成コードは以下のようになります。


※本当はエラー種別ごとに分岐してResume Nextするとか、SUMを関数化すべきだと言いたいところですが、シンプルなクイズにしようとしてこうなりました。許して下さい。

※SUM関数は文字列型の数字は無視するのでSUM関数とも仕様が違いました。本当にごめんなさい。


お礼

たくさんの回答、提案ありがとうございます。


以下の提案については、各自のコーディングルールの違いによるもので、本題への直接的な影響はないため省略しました。

  • Option ExplicitやOption Base ?によるのではないか
  • 配列arrの宣言 Dim arrは、Dim arr()とすべきではないか
  • For ～ Next文はNext iと書くべきではないか

これらも社内ルールがある場合は重要なチェック項目となるので、引き続きチェックしてくださいね。


まとめ

今回のクイズの出題は、ふと浮かんだことを適当に形にして出来上がったものです。

作成時間は10分くらいでした。

でも、この解説記事の作成には4時間以上かかりました。

代わりに今まで知らなかったOn 1 GoTo labelなどを知る良い機会でした。

かなり時間はかかりましたが、自分では満足しています。

以上

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)