Using the VBA Extensibility Library

Introduction

What’s the VBA Extensibility Library you say? It’s the library that provides you access to everything in the VBA Editor, including the Code Panes, the Windows, and the VBA Projects themselves. You can modify all these objects. For example, you can find out what text the user has highlighted, you can list all the components of a project including the UserForms, Code Modules, and Class Modules, and—perhaps the most exciting thing—you can modify the code in the VBA modules. This is all done through the global VBE object.

We’re going to zero in on the CodeModule object within the VBE object hierarchy. A CodeModule object provides you with the ability to list all the procedures, to add code, to delete code and to replace code. One practical use of this is to create your own Code Commenter or Error Handler for use in your projects.

Security Issues

Another practical use of this is to create viruses! That's why, by default, Office 2002 (XP) and later versions do not allow you to modify your VBA Projects in code at run-time without setting a security option. According to the Microsoft Knowledge Base, Office applications prior to Office 2002 (XP) allowed this kind of access by default. But on Office 2002 (XP) and later versions, you will need to turn on the following setting on your development machine and also on any machine on which you wish any code that modifies other code to run.

You'll know you need to turn on the following setting if, while trying to modify code, you receive Run-Time Error 6068, "Programmatic access to Visual Basic project is not trusted."

To allow access, use the following procedure:

1. On the Tools menu, point to Macro, and then click Security.
2. On the Trusted Sources tab, select the Trust access to Visual Basic Project check box.

Note that doing this presents a bit of a "security hazard" because it makes it a bit easier for potential viruses to gain access to code modules in Microsoft Office applications. You'll have to make educated decisions about security procedures and risks appropriate to each situation and environment. Please refer to the Microsoft Knowledge Base Article 282830 - PRB: Programmatic Access to Office XP VBA Project Is Denied for information straight from the source.

Delving In...

Let’s look at the VBE object a little more closely.

First of all, you can refer to the VBE object in your code simply as VBE, or as Application.VBE if you are so inclined.

The VBE object contains many useful properties and methods. We’ll discuss ActiveCodePane and ActiveVBProject, two of the most interesting properties.

The ActiveCodePane object refers to the pane of code you are currently looking at within the VBA Editor, while the ActiveVBProject object provides you with access to the entire VB Project you are currently working on. If you want to refer to another CodePane or another VBProject, you can use the CodePanes or VBProjects collections instead.

You then want to find the current CodeModule object. You can get to it through VBE.ActiveCodePane.CodeModule. If you would like to find a different CodeModule, you can use VBE.ActiveVBProject.VBComponents(<module_name>).CodeModule, where module_name is the name of the module, such as "UserForm1", "ThisDocument" or "clsMyClass".

You’ll probably want to create an object variable to keep a reference to the desired CodeModule, or at least use a With statement. For example:

Dim cm as CodeModule
Set cm = VBE.ActiveCodePane.CodeModule

or

With VBE.ActiveCodePane.CodeModule
    ---
End With

Once you have a reference to the CodeModule, the fun begins. Here’s a brief run-down of some of the properties and methods, divided into rough categories:

Code Line Manipulation Methods and Functions

Sub InsertLines(Line as Long, Code as String)

Inserts the specified lines of code at the specified line number (starting with line one). The Code argument may contain multiple lines of code if they are separated by vbCrLf's.

Example:

To add a Sub that will display the current date and time in a message box at line 5:

Call cm.InsertLines (5, "Sub ShowTime()" & vbCrLf & "MsgBox Now" & vbCrLf & "End Sub")

Sub DeleteLines(StartLine as Long, Optional Count as Long = 1)

Deletes the specified (or defaulted) number of lines starting with StartLine.

Examples:

To delete line 5:

Call cm.DeleteLines(5)

To delete three lines starting at line 5:

Call cm.DeleteLines(5, 3)

Sub ReplaceLine(Line as Long, Code as String)

Replaces the specified line with the specified code. The Code argument may contain multiple lines of code if they are separated by vbCrLf's.

Examples:

To replace line 5:

Call cm.ReplaceLine(5, "MsgBox ""New Text""")

To replace line 5 with multiple lines of text:

