"Excel VBA Help"


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums
Our Info Pages:-
The Code Cage - Microsoft Office help


Currently Active Users Viewing this Page: 1 (0 members and 1 guests)

» Lisa's VBA Clean Up Tool - a work in progress!
Page Visitors: 8258
Last Visitor was Guest On Yesterday - 23:25.
Last Modified 1st May 2014 - 18:09

This page - Lisa's VBA Clean Up Tool - a work in progress! - has content donated and maintained by Lisa Green(aka LisaGreen )

This is one of a set of pages the aim of which is to help people tidy up their VBA projects when they have finished developing, this project is a work in progress and all questions should be directed to this thread VBA Cleanup Project.

INTRODUCTION: Hi. I'm Lisa. My bio includes work on mainframes as a programmer systems analyst specialising in databases and conversion. I'm 60 years old this year and don't do that any more. I started coding in VB with V3 but have coded in Delphi C QuickBasic and many other languages including different Assemblers over the years. I like VBA. One of its strong points is the VBE and the debugger.. also.. it's free!! and comes with most MS Office applications and quite a few non Microsoft programs, The major one being Autocad I think. My hobbies include Guitar, Reading, walking with my doggies and ummm VBA. I hope you find these pages and my experiences in coding to the VBE as useful as I have enjoyed.. mostly.. gaining it.

The Idea

Many years ago in a galaxy far far away... well women can like science fiction as well you know!!! ... I used to work as a COBOL programmer on main frames. Anyone who has done this knows that part of the compilation process produces a detailed Cross Reference... and it's invaluable. Especially when cleaning up code.. deleting all those unused variables for example!! Everything about the program is there.

I missed this when I started coding in VBA... soooo I set out to create one. I started in Word and liked the result. But it turned into Topsy who "just growed" and I finally dropped it and moved on to other things. Add to that that I discovered someone had already done a crude one... all in about 200 lines of code!!

The Driving Forces

A little while ago, I resurected it as an entry into a competition for VIP status on the codecage web site, this time in Excel. Though I never did get it finished in time, it was thought good enough as an ongoing project by the codecage admin to take a bit further. That really got my interest and then I was caught. I'm sure a lot of others have had the same "problem"... you just can't switch anything off!! You or the computer!!

But the greatest mover has been.... See more...

But the greatest mover has been that I actually use these things myself. It's made things a lot simpler for me. I can write a sub or a function with no Dims and run a procedure to insert them! If I'm writing stuff that needs a similar set of Dims to another procedure... I can copy the whole lot and then run a proc to delete the ones not referenced. I don't like double space lines so I have a routine to reduce them to single space lines. My own prefered method of error handling is to do it "inline" and cope with possible errors there rather than having a label at the end with error code below it. My Insert Error Sub inserts all this around the line I'm on, along with the procedure name, line number, error number and description of the error.

It really has made me a little lazy I suppose.. but then.. I did do all the work on the procedures up front to do this!

It's also stronly re-enforced something I'm an advocate of anyway. A naming convention. The Insert Dims won't work properly without a naming convention.. they all come out as Variant... A Cross Reference is a bit silly without having a variable type stated. All of this *depends* on a naming convention.

Very briefly... here is mine...

Subs All start with lower case "sub"
Functions all start with lower case "fnc"
Variables all start with a lower case type and scope.
After the prefix the Actual name always starts with an uppercase letter.
I don't use underscores and rarely use hyphens.
Names are as long as neeeded to be descriptive, with each part of the name starting with an upper case letter.

Example ... lnglDimLineNumber: lng=long, l=local, DimLineNumber=Descriptive name.

Hide this content.

Stages Of Developement

There was a lot to do. The original Word project was quite big. This would be no different and I was now coding in Excel VBA so a lot needed altering or just plain rewriting. Partly because of that, I also decided to try and "de application" things.. so the code could be used in different flavours of VBA.

For a crude version control, I started with a generic save routine to save the workbook I was working on as a numbered file. I did actually post that for the competition!

I used a routine by Chip Pearson to get the calling application and where its EXE was located. Then used the CallByName function to call stuff in Excel Word whatever.

Although it's included with the Add In.. you can find the code Here.
Downloads page

Anyway... as I said, the Project turned into something that just kept growing!

I ended up writing code to manipulate code... See more...

I ended up writing code to manipulate code... Insert Dims.. Delete Dims.. Sort them.. Delete Debug code Insert Debug code.. Save bookmarks.. Put a breakpoint at bookmarks.. Insert error code.. lots.. and of course produce a cross reference.

Because I was trying to "de" Excel things, most of the procs use arrays with a bit of Excel code to produce a report from them at the end. My thinking was that because the report is in a set of arrays ... it will be possible to pass them to Word if I wanted or dump them to a text or rtf file or whatever.

This has led to writing a whole bunch of procedures to try and be as "generic" as possible. I just adore generic!

I started with using an XLSM workbook and ran the procs from the Tools/Macros menu.

This was fine, but I also wanted to run a bunch of Procedures against a piece of code and not have to do it one at a time.

