I’m a full time Excel developer.
That means I spend quite some time writing code in the Visual Basic Editor (VBE).
I don’t maintain a real code library, but I do copy lots of code from previous projects into new ones.
Whereas the VBE is quite a nice application to write code in, it does lack some functionality I needed a couple of weeks ago: the ability to do a search and replace operation on multiple lines of code in one go.
For example, many routines in my projects contain an error handling mechanism of some sort. These may contain something like this:
Else
Resume Next
End If
Now what if I want that to look like this:
Resume
Case vbIgnore
Resume Next
Case vbAbort
Resume TidyUp
End Select
I wanted a tool that would let me replace a number of consecutive lines of VBA code with another set of consecutive lines.
Well, here is my first go at it:
Excel VBE Multiline Search And Replace
And here is a screenshot:
Tell me what you think of it!
Regards,
Jan Karel Pieterse
www.jkp-ads.com
Jan Karel,
I think this is a great idea. I was wishing for this just the other day.
When I installed it, my Intellisense stopped working, e.g., if I type “This” and hit Ctrl-A the Intellisense pops up for a half a second and then disappears. The same thing with MsgBox parameters. When I uninstalled your addin, Intellisense returned to normal.
I do have an addin running that checks all Excel application events, so maybe that’s having an effect, I don’t know.
Doug,
That might be caused by the trickery I used to set up a hotkey (control-shift-f). Let me get rid of that and change the menu alt-key too, see if it helps.
Hi Doug,
I have updated the download so autocomplete works again.
Of course this tool would be a good candidate to write in VB6, aiming the VBE in general, not just Excel VBE. Then hooking up a shortcut key would probably prove less problematic.
Great idea! I’ve been looking for something like this for a while, never bothered to write my own (and now I won’t need to). I wasn’t able to run the setup.exe (not an administrator on this computer), but the .xla worked for me.
Not sure if you want bugs here, but I just did another search on my personal.xls and it told me the project was protected (VBE focus might have been on your .xla? not sure how though) and I had to click OK to end. Any other time I tried to use it, the menu option didn’t work, and the excel application disappeared from my taskbar (VBE still there though). Got the application to become visible again, but all the menu bars were gone and screen updating was off.
Also, pressing the Down arrow in the “replace with” box from the last line took me to the ‘current project/module’ radio buttons, I wanted to see if I had an extra carriage return in there as the replace itself added an extra line. Not a big deal, just wasn’t expecting that.
Good work!
Thanks Matt, those are useful comments indeed.
I need to work on the error handling, that’s for sure.
I sent you an email a little while ago (info@) with some code fixes, but got a message delivery delay notice about being gray listed. Let me know if you didn’t get it, I can try sending from a different address
Who was being grey listed, me or you?
Anyway, I’ve updated the download. No more disappearing Excel .
I think it was me that was being greylisted, based on the contents of the link given in the email (I’ll mask your address):
Message is waiting at win4.fastbighost.com for delivery to mail3.longhead.com.
Message delivery has been delayed to the following recipients:
Recipient: [SMTP:info@yourdomain.com]
Reason: Remote SMTP Server Returned: 450 : Recipient address rejected: Greylisted for 300 seconds (see http://isg.ee.ethz.ch/tools/postgrey/help/jkp-ads.com.html)
Clicking on that link takes you to http://postgrey.schweikert.ch/help/jkp-ads.com.html which says “If you see this error message while sending a mail, it probably means that you did something wrong”
My email just had some code changes to get around the down-arrow key thing, and the protected project. Both are things I know you can do just fine, so my email was unnecessary :) Was just trying to help you out.
Lipstick on a warthog.
That a thing CAN be done doesn’t make it a good idea. VBE is a handy debugging environment, but it’s not a particularly good editor. Myself, if I need to do thorough editing of VBA code, I write it to text files, use a more capable editor, then read it back into the .xls/.xla file. Even for ad hoc tweaks, it seems easier to copy text from VBE (leaving it selected), paste into a more capable editor, make the changes there, copy the result, paste into VBE replacing the selected (and now modified) text. This gets me regular expressions as well as multiline pattern support. And I could automate it using AutoIt if I weren’t so lazy.
Jan Karel,
I am not able to use it. Some of the things I’m seeing:
– If I click back into the VBE with your utility open and then open it again, I then have two instances open. If I try to close one they both close. If I then try to reopen, there are two instances.
– Opening your utility hides Excel (only the VBE is visible in the taskbar)
– If I pick parts of 2 consecutive lines, i.e., the end of the first and begin of the second, it shows the whole first line in the utility and none of the second
Doug: I’ll have a look. The hiding of Excel is “by design”, it is a quick way to ensure the userform does not take you to the Excel window instead of showing it on top of the VBE. I figured that to be allowed, since you’re in the VBE anyway and ther eis no UI that can start it from excel.
fzz: I don’t thing the VBE is that bad. The hassle of copying into another app and pasting back is simply too much for me. Anyway, writing this was just fun.
A real time saver, and a very well written piece of software, remembers all data between consecutive runs, and it even supports line-by-line Undoing! Some bells and whistles such as ‘Current Function/Sub’, ‘Replace All’, a ‘Replace’ button on the ‘Find’ dialog box, wildcard support, and my contrived wish of ‘Replace This String with Function/Sub Name’, which I would use for replacing a fixed string such as “ThisMethodNameToBeChanged” as in:
Const method_name As String = “ThisMethodNameToBeChanged”
with function names, i.e. with the first word found after the latest occurence of the phrase “Function ” or “Sub ” (case sensitive), but save these features for the commercial version ;) Anyway, a big thanks to the author!
This utility sounds really great. However, I feel somewhat … shall we say mentally deficient. I’ve been programming in the VBA for Excel environment for quite a number of years, have installed various add ins to Excel and such, but I cannot find out how to activate the new search and replace feature.
I saw in an earlier post where you used to use (ctrl-shift-f), but that does not work and I cannot find anywhere in your download page nor in the zip file any instructions on how to activate this feature.
David: Under the Tools menu, choose Multiline VBA F&R