Excel VBA オブジェクトの指定方法と速度

質問

Excel VBAについて質問です。
ワークシートを指定するときの書き方には色々ありますが、１～３の速度の順番はどうなりますか？

オブジェクトは、変数に入れて使用したほうが速度が速くなるそうなので、
１より２のほうが速いと思いますが、３はどうでしょうか？

１．Worksheets("sheet1")

２．Dim ws As Worksheet
　　Set ws = Worksheets("sheet1")

３．Sheet1　（VBE画面で表示されるシートのプロパティのオブジェクト名）

Excelのオブジェクトについて詳しいかたがいらっしゃいましたら教えてください。
よろしくお願いいたします。

回答No.3

real beatin（@realbeatin） · ベストアンサー

No.2 です。お礼欄、補足欄、拝見しました。

> しかしなぜかApplication.Run arrProc(i)でエラーが発生して動作させることができませんでした。。

どのようなエラーでしょうか。
｜　実行時エラー '1004'
｜
｜　マクロ 'test0' を実行できません。このブックでマクロが使用できないか、またはすべての
｜　マクロが無効になっている可能性があります。
添付画像で示した実行時エラーでしたら、
複数のモジュールにまたがってプロシージャ名が重複している場合に
簡単に再現されるものです。
対策としては、同一VBProject内に同名のプロシージャ名がないように命名し直すか、
簡単に済ませるなら、
>　　　　　　Application.Run arrProc(i)
に対して、モジュール名を明示するように書き直す方法があります。
仮に、
私が提示したプロシージャすべてが、
[Module3]という標準モジュールに書かれている場合でしたら、
　　　　　　Application.Run "Module3." & arrProc(i)
のように、モジュール名の末尾に"."を付加したものを文字列として書き加えれば、
プロシージャの呼び出しが適切に行われるようになります。
エラーに関しては、エラーメッセージの内容によって対応が異なります。

> Sheet1とは一体何なのかを知りたかったのです。

VBProject クラスのメンバーとして、
シートオブジェクトが生成される時に（新規ブック作成時やシート追加時）
[Excel Objects]（vbext_ct_Document）タイプのVBComponentが追加され
仮の名前を自動的に振られたものが、[Sheet1] になります。
VBComponentの名前については[ThisWorkbook][Module1][UserForm1]などと同等のもので、
自動的に付番されますが、変更可能な名前です。
一般的な記法で
　　UserForm1.Show
等と同様、CodeModuleに関連付けられたオブジェクトを参照する際にも
この名前を「オブジェクト名」として用いることができます。
　　Sheet1.Select
などのように、、、。
VBProject で定義された オブジェクト ≒ クラス としての
　　Sheet1
です。
VBComponent の名前が、そのままオブジェクト名になります。

> Sheet1もオブジェクト変数なのではないか

習熟の段階に合わせて「円周率は３」のように、
正しくはないけれど、一旦はそういうことにしておいて次の段階に進むことを
優先させるような教え方としては、「Sheet1もオブジェクト変数」という
覚え方でも支障はないような気はします。
実行時に格納される値（オブジェクト）を変更可能なのが変数です。
VBProject で定義された オブジェクト
ですので、実行時のアクセスもVBから見れば直接的で単経路になります。
実際的には、プロシージャやモジュールで定義・設定されている変数の方が
より単経路になる筈ですから微かにアクセスは速いようですし、
変数に格納した方が、名前の衝突や各種オーバーヘッドを事前に回避する意味があります。

> CodeNameの意味
Worksheet クラス（Chart クラス）の CodeName プロパティです。
VBProject で定義された オブジェクト名＝VBComponent名を返します。
以下、引用
｜Excel 開発者用リファレンス
｜Worksheet.CodeName プロパティ
｜オブジェクトのコード名を返します。値の取得のみ可能です。文字列型 (String) の値を使用します。
｜構文
｜式.CodeName
｜
｜式 Worksheet オブジェクトを返すオブジェクト式を指定します。
｜
｜備考
｜コード名は、選択したオブジェクトの [プロパティ] ウィンドウの Name プロパティで確認できます。デザイン時に、このプロパティの値を変更してコード名を変更することができます。実行時に、このプロパティの値をコードで変更することはできません。
｜オブジェクトのコード名はオブジェクトを表すオブジェクト式の代わりに使用できます。たとえば、ワークシートのコード名が "Sheet1" である場合、次の式は同じです。
｜Worksheets(1).Range("a1")
｜Sheet1.Range("a1")
｜シート名とコード名は異なる場合もあります。シートの作成時は、シート名とコード名は同じです。しかし、シート名を変更してもコード名は変更されません。また、Visual Basic Editor の [プロパティ] ウィンドウでコード名を変更してもシート名は変更されません。
｜使用例
｜次の使用例は、ワークシート 1 のコード名を表示します。
｜MsgBox Worksheets(1).CodeName
以上引用。
ヘルプでのここら辺の扱いについては、[コード名]のような用語が統一されていないようなので、
こちらでは積極的に紹介しなかったのですが、
今回のお尋ねについて一通り解説した内容ではあります。
私の説明もうまくないように感じていますが、伝わるものがあるといいです。