After advice from Simon, I did something I'd been putting off ... mostly because I was in my comfort zone :-) ..and moved the project to an add in rather than an XLSM. This had its own set of problems as I couldn't use my save procedure any more! I wrote a little Sub called subSaveMe and put it in its own module. Here it is.

Hide this content.

Before I go any further you can download what I have so far... the complete Add In... from this page
Downloads page

Issues / Stumbling Blocks / Solutions

The prime consideration when writing code to play in the VBE is don't do it to yourself! Sometimes it works but mostly it doesn't. Can't be relied upon. I'm not going to mention the probable gender of the designer. ;-)

"Excel has stopped working"... See more...

VBA uses p-Code to run. As most of us know, this is not "real" compiled code but a sort of interpreter implementation. It uses pointers to "know" where things are and where to go next. If you alter the position of the code you are running then you've just pulled its feet out from under it. It hasn't a clue. I can't tell you how many times Excel and word have shut down on me and restarted. One er... pointer... though is this seems to be at module level. You can run procedures in one module against procedeures in a different module quite safely. So I split up the main procedures into different modules so I can run various ones againt other modules in the same project.

With all of those "Excel has stopped working" messages ... you loose stuff. I've rewritten loads!! Had to! Key then is... save a lot. I also coded to Exit Sub if the name of the procedure I'm altering is the same as the one I'm using to do the altering. That helped!! :-)

The biggest problem has been not being able to use breakpoints for debugging. I've been forced to use Debug.Print and being the person I am, I've written quite a bit of code meant to insert Debug.Print lines and comment / delete them.

I've had to learn how to use the menu items in the VBE. That was a revelation! It's a great pity I think, that some of the actions from the menus and icons don't have keys associated with them.

Stephen Bullens VBEOnKey has been great here... I use it to run subs to set bookmarks and breakpoints at the same time with a key combo for example. This means I can page through the breakpoints... again with a key combination. If you use a "normal" naming convention there will be Upper case letters in the middle of your names. It's a real bother sometimes to not be able to move to the next Upper case letter. This is another case where I used VBEOnKey to run a Sub I wrote to do that.

Here's a link to Stephens Page where you can Download VBOnKey and lots of other goodies! Stephen Bullens Excel Page

One problem though has been that VBEOnKey doesn't work properly when you access the VBE objects all the time and it needs resetting :-( Doesn't seem to be a way around that.

Another terrific piece of software has been MZTools. One thing I've tried NOT to do is duplicate anything from it. I was originally going to include a statistics page for example, but MZTools covers that more than adequately! The Review will tell you all of the places a Dim isn't used. I do that as well but I delete them and then sort what's left and tell you what I've done.

Here's where you can download the ummmmmm Free Copy of MZTools 3. MZTools

While I'm mentioning software I'd like to recommend that you check out Chip Pearsons site. He's a wizard of the first water and a charming man as well.

You can find his pages Here. Pearson Software Consulting, LLC

Hide this content.

And another problem was..... Spurious Events! See more...

I asked about this on the forum. You can find find the thread here. Weird ListBox Behaviour

On the original UserForm I had lots of Frames stacked on top of one another and going visible invisible and so on depending on what was clicked pressed or spat at. I had a Change event firing twice where I only wanted it happeing once. Aflatoon spent quite a bit of time helping me and solved it by suggesting a module level boolean variable and checking it inside the Change event. The "trick" here though was to put the same code in *all* of the ascociated ListBoxes that were being updated.

Here're the code..
Option Explicit 
Dim blnmLoading As Boolean ' Module level Boolean

Private Sub lstProjects_Change() If blnmLoading Then ' Added Code Exit Sub ' Added Code End If ' Added Code subSelect "Projects" ' ********************************************************************* End Sub

And Aflatoons comments...
"Userforms can be a little touchy about stacking top layer control in my experience and you have frames on frames on a multipage"

When the ListBoxes were being Changed I set blnmLoading to True and False when done.
Simple... Thank you Aflatoon!!

Personal style point. I put a row of asterisks at the bottom of all my procedures because it helps when scrolling in a codepane window and especially printing to make the end more noticeable.

This led me to revamp the UserForm. I got rid of the Frames and stretched the list boxes out so they were all visible at the same time.

Hide this content.

A hiccup occured while I was building a bookmarks module. No matter how I tried I could not get beyond the first bookmark programatically. As a last resort I dotted DoEvents all over the place. It worked. By deleting them one by one I found the ones that were actually needed. It ended up being around a call to recurse.

Sub subccSaveBookMarks()
End Sub

Sub subccSaveBookMarksR()
End Sub

DoEvents is your friend when coding for the VBE.

The UserForm so far!

Here is the "Main" multipage of the form to select procedures to run and procedures to run against.

VBE Code Utilities

The first two pages on the multipage are labelled Tree and List. On Tree there is a treeview of the projects, modules, Subs in the Modules and which Subs are called in them. D/Click on a sub and you will open it in a new codepane. On List there is a listbox showing all the procedures with project, module, number of lines and the first line number of the procedure. D/Click on a sub and you will open it in a new code pane. This is a sorted list of ALL the subs.