Call cm.ReplaceLine(5, "x = 5" & vbCrLf & "y = 7")

Function Lines(StartLine as Long, Count as Long) as String

Returns a string containing the text of the specified number of lines, starting at the specified StartLine. If Count is greater than 1, then the returned string is separated by vbCrLf's.

Examples:

To get the code at line 5:

s = cm.Lines(5, 1)

To get the 3 lines of code starting at line 5:

s = cm.Lines(5, 3)

Sub AddFromFile(FileName as String)

Simply put, AddFromFile will insert the contents of a disk file near the top of the CodeModule. The disk file should be an ASCII text file. AddFromFile does not allow you to specify the location at which you would like the code placed. If you want to specify a location for the text, you should probably read the contents of the disk file using disk I/O functions and then use the InsertLines function instead.

Sub AddFromString(String as String)

Again, simply put, AddFromString will insert the contents of the specified string near the top of the CodeModule. You should separate multiple lines by vbCrLf's. Like AddFromFile, AddFromString does not allow you to specify the location at which you would like the code placed. If you want to specify a location for the text, you should probably just use the InsertLines function instead.

Function CreateEventProc(EventName as String, ObjectName as String) as Long

This method will create the specified event procedure for the specified object and returns the line number it was inserted at. It's a little troublesome in that it does not allow you to specify where you would like the procedure inserted, but it does automatically fill in the correct parameters to the event procedure, and at least it does let you know where it was inserted.

Example:

Call CreateEventProc("MouseMove", "cmdOK") inserts the following code:

Private Sub cmdOK_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, _
    ByVal X As Single, ByVal Y As Single)

End Sub

Line Count Functions

Function CountOfLines() as Long

Returns the total number of lines in the CodeModule.

Function CountOfDeclarationLines() as Long

Returns the number of declaration lines in the CodeModule. Declaration lines are all the lines above the start of the first procedure, usually containing comments and "Option Explicit" statements.

Procedure Functions

Function ProcOfLine(ByVal Line as Long, ByRef ProcKind as vbext_ProcKind) as String

Returns the name of the procedure that the specified line is contained in. Also, in the ProcKind parameter, it returns the kind of procedure that it is. If you don't care what kind of procedure it is, then you can just pass in 0 for the ProcKind parameter.

Possible return values for ProcKind are:

vbext_pk_Let:  Property Let Procedure
vbext_pk_Set:  Property Set Procedure
vbext_pk_Get:  Property Get Procedure
vbext_pk_Proc: Regular Procedure (Sub or Function)

Examples:

If your code module looks like:

1: Option Explicit
2:
3: Private Function GetNumberOfFish(ByVal FishBowl as Integer) as Long
4: GetNumberOfFish = 100
5: End Function

Then if you make the following call:

Dim ProcName as String, ProcKind as Long
ProcName = cm.ProcOfLine(4, ProcKind)

ProcName will be "GetNumberOfFish" and ProcKind will be vbext_pk_Proc.

If you don’t care which kind of procedure GetNumberOfFish is, then you could just use:

ProcName = cm.ProcOfLine(4, 0)

Function ProcStartLine(ByVal ProcName as String, ByVal ProcKind as vbext_ProcKind) as Long

Returns the line number at which the specified procedure begins. You specify the procedure by providing the ProcName and also the ProcKind. This helps VBE distinguish between several different versions of the same procedure. For example, you may have a Property Let Count procedure and also a Property Get Count procedure. You specify which one you want to reference by using the ProcKind parameter. ProcStartLine returns the line at which the procedure starts, including any comments that appear before the actual procedure declaration, which is slightly different than ProcBodyLine, as you will see below.

Example:

If your code module looks like this:

40:
41: ‘*****************************
42: ‘This is a comment block.
43: ‘This is another comment line.
44: ‘*****************************
45: Sub TestSub()
46: MsgBox "Hello"
47: End Sub

Then ProcStartLine("TestSub", vbext_pk_Proc) returns 40.

Function ProcBodyLine(ByVal ProcName as String, ByVal ProcKind as vbext_ProcKind) as Long

