エクセルVBA/ Formatで文字列が数値に化ける？

Question

いつもお世話様です。
エクセルVBAでFormatを使うと、文字列中にeが一つ入っていると、「指数」とみなされて勝手に数値に化けてしまうようです。
話を簡単にするため、問題のコートを簡易化したコードが下記のtest1です。
入力されるのは常に３文字以内の英数です。

test1のコードは、ab9と入れればAB9、01とか20とか入れると、予定通り001や020を返してくれます。
ところが、なかには1E1や４E３なども入力する必要があり、これを入れると010や4000に化けてしまいます。
現在は、対処するため、下記test2のように、文字列中に"E"があるかどうかで処理を分岐させていますが、ほかに何か良い方法はないでしょうか？

Sub test1()
Dim x As String, y As String, z As String
x = Application.InputBox("CODEを入力してねん。", Type:=2)
y = StrConv(StrConv(x, vbUpperCase), vbNarrow)
z = Format(y, "000")
MsgBox z & " Typeだよ。"
End Sub

Sub test2()
Dim x As String, y As String, z As String
x = Application.InputBox("CODEを入力してねん。", Type:=2)
y = StrConv(StrConv(x, vbUpperCase), vbNarrow)
If InStr(y, "E") > 0 Then
z = y
Else
z = Format(y, "000")
End If
MsgBox z & " Typeだよ。"
End Sub

kobouzu_su · Accepted Answer

エキスパートさん、こんにちは。

