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)
Me.lbxFound.Clear
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.AddItem vbc.Name
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))
End If
Next i
Next vbc
End If
End Sub
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
Load ufFind
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.”
End If
Else
MsgBox “Unable to get the window handle of the VBE.”
End If
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.Show
cm.CodePane.SetSelection .LineNo, lStartCol, .LineNo, lEndCol
Application.OnTime Now + TimeSerial(0, 0, 1), “ShowComp”
End If
End With
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
I know this is a bit off topic, but you stated that, “The second problem is that after I do a Find, none of my menu items work.” I have some code that when I run it, every item in my Excel menu and ribbon is grayed out, and I can’t click anything. My only solution is to force quit excel and restart it. If what you are describing is the same as the problem I am having, I would very much appreciate a suggestion to fix it. It drives me crazy since this issue doesn’t happen when I run the same exact code on another computer.
I very much appreciate your posts and blog. Thanks!
I commented out the line
Application.VBE.VBProjects("VBHelpers").VBComponents("UCodeFind").DesignerWindow.CloseThen the form designer didn't pop up any more.
Hi DK:
Fixed the problems with the menu (I think) and definitely with seeing the form in design view.
Enhanced the functionality.
Simplified the UI.
Changes:
All the code after uffind.show (i.e., the post-.Show code) is now in the uf’s cmdFind_Click module (with redundant lines commented out and variable references appropriately adjusted). The module level mVBComp is no longer needed. Consequently, it and ShowFound are both gone.
ufFind is now shown modeless.
lblFound_Click now calls cmdFind_Click. Consequently, click a row in lbl_Found to see the corresponding code module line immediately.
When I closed the userform I always landed up in Excel. So, I added the ‘getBackToVBE’ routine and added a OnTime statement in cmdCancel_Click.
Also added a QueryClose routine that simply calls cmdCancel_Click. That way we get the same behavior irrespective of how one dismisses the dialog box.
I also made some changes in MToolbars that you may or may not want:
All the menu items are now added temporarily. (used the changes made above to find all references to Controls.Add {grin})
Some duplicate code has been consolidated into a single routine.
May have made some other changes that I forgot to document above. {grin}
http://www.dailydoseofexcel.com/blogpix/dk_vbhelpers_modified_tm.zip
That code fine will be a godsend for me (when it works fully).
I have an application written in VBA which has hit the 5000 lines of code mark, so I split it over several modules to help organise it. However this means I need lots of global variables to help pass things around and trying to capture all instances whilst debugging is a nightmate.
SpreadsheetGeek: Skip the globals and pass information as needed using procedure parameters. The up front additional typing may seem like more work but there’s a *huge* benefit. You will require *much, much, much* less debugging.
There are times, rare but not zero, I change and deploy production code *without* testing! Because of my extreme modular designs, I can be 100% sure that a change to a routine will not affect any other routine except through the function value or the byref arguments.
Didn’t you guys try the Find function provided in MZ Tools? Does everything I need.
Nice work Dick, and good work Rob and Tushar.
I was surprised by its speed, I wonder if writing it in VB6 would make it even faster?
Wouldn’t it be ace if you could dock that form…
Great work Chaps
Ross
I was wondering the same thing – what are you looking for that MZ Tools doesn’t do – I also use it all the time
MZ Find does everything I want and more. It’s the “and more” part that I don’t need. I never search outside of the active project, so I don’t need that option. I don’t need to limit my search to the current module either. If I have that much stuff in one module, I’m doing it wrong. I don’t need two dialogs – show me the results in the same dialog where I type my search terms. Also, the tree view is unnecessary. See, all stupid stuff that I shouldn’t be complaining about.
I also don’t like how I have to use the mouse to close the results box or to cancel out of the find dialog. I’m learning why it’s that way as I try to do it myself, though.
“I also don’t like how I have to use the mouse to close the results box or to cancel out of the find dialog. I’m learning why it’s that way as I try to do it myself, though.”
What am I missing? Create a button and change its Cancel property to True. Then, the button’s Click event procedure will run when someone uses the Esc key to close the dialog box.