Excel VBA プログラミング　「自動型変換」に関する落とし穴

2005.9.8 作成 2018.6.25 微修正 Excel で VBA プログラミングするときに陥る「自動型変換」 に関係する落とし穴について書きます。これは恐ろしい罠と 言ってもよいでしょう。 まず、次の事実を知る必要があります。 1. セルに入るデータは「数値」「文字列」「日付型」などの 型を持っている 2. 数値 10 と文字列 "10" は区別される（等しくない） 3. 1. での型と「セルを右クリック」→「セルの書式設定」→「表示形式」 で設定する事項（標準 or 数値 or 日付 or 文字列 or ....） は「別物」である。 1. の型は次のようにして表示させることが出来ます。 ここではイミディエイトウィンドウに表示します。 Debug.Print TypeName(Range("A1").Value) Double, String, Date などの型が得られます。ここから先は Double 型と String 型の 2 つだけを対象として議論を 進めます。 3. はセルに物を代入するときに影響を及ぼします。場合に よっては自動的な型変換が発生します。 Range("A1").NumberFormatLocal = "@" ' 表示形式は文字列 Range("A1").Value = 2 ' セルには数値 2 が入る Range("A1").Value = "2" ' セルには文字列 "2" が入る Range("A1").NumberFormatLocal = "0_ " ' 表示形式は数値 Range("A1").Value = 2 ' セルには数値 2 が入る Range("A1").Value = "2" ' 文字列 "2" が変換されて数値 2 が入る Range("A1").NumberFormatLocal = "G/標準" ' 表示形式は標準 Range("A1").Value = 2 ' セルには数値 2 が入る Range("A1").Value = "2" ' 文字列 "2" が変換されて数値 2 が入る セルの表示形式が「文字列」の場合、型変換は起こりません。 「標準」「数値」の場合、その文字列が数字だけからなる場合、 自動的に数値に変換されます。 セルの表示形式を変更しても、中に入っている値の型は不変です。 Range("A1").NumberFormatLocal = "@" ' 表示形式は文字列 Range("A1").Value = "2" ' セルには文字列 "2" が入る Range("A1").NumberFormatLocal = "G/標準" ' 表示形式を標準に変更 Debug.Print TypeName(Range("A1").Value) ' セルの中は文字列のまま Range("A1").NumberFormatLocal = "0_ " ' 表示形式を数値に変更 Debug.Print TypeName(Range("A1").Value) ' セルの中は文字列のまま （注意！）ここでは数値型と文字列型だけを議論の対象と しています。日付型の表示形式を文字列型に変更すると、 TypeName は Date → Double と変わります。難解です。 Integer 型や String 型変数への代入文では CInt(), CStr() が 適用されます。 Dim I as Integer, S as String Range("A1").NumberFormatLocal = "@" ' 表示形式は文字列 Range("A1").Value = "2" ' 文字列 "2" が入る I = Range("A1").Value ' 数値 2 に変換される Range("A1").Value = 2 ' 数値 2 が入る S = Range("A1").Value ' 文字列 "2" に変換される If 文が曲者です。If の片方が定数の場合は、 セルの内容がその定数の型に変換されてから比較が行われます。 If Range("A1").Value = 2 Then ' セルの内容が数値に変換されてから比較 If Range("A1").Value = "2" Then ' セルの内容が文字型に変換されてから比較 しかし、 If Range("A1").Value = Range("A2").Value Then の場合は型変換は起こりません。ゆえに Range("A1").NumberFormatLocal = "0_ " ' 表示形式は数値 Range("A1").Value = "2" ' 自動型変換されて数値 2 が入る Range("A2").NumberFormatLocal = "@" ' 表示形式は文字列 Range("A2").Value = "2" ' 文字列 "2" が入る If Range("A1").Value = Range("A2").Value Then この If 文では数値 2 と文字列 "2" を比較するので「偽」 となります。今までの議論は「セル」を「バリアント型変数」に 言い換えても成立します。 このように、数値 2 と文字列 "2" は区別されます。セルに入っている 物の型は TypeName で調べないと分かりません。 If 文の片方が型が決まっている変数の場合を見てみます。 Dim a As Double, s As String a = 2 s = "2" If a = s Then Debug.Print "Yes" Else Debug.Print "No" End If 上のプログラムの場合、"Yes" となります。 Double 型と String 型を比較する場合、 String 型の内容が数値に変換可能な場合は CDbl で double 型に変換するようです。 s = "2" のかわりに s = "a" とすると If 文の実行時に 「型が一致しません」のエラーが出てプログラムはストップします。 結論として、If 文でセルの内容を比較するとき、セルの中の物の 型が不明であるときは、両方とも数値に変換するときは Val(str) 引数を数値に変換する 文字列に変換するときは CStr(num) あるいは Trim(Str(num)) ' Str は引数を文字列に変換するときに、 ' 符号を表す空白が入るので、Trim で切り落とす のような処理を入れた方が良いかも知れません。 ---------------- VLookup 関数は数値と文字列を区別します。ですから、 VLookup(2,A1:C10,2) VLookup("2",A1:C10,2) は別の結果となります。 ---------------------- このように、Excel では「セルに代入するとき」に 自動的な型変換が行われるのが落とし穴です。 「表示形式」は、自動的な型変換の作用に影響を及ぼします。 そして、「セルの中に入っている物の型」と「表示形式」 は別物です。 比較においては 10 <> "10" です。また、"10" <> "10 " です。 すなわち、文字列の比較においては空白の有無も考慮されます。 文字列の比較を行うときに空白を無視したいときは、 Trim 関数を適用してから行う必要があります。 If 文では「片方の型が決まっているとき」に自動的な型変換 が行われます。「String = Double」などのときも自動的な 型変換が行われます。 VLookup では自動的な型変換は行われません。 自動的な型変換は思わぬバグを引き起こす可能性があり、 とても怖い処理系に思えます。罠が潜んでいます。 自動的な型変換を避けるには、セルの表示形式は原則として 「文字列」に設定しておくのが良いと思われます。 ------------------- 余談ですが、Fortran では長さの異なる文字列を比較する場合、 短い方の文字列の末尾に空白を加えて同じ長さにしてから 比較するという仕様でした。