お礼

お礼が遅くなり申し訳ありません。
エラーの件、恥ずかしながらご指摘の通りで無事動かすことができました。
とても詳しい解説ありがとうございます！！
完全に理解したとは言えませんが、なんとなくわかったような気がします。

VBComponentであるSheet1より、プロシージャやモジュールで定義・設定されている変数wksのほうが単経路で速いのですね。
なぜ後者のほうが単経路になるのかまでは理解できませんでしたが、そういうものだというふうに捉えるべきでしょうか？

私はオブジェクトやクラスについて、まだまだ勉強が足りません。。
ただ動けばよいではなく、きちんとした美しいPGMを書けるようになりたいです！

回答No.4

mitarashi

#1です
>もしかして３(Sheet1)もオブジェクト変数なのでしょうか。。。？

Sheet1はオブジェクトそのものというかインスタンスじゃないのかなと考えて検索してみました。参考URLに深～い話が載っています。
「Sheet1はクラス(雛形)であり、同名のインスタンス（雛形を元に生成したオブジェクト）でもある。Sheet1のシートモジュールにプロシージャを追加できるのは、クラスだから。プロシージャを追加したりすると、インスタンスが再生成されるのでSheet1インスタンスのメモリ上のアドレスが変わる。ユーザーがアクセスするのはクラス名と同名のインスタンスの方である。」
そう言われると、成る程と思えます。文中ではオブジェクト参照としているのを敢えてインスタンスに書き換えてみましたが、このオブジェクト参照というのはオブジェクト変数とは違い、いわゆるオブジェクトと思います。と、言うわけで「オブジェクト変数」では無いと思います。

参考URL：

http://excelappwithvba.web.fc2.com/managing_excel_objects/study_of_excel_objects.html

お礼

またまたの回答ありがとうございます！！
ご紹介していただいたサイトを読みました。
まさに私が疑問に思っていたことについて書かれていました。
なるほど、変数ではなくオブジェクトそのものということですね！
これはつまりNo.3の方のおっしゃっているVBComponentオブジェクトになるのかなと思いました。

Excelは奥が深いですね。まだまだ勉強が足りません。。

回答No.2

real beatin（@realbeatin）

こんにちは。
回答書いている間に先着回答がありましたが（ネタカブリもありますが）、
せっかく書いていたのでお邪魔します。

ひとまず直接の回答として、以下のmainプロシージャを実行して、
イミディエイトウィンドウで結果を確認してください。

' ' ///
Option Explicit

#If VBA7 Then '
　　Private Declare PtrSafe Function timeGetTime Lib "winmm.dll" () As Long
#Else
　　Private Declare Function timeGetTime Lib "winmm.dll" () As Long
#End If

Private wks As Worksheet
Private arrTime() As Long
Private buf
Private Const T As Long = 100000　'　試行数は適当に

Sub main()
Dim arrProc()
Dim nUB As Long
Dim i As Long
Dim j As Long
　　Set wks = Sheets("Sheet1")
　　arrProc() = VBA.Array("test0", "test1", "test2", "test3", "test4", "test5", "test6")
　　nUB = UBound(arrProc())
　　ReDim arrTime(nUB, 0 To 1)
　　For i = 0 To nUB
　　　　arrTime(i, 0) = timeGetTime()
　　　　For j = 1 To T
　　　　　　Application.Run arrProc(i)
　　　　Next j
　　　　arrTime(i, 1) = timeGetTime()
　　Next i
　　Set wks = Nothing
　　For i = 0 To nUB
　　　　Debug.Print "test" & i, arrTime(i, 1) - arrTime(i, 0); "milli-Sec."
　　Next i
End Sub

