マクロで発生するエラーを無視するには

質問

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim CellRange As String, S As String
CellRange = "A1:D5"

ActSheet = ActiveWorkbook.ActiveSheet.Name '現在開いているシート名を取得
ActSheet = Left(ActSheet, 5) 'シート名から「Sheet」の部分だけ抜き出す

Set r = Intersect(Target, Range(CellRange))
If Not (r Is Nothing) Then
Application.EnableEvents = False
For Num = 1 To 3
S = ActSheet & Num
Worksheets(S).Range(r.Address).Value = r.Value
Next
Application.EnableEvents = True
End If
End Sub

シートSheet1、Sheet2、Sheet3がある時に、
あるシートのセル範囲A1:D5の中のセルに値が書き込まれたら、他のシートの同じ位置に同じ値を入力させるコードです。
このコードは過去に質問した時に教えていただいたもののほぼコピーです。
値を入力し、確定後にシートを切り替えることで正常に動作します。
ただし、セルに値を入力したが『確定していない状態』でシートを切り替えるとエラーが発生して止まります。
エラーメッセージは「実行時エラー'1004'　Intersectメソッドは失敗しました。'_Global'オブジェクト」と出ます。
確定せずにシートを切り替えたのだからエラーが出るのは当たり前だと思うのですが、
なんとかエラーが出ないようにする方法は無いでしょうか。

欲を言えば、
シートを切り替えた時点でセルに入った未確定の値は確定扱いになって、
『他のシートの同じ位置に同じ値を入れる』のマクロが正常に動作するのが最も望ましいですが、
不可能な気もするので、
未確定の状態でシートを切り替えた場合は『他のシートの同じ位置に同じ値を入れる』のマクロは実行されなくても良いです。
ただエラーメッセージが出て止まってしまうのを避けたいです。

「エラーが出た時にエラーメッセージの「終了」を押す」というマクロが組めればそれでも問題ないのですが、
流石にその操作は「マクロを記録する」では記録してくれませんでした。

回答No.5

kobouzu_su · ベストアンサー

こんばんは。

>このコードは過去に質問した時に教えていただいたもののほぼコピーです。

ということはそれからあまり進歩してないと言うことですね？？？
なとど詰まらぬ突込みは置いといて、、(^^;;;

＞Set r = Intersect(Target, Range(CellRange))　

これの、Range(CellRange)の前に、Sh.　を付けて

　Set r = Intersect(Target, Sh.Range(CellRange))

とすればＯＫです。

-------　Sh　とは。---------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
この引数の、Sh as Object の　Sh　が入力したセルのシート、つまり　Sheet1　のことです。
--------------------------

が、なぜエラーになるかの原因が分からないまま、ただ上手くいくコードだけでは意味がないと思われますので。

（エラーの原因）
Sheet1　がアクティブのとき該当セルに値を入力して確定しないまま
Sheet2　をアクティブにした場合を想定

>Set r = Intersect(Target, Range(CellRange))

これでは、Targetのシートは値を入れたシートである、Sheet1　で
Range(CellRange)のシートは、値を入れた後アクティブにしたシートである　Sheet2　となり、
●●異なるシートのセルを比べているのでエラーになるのです●

今回のようにRangeオブジェクトの前にSheetオブジェクトを省いたときは
そのRangeオブジェクトがどのSheetのものかちゃんと理解しておかなければいけません。
但し、ActiveSheetだけを扱うときはこの限りではありませんが。

それから、これを修正しても質問者が提示したコードにはちょと感心しないところもありますが、
今回はあまり問題とならないのでそれはまた次の機会に。

以上です。

お礼

回答ありがとうございます。
返信が遅れて申し訳ありません。

>>ほぼコピーです
>ということはそれからあまり進歩してないと言うことですね？？？
おっしゃる通りで返す言葉もありません(苦笑）。
コードを眺めているうちに、なんとなく意味はわかる気がする、といった程度です。
「エラーが出た時にエラーメッセージの「終了」を押す」を本気で「マクロを記録する」で記録しようとしたくらいですから…。
余談ですが、
私としては「挙げてもらったほぼコピーのコードに文句付けるな」という風に言われるのではないか、
という点についてのみ心配でした(笑)。

エラーの原因に関しては、非常に納得できました！
「未確定なんだから当然では？」などと、深く考えてすらいませんでした。

今回のサンプルコードにおいては、Range(CellRange)の前に、Sh.を付けることで望む動作となりました。
ただ、このサンプルコードをもとに作りたい目的のコードにおいては、
若干条件が異なるため気になる点が残ってしまいました。
もう少し考えてみます。

>それから、これを修正しても質問者が提示したコードにはちょと感心しないところもありますが
是非教えて頂きたいです。
一から自分で組んだものでないため、聞いても理解できない可能性が高いですが、
聞いておきたいと思います。

回答No.6

Wendy02

こんにちは。#4です。

>今回のエラーは回避できるらしいことがわかったので、エラーの無視ではなく回避する方向で進めようと思っています。

それは、#5のことを言っていますね。そのほうが、コンセプトはそのままで、変更も少しですみますからね。

私も、それは分かっていましたが、あくまでも、こちらは、ご質問の元の意に従ったということと、誤った処理方法は、あくまでもエラー処理として扱うことにしました。入力状態のまま、他のシートを参照した場合に、そこでエラー処理をするという最初のご質問のコンセプトは、私は間違いがないと思います。

最初のご質問：
>確定せずにシートを切り替えたのだからエラーが出るのは当たり前だと思うのですが、
>なんとかエラーが出ないようにする方法は無いでしょうか。

別に、趣旨を変えることはかまいませんし、こちらのは、かなり変わった特殊なテクニックのコードですから、その立場を主張するつもりはありません（^^;。一見、初級のようで、一旦、Select したセルを外して、ActiveCell にすることで、串刺しを成功させています。

それと、なぜ、入力中に他のシートを参照しようとしたのか、その原因を考えて、FormulaLocal にしています。

また、ループして値を入れていくのは、Range「アドレス」を文字化して、シートそれぞれに、Rangeの引数にアドレスを入れて代入することなのですが、それは、私のVBAのコードのスタイルには合わないので別の方法を使っています。なんとなく、座りが悪いからです。

>実際に使いたいコードではBook内の一部のシートなので、

それは、Index で行うか、

For Each sht In Worksheets　

Worksheets の代わりに、

For Each sht In Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))

