知らずに落ちると抜け出せなくなるVBAの落とし穴です。
今回の罠は超単純です。
Val("1") ' ← 1が返ります Val("１") ' ← 0が返ります
という罠です。
皆さん気をつけましょう。
Val関数の仕様
Val関数は、文字列を数値にする関数ですが、
- 文字列の先頭から数値と判定できる部分までを変換
- できなければ0を返す
- 空白は無視する
という特徴があります。
主な変換例はこんな感じ↓
- 「123円」 ⇒ 123
- 「みかん123円」 ⇒ 0
- 「 1 2 3 円」 ⇒ 123（半角スペースは無視してくれる)
「123円」のように単位が文字として入っているものを、
「123」にしたいときに使えるなどと、詐欺まがいの紹介をされている関数です。
だからこそ今回の罠はやっかいで、
- 「１２３円」 ⇒ 0
という形で罠が発動します。
「円とか人とかの単位をなくして数値を取ってきたい！」
という需要の関数なのに、
「円とか人とかを入力する日本語入力は数字とは認めない」
という、自らの存在価値を揺るがす仕様です。
Val関数は使ってはいけない
この問題の解決策は、「Val関数を使わない」に限ります。
Val関数は正直言って使用禁止関数です。
理由１：そもそも仕様が罠だらけ
こんな感じで、全角の日本語だけ対応してもしょうがないくらい罠だらけです。
特に「桁区切りカンマがアウト」っていうのは、
別の罠の記事を書きたいくらいに罠です。
要するに、「単位を無くす用途には向かない関数」なのです。
悲しい話ですが、「１，０００円」がお金に見えないとか抜かしてる時点で、
お金の管理を任せることができる関数ではありません。
この手の「関数を作った人が決めた仕様を覚えなければいけない」系の関数は、
コードの書き手にも読み手にも、不要な学習コストを強いるので、
あまり使わないようにしましょう。
理由２：エラーを出さない
さらなるデメリットがこれです。
Val関数はいかなる場合でもエラーを出しません。
しれっと0を返しやがります。
「エラーなんて出ない方がいいじゃん！」
と思わないでください。
プログラムのバグというのは、大きく分けると↓の2種類に分かれます。
- たまにエラーで止まるが、エラーなく終了できたときは正しい結果になる。
- エラーは出ずに常に終了するが、たまに間違った結果になる。
どっちが嫌なバグですか？
考えるまでもないですね。２が嫌です。
１はエラーとその場で格闘すれば済みますが、２はもはや地雷です。
むしろ踏まなかった時の方が後々怖いことになります。
そしてVal関数というのは「何があってもエラーを返さない」という点で、
まさに２のバグを極めたような関数です。
例えば「ユーザーが入力した伝票100枚をVal関数で合計」したとしましょう。
途中1,000円が1になったり、１００円が0になったりしたとしても、
エラーにならないので計算結果が出てしまいます。
もし残り98枚が正しいと、気付けるような数字の違いになりません。
いつか誰かが気づくまで、間違った集計を出し続ける恐怖の関数です。
皆さんの周りにもいるんじゃないでしょうか。
悪い報告を持っていくとあからさまに機嫌悪く聞くので、
結果的に報連相が滞って、不必要に問題を大きくするダメ上司が。
あいつが好きそうな関数がVal関数です。
エラーというのは、いわばプログラムからのホウレンソウです。
「どんな些細なことでもすぐに報告してくれ」という広い心でプログラミングをしていきましょう。
代わりになる関数
単純に数字の文字列を数値にしたい
単に文字列を数値に変換する のであれば、
CLng(整数)やCDbl(小数)で変換できます。
というか、そもそもシート関数のVALUE関数と同じ動きをする、
数字⇒数値の関数は、こちらが正しい関数です。
なんかVal関数がイマイチな関数な割に知名度があるのは、
- WorksheetFunction.Value が無い！
- WorksheetFunction.Leftがないのと一緒で、同名の関数があるのかな？
- Valってのがあるのか。ふむふむ文字列⇒数字の関数。これだ！
という(私もしていた)勘違いが主な原因と推測します。
ワークシート「VALUE関数」 ⇔ VBA「CDbl関数」
が正しい関数の対応ですので覚えておきましょう。
ただ、VBAは変数代入時に「暗黙の型変換」をやってくれるので、
Dim Long型の変数 as Long Long型の変数 = "１２３４"
このように数値型の変数に、数値に変換したい文字列を入れてしまっても、
ちゃんと変換されて代入されます。
なので、CDblが正規の関数といいつつ、
結局はあまり使わないですね。
CDbl変換、変数代入どちらの方法も、数値に出来ない文字列ならエラーを返しますが、
エラーになるかどうかを先に判定するにはIsNumeric関数を使えばOKです。
Dim Long型の変数 as Long If IsNumeric(数値にしたい文字列) Then Long型の変数 = 数値にしたい文字列 End If
テキストから数値だけを取り出したい
Valが果たせなかった「1000円⇒1000」という役目を果たす関数は、
残念ながらありません。
よって自分で作るしかありません。
こちらのページで紹介していますのでよろしければどうぞ。
ただし、「1000円(税10%)」を100010にするか、1000にするかなど、
こちらも絶対安心な関数ではないです。
そもそもユーザーが好き勝手書いたテキストを絶対安心に数値に変換するなんて無理や。
長く使いそうなマクロで、「円」が入っているだけとわかっているデータなら、
CLng(Replace(数値にしたい文字列,"円",""))
のように、Replaceで円を消してからCLngで数値変換するなど、
目的をストレートに表現したテキスト加工コードを書いた方が、
意図しない文字列に遭遇した際に、エラーになってくれるので安心かもしれません。