Private Sub test0()
　　buf = wks.Name
End Sub
Private Sub test1()
　　buf = Sheets("Sheet1").Name
End Sub
Private Sub test2()
　　buf = Worksheets("Sheet1").Name
End Sub
Private Sub test3()
　　buf = Sheet1.Name
End Sub
Private Sub test4()
Dim o As Worksheet
　　Set o = Worksheets("Sheet1")
　　buf = o.Name
End Sub
Private Sub test5()
　　buf = Sheets(1).Name
End Sub
Private Sub test6()
　　buf = Worksheets(1).Name
End Sub
' ' ///

あらためて、ご質問の意図を量りかねる面もあるのですが、
基本的にＶＢＡで"速さ"を語る場合は、
同一のオブジェクトへの参照を繰り返さない、というのが大原則です。
そういった意味からすると、
worksheetオブジェクトへのアクセスタイムが問題になるような場面というのは、
worksheetが数千あるような場合に限られるでしょうし、
そのようなブック仮に作成出来たとしてもマクロ以前にブックが使い物にならないでしょう。

test0で示したように、繰り返し参照するものは変数に格納しておく、
というようなことぐらいしか、
シート参照に関しては大してアドバイス出来るようなコツみたいなものはありません。

Sheet1をリテラルではなく変数で扱う場合でしたら、
私は基本的にSheetsで指定します。

Sheet1 のようにCodeNameでオブジェクトを呼び出せるのなら、
アクセス自体は速いですけれど、
ブックのウィンドウに表示される名前を直接編集できるName、と、
主にVBEプロパティページから変更可能なオブジェクト名としてのCodeName、と、
それぞれが別の名前になることを想定すると、扱い難いですし混乱の種です。
一度しか使わないマクロを短時間で書きたい時ぐらいにしか、
CodeNameでオブジェクトを呼び出すことは（私は）しません。
解答に書く人も滅多に見掛けないですよね。

とりあえず、
worksheetオブジェクトへのアクセスタイムの差というのは無視した方が好いように思います。
変数やWithブロックを使うなどしてアクセス回数を減らすことを重視してあげてください。
或いは、こちらが想定できていないような
差が出る場面がある、ということでしたらば、補足欄にでも書いてみて下さい。

お礼

ご回答ありがとうございます！
しかしなぜかApplication.Run arrProc(i)でエラーが発生して動作させることができませんでした。。

補足

質問の意図としては、3.のSheet1とは一体何なのかを知りたかったのです。CodeNameの意味もよくわかっていません。。
Worksheets("Sheet1")を何度も書くと遅くなるのは、その都度インスタンスが作成されるからではないかと思っていました。変数に入れて使いまわして速くなるのは、インスタンスの作成が１度で済むからですよね？
回答1の方の測定結果から、もしかして3.のSheet1もオブジェクト変数なのではないかと思えてきました。

回答No.1

mitarashi

詳しくないので試してみました
dim buf as string
buf = Worksheets("Sheet1").Name
といった操作を100万回やらせてみました。(Win7Home-64bit,xl2010-32bit,Core-i5 3.2GHz)
1.　Worksheets("Sheet1").Name　3.042秒
2.　ws.Name　　　　　　　　　　0.107秒
3.　Sheet1.Name　　　　　　　　0.110秒
実行の最初の数回は捨て、5回の測定値の平均です。
Worksheets("Sheet1")は結構遅いのに驚きました。
2と3は最初dim buf as variantでやったときは殆ど一緒でした。
ご参考まで。

試験したコードは下記の通りです。既に出典は分かりません。
'高分解能パフォーマンスカウンタ用API
Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long

Sub test()
Dim curStartTime As Currency
Dim curEndTime As Currency
Dim curFreq As Currency
Dim ws As Worksheet
Dim buf As String
Dim i As Long

'高分解能パフォーマンスカウンタの周波数を取得
If QueryPerformanceFrequency(curFreq) = 0 Then
MsgBox "ハードウェアが高分解能パフォーマンスカウンタをサポートしていません！", vbCritical
Exit Sub
End If
Set ws = Worksheets("Sheet1")
'開始時刻を取得
Call QueryPerformanceCounter(curStartTime)
For i = 1 To 1000000
buf = Worksheets("Sheet1").Name
' buf = Sheet1.Name
' buf = ws.Name
Next i
'終了時刻を取得
Call QueryPerformanceCounter(curEndTime)
'結果の表示
Debug.Print (curEndTime - curStartTime) / curFreq
End Sub

お礼

ご回答ありがとうございます！
２と３は変わらないのですね！
２はオブジェクト変数ですが、もしかして３もオブジェクト変数なのでしょうか。。。？