のようにすればよいです。

>「On Error GoTo 0」を入れてエラー処理を元に戻すようにする、という意味ですよね？

エラートラップを設けた部分は、最後に、エラートラップを外します。エラートラップで、セルのオブジェクトが取れているか取れていないかだけの判定をします。通常は、Err.Number を取りますが、'1004'というのでは、ワークシートの実行時エラーなので、それでは、曖昧すぎますので、セルのオブジェクトの取得を判定にしました。

お礼

>入力状態のまま、他のシートを参照した場合に、そこでエラー処理をするという最初のご質問のコンセプトは、私は間違いがないと思います。

私としては、今回エラー処理の仕方を聞いたのは苦肉の策でした(苦笑)。
エラーは回避したいが、入力状態で他シート参照だからエラー発生は当然(あくまで自分の認識)。
では無視するしかない、と。
これがコードの組み方で回避できるのであれば、
「エラーが出たら無視してもらう」といった回避法よりはカッコイイと考えます(これも自分の印象です)。
聞いたまんまを実行して「回避できました」と言うのも図々しいですが…。

ただ、今回こんなレベルのスキルで作ろうとしているものは、ヒトに使わせたいものなので、
製作段階でこちらが意図できないエラーは無視してもらう、という意図ではエラー処理を入れておこうと思います。

>For Each sht In Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
シートの数は可変なのでこのままでは使えませんが、
検索したところ、
http://homepage1.nifty.com/rucio/main/dotnet/shokyu/standard28.htm
このあたりを組み合わせればできそうだと思っています（まだ組めてません）。

ありがとうございました。

回答No.4

Wendy02

こんばんは。

>確定せずにシートを切り替えたのだからエラーが出るのは当たり前だと思うのですが、
>なんとかエラーが出ないようにする方法は無いでしょうか。

エラーは、Err.Number で取るよりも、実際のオブジェクトを確保していないことで、エラーを判定すればよいです。ただ、エラー処理の仕方は、かなり難しいです。エラーの発生の原因をよくつかんでから行うべきだと思います。そうしないと、おかしなことになります。また、On Error Resume Next なら、それを一旦、エラー・トラップは、終わらせるべきだと思います。