Returns the line number at which the body of the specified procedure begins. You specify the procedure by providing the ProcName and also the ProcKind. This helps VBE distinguish between several different versions of the same procedure. For example, you may have a Property Let Count procedure and also a Property Get Count procedure. You specify which one you want to reference by using the ProcKind parameter. ProcBodyLine returns the line at which the body of the procedure starts. This is the line where you will find the actual procedure declaration.

Example:

If your code module looks like the one above, then ProcBodyLine("TestSub", vbext_pk_Proc) returns 45.

Function ProcCountLines(ByVal ProcName as String, ByVal ProcKind as vbext_ProcKind) as Long

Returns the number of lines that the specified procedure occupies. You specify the procedure by providing the ProcName and also the ProcKind. This helps VBE distinguish between several different versions of the same procedure. For example, you may have a Property Let Count procedure and also a Property Get Count procedure. You specify which one you want to reference by using the ProcKind parameter. ProcCountLines returns the number of lines including any preceding comment lines.

Example:

If your code module looks like the one above, then ProcCountLines("TestSub", vbext_pk_Proc) returns 8.

Function Find( _
    ByVal Target as String, _
    ByRef StartLine as Long, ByRef StartCol as Long, _
    ByRef EndLine as Long, ByRef EndCol as Long, _
    Optional ByVal WholeWord as Boolean = False, _
    Optional ByVal MatchCase as Boolean = False, _
    Optional ByVal PatternSearch as Boolean = False
) as Boolean

This one's a doozy! Lots of parameters, but a very powerful function. It provides the same functionality as the Find dialog in the VBA Editor. You provide the string you are searching for in Target. You provide the range of lines and columns you wish to search in the StartLine, StartCol, EndLine and EndCol parameters. The first line is line 1 and the first column is column 1. If you want to search all the way to the last line, you can specify -1 as the EndLine. If you want to search all the way to the end of the EndLine, you can specify -1 as the EndCol. Therefore, if you wish to search the entire CodeModule, you can specify these four parameters as 1, 1, -1, -1. However, the function will return the location at which Target is found in these four parameters. So, if you would like to receive this information passed back to you, you must declare variables for these parameters and set them to the correct values before calling the Find function.

You can also specify several optional Boolean parameters. If you want to search for whole words only, then specify True for WholeWord. Specifying True for MatchCase makes your search case-sensitive. And finally, if you specify True for PatternSearch, then the Target is interpreted as a regular expression string rather than as a string literal. Using PatternSearch, you can use * and ? wildcard characters and some others. See the topic "Wildcard Characters Used in String Comparisons" in the VBA Help system for complete details. Notice you cannot specify True for both PatternSearch and MatchCase.

Finally, Find returns True or False to let you know if the Target string was found. So if you don’t care where the string was found, but only if it was found or not, then you can just use numbers for the line and column parameters instead of variables.

Examples:

If you don't care about where the target is found, but simply if it is found or not, and if your code module looks like the one above, then:

Find("Sub", 40, 1, 47, -1) returns True.

If you do want to know where the target is found, then declare four long variables, set their values to indicate the search range, and then if Find returns True, you have the location at which the target was found in the four variables. For example:

Dim StartLine as Long, StartCol as Long, EndLine as Long, EndCol as Long
Dim Found as Boolean
StartLine = 40: StartCol = 1: EndLine = 47: EndCol = -1
Found = Find("Sub", StartLine, StartCol, EndLine, EndCol)

returns True, and places the following values in StartLine, StartCol, EndLine, EndCol: 45, 1, 45, 4.

Then if you want to implement a "Find Next" function, you could set:

StartLine = EndLine: StartCol = EndCol: EndLine = 47: EndCol = -1

and then call Find again:

Found = Find("Sub", StartLine, StartCol, EndLine, EndCol)

This call returns True, and place the following values in StartLine, StartCol, EndLine, EndCol: 47, 5, 47, 8.

Wrap Up

We’ve taken a quick tour of most of the CodeModule methods and functions. For more information, you can use the VBA Help files and the Object Browser (F2). You may want to experiment and see what you can discover on your own. Happy coding!

Thanks to Greg Chapman for bringing the security issue to my attention during the development of this article.