エクセル VBA 自動処理の途中終了について

質問

エクセルで単語のフラッシュカードを自動表示させたいと考えています。

エクセルの１セルの大きさを縦最大、横120位に広げ、C列に単語、D列に意味を縦に並べ、
C２　１秒後　D２　１秒後　C２　１秒後　D２　１秒後　
C３　１秒後　D４　のように単語と意味を交互に２度ずつ表示させます。

For Next を使い表示はできるようになりましたが、途中で止めたいときに、escを押すと
For Nextの処理を最後まで一気に行ってから止まってしまいます。

C５を表示していたら、その場所でPause をし、スタートボタンで再度継続して表示したいと思います。また、単語や意味のセルにはそれぞれ別の文字装飾をしてあるので、（赤や青、大きさなど）
セルを移動して表示したいと考えています。

実は他のサイトでも質問しましたが、思ったような回答を得られませんでした。よろしくお願いします。

Sub セル移動()

Dim waitTime As Variant

i = 0
Range("c2").Select
waitTime = Now + TimeValue("0:00:01")
Application.Wait waitTime

Range("c3").Select
waitTime = Now + TimeValue("0:00:01")
Application.Wait waitTime

For i = 1 To 50
ActiveCell.Select

Selection.Offset(0, 1).Select
waitTime = Now + TimeValue("0:00:01")
Application.Wait waitTime

Selection.Offset(0, -1).Select
waitTime = Now + TimeValue("0:00:01")
Application.Wait waitTime

Selection.Offset(0, 1).Select
waitTime = Now + TimeValue("0:00:01")
Application.Wait waitTime

Selection.Offset(1, -1).Select
waitTime = Now + TimeValue("0:00:01")
Application.Wait waitTime

Application.OnKey ("{esc}"), "shuryo"

i = i + 1

Next i

End Sub

Sub shuryo()
Application.ScreenUpdating = False

Range("c2").Select
Application.GoTo reference:=ActiveCell, scroll:=True
Application.ScreenUpdating = True
Exit Sub
End Sub

回答No.3

mitarashi · ベストアンサー

#2です。
コードの簡略化を図った際に次の行に移る箇所を間違っておりました。
Do～Loopの間は、下記の通り訂正願います。
また、参考URLを添付し忘れたので、添付いたします。
Do
For i = 1 To 2
currentRange.Select
Sleep 1000
currentRange.Offset(0, 1).Select
Sleep 1000
DoEvents
If stopFlag Then
Sheets(2).Range("A1").Value = currentRange.Address
Exit For
End If
Next i
Set currentRange = currentRange.Offset(1, 0)
Loop Until stopFlag

参考URL：

http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_090_050.html

お礼

mitarashi様

回答ありがとうございます。

currentRange.Addressをとりたいとは思っていたのですが、やり方がわかりませんでした。
２度ずつ繰り返すところまで再現していただき、ありがとうございます。
sleepやstopFlagなどもう少し勉強したいと思います。
無事に思い通りの作業ができました。

回答No.5

matsu_jun

ANo.4 の matsu_jun です。
下の方にある

　　If i i < 52 Then

は、

　　If i < 52 Then

の誤りでしたね。失礼いたしました。

回答No.4

matsu_jun

issan55さん、こんばんわ、ANo.1のmatsu_junです。

mitarashiさんの回答通り、
Sleepを組み込んだ上で、利用するのが一番良いようですね。

で、issan55さんの元のコードを極力変更しないよう、改造してみました。

'---------------------------ここから----------------------------
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub セル移動()

　　Dim waitTime As Variant

　　Application.EnableCancelKey = xlErrorHandler
　　On Error GoTo ErrJob

　　'i = 0
　　Range("c2").Select
　　Sleep (1000)

　　Range("c3").Select
　　Sleep (1000)

　　For i = 1 To 50
　　　　ActiveCell.Select

　　　　Selection.Offset(0, 1).Select
　　　　Sleep (1000)

　　　　Selection.Offset(0, -1).Select
　　　　Sleep (1000)

　　　　Selection.Offset(0, 1).Select
　　　　Sleep (1000)

　　　　Selection.Offset(1, -1).Select
　　　　Sleep (1000)

　　　　i = i + 1

　　Next i

ErrJob:
If i i < 52 Then
　　　　If MsgBox("中断しますか?" & vbCrLf & "(OK:中断、キャンセル:再開)", vbOKCancel) = 2 Then Resume
　　End If

　　Range("c2").Select
　　Application.Goto reference:=ActiveCell, scroll:=True
　　Application.ScreenUpdating = True
　　Application.EnableCancelKey = xlInterrupt

End Sub

'---------------------------ここまで----------------------------

作業中にEscキーを押すとダイアログが表示されます。
issan55 さんが、shuryo()でやりたかった処理は、通常に「セル移動」が終了した時と、中断ダイアログで終了させた時と、両方で実行されます。

お礼

matsu_junさん

再度のご回答ありがとうございます。
いろいろと勉強になります。
mitarashiさんのコードでうまく動きましたので、そちらを使わせていただきますが、
まだまだ素人ですので、コードをじっくり見させていただき、今後の参考にさせていただきます。
これからもよろしくお願いします。

