VBA開発環境(VBE)にオリジナルの機能を追加する

VBA開発を行うに当たって、特に厄介な課題の一つとして挙げられるのが「開発環境であるVBEがショボすぎる」という事である。

VBEをカスタマイズする方法は色々あるが、ここでは「開発環境（VBE）にコマンドを追加して自作のVBAプログラムを実行する」方法を紹介する。

---

きっかけ

以前よりVBAには「実行中のプロシージャを特定する術が無い」という課題があった。

差し当たっての解決策として、私はプロシージャ流入時に下記の文を記載している。

Const PROC_NAME = "functionName"

また、モジュール先頭にも同様の文を記載している。

Const MODULE_NAME = "ModuleName"

これにより、デバッグログを残す際に下記のような出力が出来るようにしていた。

Debug.Print MODULE_NAME, PROC_NAME, "hogehoge"

しかし、新たに関数を作成したときや、リファクタリングにより名称を変更した際に都度メンテするのは大変である。

そこで、

VBAのソースコードをVBAから変更することはできないか

という発想に至るのは、想像に難くない。

しかし、VBE上で任意のプロシージャを F5 マクロ から探し出して実行するのは大変だ。

イミディエイトから叩くという手もあるが、ログなどで流れてしまうので繰り返し実行するのは面倒だ。

しかも異なるプロジェクトだと操作が増えてさらに面倒だ。

VBEのツールバー、メニューバーまたはショートカットキーを登録して、気軽に実行するようには出来ないだろうか。

実はVBAならそれが出来る

そのために必要な課題は以下の3つ

• ソースコードを書き換えるVBA
• VBEに機能を追加するVBA
• ショートカットキーを割り当てる設定

準備

詳しい説明に入る前に、注意事項を一つ。

VBEを操作するにはVBAのセキュリティレベルを下げる必要がある。

これにより、ウイルスがVBAのソースコードを悪意のあるコードに書き換えて、エクセルファイル等に容易に感染させることが可能となる。

非IT企業でVBAを書く人は、社内ではITスキルが高い人のはずである。もし開発者からウイルスが蔓延したとなっては今後の立場が危うくなるので、じゅ～～～ぶんに注意して欲しい。

実行時エラー'1004': プログラミングによる Visual Basic プロジェクトへのアクセスは信頼性に欠けます

が出た時は、Excel側のセキュリティ設定を落とす必要がある。

１．「ファイル」メニューの「オプション」の「セキュリティ センター」の「セキュリティ センターの設定」ボタンを実行 ２．「マクロの設定」の「開発者向けのマクロの設定」の「VBA プロジェクト オブジェクト モデルへのアクセルを信頼する」にチェックを入れ「OK」ボタンを実行

（CodeModule 等で）ユーザー定義型は定義されていません。

が出た時は参照設定が足りていない。

VBEで目的のプロジェクトがアクティブな状態で、「ツール」の「参照設定」メニューで「Microsoft Visiual Basic for Applications Extensibility」を追加

ソースコードを書き換えるVBA

実はソースコードを書き換えるのは簡単である。

例えば下記のコードを実行するとソースコードを読み取れたのが分かるはずだ。

?ThisWorkbook.VBProject.VBComponents(1).CodeModule.Lines(1,10)

例えば下記のコードを実行すると、ThisWorkbookモジュールの1行目が「Option Explicit」に置換されるのが分かるはずだ。

※行の文字列の置き換えなので、ソースコードが極端に破壊されることはないはずだが注意すること。

Call ThisWorkbook.VBProject.VBComponents(1).CodeModule.ReplaceLine(1,"Option Explicit")

本題のConst PROC_NAME = "functionName"の更新プログラムだが、StackOverflow のソースコードを拝借して、Access用かつバグが見受けられたので色々と修正してExcelで動くようにした。

ただし、このコードは既に記載されている Const PROC_NAME = "functionName" の名称を更新してくれるだけなので、リファクタリングでしか役に立たない。

後日 Const PROC_NAME = "functionName" を挿入するプログラムも作りたい。

Sub 全てのPROC_NAMEを更新()
    Call FixAllProcNameConstants()
End Sub

	Option Explicit
	'vbeProcedureオブジェクトを作成してそれらのコレクションを返す
	'モジュール中のコードを解析し、モジュール内のプロシージャ情報をリストアップする
	Private Function getProcedures(codeMod As CodeModule) As Collection
	Dim StartLine As Long
	Dim ProcName As String
	Dim lastProcName As String
	Dim lastProcKind As vbext_ProcKind
	Dim procs As New Collection
	Dim proc As vbeProcedure
	Dim ProcKind As vbext_ProcKind
	Dim i As Long
	' Option句の行の省略
	StartLine = codeMod.CountOfDeclarationLines + 1
	'各プロシージャの最初行でオブジェクトを作成しコレクションに追加
	For i = StartLine To codeMod.CountOfLines
	ProcName = codeMod.ProcOfLine(i, ProcKind)
	If ProcName <> lastProcName Or ProcKind <> lastProcKind Then
	Set proc = New vbeProcedure
	proc.initialize ProcName, codeMod, ProcKind
	procs.Add proc
	lastProcName = ProcName
	lastProcKind = ProcKind
	End If
	Next
	Set getProcedures = procs
	End Function
	'Const PROC_NAME = "プロシージャ名"を更新する。
	Private Sub fixProcNameConstants(codeMod As CodeModule)
	Dim procs As Collection
	Dim proc As vbeProcedure
	Dim i As Long
	'プロシージャ情報を管理するオブジェクトコレクションを取得
	Set procs = getProcedures(codeMod)
	Debug.Print
	For Each proc In procs
	With proc
	Debug.Print .ProcKind, .StartLine, .CountOfLines, .EndLine, .ParentModule, .Name
	For i = .StartLine + 1 To .EndLine
	If InStr(1, .ParentModule.Lines(i, 1), "Const PROC_NAME", vbTextCompare) Then
	' Debug.Print .ParentModule.Lines(i, 1)
	.ParentModule.ReplaceLine i, "Const PROC_NAME As String = " & Chr(34) & .Name & Chr(34)
	' Debug.Print .ParentModule.Lines(i, 1)
	Exit For
	End If
	Next
	End With
	Next
	End Sub
	'全てのPROC_NAMEを更新する
	'・モジュール中に「Const PROC_NAME = ""」の記載がある時、
	'　ダブルクォーテーション内のプロシージャ名を現時点の名前に更新する
	'・ただしDevUtilitiesモジュールは除く
	Public Sub FixAllProcNameConstants()
	Dim prj As VBProject
	Set prj = Application.VBE.ActiveVBProject
	Dim codeMod As CodeModule
	Dim vbComp As VBComponent
	For Each vbComp In prj.VBComponents
	Set codeMod = vbComp.CodeModule
	If Not codeMod.Name = "DevUtilities" Then
	fixProcNameConstants codeMod
	End If
	Next
	End Sub

