読者です 読者をやめる 読者になる 読者になる

t-hom’s diary

主にVBAネタを扱っているブログです。

VBA 参照設定にマクロで根こそぎチェックをいれてみた。

VBEの参照設定の画面はお世辞にもよくできているとは言い難い。
膨大な項目があるのに基本的に目視でスクロールして探す必要があるからだ。

大体使いたいものは「Microsoft~」なので、「m」キーを押すと頭文字Mの最初の行までは飛ぶ。
f:id:t-hom:20160209035636p:plain

これがフォルダとかなら、すばやく「mic」と打てばMicrosoftまでは飛んでくれるのだが、この参照設定では「mi」と入れたタイミングで頭文字Iに飛んでしまうのだ。

はっきり言って、イケてない。せめて検索機能が欲しい。
そうだ、自分で作ろう!

というのが今回の発端。

まだ完成はしていないが、マクロで根こそぎ参照設定するところまでできた。

この参照設定は、レジストリのHKCR\TypeLibを見ている。
VBAレジストリを読めれば、ライブラリの実態ファイルのパスを取り出すことができ、ライブラリのファイルパスが分かればVBAで参照設定できるのだ。

実際にやってみたのがこちらのコード。
ただし、参照設定を外すコードを用意してないので、実行の前に全ての重要なExcelファイルを閉じて、新規のExcelファイルで試すと良い。

Sub 根こそぎ参照設定()
    Dim レジストリ As Object: Set レジストリ = _
        CreateObject("WbemScripting.SWbemLocator") _
            .ConnectServer(, "root\default") _
                .Get("StdRegProv")

    Const HKCR = &H80000000
    Dim TypeLibの子, TypeLibの孫,,, 値
    
    レジストリ.EnumKey HKCR, "TypeLib", TypeLibの子
    For EachIn TypeLibの子
        レジストリ.EnumKey HKCR, "TypeLib\" &, TypeLibの孫
        If Not IsNull(TypeLibの孫) Then
            For EachIn TypeLibの孫
                レジストリ.GetStringValue HKCR, "TypeLib\" && "\" && "\0\win32", ,On Error Resume Next
                    ActiveWorkbook.VBProject.References.AddFromFileOn Error GoTo 0
            Next
        End If
    Next
End Sub

また、実行するためにはセキュリティ設定でVBAプロジェクトオブジェクトモデルへのアクセスを信頼するにチェックをつけておく必要がある。
f:id:t-hom:20160209040445p:plain


さて、実行の結果がこちら。スクロールバーに注目してほしい。
f:id:t-hom:20160209040738p:plain

ご覧のとおり殆どの参照設定にチェックが入っている。
その数なんと674!!

あ…でも根こそぎではなかった。すんません。


参照設定は以下のマクロで書きだすことができる。
Microsoft Visual Basic for Applications Extensibility 5.3の参照が必要。

Sub 参照設定書き出し()
    Dim Ref As Reference
    i = 2
    For Each Ref In ThisWorkbook.VBProject.References
        Sheet1.Cells(i, 1).Value = Ref.Description
        i = i + 1
    Next Ref
End Sub

Excelに書き出してみたところ。
f:id:t-hom:20160209041522p:plain

10分の1サイズ。
f:id:t-hom:20160209041816p:plain


さて、さすがにこのままでは使い物にならないので、インターフェースを作って取捨選択できるようにしたい。

VBAからレジストリを参照するために参考にしたサイト】
www.wmifun.net
www.wmifun.net

当ブログは、amazon.co.jpを宣伝しリンクすることによってサイトが紹介料を獲得できる手段を提供することを目的に設定されたアフィリエイト宣伝プログラムである、 Amazonアソシエイト・プログラムの参加者です。