回答No.2

mitarashi

Application.Onkeyでうまくいかない理由は#1の方が解説して下さっているので、代替案です。
単語が一番左側のワークシートにあるとし、二番目のシートをセル位置の保存に使用しています。
ご質問には「ボタンで」といった記述もありますが、後学のためにEscキーのままで作成しております。
前回止まった位置を別シートに記録していますので、最初に戻すには別のプロシージャで消去する必要があります。
ご参考まで。

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub test()
Dim currentRange As Range
Dim i As Long
Dim xlAPP As Application
Dim stopFlag As Boolean

On Error GoTo handleCancel
Set xlAPP = Application
xlAPP.EnableCancelKey = xlErrorHandler
stopFlag = False
If Sheets(2).Range("A1").Value = "" Then
Set currentRange = Sheets(1).Range("C2")
Else
Set currentRange = Sheets(1).Range(Sheets(2).Range("A1").Value)
End If
Do
For i = 1 To 2
currentRange.Select
Sleep 1000
currentRange.Offset(0, 1).Select
Sleep 1000
DoEvents
If stopFlag Then
Sheets(2).Range("A1").Value = currentRange.Address
Exit For
Else
Set currentRange = currentRange.Offset(1, 0)
End If
Next i
Loop While Not stopFlag
xlAPP.EnableCancelKey = xlInterrupt
Exit Sub

handleCancel:
If Err.Number = 18 Then
stopFlag = True
Resume
Else
MsgBox Err.Number & ":" & Err.Description
Exit Sub
End If
End Sub

回答No.1

matsu_jun

issan55 さん、こんばんわ

まずはプログラム中の、OnKeyコマンドについて解説していきましょう。

Application.OnKey ("{esc}"), "shuryo"

という命令を実行したことで、一度Excelを終了し、再起動するまでは、Escキーを押した時に、shuryo という処理を実行することになります。(別のシートや別のブックを選択していてもです。）
これは上記の通り、一度Excelを再立ち上げするか、別のマクロで

Application.OnKey ("{esc}")

という命令を実行するまでは戻りません。

この命令そのものは、プログラム（この場合は「セル移動」）の実行を、Escキーを利用して中断するという機能はありません。
あくまで他のプログラムが実行されていない時にEscキーを押せば、「shuryo」プログラムが実行されるよう定義したに過ぎません。

さて、Escキーは、基本的には現在の処理を中断するキーとして割り当てられているので、Application.Wait の処理実行中に Esc キーを押したら、以降Wait命令をキャンセルし、さらにマクロの実行中であれば、マクロの実行をキャンセル（確認メッセージは出ますが）します。
Escキーを押した時のこの動作は別の命令でキャンセルしない限り、有効であり続けます。

さて、そうすると、issan55さんは、Escキーに複数の機能を割りつけたことになります。

このことをより明確にするため、以下の実験を行なってみてください。

1) 最低一度「セル移動」を実行したあと新規ブックを開き、そこでEscキーを押してみてください。開いているブックの開いているシートのC2セルにカーソルが合うはずです。

2) 「セル移動」中の
　　For i = 1 To 50
　の50を10000に変更して実行してみてください。
　最初にEscを押したときは、セル移動のスピードが上がります。（Application.Waitがキャンセルされたため）
　そのまますぐににEscを押してみてください。そのときは、開発画面に切り替わり、「コードの実行が中断されました」というダイアログが表示されるはずです。（「終了(E)」ボタンをクリックすれば終了します）
　その後、再度Escを押すと、"shuryo"が実行され、C2セルが選択された状態に戻ります。

ソースを拝見させていただきますと、issan55様はまだVBAを利用し始めてからあまり経験が無いように見受けられますが、よくここまでのコードをお書きになったものと感心いたします。
ただし残念ながら、それなりに改造を施さないと、issan55様が期待するとおりの動作をさせるのは難しいと思います。

私としても、ちょっとすぐに思いつく方法だと、いずれにしても何らかの不都合（時間がバラつくなど）が出てきてしまいます。

また、現状の情報だけでは最適な方法を特定もできません。
　・Sub セル移動 プロシージャは何をきっかけに実行されているか？(画面上にボタンを置いたりしているのか？マクロメニューから実行しているのか？フォームを設計しているのか？)
　・Excelのバージョン
　・OSのバージョン
　・Escキー以外にキーを利用しても良いか？　など

とりあえず、「EnableCancelKey」もしくは「DoEvents」あたりを調査して、issan55さんも、もう少し頑張ってみてください。

お礼

matsu_junさん
丁寧な解説ありがとうございます。本やネットの解説をみながら何とか動くようになると、
今度は途中で止めたくなったりと、やりたいことが次々と浮かんできてしまいます。
まだまだ素人ですが、実験は（１）、（２）どちらも試してみるとその通りになりました。
また、質問のときの自分の環境や状況説明についても、これからの参考にさせていただきます。
ほんとうにありがとうございました。