Meta Programming in VBA: The VBIDE and Why Documentation is Important

A while back I shared a Stack Overflow Question about dynamically adding code to a module in VBA. I promised I would come back and write more about my experience in the area of meta programming VBA later. I guess now is the time friends. You see, I’ve been working on a toolbox of sorts. A big part of that project is making it easier to work with the Microsoft Visual Basic for Applications Extensibility library. That library is not well documented to say the least. So, I want to share what I’ve found out about the Extensibility library with you.

Before we get started:

1. Add a reference to the Microsoft VBA Extensibility Library by going to the Tools menu and selecting References. Scroll down until you see Microsoft Visual Basic for Application Extensibility 5.3 and check the box.
2. You also need to enable programmatic access to the VBA Project.
   1. Office 2003: In the host program (Excel, Access, Word, etc.) go to the Tools Menu. Choose Macros, then Macro Security. In that dialog, click on the Trusted Publishers tab and check the Trust Access to the Visual Basic Project setting.
   2. Office 2007: Click the Developer item on the main Ribbon and then click the Macro Security item in theCode panel. In that dialog, choose Macro Settings and check the Trust access to the VBA project object model.

Courtesy Chip Pearson – Programming the VBA Editor

The VBE Object Model

The VBE object model consists of a few parts.

• VBProject – This is holds all of the code modules in a given Office document.
• VBComponent – A “wrapper” around a given code module.
• CodeModule – The actual place where the code in a given component is stored and accessed from.

Essentially, VBProject and VBComponent allow us to access the code module while CodeModule does all of the heavy lifting of accessing the code inside of it.

We can get a handle on a given CodeModule with the following snippet of code. (Assuming we have a project named “VBA Project” and module named “Module1”.)

Dim module As VBIDE.CodeModule
Set module = Application.VBE.Projects("VBA Project").VBComponents("Module1").CodeModule

Now we can leverage the methods and properties of the CodeModule to read and write code with other code! Which, in my opinion, is awesome.

Don’t get too excited…

There are some caveats though. To begin with, there is no concept of a procedure beyond the various “Proc” methods. To make it worse, they all need to know what kind of procedure they’re being called on to work.

For example, the ProcOfLine property takes two required parameters.

• line – A long specifying the line to check
• prockind – Specifies the kind of procedure to locate. Because property procedures can have multiple representations in the module, you must specify the kind of procedure you want to locate. All procedures other than property procedures (that is, Sub and Function procedures) use vbext_pk_Proc.

So, we could find the name first procedure in a module like so.

Dim module As VBIDE.CodeModule
Set module = Application.VBE.Projects("VBA Project").VBComponents("Module1").CodeModule

Dim lastDeclarationLine as Long
Dim procName as String

lastDeclarationLine = module.CountOfDeclarationLines

procName = module.ProcOfLine(lastDeclarationLine +1 , vbext_pk_Proc)

And then later get other information about the procedure based its name.

Dim countOfProceduresLines As Long
countOfProceduresLines = module.ProcCountLines(procName, vbext_pk_Proc)

This works fine so long as we’re only dealing with *.bas files. We’ll only ever come across Functions or Subroutines, but the second we come across a *.cls file with a property or two LOOK OUT! Here comes the oh so vague runtime error number #35….

Okay, so just how the hell do we find out what kind of procedure we’re working with? How do we find out whether we’re dealing with a Property or a Subroutine?

Well, it turns out that both the VB6 and Office 2013 documentation for ProcOfLine is incomplete. Luckily, there is some extra information in the Access 2013 doc for ProcOfLine.

Note that the pprockind argument indicates whether the line belongs to a Sub or Function procedure, a Property Get procedure, a Property Let procedure, or aProperty Set procedure. To determine what type of procedure a line is in, pass a variable of type Long to the ProcOfLine property, then check the value of that variable.

That’s right. procKind is an Out parameter. It gets passed by reference. So where all of the other “Proc” properties of a CodeModule need to know what kind of procedure they’re working with, ProcOfLine is the one that actually returns it.

The correct way to do this is to declare an uninitialized procKind variable to pass to ProcOfLine before calling any of the other properties that require a ProcKind parameter.

Dim module As VBIDE.CodeModule
Set module = Application.VBE.Projects("VBA Project").VBComponents("Module1").CodeModule

Dim lastDeclarationLine As Long
Dim procName As String
Dim procKind As vbext_ProcKind

lastDeclarationLine = module.CountOfDeclarationLines

procName = module.ProcOfLine(lastDeclarationLine +1 , procKind)

Dim countOfProceduresLines As Long
countOfProceduresLines = module.ProcCountLines(procName, procKind)

You’re welcome.

Now, I won’t bash too hard on Microsoft for not updating the VB6 documentation. It’s been around forever and I’m simply grateful that they continue to host it. The Office 2013 documentation is a different story though. I can see no reason why the Access 2013 and the Office 2013 documents for this property should be different. Other than perhaps simple oversight and the fact that they even have two separate documents for it. Let this be a lesson to everyone. Keep good documentation and keep it all in one place. You might save someone an awful lot of time and effort.

If you’re interested in more about metaprogramming in vba, I recommend checking out my VBEX project on GitHub and browsing through some of the Q&A’s over on Code Review SE. I do plan on following up on this post to explain how I feel I’ve improved on the extensibility library with some custom classes, so stay tuned.

Until next time, Semper Cogitet.