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

質問

いつもお世話様です。
エクセル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

回答No.2

kobouzu_su · ベストアンサー

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

FORMAT関数をまだ自分のものにしておりませぬね。
と、ちょっときついことを言ってみる。(^^;;;

Format(y,"000")　は、数値表示書式指定文字を使ってますから
文字列１Ｅ２（１０＾２）も数字になります。

質問には英数混合３桁の例で、ａｂ９　とありますが
英数混合で３桁未満はどう表示するのでしょうか？

　ａｂ　→　０ＡＢ　　→　■ＡＢ　　→　ＡＢ
　ｂ　　→　００Ｂ　　→　■■Ｂ　　→　Ｂ
　１ａ　→　０１Ａ　　→　■１Ａ　　→　１Ａ

■はスペース

３桁未満の表示の仕方によっては、＃１の回答も
単純には使えないことはお分かりですね。

（おまけ）
いまのままで　１ａ　とか　１ｐ　を試したみてください。

何れにしろ分岐が必要になると思われます。
以上です。

お礼

これはこれは、kobouzu_su様、いつも有難うございます。
１ａ　とか　１ｐ　も化けるんですね！亜茶^2！！
これは指数じゃないですよね？な、なんなのでしょう？
先頭に0をつけるのはあくまで数字だけの場合ですので1Aや1Pはそのまま表示されればいいのです。

試行錯誤の結果、以下のように対応しましたがこれで正解でしょうか？

Sub test5()
Dim x As String, y As String, z As String
x = Application.InputBox("CODEを入力してねん。", Type:=2)
If Len(Trim(x)) > 3 Then
MsgBox "そんな長いTypeCode知りませぬ。", vbCritical, " (; ´・ω・`)σ" & x
Exit Sub
End If
y = StrConv(StrConv(x, vbUpperCase), vbNarrow)
z = IIf(IsNumeric(y), Right$("000" & y, 3), y)
MsgBox z & " Typeだよ。", , " （￣ー￣）v "
End Sub

回答No.6

kobouzu_su

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

＞Trim関数は文字列の先頭と末尾のスペースだけ削除するんですよね
＞なら真中の■に影響ないのでは？

（■はスペース）

２■５　→　０２５　

と変換したい場合は問題ありですよね、ということでした。
３桁未満の数字の頭には、０を付けるということでしたので。
そうでなければ問題なしです。

何れにしろ数値を判定させるのはなかなかのものですね。
以上です。

お礼

kobouzu_suさま、有難うございました。
ご迷惑でしょうが、これからも末永くご指導くださいませ。

回答No.5

KenKen_SP

> 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

お礼

KenKen_SPさま、ご丁寧に有難うございます。
「正規表現」というのはまったく馴染みがないのですが、これもいずれ勉強したいと思います。

今回も有難うございました。

回答No.4

kobouzu_su

またまた登場、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)
と、＋で結合して使うのがふつうです。

以上です。

お礼

kobouzuさま、いつも有難うございます。
素晴らしい知識のみなさまに教えていただき「質問のエキスパート」？は非常に助かっております。

IsNumericではFalseになるけど、Formatすると、2Aは午前二時、3Pは午後３時なんですね！初めて知りました。おどろきました。

> Trimはあまり感心しませんね。
> ２■５　とかが上手くいかないですよね。

すみません、これはちょっとわかりませんでした。
■は、スペースの意味ですか？
Trim関数は文字列の先頭と末尾のスペースだけ削除するんですよね？なら真中の■に影響ないのでは？それとも他の問題でしょうか？

MsgBox StrConv("ａｂｃ", vbUpperCase + vbNarrow)　と、＋で結合して使えるんですね！！これも初めて知りました。おどろき^2 です。

回答No.3

KenKen_SP

こんにちは。

どうせならもう一歩踏み込んでみては？

[つっこみ] 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

お礼

ご指摘有難うございました。いろいろ問題ありますねえ！
今回は、Typeの誤入力を防ぐ方法ではなく、入力した1E1等がが化けることが問題なので.12、+12、-12は特に差し支えありません。（一応ABSで対処はしましたが）

参考URLを見ましたが、Eだけじゃなく3D2のようなのも数値に化けるんですね！これは問題です。Eは指数表示というのは存じてましたが、Dは何なのですか？さきほどの1Pや1Aもわかりません。よろしかったらご教示ください。

また、参考URL ではIsNumericでTRUEとなると書かれた3D2や3E2が下記のコードではFALSEとなるようですが何故でしょうか？

Sub test6()
Dim x As String, y As String, z As String
x = Application.InputBox("CODEを入力してねん。", Type:=2) ')
If Len(Trim(x)) > 3 Then
MsgBox "そんな長いTypeCode知りませぬ。", vbCritical, " (; ´・ω・`)σ" & x
Exit Sub
End If
y = StrConv(StrConv(Trim(x), vbUpperCase), vbNarrow)
If IsNumeric(y) Then
z = Right$("000" & Abs(y), 3)
Else
z = y
End If
MsgBox z & " Typeだよ。", , " （￣ー￣）v "
End Sub

補足

すみません。勘違いでした。お礼で書いたABSは使えませんね。
また3D2や3E2もFALSEではなくTRUEでした。
ただ、1Pや1AはFALSEです。

Dは何なのですか？また1Pや1Aも何なのでしょうか？という質問です。
よろしくお願いいたします。

回答No.1

Hayashi_Trek

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

お礼

阿茶！
そうかあ、何もFormatする必要がなかったんだぁ！

・・・でも、目からうろこです。
有難うございました。