なお、シートをループするなら、
For Num = 1 To 3
は、単に、Index ですればよいですね。ただし、私の以下のマクロの場合は、シートの選択する順序があります。

以下は、串刺しで、行う特殊な方法です。

'----------------------------------
'ThisWorkbook モジュール
'----------------------------------

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
　　Const CellRange As String = "A1:D5"
　　Dim sht As Worksheet
　　Dim r As Range
　　On Error Resume Next
　　If Target.Count > 1 Then Exit Sub
　　Set r = Intersect(Target, Range(CellRange))
　　On Error GoTo 0
　　If r Is Nothing Then Exit Sub
　　Application.ScreenUpdating = False
　　Application.EnableEvents = False
　　Sh.Select False
　　For Each sht In Worksheets　
　　　If Not Sh Is sht Then
　　　　 sht.Select False　　'シート選択
　　　End If
　　Next
　　r.Select
　　ActiveCell.FormulaLocal = r.FormulaLocal　'串刺し
　　Sh.Select
　　r.Offset(1).Select
　　Application.EnableEvents = True
　　Application.ScreenUpdating = True
End Sub

お礼

回答ありがとうございます。
返信が遅れて申し訳ありません。

サンプルコードまで挙げてもらっておいて非常に申し訳ないのですが、
今回のエラーは回避できるらしいことがわかったので、エラーの無視ではなく回避する方向で進めようと思っています。

>また、On Error Resume Next なら、それを一旦、エラー・トラップは、終わらせるべきだと思います。

「On Error GoTo 0」を入れてエラー処理を元に戻すようにする、という意味ですよね？
参考にします。

>ループは～～単に、Index ですればよいですね
「For Each」で検索したところ、
コレクション専用のループ方式、という説明が見つかりましたが、これのことですよね？
定時したサンプルでは同期させたいシートはBook内全てでしたが、
実際に使いたいコードではBook内の一部のシートなので、巧く使えるかわかりません。
ループ内部に「目的のシートじゃなければ行わない」といったif文を入れてみる、くらいしか思いつかないです。

回答No.3

x0000x

#2です。

質問内容を良く読まないで回等しました。

>欲を言えば、
>シートを切り替えた時点でセルに入った未確定の値は確定扱いになって、
>『他のシートの同じ位置に同じ値を入れる』のマクロが正常に動作するのが最も望ましい

以下の2行を変更してみてください。
'Set r = Intersect(Target, Range(CellRange)) '旧
Set r = Intersect(Range(Target.Address), Range(CellRange)) '新

'Worksheets(S).Range(r.Address).Value = r.Value '旧
Worksheets(S).Range(r.Address).Value = Target.Value '新

入力途中でも、同様な動作が可能となります。

お礼

回答ありがとうございます。
返信が遅れて申し訳ありません。

サンプルにおいては満足のいく結果となりました。
コードの細かい意味まではまだ理解できてませんが・・・。
実際にこのコードを、自分の目的のコードに組み込んでみたのですが、
こちらでは条件が異なるためちょっと不具合があったので、もう少し考えて見ます。

回答No.2

x0000x

こんばんは。

エラーを表示しないで、処理をスキップするなら
以下の様に「On Error Resume Next」を追加します。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next

エラー時は、次行から処理します。
次行の
If Not (r Is Nothing) Then
により、条件が成立しないため、何も処理されません。

お礼

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

無事(？)、エラーを無視してくれました。
今回はエラー回避の方向でも沢山回答をいただけたので、回避を考えてみることにします。

しかしこの「エラーを無視する」って記述を知ってしまうと、
とりあえずモノだけ完成させたい、って時に乱用してしまいそうです(苦笑)。

回答No.1

noname#108256

以下をマクロの先頭へ追加してみては？
On Error Resume Next

＃ほんとはきちんとエラー処理したほうがよいです。
もしやりたいならマクロ勉強しましょう。

お礼

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

挙げていただいた一行を組み込んだところ、思ったように動作しました。
しかし多くの方から回答をもらえたので、
今回のエラーについては無視ではなくエラー回避の方向で考えてみたいと思います。

言い訳ですけど、
私が「エラー発生を無視する記述」を求めたのは、
今回の件では「未確定なんだからエラー発生は当然。ゆえに回避不可」かな、と思っていたので。
しっかり原因があって(それこそ当然な気もしますが)、回避できるんですねー。