ExcelVBAでシステムエラーが表示された

ExcelVBAでマクロを実行させたとき「システム エラーです:&H80010108(-2147417848) 起動されたオブジェクトはクライアントから切断されました。」というエラーメッセージが表示されたのでメモ。

この画面でOKを押すとメッセージが閉じるが、再度マクロを実行させると今度は「400」とだけ書かれたメッセージが表示された。

一度Excelを閉じて再度開くと、今度はマクロを実行してもエラーメッセージが表示されず勝手にExcelブックが閉じるようになってしまった。

対処方法

標準モジュールを解放し、再度作成する。

エラーメッセージが表示されたマクロは、いずれも標準モジュールに作成している。何らかの原因で標準モジュールが破損したため、正常にマクロが実行できなくなっていたと思われる。

エラーの調査

システムエラーは、シート上に配置されたボタンを押したときに発生した。なので、ボタンに登録されているマクロがエラーの原因と思われる。

このシステムエラーが表示されたExcelファイルには、標準モジュールにMainとCreateSheetの2つのSubプロシージャがある。ボタンに登録しているのはMainプロシージャだが、念のため両方ともプロシージャ単体で実行できるか確認した。いずれもシステムエラーは表示されなかったので、マクロの記述には問題がないことが分かった。

さらに詳しく調べてみると、「マクロの登録」画面でなぜかExcelブック内のプロシージャが表示されないことが分かった。

この画面はボタンにマクロを登録するときに使用するが、通常は以下のようにExcelブックに存在するプロシージャ名が表示される。*1

この内容から、標準モジュールとの接続に問題があるのではないかと考えた。試しに標準モジュールを解放し再度作成したところ、システムエラーは表示されなくなった。

まとめ

システムエラーが出たのは今回が初めてなので、かなり焦った。このエラーが出たのは比較的簡単な処理のマクロだったので、それがまだ救いだった。

標準モジュールが破損した原因はよく分からないが、こういうときのために定期的にバックアップを残しておく必要があるなと思った。

*1:Privateプロシージャの場合はマクロの登録画面には表示されないので注意。

ExcelVBAで「Rangeメソッドは失敗しました」というエラーが出た

ExcelVBAのちょっと変わった仕様に振り回された。

アクティブシートのデータを別のシートにコピーする処理で、「Range’メソッドは失敗しました:’_Worksheet’オブジェクト」というエラーが出た。

原因を特定するのにけっこう時間を使ったので、メモ。

問題のコード

Dataシートの1列目のデータをOutputシートの1列目にコピーするコード。

実行時はDataシートを選択。

Dim LastRow As Long
Dim ws1 As Worksheet
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
'データ貼り付け先シートを指定する
Set ws1 = ActiveWorkbook.Worksheets("Output")
With Worksheets("Data")
    .Range(.Cells(1, 1), .Cells(LastRow, 1)).Copy  Destination:=ws1.Range(Cells(1, 1), Cells(LastRow, 1))
End With

エラーが出たのは下の箇所。

 .Range(.Cells(1, 1), .Cells(LastRow, 1)).Copy  Destination:=ws1.Range(Cells(1, 1), Cells(LastRow, 1)) 

一見すると特に問題がないように思える。

原因

ExcelVBAの仕様で、単にCellsやRangeと書いた場合アクティブシートから範囲を取得するとのこと。

learn.microsoft.com以下、リンク先本文から引用。

オブジェクト修飾子を指定せずにこのプロパティを使用すると、ActiveSheet.Range のショートカットとなります。つまり、アクティブ シートから範囲を取得します。アクティブ シートがワークシートでない場合、このプロパティは失敗します。

そのため、エラーが出た箇所は以下のコードと同じ意味になる。

 Destination:=ws1.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(LastRow, 1)) 

このマクロはDataシートを選択して実行していたので、エラーの出た箇所では「Dataシートの内容をOutputシートのDataシートのセルにコピーする」という訳の分からないことになってしまっていた。

対応

例えば、以下のように事前にOutputシートをアクティベートしておく。

Set ws1 = ActiveWorkbook.Worksheets("Output")
ws1.Activate
With Worksheets("Data")
    .Range(.Cells(1, 1), .Cells(LastRow, 1)).Copy  Destination:=ws1.Range(Cells(1, 1), Cells(LastRow, 1))
End With

この記述を追加することで、アクティブシートがOutputシートになるためエラーが出なくなる。

DataシートはWithステートメントですでに指定しているため、アクティブシートでなくても問題ない。

おわりに

ExcelVBAって広く使われている割にちょっと変わった仕様が多い気がする。

参考にどうぞ。

Excel VBAで変なセル指定の書き方見つけてしまった

Excel VBAでセルに値を入れるときは、以下のようにCellsやRangeを使うのが多いと思う。

    '例：セルB1に数値を入れる
    Cells(1,2).Value = 100
    Range("B2").Value = 100
    

ところが、会社でExcelのマクロを見ているとこんなコードを見つけてしまった。

    Cells(1,2).Range("B2") = 100
    

Cellsの部分だけを見るとセルB1だと思ったけど、RangeでセルB2を指定している…。

これは一体どのセルに値が入るのか？？

答え

セルC2だった。

なんか納得いかなかったのでマイクロソフトの公式ページを見てみた。

learn.microsoft.com

なんとなくだけど、Rangeで指定したセルの相対位置をCellsで指定しているんだろうということは分かった。

この書き方の意図は分からないが…。

インターネットで調べた限りではこの書き方を紹介しているサイトはなかったので、おそらく一般的な書き方ではないのだと思う。

でも、コンパイルが通るということは文法的には問題ないということか…。