Excel VBA 備忘録 基礎知識編

ときどきExcelのVBAを書きますが、普段使わない言語だとなにかとつまづきます。

自分自身のための備忘録ですが、きっと初心者ならだれもつまづくところだと思います。

VariantとObject
    Variant
　    すべての変数。

　    Integerなどの値のみを持つプリミティブなデータ型とオブジェクト型のどちらも代入可能。

       配列もプリミティブなデータ型なのでVariantで宣言した変数にしか代入できない。    

    Object

　    データとプロパティー、メソッドがカプセル化されたもの。

       オブジェクト型のみ代入可能。
       これにプリミティブなデータを代入すると「オブジェクトが必要です」エラーとなる。

Null, Nothing, Empty, vbNullString の違い

　Null             Variant型変数内に有効な値が入っていない状態

　Nothing       Objectとして空の状態

　Empty         Variant型として空の状態

   vbNullString 値が０のString 

代入時にSetが必要な場合、不要な場合

   Setが必要な場合

        Objectに代入する場合で、値だけでなくObjectのプロパティーの設定が行われる。

　Setが不要な場合

　    Integerなどのデータ型に値のみ代入する場合。

Sub呼び出しでCallが必要な場合

　引数を()でくくる場合。

コンパイルエラー：プロパティーの使い方が不正です

    Setを付けずにObjectへ代入しようとした場合。

コンパイルエラー：型が一致しません

　Objectで宣言した変数ににIntegerなどの値をSetしようとした場合。

　　Dim o as Object: Set o = 1

コンパイルエラー：オブジェクトが必要です

   Variantで宣言した変数に値をSetしようした場合。

　　Dim v as Variant: Set v = 1

　Stringで宣言した変数にSetで文字列を代入しようとした場合。

　　Dim s as String: Set s = "str"

コンパイルエラー：ユーザ定義型は定義されていません

    Dimや引数の型宣言で規定のデータ型にもユーザ定義型にもない名前が使われている場合。

    だいたいは書き間違え。

実行時エラー：オブジェクトが必要です

    値がNothingのオブジェクトのプロパティーにアクセスした場合。

    Objectで宣言した変数にプリミティブなデータを代入しようとした場合。

    Objectで宣言した変数にプリミティブなデータを代入しようとした場合。

実行時エラー：オブジェクト変数またはWithブロック変数が設定されていません。

    Objectで宣言した変数にSetをつけずにオブジェクト、または値を代入しようとした場合。

    　Dim o as Object: o = "a" 

    　Dim o as Object: o = 1 

実行時エラー　400

VB Excelのシートを名前で探す方法 高速版

VBAでExcelのシートを名前で探す方法を調べると、たいていループで名前が一致するものを探す方法が紹介されています。

たとえばこんなコードです。

    Sub Sample1(name As String)
　     For i = 1 to Worksheets.Count
            if Worksheets(i).name = name Then
                Debug.Print Worksheets.Index
                Exit For
            End If
        Next
    End Sub

一回だけ実行する場合は十分高速で問題ないでしょう。しかしループの中で使用すると繰り返し実行されるため、場合によってはかなり時間がかかります。 かつ結果"シート名"と一致するものがない場合はシート数Ｘループ回数実行され、無駄に時間を浪費します。

上記の例ではWorksheets(i)と引数にindexを使用しています。またiの範囲がCountの範囲内なので、Worksheet(i)がエラーを起こすことはありません。

では、Worksheetsは引数にシート名を使用することもできるので、次のようなコードを試してみましょう。

    Dim sheet As Worksheet
    Set sheet = Worksheets("シート名" )

"シート名"のWorksheetが存在する場合は問題ないのですが、存在しない場合は「インデックスが有効範囲にありません。」というエラーが発生します。Worksheet(i)でもiが有効範囲外であれば同じエラーが発生しますので、内部では同様のループ処理が行われていることが想像できます。

今度は、つぎのような方法を試してみます。

    Sub Sample2(name As String)
        On Error GoTo NotFound
        Debug.Print Worksheets(name).Index
    NotFound:
    End Sub

これでエラーが回避でき、コードもすっきりしているかと思います。面白いことに、計測してみると単純ループの５５～６０％程度の処理速度となります。７５～８０％のスピードアッ！！とも言えます。内部的にはループと同等のことを行っていると思いますが、コンパイル済みかどうかの差が大きいのでしょう。

ループによる実行回数が数千回になるような場合は、数単位での差になるのでバカにできません。

このひとつ前のブログ『InternetExplorer.getElementById 「オブジェクトが必要です」エラー対策』で使用した「On Error Resume Next」も試してみました。

    Sub Sample3name As String)
        Dim sheet As Worksheet
        On Error Resume Next
        Set sheet = Worksheets(name)
        If Not sheet Is Nothing Then
            Debug.Print Worksheets(name).Index
        End If
    End Sub

