Excelマクロで他のシートへのRangeが異常終了する

■　相談

「シートのコンボボックス（ActiveXコントロール）の値を変更した時に、別のシートに対して処理うマクロを実行すると異常終了するんだけど何で？」と相談を受けました。

んで、それに対して「処理の内容を標準モジュールに書いてCALLで呼んで」と返しました。

・・・うん、なんか冷たいのでもうちょっと解説します。

■　再現

相談はコンボボックスが起点でしたが、ActiveXコントロールはどれでも同じ現象を再現できます。

まず、シート「ShtA」と「ShtB」を作成します。分かりやすいようにコード名を同じにしておきます。次に、シート「ShtA」にActiveXコントロールのコマンドボタンを設置して、クリック時シート「ShtB」に移動してセルを選択する処理を作成します。


ソースはこんな感じ。

Private Sub CommandButton21_Click()
    ShtB.Select
    ShtB.Cells(3, 3).Select
    ShtB.Range(Cells(3, 3), Cells(6, 6)).Select
End Sub

コマンドボタンをクリックすると、「実行時エラー '1004': 'Range'メソッドは失敗しました:'_Worksheet'オブジェクト」というエラーが発生します。
「デバッグ」ボタンを押してエラーの発生個所を確認すると、メッセージ通りにRangeメソッドで異常終了したことが分かります。


シート「ShtB」の選択、Cellsを使用したセルの選択は動くのに、Rangeを使用したセルの選択はダメですか。Rangeは範囲を指定していますが、１つのセルを選択するようにしても同じエラーが発生します。

■　対応

ではどうするかっていうと、最初に書いた「処理の内容を標準モジュールに書いてCALLで呼んで」を実装すれば良いわけです。

まず、標準モジュールにコマンドボタンをクリックした時に実行していた処理を作成します。


コマンドボタンのクリック時の処理を、標準モジュールに作成した処理をCallで呼ぶように修正します。


これで、問題なく動くようになりました。

■　考察

実は原因はしらないのですが、ある程度の予測は出来ます。少し長々と私見を語りますので、飽きた方は最後の「後書き」まで読み飛ばして下さい。


ソースは正しいのにRangeが異常終了するのは良くあることなのですが、大抵の場合は、「Range(**).Select」はアクティブなシートでなければいけないという縛りに引っかったことが原因だと思います。

なので、マクロの作成に手慣れてくると、事前に対象のシートに移動（今回でいえば「ShtB.Select」）したり、そもそも「Range(**).Select」を使わなくても良いようにソースを書くようになってくるかと思います。

しかし、今回の場合は事前にシート「ShtB」に移動しているのにエラーが発生しています。では、何が原因なのか。初めてこの現象に遭遇した時に考えたのが下記の内容です。

まず、ActiveXコントロールはExcelのシートオブジェクトに強く依存していて、ActiveXコントロールに対してマクロを記述する時は、シートの中に処理を記述することになります。今回でいえば、コマンドボタンをクリックした処理はシート「ShtA」の中に作成されています。

ならば、ActiveXコントロールに対して何らかの操作をした時に実行される処理は、そのActiveXコントロールが存在するシートが起点になっている。そして、処理の実行中は起点となったシートが選択された状態になっているのではないでしょうか？

それを前提として今回の処理を考えると、「ShtB.Select」によって見た目上はシート「ShtB」に移動しているけど、内部的にはシート「ShtA」が選択されたままなので、「ShtB.Range(Cells(3, 3), Cells(6, 6)).Select」を実行した時に、前述の縛りに引っかかってエラーになったと想像出来ます。

そうであれば、ActiveXコントロールでなくてもシートに依存するタイプの処理であれば同じ結果になる筈です。ためしに、シート「ShtA」にWorksheet_BeforeRightClick（右クリックした時に実行する処理）を追加して同じ処理を実行させたところ、同じエラーが発生しました。

では、なぜCallを使うと動くのかという事なのですが、ExcelヘルプのCallステートメントの説明「Sub プロシージャ、Function プロシージャ、ダイナミック リンク ライブラリ (DLL) プロシージャに制御を渡すフロー制御ステートメントです。」という文の中の「制御を渡す」という部分が理由のような気がします。

私の場合、Callステートメントは今回のようにExcel内の別のモジュールを呼ぶ時に使うことが多いのですが、Excelヒントにあるように外部の処理（DLL）を実行することも出来ます。ならば、Callステートメントの内部処理はExcel外の処理を呼び出すこと、つまりOS側に制御を渡すように設計されていると思います。

「制御を渡す」という事は、Callで処理を呼び出した場合、その処理はCallを実行したExcelとは全く異なる処理として実行されている筈です。Callを実行した方では、呼び出した処理が終了するのを待っている状態ってことですね。

そのため、呼び出された処理の方で「ShtB.Select」を選択した場合、呼び出し元の方ではシート「ShtA」が選択されていたとしても、呼び出された処理から見るとシート「ShtB」が選択されているため、「ShtB.Range(Cells(3, 3), Cells(6, 6)).Select」を実行しても前述の縛りに引っかからずに正常に動いたのではないでしょうか。

まぁ、想像の域は出ないのですが、発生している現象から原因を想定するというのは楽しいですね。

■　後書き

ところで、一通り書き終わってからソースを見直したのですが、これはバグを生み出しそうな記述があって良くないですね。

問題の個所は、エラーが発生していたここです。

ShtB.Range(Cells(3, 3), Cells(6, 6)).Select

Rangeの中のCellsにも「ShtB.」を付ける方が良いかと思います。

ShtB.Range(ShtB.Cells(3, 3), ShtB.Cells(6, 6)).Select

あるいは、「ShtB.」を外す手もありますかね。省略すればアクティブなシートになるので、事前に「ShtB.Select」でシート「ShtB」に移動しているので省略可能ですし。

Range(Cells(3, 3), Cells(6, 6)).Select

なお、私のお勧めは全てに「ShtB.」を付ける方法。コードは冗長になりますが、保守性と処理の安定性が高まります。

投稿記事の一覧：http://harikofu.web.fc2.com/

--- blog end ---