FORMAT関数をまだ自分のものにしておりませぬね。
と、ちょっときついことを言ってみる。(^^;;;

Format(y,"000")　は、数値表示書式指定文字を使ってますから
文字列１Ｅ２（１０＾２）も数字になります。

質問には英数混合３桁の例で、ａｂ９　とありますが
英数混合で３桁未満はどう表示するのでしょうか？

　ａｂ　→　０ＡＢ　　→　■ＡＢ　　→　ＡＢ
　ｂ　　→　００Ｂ　　→　■■Ｂ　　→　Ｂ
　１ａ　→　０１Ａ　　→　■１Ａ　　→　１Ａ

■はスペース

３桁未満の表示の仕方によっては、＃１の回答も
単純には使えないことはお分かりですね。

（おまけ）
いまのままで　１ａ　とか　１ｐ　を試したみてください。

何れにしろ分岐が必要になると思われます。
以上です。

kobouzu_su · Answer

エキスパートさん、こんにちは。

＞Trim関数は文字列の先頭と末尾のスペースだけ削除するんですよね
＞なら真中の■に影響ないのでは？

（■はスペース）

２■５　→　０２５　

と変換したい場合は問題ありですよね、ということでした。
３桁未満の数字の頭には、０を付けるということでしたので。
そうでなければ問題なしです。

何れにしろ数値を判定させるのはなかなかのものですね。
以上です。

KenKen_SP · Answer

> Eは指数表示というのは存じてましたが、Dは何なのですか？

「D」も「E」もともに指数を表しています。両者の違いは、

　・E　　有効桁数が Single（単精度）の指数
　・D　　有効桁数が Double（倍精度）の指数

です。

> 1Pや1Aもわかりません。

これは IsNumeric の問題ではありません。Format の問題ですね。
こちらについては、kobouzu_su さんが詳しく回答なさってます。

以下は余談までに。

IsNumeric 関数は仕様として、指数表記の文字、小数点、桁区切り
のカンマなども数値として評価します。

つまり、、

IsNumeric 関数はあくまで「数値として評価できるか」を判定する
関数であって、判定対象が必ずしも数字で構成されていることを
保証しません。

これはこれで都合が良いことも多いのですが、例えばデータベース
絡みの処理を考えてみると、整数フィールドに値を代入する SQL 
を発行する場合に、指数表記の文字などが混入すると SQL でコケ
るか、間違った値が保存されてしまいます。
単純に IsNumeric でしか入力チェックしてないとスルーしちゃうん
ですね...

このような心配があるときは、#3 の参考 URL にある IsDigit の
ような自前関数を作ってチェックを行う必要があります。

IsNumeric にバグがある訳ではなく、あくまで仕様なのですが、
このことを理解していないと、思いもよらない結果を返すという話
になってしまいます。

　# 同じように IsDate 関数も英語表記の日付式で True を返します。

結局は、仕様を良く理解した上で利用する...「上手に使え」という
ことになってしまうのですが^^；

ご参考までに、正規表現を使った例です。

Sub test6()
　　Dim x As String
　　Dim y As Variant
　　
　　x = Application.InputBox("CODEを入力してねん。", Type:=2)
　　v = CheckCode(x)
　　If VarType(v) = vbBoolean Then
　　　　MsgBox "(; ´・ω・`)σ不正値みたい", vbCritical
　　Else
　　　　MsgBox CStr(v) & " Typeだよ。", vbInformation, " （￣ー￣）v "
　　End If
　　
End Sub

' // Code チェック関数
Private Function CheckCode( _
　　　　ByVal sCode As String _
) As Variant
　　
　　' // コードの長さ（文字数）
　　Const MAX_DIGIT As Long = 3&
　　
　　CheckCode = False
　　' // 引数 sCode が3文字より多ければ終了
　　If Len(sCode) > MAX_DIGIT Then Exit Function
　　
　　' // 前後の不要スペース除去・半角大文字に補正
　　sCode = Trim(sCode)
　　sCode = StrConv(sCode, vbUpperCase Or vbNarrow)
　　
　　' // 正規表現で入力チェック
　　Dim reg As RegExp
　　Set reg = CreateObject("VBScript.RegExp")
　　reg.Pattern = "^[0-9]+$"
　　If reg.test(sCode) Then
　　　　' // Return: 数字のみの場合-->桁数補正
　　　　CheckCode = Right$(String$(MAX_DIGIT, "0") & sCode, MAX_DIGIT)
　　Else
　　　　' // Return: 数字以外が含まれる場合
　　　　' //　　　　 規定の文字以外がないかチェック
　　　　reg.Pattern = "^[0-9A-Z]+$"
　　　　If reg.test(sCode) Then
　　　　　　CheckCode = sCode
　　　　End If
　　End If
　　Set reg = Nothing

End Function

kobouzu_su · Answer

またまた登場、kobouzuです。(^o^)/

KenKen_SPさんまで登場してくださるとは、エキスパートさんはなんと幸せものでしょう。
鬼に金棒とは、こういう状況を言ふのですよねぇ。
あと、一人、登場されると、金棒２本ですね。
ということで、当方への質問のみの返答ということで。
２ａ　と　３ｐ　とかと、Format(y,"000")の関係について。

以下をお試しください。

Sub test()
　Dim y
　y = "2a"　　'''　y = "5p"
　Range("A1").Value = Format(y, "0.0000000")
　Range("A2").Value = Format(y, "0.0000000")
　Range("A2").NumberFormat = "hh:mm:ss"
End Sub

どうでせしょう。お分かりになりましたか？

それから、
＞If Len(Trim(x)) > 3 Then
と
＞y = StrConv(StrConv(Trim(x), vbUpperCase), vbNarrow)

Trimはあまり感心しませんね。
２■５　とかが上手くいかないですよね。

それと細かいことですが、vbUpperCase,vbNarrowは次のように
y = StrConv(？？？？, vbUpperCase+vbNarrow)
と、＋で結合して使うのがふつうです。

以上です。

KenKen_SP · Answer

こんにちは。

どうせならもう一歩踏み込んでみては？

[つっこみ] IsNumeric だけでは不十分（意地悪じゃないですよ^^;）

　　例）.12、+12、-12、!!!

[ご提案] 規定の文字以外を弾くチェック関数を作ってみては？

　　・方法１： 正規表現を使ってみる
　　　　　　　 CreateObject("VBScript.RegExp")
　　　　　　　 Test メソッド
　　　　　　　 マッチングパターン： [0-9A-Z]{,3}
　　　　　　　 
　　・方法２： 一文字ずつ Mid で取り出して判定
　　　　　　　 Like 演算子を使ってみる、または文字リストを作っておき、
　　　　　　　 それと比較する
　　　　　　　 文字列長は3文字判定しておく

[参考URL:]
http://homepage1.nifty.com/rucio/main/technique/InputChk2.htm

Hayashi_Trek · Answer

素直に
z = Right$("000" & y, 3)
ではどうですか？

エクセルVBA/ Formatで文字列が数値に化ける？

エキスパートさん、こんにちは。

エキスパートさん、こんにちは。

> Eは指数表示というのは存じてましたが、Dは何なのですか？

またまた登場、kobouzuです。

こんにちは。

この回答への補足

素直に

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング