たまには、Excel マクロ・VBAのテクニカルな話を。
さいきんの受講生から無料相談で受けた質問に、こんなケースがあった。
—————————————————–
[1] ある表のA列に、1秒間隔で古い順に並べ替えられた時刻が並んでいる。
[2] このとき、ある条件に合致する時間範囲だけを取ってきて計算をしたい
ということで、その表のA列の値を上から順に調べ、目的の時刻のものを見つけたらそこから作業開始！
ということでこういう感じのマクロを作った。
Sub CheckTime()
Dim rw As Long
Dim myDt As Date
myDt = #11:00:00 AM#
rw = 0
Do
rw = rw + 1
Loop Until Range("A" & rw).Value = myDt
End Sub
ところが、思い通りに動かない。
質問の内容は、「検出したかった時刻は確かにA列にあるのに、このマクロを実行すると、何もひっかからないでExcelの最終行まで行ってしまい、そのままエラーになってしまうことがある。どうしてなのか。」というものだった。
「うまくいかない」ではなくて、「行かないことがある」というのがミソ。問題なく動くこともある。まったく同じに見える条件でも、うまくいかないこともある。
日付時刻では、この手の問題がときどき起こる。
で。
質問では再現手順は詳しく書かれていなかったのだが、たぶんAutoFillの問題だろうと思って返答したら。
やはりビンゴだった。
—————————————————–
この手の問題は、実は、AutoFillを使うと起こる。
これは、AutoFillで入る値は、厳密な日付時刻と微妙に異なることがあるためにおこる現象だ。
興味のある方は、以下を試してもらいたい。
[1]
セルA1に 1:00:00 と入力
セルA2に 1:00:01 と入力
セルA3に 1:00:02 と入力
[2]
上記の3つのセルに入った値を使って、セルA20までAutoFillで時刻を入力。
(1:00:20 まで入る）
[3]
セルB1からセルB20まで、手打ちで 1:00:00 ～ 1:00:20 を入力。
[4]
セルD1に、関数 =IF(A1=B1,TRUE,FALSE) を入力。
そのまま、セルD20までコピー。
すると、A列とB列では同じ値が入っているのでセルD1～D20にはTrueが並ぶはずなのに、TrueとFalseが混在した状態になってしまう。
上の写真では、14行目以降は全部Falseだ。
例えば、ここで、10行目について調べてみよう。
試みに、以下のマクロを実行してみる。
Sub Test()
Debug.Print CDbl(Range(“A10”).Value)
Debug.Print CDbl(Range(“B10”).Value)
End Sub
すると、イミディエイトウィンドウに表示されるのは、以下の2つの数字。
4.17708333333334E-02
4.17708333333333E-02
確かに、微妙に異なる。
どうしてこうなるのかということを文章だけでいきなり説明するのはちょっと大変なのだが、なんとか感覚的に説明してみると、「日付時刻の情報は実は内部的にはシリアル値（倍精度小数）で管理されているのだが、AutoFillを使って時刻を連続入力していくときには、小数点以下の非常に小さい桁のところで、本来入るべき日付時刻の値と微妙にズレていってしまう。その差はとても小さいのでセル上の時刻表記に影響が出るほどではないだ。だが、『If文で比較演算をするとFalseと言われる』というくらいには違う」というところだ。
……まあ、理屈はともかく、「AutoFillで時刻情報を入力すると微妙に誤差が出てしまうもの」と覚えておいてもらいたい。
Excelのバグと言えなくもないが、仕様とも言えなくもない。このくらいのところは、微妙なラインだ。
で。
バグかどうかなんていう話はともかく。
AutoFillで入力されたデータでは日付の比較演算ができないからお手上げだということではちょっと困るので、回避策のご紹介。
AutoFillで入ったこの手の微妙に細かい値の違いを気にしないように処理するには、以下の要領で、DateDiff関数を使うのが一案である。
Sub CheckTime()
Dim rw As Long
Dim myDt As Date
myDt = #11:00:00 AM#
rw = 0
Do
rw = rw + 1
Loop Until DateDiff("s", Range("A" & rw).Value, myDt) = 0
End Sub
