Search Your VBA Code (Excel Add-In)

Share this

February 22, 2013

Search Your VBA Code (Excel Add-In)
 

Introduction


A couple of days ago, a blog reader (Harm) asked me if it is possible to search in all subs/functions/forms of the VBA code to see if there is a particular string present. Although I had written in the past a code that loops through all the VBA code of a workbook I decided to go one step further. So, I adjusted my old code and I created a new add-in.

The add-in works similarly to built-in search, but has the advantage of displaying the results in 3 listboxes. In this way you can simply select the desired value from the listboxes and with a single click you will be transferred to the line of code that contains the string you are searched for. This is quite useful in cases of public variables, which are used in many modules, as well as in workbooks that contain many lines of VBA code.


VBA tip


When I was writing the VBA code, I encountered the following error: I couldn’t find a way to open the VBA code that lies “behind” a userform. However, using the “trial and error” technique I ended up with the following lines of code:

With Application

    'Show the VBE main window.
    .VBE.MainWindow.Visible = True

    'Activate the selected module.
    .Workbooks(Me.cmbWorkbooks.Value).VBProject.VBComponents(Me.lbModule.List(Me.lbModule.ListIndex)).Activate

    'Show the VBA code of the selected module.
    .Workbooks(Me.cmbWorkbooks.Value).VBProject.VBComponents(Me.lbModule.List(Me.lbModule.ListIndex)).CodeModule.CodePane.Show

    'Select the characters that the user searched for.
    With .VBE.ActiveCodePane.CodeModule
        .CodePane.SetSelection Me.lbRow.List(Me.lbRow.ListIndex), Me.lbColumn.List(Me.lbColumn.ListIndex), _
        Me.lbRow.List(Me.lbRow.ListIndex), Me.lbColumn.List(Me.lbColumn.ListIndex) + Len(Me.txtFindWhat)
    End With
End With

It should be highlighted that the above code, as well as the entire add-in are based on a reference to Microsoft Visual Basic for Applications Extensibility 5.3 library. 

 

Demonstration video


The short video below demonstrates the add-in installation and usage.

 

Note that in order to use this add-in, you should have the VBA project unlocked. Otherwise, a message box will appear informing you that the VBA project is locked.

 

Update – 26/07/2015


Search Your VBA Code - Work With Add-Ins

A few days ago a blog reader (Jamie) asked me to incorporate a new functionality to this add-in. So, apart from opened workbooks, the updated version can now be used to search on installed add-ins too. However, the new functionality does NOT work wiith COM add-ins, but only with “typical” Excel add-ins (i.e. .xla and xlam files).

 

Downloads


 
Download
 
The add-in can be used with Excel 2007 or newer.

Page last modified: September 28, 2021

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Add Content Block
>