Excelマクロ(VBA)入門編 - エラーの対処 | エクセルマクロ(VBA)実践蔵(じっせんぐら)
- エクセルマクロ(VBA)実践蔵 >
- 項目一覧 >
- Excelマクロ(VBA)入門編 - エラーの対処
Excelマクロ(VBA)入門編 - エラーの対処最終更新日：2024-05-11
今回は初心者が陥りやすいミスや、エラー発生時の対処方法について書いていこうと思います。
- このページの目次
- 別ブックを開く「Workbooks.Open」時のエラー
- 自ブックオープン時に動作するPrivate Sub Workbook_Open()で気を付けること
- エラー「オブジェクト変数またはWithブロック変数が設定されていません。」について
- エラー「実行時エラー 9」「インデックスが有効範囲にありません。」について
- イベントをトリガに動作させたいのに、マクロが動かない場合
参考までに以下の関連記事もあります。
別ブックを開く「Workbooks.Open」時のエラー
まずはじめに、macとwindowsでは、ファイルパスの表現方法が異なります。環境に合わせた書き方が必要です。
また、同じwindowsパソコンであっても、パソコンによって、エラーになったり、ならなかったりする場合は、拡張子を表示しているパソコンとしていないパソコンで、挙動が変わることもあります。各パソコンの環境に合わせるためにも、開きたいファイルパスは、ファイル参照ダイアログから選択してマクロを動作させることをおすすめします。このサイトのものは、windows向けのものになります。
下記の記事に、WorkBook型オブジェクト変数の説明を行っています。マクロ有効ブックまでのフォルダパスとファイル名を連結して、WorkbookをOpenさせているコードも書いているので、参考にしてみてください。自ブックオープン時に動作するPrivate Sub Workbook_Open()で気を付けること
シートの情報を参照する場合、シートが開くのを待つ必要があります。モジュールの先頭で、下記を記述しておくと、シート情報がないというエラーはでなくなります。
Dim Dammy_Rng As Range
'シートが開くのを待つ
Set Dammy_Rng = ActiveSheet.Range("A1")
エラー「オブジェクト変数またはWithブロック変数が設定されていません。」について
「オブジェクト変数またはWithブロック変数が設定されていません。」のエラー内容は、「どこかのオブジェクトが持っている機能を呼び出そうとしているようだけれど、どのオブジェクト使おうとしているのか指示が足りていません。」という意味になります。
このエラーがでる原因は、主に「変数の誤記」、「Withブロックを理解していない」、「VBE環境の違い」の三種類だと思います。この３つを順番に解説していきます。
一番多いのは、一つ目の「変数の誤記」で対処方法は、「変数の宣言を強制する」環境を作ることになります。以下、理由も踏まえて解説していきます。
まずは、設定方法です。VBEProject画面のメニューバー「ツール」にある「オプション」を選択し、「変数の宣言を強制する」にチェックを入れると、誤記の可能性がある行を教えてくれるようになります。
問題のある個所だけを直したい場合は、解説の方を読んでもらえれば、問題箇所を見つけられるかもしれません。
例を下記のコードを使って説明していきます。
Sub writeCell()
'[1]変数宣言 省略可
Dim Rng As Range
'[2]変数に情報の設定
Set Rng = Range("A1")
'[3]変数を使用する
Rng.Value = 123
End Sub
コードは、主に[1]変数宣言、[2]変数に情報を設定、[3]変数を使用する、の３段階に分けられます。
[1]変数宣言は、Rangeというオブジェクトを扱う変数として、Rngをこれから使っていきますという宣言になります。空箱にラベルを張って、これを入れますと宣言しただけで、実際には何も入っていません。このコードは省略して記述することができます。
[2]変数に情報の設定では、上の例では、A1セルの情報を持つRangeオブジェクトを変数Rngに設定しています。これで、中身が入ったことになります。
[3]変数を使用するでは、Rangeオブジェクトの持つ機能を、変数名Rngに置き換えて使用することができます。「Rng.Value」は、「Range("A1").Value」と同じ意味になります。
ここで、[2]の部分を、「Set Rang = Range("A1")」と間違えたとします。この場合、コンパイラは、「ユーザは変数宣言が省略されている新たな変数Rangを使用したいのだろう」と判断し、エラーにはなりません。エラーがでるのは、[3]の使用時で、変数Rngには、何も設定されていないため、「オブジェクト変数またはWithブロック変数が設定されていません。」のエラーが発生します。この場合は、「変数の中身空っぽですよ」というエラーだと読み替えて構いません。エラーが発生している行数ではなく、エラーが出ている変数にSETする箇所の見返しが必要です。
また、[2]を正しく記述し、[3]で間違えて「Rang.Value = 123」と記述しても、同様に、変数Rangには何も情報が設定されていないため、「オブジェクト変数またはWithブロック変数が設定されていません。」のエラーが発生します。この場合は、指摘された行数の修正が必要です。
他の人が記述したコードをもらってくる際も、[3]だけ記述しても、「オブジェクト変数またはWithブロック変数が設定されていません。」エラーとなるため、[2]の変数に情報の設定をしているコードをもらってくる必要があります。
２つ目、Withブロックの書き方について
上記のサンプルを変数を使わずにWithブロックで記述してみます。
Sub writeCell_with()
With Range("A1")
'Withブロックを使用してセルに値を書き込む
.Value = 123
End With
End Sub
変数を使用する場合は、一度 Rngに置き換えを行っていますが、Withブロックで記述すると With ○○ ～ End Withでくくられている区間は、○○(オブジェクト名)が省略可能とする書き方になります。
そのため、With ○○ ～ End Withの記述をせずに、いきなり「.Value = 123」だけを書いたら当然「オブジェクト変数またはWithブロック変数が設定されていません。」エラーになります。
３つ目、記述環境の違いによる「オブジェクト変数またはWithブロック変数が設定されていません。」エラー
下記のVBAProjectの画像は、初期状態のものと変更を加えたものになります。
オブジェクト名が変更されていない環境
オブジェクト名が変更されている環境
上記のように、プロパティウィンドウからオブジェクト名を変更することが可能です。ユーザフォームも同様です。ここに定義されている、オブジェクト名を使用して、機能の呼び出しをする場合、環境の違いによって、「オブジェクト変数またはWithブロック変数が設定されていません。」エラーが発生することもあります。シートオブジェクト名を変更することは、ほとんどないと思いますが、可能ではあります。あえて変えてみました。また、ユーザフォームを作成する場合は、配置した部品のオブジェクト名と一致するようにコードを記述しましょう。
この場合は、もちろん環境を合わせる必要があります。
もう少し詳しい説明は、標準モジュールとユーザフォームとクラスモジュールの特徴に記載しています。
エラー「実行時エラー 9」「インデックスが有効範囲にありません。」について
配列を扱える人は、このエラーが出たときにどう対処したらいいかわかると思うので、「配列って何？」という初心者の人向けに記述していきます。
説明より、ひとまずエラーになるコードを載せてみます。
エクセルを新規作成して、シート2にアクセスしに行きます。
Sub indexError()
'ブックの新規作成
Workbooks.Add
'新規作成した新しいブックのシート2にアクセス
'インデックスが有効範囲にありませんがでるコード
ActiveWorkbook.Sheets(2).Range("B1").Value = 1
End Sub
新規作成されたブックは、シートが1つしかないため、シート2を指定すると存在しないため「インデックスが有効範囲にありません。」のエラーが発生します。もし、Workbooks("○○")で、このエラーが発生する場合は、大文字と小文字のミス、スペースの有り無し、全角、半角の記述ミス、あとは、拡張子ありのPCで拡張子なしを指定した、等が考えられます。
エクセルが用意にしているオブジェクトを使用しようとしてこのエラーがでる場合、引数の指定ミスだと考えてください。オブジェクト名の最後がsで終わる集合体の中から一つを取り出したいときに、存在しないものを指定するとこのエラーがでます。
どうしてもエラー原因がつかめない場合、For Each構文を使用して、目的のオブジェクトを探す方法もあります。
下記は、開いているブックすべての名前を取得して、"ブック名"の文字列を含むか確認しています。
完全一致ではない比較になるため、確実にこの文言なら見つかるはずという文字列を"ブック名"の部分と置き換えて記述してみてください。
Sub indexErrorFix()
Dim wb As Workbook
For Each wb In Workbooks
'開いているブック全ての名前をチェックする
If InStr(1, wb.Name, "ブック名", vbTextCompare) > 0 Then
'見つけたいブックが見つかった
Exit For
End If
Next
If Not wb Is Nothing Then
'見つけたWorkbookオブジェクト(wb)が使える
wb.Sheets(1).Range("A1").Value = 1
Else
'開いていないよ。存在しないよ。
'インデックスが有効範囲ではないよ。
End If
End Sub
リストボックス、コンボボックスなんかでも、登録したリスト数以上の数値を指定したりすると範囲外なのでこのエラーになります。
イベントをトリガに動作させたいのに、マクロが動かない場合
このケースのエラーは、記述場所の間違いが多いと思います。
マクロを書く場所によるコードの違い
マクロを記述できる場所として、Visual Basic を記述する画面(エディタ)のデフォルトでは左側に表示される「プロジェクト - VBAProject」と記載されているウィンドウ(プロジェクトエクスプローラ）に表示されている「Microsoft Excel Objects」フォルダ配下の「SheetX("AAAAA")」（Xは任意の数値、AAAAAはシート名）のページと、「ThisWorkBook」、そして、右クリックして追加することができる「ユーザフォーム」「標準モジュール」「クラスモジュール」などのページがあります。
それぞれに役割があり、コードを書く際に気を付けるべき部分があります。それを知らずに、他の人の書いたコードを、適当な場所に貼り付けても、エラーになってしまったり、思うように動作してくれないということが起きます。
マクロを記述できる場所のそれぞれの特長について
シートオブジェクトについて
「SheetX("AAAAA")」（Xは任意の数値、AAAAAはシート名）のページは、正式名称では、シートオブジェクトと呼ばれます。その特徴は、大きく２つあります。
- シート名を省略して記述可能
- シートイベントの機能を持つ
- シートコピーされると一緒にコピーされる(くっついてくる)
セルに対して、書込や読込をコードから指示する場合、「どのシート」の「どのセル」に対してアクセスしたいのかという情報が必要になります。しかし、シートオブジェクトにおいては、「どのシート」に対する操作なのかという情報を省略して記述することができます。省略して記載された場合、「セルA1」や「セルB1」に対する操作は、自動的にコードを書き込んでいるシートオブジェクトのシートが対象となります。
Sub writeCell()
'A1セルに数字123と書き込む
Range("A1").Value = 123
'A2セルにテストと書き込む
Range("A2").Value = "テスト"
End Sub
もうひとつのシートオブジェクトの特徴は、シートイベントが用意されていることです。
下の画像の個所「(General)」をクリックして、Worksheetを選択してみてください。
Worksheet_SelectionChangeというメソッドが自動生成されると思います。
右側のプルダウンリストにも「SelectionChange」と記載されていると思います。
その右側のプルダウンリストも選択してみてください。
これらのメソッド(マクロ)は、シートイベントと呼ばれ、ユーザがエクセルシートに対して行った操作に対して、自動で呼び出してもらえる機能の一覧です。例えば、Worksheet_SelectionChangeイベントは、選択セルが変更されたときに呼び出されます。Worksheet_BeforeDoubleClickは、ダブルクリックした際に呼び出され、Worksheet_BeforeRightClickは右クリックしたときに呼び出されます。「Before」とついているのは、本来の動作の前にマクロが呼び出されることを意味しています。とあるセルで右クリックした場合、通常は、操作一覧メニューが表示されると思いますが、その表示させる前にメソッド(マクロ)が動作するということを意味しています。要は、ダブルクリックしたり、右クリックしたときに自動で呼び出されると思ってもらっても問題はありません。
これらの機能は、シートオブジェクトのみが持っていて、名称も決められているものです。Private Sub Worksheet_XXXXXXと記載されているメソッド(マクロ)を動作させたい場合は、シートオブジェクトに記載する必要があります。
エクセルには複数のシートが存在することができますが、シートオブジェクトにシートイベントを記載した場合、その記述されているシートだけが対象となります。例えば、Sheet1やSheet2というシートが存在し、Sheet1にWorksheet_BeforeDoubleClickというメソッド(マクロ)を記述した場合、Sheet1を操作してダブルクリックした場合にだけ呼び出されます。Sheet2でダブルクリックをしたとしても、動作はしません。Sheet2で動作させたい場合は、Sheet2のシートオブジェクトにWorksheet_BeforeDoubleClickを記載する必要があります。
ブックオブジェクト(ThisWorkBook)について
シートイベントのように、ThisWorkBookにもブック(エクセルファイル)操作に対応するブックイベントが提供されています。
プロジェクトエクスプローラでThisWorkbookをダブルクリックして開くと、シートオブジェクトど同様に、上部にプルダウンリストがついているのが確認できると思います。
左側のプルダウンリストからWorkbookを選択し、右側のプルダウンリストもクリックしてみてください。
WorkBook_Openはその名の通り、ファイルを開いたときに呼び出されるメソッド(マクロ)です。その他には、WorkBook_NewSheetは、新しくシートを挿入したときに呼び出してもらえるメソッド(マクロ)、WorkBook_BeforeSaveやWorkBook_BeforeCloseなど保存時や閉じる直前に呼び出し貰えるメソッド(関数)も提供されています。これらはイベント発生時に動作する決められたメソッド(マクロ)であり、シートイベント同様、メソッドの名前や引数の数など変更することはできません。そして、これらは、Private Sub Workbook_XXXXXXという名前で統一されており、他の場所に同じように書いても、呼び出してはもらえません。
標準モジュールとユーザフォームとクラスモジュールの特徴
標準モジュール、クラスモジュールには、ThisWorkbookやシートオブジェクトとは違い、イベントで動作するメソッドは準備されていません。ユーザフォームには、ユーザフォームのイベントがあります。
また、標準モジュール、ユーザフォーム、クラスモジュールには、インポート、エクスポート機能が備わっているため、他のマクロでも使えまわせるようなコードは標準モジュールに記載し、わかりやすい、オブジェクト名に変更して管理することができます。Visual Basic画面の「表示」メニューから「プロパティウィンドウ」を表示させてデフォルトの「Module1」から名前を変更して使用します。
例えば、Module1の中に、関数ABCを記述したとします。それを、シートオブジェクトから呼び出す際に、「Module1.ABC()」と記述することができます。Module1を省略して記述することもできます。わかりやすいように「Module1」と記述しましたが、本来は、前述したように名前を変更して使用します。例えば、「Module1」を「Calender」と書き換えていて、シートオブジェクト側に「Calender.ABC()」と呼び出すコードがある場合、まったく同じコードを「Module1」に記述したとしても、コンパイラは、シートオブジェクト側のコードでエラーになります。これも「Calender」が何かわからず、「オブジェクト変数またはWithブロック変数が設定されていません。」エラーがでることになると思います。
用語についての違いですが、シートオブジェクトやThisWorkbookにSub xxxx と記載すると、メソッドと呼ばれます。メソッドとは、オブジェクトが持つ機能という意味になります。それに対して、「標準モジュール」の「モジュール」とは、日本語で関数という意味になります。そのため、標準モジュールにSub xxxx と記載する場合は、関数と呼ばれることが多いです。
標準モジュールに記述する関数は、エクセル関数のようにセルから呼び出すこともできます。(例：=SUM("A1:A10")のような関数が自作できます)。どこかのセルが書き換わっただけで、常に再計算されるため、負荷がとてもかかりますので、個人的におすすめはあまりしません。
標準モジュールで「Range("A1")」と記述するとエクセルのVersionによっては、エラーになります。エラーの内容は「オブジェクト変数またはWithブロック変数が設定されていません。」という内容になります。この意味は、どのシートのセルに書き込みたいのかという情報が足りないためです。
最近は、シート名の支持がない場合に、一番表面にあるシート(ActiveSheet)が対象になるように改変されたようです。
しかし、複数のシートに対して、マクロでアクセスを行う場合は、省略して書かずに、変数に置き換えることをお勧めします。どのシートに対して、操作を行っているのか明確にしておいた方が、コードは読みやすいと思います。
「ユーザ定義型は定義されていません。」というエラーについて
このケースは、参照設定が足りていない場合が多いです。
例えば、下記のようなコードを書いた場合に上記のエラーが出ます。そしてエラーの位置としては、「RegExpObj As regExp」の部分が選択中の画面となり、エラー個所として指定されます。このような変数宣言でエラーが起きる場合は、参照設定が不足している場合がほとんどです。
そのため、エラー発生個所のAsの後ろの部分「regExp」と「参照設定」という言葉をセットに、GoogleやYahooで検索をかけてみてください。
Sub test()
Dim RegExpObj As regExp
Set RegExpObj = CreateObject("VBScript.RegExp")
End Sub
参照設定の追加の仕方が出てくると思います。
上記のコードの場合は、regExpオブジェクトを使用するためには、Visual Basic Editor画面のツール「参照設定」にて「Microsoft VBScript Regular Expressions5.5」にチェックを入れる必要があるため、チェックをいれることでエラーは解消します。
この操作でエラーが解消する理由は、「Microsoft VBScript Regular Expressions5.5」の中に、regExpオブジェクトが定義されているためです。参照設定画面を見て貰えれば、初期状態でいくつかチェックが入っていると思います。通常Excelマクロで最低限使用するであろう、オブジェクトは使用できるように、チェックが入れられています。しかし、拡張機能を使用したい場合（Internet ExplorerやOutlookを制御したいなど）は、別途、参照設定画面で「この機能が使いたい！」っていうことを設定する必要があります。
コンパイルエラーとして何故「ユーザ定義」と表現するかというと、ユーザがクラスモジュールを作成して、自作のクラス型の変数を定義することができるためです。その場合、「Dim 変数名 As 自作のクラス型」 と書くことができ、コンパイラは、定義されていない型(解釈できない型)に対して、「ユーザ定義型」は「定義されていません。」と、指摘してきます。
エラー内容は、初心者にとっては、何を言われているのかわからないことが多いと思いますが、エラー内容を理解している人に取っては、有益な情報となります。エラーがでて、困って、人に尋ねる場合は、「何かエラーがでた！」と報告しても何も解決はできません。そのため、人に尋ねる場合は必ず、一言一句間違えずにエラー内容を書き取ってから尋ねてみてください。検索する場合も、エラー内容で検索すると、解決策を提示しているサイトにたどり着ける可能性もあると思います。