view raw DevUtilities.vba hosted with ❤ by GitHub

	' Class: vbeProcedure
	' requires Microsoft Visual Basic for Applications Extensibility 5.3 library
	' Author: Christopher J. McClellan
	' Creative Commons Share Alike and Attribute license
	' http://creativecommons.org/licenses/by-sa/3.0/
	Option Explicit
	Private Const vbeProcedureError As Long = 3500
	Private mParentModule As CodeModule
	Private isParentModSet As Boolean
	Private mName As String
	Private isNameSet As Boolean
	Private mProcKind As vbext_ProcKind
	Private isProcKindSet As Boolean
	Public Property Get Name() As String
	If isNameSet Then
	Name = mName
	Else
	RaiseObjectNotIntializedError
	End If
	End Property
	Public Property Let Name(ByVal vNewValue As String)
	If Not isNameSet Then
	mName = vNewValue
	isNameSet = True
	Else
	RaiseReadOnlyPropertyError
	End If
	End Property
	Public Property Get ProcKind() As String
	If isProcKindSet Then
	ProcKind = mProcKind
	Else
	RaiseObjectNotIntializedError
	End If
	End Property
	Public Property Let ProcKind(ByVal vNewValue As String)
	If Not isProcKindSet Then
	mProcKind = vNewValue
	isProcKindSet = True
	Else
	RaiseReadOnlyPropertyError
	End If
	End Property
	Public Property Get ParentModule() As CodeModule
	If isParentModSet Then
	Set ParentModule = mParentModule
	Else
	RaiseObjectNotIntializedError
	End If
	End Property
	Public Property Let ParentModule(ByRef vNewValue As CodeModule)
	If Not isParentModSet Then
	Set mParentModule = vNewValue
	isParentModSet = True
	Else
	RaiseReadOnlyPropertyError
	End If
	End Property
	Public Property Get StartLine() As Long
	If isParentModSet And isNameSet Then
	StartLine = Me.ParentModule.ProcStartLine(Me.Name, Me.ProcKind)
	Else
	RaiseObjectNotIntializedError
	End If
	End Property
	Public Property Get EndLine() As Long
	If isParentModSet And isNameSet Then
	EndLine = Me.StartLine + Me.CountOfLines
	Else
	RaiseObjectNotIntializedError
	End If
	End Property
	Public Property Get CountOfLines() As Long
	If isParentModSet And isNameSet Then
	CountOfLines = Me.ParentModule.ProcCountLines(Me.Name, Me.ProcKind)
	Else
	RaiseObjectNotIntializedError
	End If
	End Property
	Public Sub initialize(Name As String, codeMod As CodeModule, ProcKind As vbext_ProcKind)
	Me.Name = Name
	Me.ParentModule = codeMod
	Me.ProcKind = ProcKind
	End Sub
	Public Property Get Lines() As String
	If isParentModSet And isNameSet Then
	Lines = Me.ParentModule.Lines(Me.StartLine, Me.CountOfLines)
	Else
	RaiseObjectNotIntializedError
	End If
	End Property
	Private Sub RaiseObjectNotIntializedError()
	Err.Raise vbObjectError + vbeProcedureError + 10, ThisWorkbook.Name & "." & TypeName(Me), "Object Not Initialized"
	End Sub
	Private Sub RaiseReadOnlyPropertyError()
	Err.Raise vbObjectError + vbeProcedureError + 20, ThisWorkbook.Name & "." & TypeName(Me), "Property is Read-Only after initialization"
	End Sub

view raw vbeProcedure.vba hosted with ❤ by GitHub

VBEに機能を追加するVBA

VBEの機能追加については、

http://suyamasoft.blue.coocan.jp/ - エクセルVBA - サンプル - VBProject

に素晴らしいサンプルがあるので、これを使わせて頂いた。

元記事のソースコードの利用規約が不明なので、リンク先の紹介に留める。

でも、そのうちオリジナルのソースコードの書き下ろしが終わったら公開する。

ショートカットキーを割り当てる設定

VBEのコマンドにショートカットキーを割り当てる方法は、以前下記の記事で紹介した。

www.excel-chunchun.com

簡単に箇条書きにしておくと、以下の設定・条件が必要

1. ツールバーにコマンドを設置
2. コマンドボタンの表示スタイルを「イメージとテキスト」に変更する
3. コマンドボタンの名称に (&A) のようなアクセスキー文字列を追記する
4. VBEのウィンドウサイズに余裕をもたせて、必ずコマンドボタンが画面上に表示された状態にする
5. VBEのメニュー等で同じアクセスキーを使用していない

以上の条件を満たす時、コマンドを Alt+A のような形で実行できるようになる。

まとめ

PROC_NAMEが自動で記入されるようになれば、正確なログを出力できるようになる。

つまりはデバッグが容易となり、開発効率が向上する。

今後はガンガン使っていきたい。

もっと早く作ればよかった。

以上

---

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)