I’ve never been a fan of the Find function as it is usually implemented. You open the Find dialog, type in your text, set some options, and go to the first instance. Then you click Find Next to go to the next instance. Excel has a Find All button that’s a little better, but is still overkill for 99% of my Find needs. That’s why I wrote Inflexible Find, which I use extensively every day.
Now I want something similar for the VBE. I want to type in some text and have it show my everywhere that text is. I don’t care about upper and lower case. I don’t care what module it’s in. Sometimes I do care about that stuff and I can use the built-in Find when I do. But for most Find operations, I just want something quicker. I set about writing an inflexible find for the VBE. It isn’t going well.
Here’s what the built-in find looks like
Here’s what my find looks like
I like mine better except for all the reasons that I don’t. There’s nothing fancy about the code. When I type something in the textbox, the listbox fills with lines that contain that string
Dim vbc As VBComponent
Dim cm As CodeModule
Dim i As Long
Dim sFindWhat As String
sFindWhat = LCase(Me.tbxFind.Text)
If Me.Vbp.Protection = vbext_pp_none Then
For Each vbc In Me.Vbp.VBComponents
Set cm = vbc.CodeModule
For i = 1 To cm.CountOfLines
If InStr(1, LCase(cm.Lines(i, 1)), sFindWhat) > 0 Then
Me.lbxFound.List(Me.lbxFound.ListCount – 1, 1) = cm.ProcOfLine(i, vbext_pk_Proc)
Me.lbxFound.List(Me.lbxFound.ListCount – 1, 2) = i
Me.lbxFound.List(Me.lbxFound.ListCount – 1, 3) = Trim(cm.Lines(i, 1))
This code isn’t complete yet. I was planning on shoring it up when I got some of the other details down. I’m still wrestling with those details. First, I want the userform’s parent window to be the VBE. I used code from Chip Pearson and Stephen Bullen and came up with this
Set Vbp = Application.VBE.ActiveVBProject
Set ufFind = New UCodeFind
AppHWnd = Application.VBE.MainWindow.hwnd
If AppHWnd > 0 Then
MeHWnd = FindWindow(sUFCLASS, ufFind.Caption)
Res = SetParent(MeHWnd, AppHWnd)
If Res = 0 Then
MsgBox “The call to SetParent failed.”
MsgBox “Unable to get the window handle of the VBE.”
And that works. Without this code, the VBE gets hidden and userform becomes the child of the main Excel application. With this code, the userform becomes the child of the VBE. Now I have two problems that I haven’t been able to resolve.
After the userform is closed, this code highlights the found (and selected) instance
If Not ufFind.UserCancel Then
Set mVBComp = Vbp.VBComponents(.CompName)
Set cm = mVBComp.CodeModule
lStartCol = InStr(1, cm.Lines(.LineNo, 1), .FindText, vbTextCompare)
lEndCol = lStartCol + Len(.FindText)
cm.CodePane.SetSelection .LineNo, lStartCol, .LineNo, lEndCol
Application.OnTime Now + TimeSerial(0, 0, 1), “ShowComp”
When that code completes, I unload the form. After the all the code is done running, the userform designer window comes to the front of the VBE.
Needless to say, that’s not what I want to happen. I want the code module to show with the found instance highlighted. You can see in the code above that I tried to make the VBComponent a module level variable (mVBComp) and run a ShowComp procedure to get the right CodePane to the front. It didn’t work. The code module with the found instance is directly underneath the userform’s designer window – that is, if I Control+Tab it shows the code module I want. I know this is what happens when you run a userform, like in the Excel window, but I don’t know how to prevent it in this instance.
The second problem is that after I do a Find, none of my menu items work. My event handler class (that I took from Chip) loses scope and doesn’t work any more. It’s similar (or exactly like) what happens when I edit code in a project and I have to re-run the Auto_Open procedure to get my global class modules back.
I know it’s tempting to look at code that the author says doesn’t work, so here you go:
You can download VBHelpers2.zip