オーバーフローを回避？出さなくしたい

Question

いつもありがとうございます。
先日までは普通に動いていたんですが、他のBOOKからマクロを動かしたらオーバーフローとなってしまいました。
更に単体で動かしてもオーバーフローとのエラーが出てしまいます。

Sub SQL抽出()

Dim firstday As String
Dim lastday As String
Dim jan As Variant
Dim path As String
Dim wbpi As Workbook
Dim wb As Workbook
Dim re As String
Dim r As Long
Dim s As Long
Dim i As Long


Set wbpi = ThisWorkbook
firstday = wbpi.Worksheets("グラフ").Range("B2")
lastday = wbpi.Worksheets("グラフ").Range("D2")
jan = wbpi.Worksheets("グラフ").Range("B3")

最終行でjanに値を入れるとオーバーフローになります。
当初、String型にしていたのですが、それでオーバーフローになってしまったので
Variant型に変更したんですが、それでもオーバーフローになります。
変数Janの名の通りに、Janコードが入ります。
1つの場合もありますが、複数の場合には、4902705116542,4901730077583のようにカンマ区切りの値が入ります。
末に.valueをつけてもダメです。

Comments

[ttyp03]

変数宣言しないでやったらどうなりますか？（Option Explicit宣言してたらコメントアウトしてください）ステップ実行して変数janの型が何になるか見てみてください。

[otn]

エラーメッセージを書いてください。

[Naoko_Coco]

ttyp03さん
変数宣言しないというのはどういうことでしょうか？
型を指定しないということでよい？それでもオーバーフローしてしまうので
janに値が入らずTypeNameまで行ってくれないです。
janはEmptyのままです。

otnさん
エラーメッセージは
実行時エラー6でオーバーフローしました。です。

[ttyp03]

Dim janの行をコメントアウトするということです。
VBAは宣言しなくても自動で最適な型で動作する仕組みがあります。

[Naoko_Coco]

やりました。
どうやら、janが1つしかないときにオーバーフローするようです。
カンマがついているときにはStringで読み込んでます。

[Naoko_Coco]

ｃLngやｃStrで突っ込んでもオーバーフローになります。

[ttyp03]

こちらで試してみたけど、何をやってもエラーにならないんですよね。

[ttyp03]

少なくとも複数個入る可能性があるなら、Stringを使うのは正しいと思います。

[Naoko_Coco]

他のBookでも同じような処理やってるんですが、そっちはオーバーフローにもならず、ちゃんｔStringで入ってるんですよね。。。
なんで急にオーバーフローになってしまったのか。。。謎なんです。。。
なんか解決策ってありますでしょうか？

[Naoko_Coco]

原因がわかりました！
セルの書式が日付になってました。
これを標準にすることで問題なく値を受け取れました！
今までできてたってことはだれかいじったのかな？

[ttyp03]

あまりお役に立てなかったですけど、原因わかってよかったです。

[Naoko_Coco]

いえ、こちらこそいつもありがとうございます。
また、よろしくお願いします。

[PineMatsu]

Janコードが1つの時、セルのプロパティを文字列にすればどうでしょうか？「4902705116542」って4バイト符号付き整数を超えちゃうので、VBAではDouble型か文字列で持つしかないように思います。

[Naoko_Coco]

PineMatsuさん
janを読み込む前に書式を変更にしました。
wbpi.Worksheets("グラフ").Range("B3").NumberFormatLocal = "@"
これをいれたところ、思い通りに動いてくれました。
ありがとうございます。

Answer 1

解決したと思い込んでいるようですが、本質的には解決していません。

これを標準とすることでオーバーフローがなくなりました。

セルの書式を標準にするということは、入力された値が何かExcel君の判断にまかせるということです。
数値ぽっい値を入力すると、数値だろうと判断して、数値の書式を自動で適用するということです。
これは便利なんですが、Excel君が判断間違いすることもあります。
たまたま、うまくいっているだけで、場合によってはうまく判断してくれない可能性が残ります。

当初、String型にしていたのですが、それでオーバーフローになってしまったので
Variant型に変更したんですが、それでもオーバーフローになります。

まず、JANコードは数字の羅列ですが、数値ではありません。
数字（文字）の羅列ですので、String型にすべきです。
Variant型はどんな型でも代入出来て便利なんですが、これもどのような型になるのか曖昧なので想定通りの動きにならない危険性を含んでいます。

結論として、JANコードの場合、最善の方法は、
セルの書式設定は「文字列」にして、
変数は String型にすべきです。

ついでに言うと、

Dim firstday As String
Dim lastday As String

これも、変数名からいうと、日付データを格納するのだと思いますが、
だとしたら、String型でなく、Date型で宣言すべきです。
代入するセルの書式設定は、日付にします。

データ型を曖昧にしておくと、思わぬところで想定外の動作になる危険性があります。

Comments

[Naoko_Coco]

回答ありがとうございます。
なぜかjanを読み込む際に日付形式になってしまうので
読み込む前に文字列に書式設定を変更するコードを入れました。
型もStringにしてあります。
日付データも最終的に文字列変換するのでStringにしてます。
アドバイスありがとうございます。

Answer 2

原因がわかりました。
セルの書式設定が日付になってました。
これを標準とすることでオーバーフローがなくなりました。

お騒がせしました。

Comments

[PineMatsu]

やはり書式でしたか。良かったですね。

[Naoko_Coco]

janを読み込む前に書式を変更するコードを書いて、無事にできました。
ありがとうございます。