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.
Any comment that starts like that deserves my full attention. I’ve never used any editor except the one that ships with Office (the VBE). If using an external editor is better, then I don’t want to be left out in the cold.
If you edit VBA in something other than the built-in editor, leave a comment with which editor you use. If you could also list a few of its good and bad points, that would be peachy.
Thanks.
I sometimes use good ole NotePad to do quick find and replaces and some other manipulation. It’s quite uneccesary I know; but I somehow feel it’s safer than putzing around directly in the VBE.
It’s actually kind of silly, but so is turning on the bathroom light in the middle of the night to make sure no snakes are in the toilet. Yes, I still do that.
I use NotePad too. I store a lot of code either as exported modules or as procedures pasted into Notepad and stored as text files. So It’s easy enough to open the stored txt or bas in Notepad, extract what I want, clean it up a little if necessary, and pop it into an active project.
It’s not really its own editor, but MZTools greatly expands the editing capabilities of the VBIDE.
fzz,
What sort of heavy duty code editing have you done previously thats required the use of a regular expression? Not saying that I don’t believe you have done it, simply that if its getting that complex I’d be concerned about the robustness of the edited code
Dave
Most Unix, Linux, BSD and, yes, even Windows code has been edited at some point using vi, EMACS, and the old Microsoft Editor. If you’re fearful of regex-altered code, you should be afraid, very afraid. Then again, maybe that explains all the buffer overflow exploits.
Simple multiline example:
If a Then
If b Then
should have been
If b Then
If a Then
In vim, it’s just
:g/^( *)(If a Then) *(
*)(If b Then) *$/s//1432/[Enter]
where [Enter] means press the [Enter] key.
Commenting/uncommenting large blocks of code would be another example. Comment from next Else to line above immediately subsequent End If,
:/^ *Else *$/,/^ *End If *$/-1s/^ */&’/[Enter]
and uncomment it.
:/^ *’Else *$/,/^ *End If *$/s/^( *)’/1/[Enter]
This is either obvious or opaque. If, like me, you spent a goodly chunk of your youth programming on Unix systems, VBE is frustratingly lacking in editing tools. If you’ve never used an editor more capable than VBE, is it truly surprising you might believe it’s perfectly adequate?
FTHOI, I use [g]vim whenever I can, supplemented with TextPad when I want to edit different files side by side. I usually create VBA code in the VBE, but I often EDIT it outside the VBE. This may also be due in part to also using RCS to check in/check out VBA code, so mostly everything is in the RCS store.
What’s RCS?
And to your S&R codes: they are so complex in themselves (with all them slashes, brackets and such, which I expect need to be in proper order) that I’d rather just edit the VBA code directly.
I would add: what’s (g)vim and why would it be in a VBE?
While commenting is a critical aspect of most code block, there is a lot to be said for writting code that is to some degree, self commenting.
I also find notepad a handy storage file for *.cls, *.bas, *.xlm, etc. Most firewalls filter out even zip and or txt but most certainly cls, bas, etc. as email attachments. I stroe them in text and change the extention before sending to some innocuous TLA ‘ three letter accronym.
BTW. I still turn the light on in the WC at night as well. The misses gets pissy when I pisses and misses. :rolleyes:
I use UltraEdit32 (http://www.ultraedit.com/) which simplify the code life a lot.
Kind regards,
Dennis
RCS = revision control system
[g]vim = either gvim or vim, former graphical, latter console mode, both editors
Regular expressions are meant to be terse and precise. That’s their strength and beauty, but it seems their beauty is in the eye of the beholder.
As for ‘so complex’, this seems to be your first exposure to them, so not a surprising reaction. But you might want to ponder why every scripting language including WSH/VBScript/JScript and .Net and most serious text editors have them.
Editor preference has always been highly subjective. You roll your eyes at the regexs above, and I roll my eyes at multiline editing add-ins. Neither of us can see compelling benefits for the other’s approach. But maybe a final problem may make my preference somewhat more understandable: ever needed to change a token in the code that also appeared in several string constants, but the strings shouldn’t be changed? How’d you do that in VBE without having to select around the string constants? This requires 3 search and replace operations over the entire subject code with regexs. And that’s only because I use relatively antiquated/unsophisticated editors. There are some that can restrict search and replace operations to code, quoted text or comments.
“:g/^( *)(If a Then) *(
*)(If b Then) *$/s//1432/[Enter]”
Looks like an emoticon gone bad.
I regularly use TextPad. I particularly like the Synax Highlighting which colour codes statments, comments etc. which NotePad doesn’t. The main downside is the lack of immediate syntax checking.
Also +1 for MZTools.
VBA editor only for me. Occasionally, I’ll use an Excel worksheet to construct a block of statements by using formulas. For example, I copy a bunch of enumerated constants from the Help system, and then write formulas to create If-Then statements.
Call me old-fashioned, but I just can’t see how using an external editor would have any advantages for me. I like things simple.
So why not write a regexs for VBAIDE? – or has it been done?
I like to use the native editor for whatever I’m using – mainly because I’m easily confused, and I like it to pick up on my spelling/syntax/typo errors as I go (e.g. “Slect Case” is an old, old friend….!) – so VBE for me when I’m using VBA. I frequently store blocks of frequently-used code in Notepad just for convenience – it’s easier to hook out of that than to go back to the original location and lift it out.
I turn on the lights in the WC at night because assuming the toilet lid is up is a mistake I never, ever, want to make again…..
I found this doing a Google plus at least 10 other pages I didn’t bother looking at. But the main point for me in this quote is what I put in bold. I think that would be true for anyone else as well. I would imagine the amount of text used to write the comments would far out weigh any advantage of using them. Which begs the question: “what advantage?”
BTW. The above post was in response to MikeC question about regx in VB; which the search site was demonstrating a way to use it in VB. :-)
OOPS. I meant Ross. Damn gotta get some coffee!
^1?s*-?s*(d{3}|(s*d{3}s*))s*-?s*d{3}s*-?s*d{4}$
Optional 1 at the beginning of text
Followed by optional spaces, an optional dash and more optional spaces
Followed by either 3 digits or by (, optional spaces, 3 digits, optional spaces, )
Followed by optional spaces, an optional dash and more optional spaces
Followed by 3 digits
Followed by optional spaces, an optional dash and more optional spaces
Followed by 4 digits at the end of text
It’s all a question of experience, children. There are newsgroups for all the major scripting languages as well as comp.editors and comp.unix.shell. You can see the uses to which they’re put, or you can enjoy wallowing in your own ignorance.
As for the article cited, looks like it was
http://visualbasic.about.com/od/usingvbnet/l/blregexa.htm
which also contains: ‘This is a technology that has earned it’s place with generations of Unix, Perl, and other technologies. And now that VB.NET has adopted it, you need to know about it!’ Gosh, I wonder why the author would write that?
MZTools is one of the most useful tools I’ve ever used. A tremendous timesaver for working with large collections of either VBA code or VB6 code. I use it with great regularity for searching, but it can do much more like assign hotkeys to comment and uncomment blocks of code.
I’ll stick up for vim and regexp use for VBE editing. Just like anything else, once you get used to the format of regular expressions, they become second nature and you see right through the slashes and see their true inner beauty…
A handy and free text editor with syntax files for many languages (but not Excel) is Crimson Editor
Lots of good features that make coding a lot easier
humm there is a addin for FF that uses regular expressions to weed out adds and stuff it’s quite stright forward, when someone tells you what to do ;-)
Personaly I have never had a issue with updateing source code, buy hey, I’m with dick, if theres a diffrent approch lets talk about it.
I think this has been a good discussion, thanks for the input everybody.
Cheers
Ross
I’m (a relative novice) still curious to know why I would use regx?
Why would anyome use anything other than the VBE???
How can you live without the native syntax checking ? And what about Intellisense? Ocassional
Are you all such “hot” developers that you don’t need these things? I sure do, and I’ve been doing this crap since way before VBA existed…. (give me back my XLM and I’d be a happy guy ;-))
Dick
And what about macro recording ?? I use that too…..
I already said I use VBE to CREATE code. That’s when ‘Intellisense’ is useful. What I don’t like it for is EDITING existing code. As for syntax checking, it’d be automatic once code is pasted back into the VBE if automatic syntax checking is enabled. But I can’t stand it while I’m typing. I prefer running Debug > Compile[…] when I’m ready to check syntax. As for recording macros, I do that too, but when was the last time you left recorded VBA code as-is?
fzz :
OK I see what you mean.
I just leave it in VBE then although sometimes when I have corrupted Projects I’ll copy and paste it into Wordpad, save the file and then import it back (or I’ll export it as bas and reimport it.
Just an old habit I guess ;-)
Dick M:
I use all of the tools of the VBE (intellisense, the object browser, syntax checking, etc.), but sometimes I need to do something a bit different, which is what I use NotePad for. The macro recorder is indispensable. In fact, I tell people that my two favorite programming tools are the macro recorder and Google (third on the list would be MZTools).
Fzz:
Go to VBE’s Tools menu and choose Options. On the Editor tab, uncheck Auto Syntax Check. Any bad lines will still be shown in red, but you will no longer get that annoying modal dialog.
fzz: I agree regex’s are powerful. And indeed, they look foreign to me, since I don’t use them.
Everyone his/her own set of tools I guess and for those who done speak regex, my tool just may help a bit.
I hardly ever need to refactor code in this way, and my instict is to say that well-written, well-designed code shouldn’t need it.
That said, I’m sure that a RegExp find and replace add-in for the VBE would be almost trivial to knock up. A lot easier (and safer, I would suggest) than having continually to move code between environments, anyway.
On a vaguely related note, available for a short time is my VBE macro editor which uses direct VBScript execution against the VBE library to bypass the ‘Trust Access To VB Project’ setting. http://www.asta51.dsl.pipex.com/zip/
Rob
I am with John W.
I like the idea and I much prefer the Visual Studio Interface, but the only thing I can guarantee when I arrive at a client is that where ever I end up I won’t be allowed to install anything other than the basic Office. So I get used to using the tools I can guarantee I will have.
Excel will get you any nasty blocks of code you might want to write and arguably there shouldn’t be too much of that anyway.
FWIW I’ve recently had need of an alternative general purpose editor.
Got TextPad, that’s o.k., work on multiple files at once, macros etc.
Notepad2 is one step up from good ol’ Notepad itself (but includes line numbering and syntax highlighting), worth a look if you’re a Notepad traditionalist. (It’s free). A major update released on 7th April, it now supports multi-line search and replace. I particularly like the Ctrl+l hotkey to launch the current document (i.e. for testing vbscript or batch files).
Notepad++ caught my attention because you can define your own syntax highlighting schemes AND code folding schemes (what a great term to throw into conversation).Unfortunately it’s currently limited in that you can’t use a composite keyword (such as “End If”) to signify the end of a code block.
There are plenty of pre-defined syntax highlighting schemes (I count 46). It’s got LOTS of other options including some exotic search/replace functionality, macros, and mutiple files open at once in a tabbed format. (It’s also free).
Haven’t tried Crimson Editor but homepage feature list looks good, might be what I need for Smartware code highlighting/folding.
For Rob Bruce
I downloaded your add-in and read RemoveLineContinuationUnderscores. It may be personal opinion, but I’d prefer the vim regex substitution command
%s/ _ *
*/ /g
and I’ll live with the copy/paste overhead.
As for ‘refactoring’, I write code for workbooks for my department that needs to interoperate with particular workbooks from other departments, over which I have no control. Will they listen about using defined names? Will they leave key formulas in the same place for more than a fiscal quarter at a time? Whatever. Call my coding style reactive.
I use the Zada Error Handler (http://www.zada.com.au/accessaddins.htm).
This is an add-in for VBE.
Been playing around with Notepad ++ – thanks for the heads up gruff999. I expecially like the code folding, I wish VBE had that feature.
So what is “code folding”?
Actually, I just learned about this thing called “Google”. There’s also this site called “Wikipedia” which seems to have lots of definitions, such as this for code folding:
“Code folding is a feature of some text editors, source code editors and IDEs that allows the user to selectively hide and display sections of a currently-edited file as a part of routine edit operations. This allows the user to manage large regions of potentially complicated text within one window, while still viewing only those subsections of the text that are specifically relevant during a particular editing session.”
I go with the crowd: the built-in IDE is the most useful and convenient for Office. But if you leave the office and start anything like embedded development, you’ll need a good editor; my choice is UltraEdit32.
Historical note: my first DOS based editor was Brief. I think that was owned by Borland at the time (early 90s), and cost about three times what you pay for a good commercial editor today.
Regards,
Bill
EditPlus with syntax highlighting works great on .bas files. I use the VBA IDE 99% of the time, but ALWAYS copy and paste code into a scratch pad file that I keep open all the time. I’m sure everyone has lost some code due to an Excel crash or freeze at one time or another. I guess I have a bad habit of writing too much code (err…too much *bad* code) while VBA is “paused”, which means I can’t save often. Copying and pasting VBA code into a text file works well and has saved me lots of code!
EditPlus is more like a swiss army knife for text manipulation. One great feature is the ability to select columns of text…something you just can’t do inside the VBA editor. It’s a great feature for whipping together long Select Case statements from messy text acquired elsewhere.
I do VBA every day and am a big fan of Kusleika, de Bruin, Bovey, Walkenbach, Peltier, Blood, Pearson, Erlandsen, McRitchie, Colo, … all you guys. I found this post while googling for another I saw years ago about recommending to paste code into fresh modules under certain conditions. I swear I’ve seen some weird behavior in the VBE lately, but I think it was because I used “_” in procedure names. If you do a lot of VBA you’ll know why that’s not a good idea. Recently I’ve hit on trying to do cheesy “metaprogramming” (abusing the term). I’ve always edited code in regular Excel worksheets using formulas to build strings. Now I have written a class that takes input(box) and output strings to the Immediate Window or to a worksheet for later pasting. It includes Loop and Conditional constructors to do nesting and indentation (pushing and popping indent levels from a stack class {Forth, anyone?}). The input for those inputboxes is an equally cheesy markup language, if you will, so.. /S to start a Select Case and S/ to end it. Another thing in the class is a Class Module helper to do all the Dim, Get, and Set/Let for properties. But the feature I use most is a Combine & Concatenate helper (which I need to develo further as it is such a time saver). Ignoring many of the good naming conventions is a bad habit of mine, but I do find it helpful to end variable names in “zx” or other unlikely strings, so I can do find/replace on them for ” = ” (assignments). I keep the class in a file called “meta.xls” and I always have it open. I arrange the modules in the VBE (undocked) first thing and then I get Productive with a capital “P”. After I spif up the class I’ll offer it on Aaron Blood’s site in case anybody is interested. I used to employ AutoIt to type in the VBE but not since I wrote my meta class. But AutoIt is a great way too. Whoever mentioned Crimson editor – I can say rectangular selection is nice to have. That’s the one feature I wish VBE had. I don’t want to get hooked on MZtool or other extensions/addins. My company wouldn’t let me install it and even if they did sooner or later I’d have to re-install it. Plus I’d be disappointed (or lost?) on every machine that didn’t have it. Maybe some day I can work doing VBA and Excel exclusively like some of you, then I could mod the VBE with wild abandon!
“My company wouldn’t let me install it [MZ Tools]”
My company lets me install whatever I want. Oh wait, I own my company.
Freelancing hath its privileges.
The program TextPad is the most ingenious editor I have ever seen. I like things simple too, but the VBE has a very annoying pop-up error message that frays every one of my nerves. TextPad doesn’t to that. Neither does NotePad. But, TextPad allows you to keep full libraries of subroutines that can be called into the document immediately with a simple click. No more of “I know that routine was in one of these excel apps, now where did I put it……….” As far as programming goes, I think the guys who created TextPad deserve a NobelPeace Prize.
John – What’s the pop-up error?
jedit is my IDE of choice and it handles everything (text based code) wonderfully.
for quick-coding on windows, I use notepad++
my big question/issue is excel – am I supposed to import/export every time I want to edit the VBscript? Is there no way to auto-link to an external file? how weak is it that I have to remove a class and then import the class to run it? if the script editor that came with excel didn’t suck i could just use it, yes I know… but there is no syntax highlighting, no diffing, no folding, no regex searching, no anything I rely on… well, it does let me type.
One thing really missing in VBE is the ability to scroll down with the mouse wheel.
To work around this limitation I use FreeWheel, a small piece of programm written 10 years ago…
Available here : http://www.geocities.com/SiliconValley/2060/freewheel.html
F
I never felt the need to work outside the VBE until today. Today I really wished for folding within VBE. It saves a lot of time and focus to write big chunks of code and then fold and scroll past it as if it’s one line.
As a side note, I use EditPad Pro after having tried several others, it has extensive (to the extend of my capabilities at least) RegExp support as well as multiline/multifile search/replace, fold in place and “copy/paste lines containing …” facilities. Furthermore the creators of this program have a free regex helper tool which really is a nice guide to create mediocre regexp phrases.