Programmatically Search VBA Code

If you are involved in the process of reverse engineering a whole suite of VBA applications, you may at some point need to start looking at dependencies hardcoded within the actual code. If you are faced with a large number of applications, documenting these can be a time consuming task.

The following post describes, how you can create your own tool (in Excel) to search the VBA code of other applications to help you with this. It will be an example of how to work with the Visual Basic for Applications Extensibility 5.3 library. The tool will be flexible enough to search the VBA modules within Excel, Access, PowerPoint and Word. It will also tell us of where in the code we can find the search criteria by identifying the module name and line number.

Some considerations

Before we begin, there are a few points to note about this approach:

  • We will only be able to search the VBA code in applications that do not have the VBA editor locked. Unfortunately, there is no way to programmatically unlock the VBA editor.
  • For some Excel spreadsheets, you will have to make sure that the option “Trust access to the VBA project object model” has been checked. This can be found under the Macro settings option, within Trust Center.

Preliminary Steps

In order to create our tool, we need to make sure we have the right project references in our application. Create a new workbook, and then in the VBA IDE, go to Tools>References and make sure the following references are ticked:

Building the Code

This project will involve creating two classes. There will be a main class called csSearchVBA which carries out the search, and another class called csResults which will hold the details of items found in the search. Having another class to act as  a data structure like this, makes it easier to work with the results we get back from the search. All the information we need is encapsulated in this data model, and we don’t need to concern ourselves with the details of where the VBA Extensibility library is pulling the information from.  The diagram below gives an overview of the two classes and their relationship:

Let’s now create the classes. First, we are going to create the class to hold the results. Create a new class and call it csResults, and then paste the code below:

You can see that the class has no methods, it just has the attributes we are interested in capturing for each search. Next, we are going to create our main class which is going to perform the search. Create another new class called csSearchVBA, and paste the following code in:

Essentially, these two classes are all that is required for the tool to work.  You can create your own frontend for it, as you see fit. But next I will show you how to use the classes to test the process out. Once you understand how to use the classes, then fleshing out the code into an application with a frontend becomes a relatively minor task.

Testing the Application

The code below demonstrates how you can use the tool you have created to search the VBA code of a file that you specify. In the example, the code will search for the words “Hello” and “Goodbye” in the VBA code found within a file called “DummyMacro.xlsm” and it will output the results to the first sheet in the workbook.

And that’s it, hopefully this will help save you some time having to look through a whole bunch of code for a particular word or phrase.

Thank you for reading this post. Please take time to read the disclaimer about content found on this site.

Share :Facebooktwittergoogle_plusredditpinterestmail