これは処理速度的にはSample2と同等でした。この例の場合ではSample2の方がコード的にもすっきりしていますが、使用する場面では使い道があるかもしれせん。

---------------------

おそらくほとんどの場合は上記の方法で十分高速になると思いますが、100シート超えで千回超えの検索をするような場合にはDictionaryを使うとさらに高速になります。シート数が少ないとかえって逆効果になることもあるでしょう。

Dim SheetNameDict As Object

Rem シート名がnameのWorksheetを返す。
Function GetSheetByName(name As Variant)
    Rem SheetNameDict初期化。SheetNameDict未設定の場合、
    Rem SheetNameDictに全ワークシートのKey＝name, Value = sheetをセット。
    If SheetNameDict Is Nothing Then
         Set SheetNameDict = CreateObject("Scripting.Dictionary")
         For Each sheet In Worksheets
            Call SheetNameDict.Add(sheet.name, sheet)
         Next sheet
    End If

    If SheetNameDict.Exists(name) Then
        Set GetSheetByName = SheetNameDict(name)
    Else
        On Error GoTo NotFound
        Dim sheet As Worksheet
        Set sheet = Sheets(name)
        Set GetSheetByName = sheet
        Call SheetNameDict.Add(name, sheet)
        Exit Function
NotFound:
        Set GetSheetByName = Nothing
    End If
End Function

使用例
Dim sheet as Worksheet
Set sheet = GetSheetByName("someSheetName")
if Not sheet is Nothing then ....

なお、このサンプルの場合は途中でSheetが削除されるとその後存在しないSheetを返すことになるので、Sheetを削除すると同時にSheetNameDict の項目も削除します。

Call SheetNameDict.Remove(name)

VB InternetExplorer.getElementById 「オブジェクトが必要です」エラー対策

VB InternetExplorer.getElementById 「オブジェクトが必要です」エラー対策

VBの InternetExplorer.getElementById は該当する要素がない場合に「オブジェクトが必要です」が発生する。これの扱いに困ったが、次の要領で対処できた。

まずは、次のようなコードでエラーが発生する。

    Dim elm As IHTMLElement
    Set elm = ie.document.getElementById("someID")

要素がない場合にgetElementById はNullを返す。

Nullを代入できるのはVariant型だけなので、IHTMLElementとかObjectで宣言した変数に代入しようとすると「オブジェクトが必要です」が発生する。

そこで、次のようなコードにしてみる。

    Dim elm As Variant
    Set elm = ie.document.getElementById("someID")

こんどは「型が一致しません」エラーが発生する。
これはSetではNullをObjectとして扱おうとするため発生する。

それでは、これではどうだろう。

    Dim elm As Variant
    elm = ie.document.getElementById("someID")
    if IsNull(elm) Then Exit Sub

    Dim divs as IHTMLElementCollection
    Set divs = elm .getElementsByTagName("div")

Nullのときの処理はうまくいくが、今度はelmがObjectとして設定されていないため、後続のコードで「オブジェクトが必要です」エラーが発生する。

そこで次のように変更し、エラー発生時に処理を継続してみた。

    On Error Resume Next
    Set elm = ie.document.getElementById("someID")
    If IsNull(elm) Then Exit Sub

    Set divs = someID.getElementsByTagName("div")

一応うまく処理できるようになったが、ちょっとおかしい。

IsNull(elm)がTrueにならない。この場合、elｍがEmpty値になっている。

Setで代入するので、elmはObjectでなければならず、Nullにはならないということですね。

そのまま後続処理に進んでも上記のコードではエラーは発生しないが、具合が悪い場合もあるかもしれない。

そこで、IFの判別を次のようにすると有効になる。

    On Error Resume Next

    Dim elm As Object, divs as IHTMLElementCollection
    elm = ie.document.getElementById("someID")
    If elm Is Nothing Then Exit Sub 

On Error Resume Nextとした結果 elm がEmpty値になるため、elmをObjectで宣言している。

次の式でも判別できる。

    If IsEmpty(elm) Then Exit Sub

NothingはObjectとして空の状態、EmptyはVariant型として空の状態ということですね。Set式のあとなので実際にはObjectとして扱ってよいと思いますが、Dimで宣言した型に合わせるのがコード的には一貫性があると言えるでしょう。

実際にはNullになることはないが、次の方がより安全かもしれない。

    If IsNullOrEmpty(elm) Then Exit Sub

なお、

    On Error Resume Next

でエラーを無視するようにしたので、前後の処理の関係ではこのあとで再度On Errorを設定しなおす必要があるでしょう。