機能と数式 | VBA | セミナー | オンラインソフト | お問い合わせ | その他
Top > Excel > VBA > VBAのエラー

For Each に指定する変数はバリアント型変数またはオブジェクト型でなければなりません





For Eachステートメントで使用する制御変数には、指定したコレクションのメンバと同じオブジェクト型変数か、バリアント型変数を指定しなければなりません。

固有オブジェクト型と総称オブジェクト型


変数のオブジェクト型には、固有オブジェクト型総称オブジェクト型の2種類があります。固有オブジェクト型は、変数の型宣言で、固有のオブジェクト型を指定します。たとえば、次のコードでは、Range型の変数と、Worksheet型の変数を宣言しています。

Sub Sample()
    Dim buf As Range
    Dim tmp As Worksheet
End Sub

Range型として宣言した変数bufには、Rangeオブジェクトしか格納することができず、Worksheetオブジェクトを格納しようとするとエラーになります。本当は"格納"ではなく"参照"なのですが、ここでは便宜的に"格納する"という表現で解説します。

一方の総称オブジェクト型は、すべてのオブジェクトを格納できる型です。変数を宣言するときはObjectを指定します。

Sub Sample()
    Dim buf As Object
    Dim tmp As Object
End Sub

総称オブジェクトのObjectで宣言した変数には、RangeオブジェクトでもWorksheetオブジェクトでも、すべてのオブジェクトを格納できます。



For Eachステートメントで使用する制御変数には、Inの後で指定するコレクションのメンバと同じオブジェクト型を指定しなければなりません。

Sub Sample()
    Dim C As Range
    For Each C In Range("A1:B3")
        MsgBox C.Value
    Next C
End Sub

Sub Sample()
    Dim ws As Worksheet
    For Each ws In Worksheets
        MsgBox ws.Name
    Next ws
End Sub

または、固有オブジェクト型ではなく、総称オブジェクト型を使用することもできます。

Sub Sample()
    Dim C As Objext
    For Each C In Range("A1:B3")
        MsgBox C.Value
    Next C
End Sub

Sub Sample()
    Dim ws As Object
    For Each ws In Worksheets
        MsgBox ws.Name
    Next ws
End Sub

あるいは、オブジェクト型に限らず、すべての型を格納できるバリアント型(Variant)を使用することも可能です。

Sub Sample()
    Dim C As Variant
    For Each C In Range("A1:B3")
        MsgBox C.Value
    Next C
End Sub

Sub Sample()
    Dim ws As Variant
    For Each ws In Worksheets
        MsgBox ws.Name
    Next ws
End Sub

つまり、For Eachステートメントの制御変数には、次のいずれかの型を指定することになります。

  1. コレクション内のメンバと同じ固有オブジェクト型
  2. すべてのオブジェクトを格納できる総称オブジェクト型
  3. 何でも格納できるバリアント型

バリアント型の意味が正確には少し違いますが、おおむね次のようなイメージです。



このうち、本エラーが発生するのは、For Eachステートメントの制御変数に、固有オブジェクト型・総称オブジェクト型・バリアント型 以外の型を指定したときです。





固有オブジェクト型を指定しているのですが、そのオブジェクト型が、コレクションのメンバと一致しないときは、別のエラーが発生します。



次のコードで、コレクションのメンバはRangeオブジェクトですが、制御変数にWorksheet型を指定しています。

Sub Sample()
    Dim C As Worksheet
    For Each C In Range("A1:B3")
        MsgBox C
    Next C
End Sub




固有オブジェクト型を指定するメリット


オブジェクト型変数を使用するときは、総称オブジェクト型ではなく固有オブジェクト型を使う方がいいと言われています。その理由のひとつは「総称オブジェクト型より固有オブジェクト型の方が、マクロの実行速度が速い」です。For Eachステートメントの制御変数の場合も、総称オブジェクト型と固有オブジェクト型で速度の違いがあるのでしょうか。実際に試してみました。

Declare Function GetTickCount Lib "kernel32" () As Long

Sub Sample1()
    Dim Start As Long
    Dim C As Range
    Range("A1:X256").Clear
    Start = GetTickCount
    For Each C In Range("A1:X256")
        C.Value = Int(Rnd * 6) + 1
        C.Interior.ColorIndex = C.Value
    Next C
    Debug.Print (GetTickCount - Start) / 1000
End Sub

For Eachステートメントで大量のセルを操作しました。制御変数Cの型を「固有オブジェクト型(Range)」「総称オブジェクト型(Object)」「バリアント型(Variant)」に変えて、それぞれ5回実行します。Range("A1:X256")は、約6000個のセルです。まったく同じコードで、セル数が約12000個のRange("A1:X512")でも試してみました。計測は、2台のパソコンで行いました。1台はAspire Oneという、最近流行のネットブックです(これをPC-Aとします)。もう1台のパソコンは、事務所の中でも比較的スペックの低いデスクトップです(これをPC-Bとします)。

【PC-Aで約6000個のセル操作】
RangeObjectVariant
 1回目 3.297  3.281  3.406
 2回目 3.266  3.406  3.375
 3回目 3.234  3.406  3.328
 4回目 3.235  3.312  3.328
 5回目 3.218  3.359  3.344
 平均 3.250  3.353  3.356
 割合(%) 100  103.163  103.268

【PC-Aで約12000個のセル操作】
RangeObjectVariant
 1回目 6.407  6.438  6.359
 2回目 6.250  6.453  6.563
 3回目 6.344  6.359  6.438
 4回目 6.328  6.359  6.422
 5回目 6.437  6.468  6.453
 平均 6.353  6.415  6.447
 割合(%) 100  100.979  101.476

【PC-Bで約6000個のセル操作】
RangeObjectVariant
 1回目 7.719  6.344  6.328
 2回目 7.531  7.704  7.734
 3回目 8.172  6.187  6.235
 4回目 8.063  7.875  6.141
 5回目 6.141  7.875  7.688
 平均 7.525  7.197  6.825
 割合(%) 100  95.639  90.698

【PC-Bで約12000個のセル操作】
RangeObjectVariant
 1回目 10.157  11.141  10.563
 2回目 11.984  10.500  10.484
 3回目 10.125  10.500  12.016
 4回目 12.157  12.125  10.516
 5回目 11.703  10.485  10.907
 平均 11.225  10.950  10.897
 割合(%) 100  97.550  97.078

PC-Aでは、固有オブジェクト型がほんの少しだけ速いですが、ほとんど変わりません。また、PC-Bでは逆に、固有オブジェクト型の方が遅い結果も出ています。これは、PC-Bに何か問題があるというよりも、速度差は誤差の範疇と考えてもいいでしょう。最近のパソコンでは「総称オブジェクト型より固有オブジェクト型の方が、マクロの実行速度が速い」というのは、意識するほどのことはない違いでしかありません。

固有オブジェクト型を使うメリットはほかにもあります。それは、コード記述時のインテリセンス機能です。
たとえば、制御変数をRange型で宣言した場合、その変数Cの後ろにピリオドを打つと、Range型で使用できるプロパティなどがリスト表示されます。



対して、総称オブジェクト型やバリアント型で宣言した変数では、このインテリセンス機能が働きません。



インテリセンス機能を活用したいのでしたら、固有オブジェクト型で宣言しましょう。

コレクションに配列を指定する場合


For Eachステートメントでは、Inの後ろに配列を指定することもできます。このときも、制御変数にはバリアント型を指定しなければなりません。



上図では、Split関数の返り値を格納した変数tmpが配列になります。For Eachステートメントで、配列tmp内の要素をひとつずつ操作しているのですが、制御変数の型を文字列型(String)としたためにエラーが発生しました。配列の要素は文字列なのですから、制御変数の型も文字列型でよさそうなものですが、For Eachステートメントで使用する制御変数は「オブジェクト型かバリアント型」と決まっています。ここではオブジェクト型を指定できませんから、バリアント型にしなければなりません。



このエントリーをはてなブックマークに追加