A tricky issue was how to actually run all of the different procedures against a piece of code. I wanted to be able to run more than one of my little... ummm.. some not so little... babies against a procedure at once, Hence the above UserForm GUI.

My solution is to...
1) Delete a module called mRun.
2) Create a new module called mRun.
3) Create a new sub in mRun.
4) In the sub, insert procedure calls from the selected procedures from the lstSelected ListBox.
## Note that it is possible to change the order of being called in the ListBox using the UP and DOWN buttons.
5) Use subGoTo... guess what that does ;-) to go to the selected procedures to run againt.
6) Run the built procedure in the mRun module.

It sounds a lot more complicated than it actually is.

I'd planned a set of "Options" to load/save... and I still may do that... but at the moment I don't include any as I thought I'd try and get feedback first on what other people may want. One of the options was whether to report to different sheets or all on the same sheet. Then I thought ... not generic enough!! so I zapped it.

Like a great many of us, I found I was writing the same code over and over. A major Sub that I use all the time has been one to return information about where the cursor is. The procedures grabs info on which proc you are in and sucks out the procedure code and various things about it. Here's the Call and What it returns.

It occured to me that I... and others... may want to run a particular set of procedures again. See more...

Running a particular set of procedures over again means saving them.


In order... these would...
Change double blank lines to single blank lines.
Delete all lines with Debug.Print.
Delete all lines with Stop.
Delete all lines with "' Inserted" at the end.
Delete all lines with "' Debug" at the end.
Remove any end of line comments.
Look for any variables without a Dim and insert a Dim for them.
Look for any Dim not being used and delete them. Sort the remainding Dims.
Produce a Cross Reference of variables.

There's a complete list of subcc Subs here.

Pressing Save on the Select and Run page will prompt for a name and a folder and save this list. The multipage called Procedures sets has a browse button prompt for a folder. The folder names are not saved! The Procedure sets are loaded to a list box and a D/click on a set will load it to the Select and Run page.

Procedure sets are saved as text files with a prefix of "ProcedureSetyyyymmddhhmm".

Hide this content.

A couple of thing`s I've often wanted to do are to reset a set of breakpoints and to page through breakpoints. I's not possible to do this at all in the VBE... or is it? :-) See more...

My approach here is to use the humble bookmark. Using the menu you can page through the bookmarks and it's possible to .Execute a menu item. It's also possible to set a breakpoint with a menu .Execute. Put the two together and you can loop through the bookmarks and set breakpoint at them.

Going to the next bookmark programatically meant buying yet another wig. Not really but I think you get the hair pulling reference! See above for the solution.

This also meant writing a similar set up for breakpoints as for the Procedure Sets. Hence the multipage called Bookmark sets.

BookMark sets are saved as text files with a prefix of "BookMarkSetyyyymmddhhmm".

Hide this content.

It's all in a name

For a long time now I've used prefixes for module names to control where they are in the project explorer. I usually put modules I'm working on at the top. You may have gathered by now that I'm an idle sod... I don't like moving my mouse too much :-) It's not rocket science and works. I do the same for procedure names... but for this project it goes a bit further. I always prefix my Subs with "sub" and my functions with "fnc". Now the subs I use for the actual manipulation.. should that be womanipulation I wonder?... are named subcc ... cc=codecode.. get it? :-). The userForm above loads list boxes with sub names. But.. the ones with subbcc at the front don't get loaded to some and do get loaded to others I don't have to provide an exception or inclusion list... though I've tried to write the Subs to do that if needed. Names... as all the great magicians will tell you... not that I am one... are important.

There's a complete list of subcc Subs here.

Incedentally, within a module I move the procs around so that Ctrl End or Ctrl Home will take my directly to the proc I'm editing. The lowly bookmark is useful here as well and I'm convinced not a lot of people use them!


As mentiond above... You can't use BreakPoints!! AAAARRRGGGGHHHH!!! Well you can... sometimes.. but it's tricky to know when. It's a whole new world learning to *depend* on Debug.Print instead of just clicking and being able to pause at that pretty red line!

I had to write subs to dump arrays to the Immediate Window because I use them so much and can't breakpoint to the VBE to Watch them.

I'm sooo happy with the routine that removes Debug.Print lines!

Having procedures in their own modules has another minor advantage of being able to bring them up from the explorer rather than wading through a long list from the pull down in the code pane. Did I mention I was lazy?

Personal style point. I always try to keep all of the code visible and not have to scroll across. This means I use a lot of continuations and because of this I found that MZTools has a problem with...

Sub subInsertDims( _ 
         Parameter1, _ 
         Parameter2, _ 

Page Visitors: 8258, Last Visitor was Guest at Yesterday - 23:25.
Page Created at 3rd October 2011 - 07:04, Last Modified : 1st May 2014 - 18:09.
The Code Cage Affilliates

http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 03:47.

Powered by: vBulletin Version 3.8.6 & Logician's WebTemplates v.3.7.00
vBulletin Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft