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

Question

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'オブジェクト」と出ます。
確定せずにシートを切り替えたのだからエラーが出るのは当たり前だと思うのですが、
なんとかエラーが出ないようにする方法は無いでしょうか。

欲を言えば、
シートを切り替えた時点でセルに入った未確定の値は確定扱いになって、
『他のシートの同じ位置に同じ値を入れる』のマクロが正常に動作するのが最も望ましいですが、
不可能な気もするので、
未確定の状態でシートを切り替えた場合は『他のシートの同じ位置に同じ値を入れる』のマクロは実行されなくても良いです。
ただエラーメッセージが出て止まってしまうのを避けたいです。

「エラーが出た時にエラーメッセージの「終了」を押す」というマクロが組めればそれでも問題ないのですが、
流石にその操作は「マクロを記録する」では記録してくれませんでした。

kobouzu_su · Accepted Answer

こんばんは。

>このコードは過去に質問した時に教えていただいたもののほぼコピーです。

ということはそれからあまり進歩してないと言うことですね？？？
なとど詰まらぬ突込みは置いといて、、(^^;;;


＞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だけを扱うときはこの限りではありませんが。

それから、これを修正しても質問者が提示したコードにはちょと感心しないところもありますが、
今回はあまり問題とならないのでそれはまた次の機会に。
　
以上です。

Wendy02 · Answer

こんにちは。#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'というのでは、ワークシートの実行時エラーなので、それでは、曖昧すぎますので、セルのオブジェクトの取得を判定にしました。

Wendy02 · Answer

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

x0000x · Answer

#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 '新

入力途中でも、同様な動作が可能となります。

x0000x · Answer

こんばんは。

エラーを表示しないで、処理をスキップするなら
以下の様に「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
により、条件が成立しないため、何も処理されません。

grkt3 · Answer

以下をマクロの先頭へ追加してみては？
On Error Resume Next

＃ほんとはきちんとエラー処理したほうがよいです。
  もしやりたいならマクロ勉強しましょう。

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

こんばんは。

こんにちは。

こんばんは。

#2です。

こんばんは。

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

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング