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.