VBA Cleanup Project.


"Excel VBA Help"

 


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

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

New Reply
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
  #1  
Unread 14th October 2011, 19:42
LisaGreen's Avatar
LisaGreen LisaGreen is offline
Solution Centre Professional
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Can use VBA
 
Join Date: Jun 2011
Location: Houten, Nederlands
Age: 65
Posts: 568
Posts Per Day: 0.27
My Top Tip Count: 1
Thanks to others: 9
Thanked 18 Times in 16 Posts
Reputation: 270 (13) LisaGreen is a jewel in the roughLisaGreen is a jewel in the roughLisaGreen is a jewel in the rough
1 Top Tips
Great_Britain
Default VBA Cleanup Project.
     Excel: VBA   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: VBA   

Please direct your all of your messages about this project to this thread.

My name is Lisa. I've written a set of procedures to help people tidy up their VBA projects when they have finished developing. It includes routines to check for and delete unused Dims and sort what's left, add Dims for non declared variables, save a set of bookmarks... which can be reloaded with breakpoints, delete Debug lines of code and some other stuff too.

The project is hosted by The CodeCage and is described Here... The Code Cage Forums Lisa's VBA Clean Up Tool - a work in progress!.

The whole XLAM Add In can be downloaded from a link on that page and there is also a link there for a list of the complete set of main procedures.

This is a *live* work in progress project. I would *very* much welcome any questions comments suggestions amendments improvements bug reports and so on... even complaints!! I'm hoping, though I think it's not too bad at the moment, that in time and with your help it will mature very nicely thank you!

And... once more with feeling... Please direct all of your messages to this thread.

Good coding and bug hunting!!

Hugs... Lisa

Reply & Quote Reply


2 members found this post helpful.
The Following 3 Users Say Thank You to LisaGreen For This Useful Post:
mancubus (15th October 2011), Peter (30th October 2011), Simon Lloyd (26th January 2012)
The Code Cage Advertisment
Advertisement
  #2  
Unread 15th October 2011, 00:17
broro183's Avatar
broro183 broro183 is offline
The Code Cage Staff
MS Office Version: Excel 2007 @ home & 2010 @ work
MS Office Skill Level: Can use VBA
 
Join Date: May 2009
Location: Waikato, New Zealand
Posts: 742
Posts Per Day: 0.26
My Top Tip Count: 0
Thanks to others: 15
Thanked 50 Times in 50 Posts
Reputation: 801 (23) broro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to behold
New_Zealand
Default Re: VBA Cleanup Project.

------ Register to get rid of these "In Post" ads! ------


hi Lisa,

I love the sound of this & it looks like you've done heaps of work on it :-)

Rory pointed me to your work while chatting about MZ Tools in an Experts-Exchange thread. I've been looking for an add-in version of MZTools that doesn't need Administrator permissions for installation & your addin could be just what I'm after :-)

I'm going to have a look through it this weekend & see what I can learn ;-)

Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...

  #3  
Unread 15th October 2011, 12:35
LisaGreen's Avatar
LisaGreen LisaGreen is offline
Solution Centre Professional
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Can use VBA
 
Join Date: Jun 2011
Location: Houten, Nederlands
Age: 65
Posts: 568
Posts Per Day: 0.27
My Top Tip Count: 1
Thanks to others: 9
Thanked 18 Times in 16 Posts
Reputation: 270 (13) LisaGreen is a jewel in the roughLisaGreen is a jewel in the roughLisaGreen is a jewel in the rough
1 Top Tips
Great_Britain
Default Re: VBA Cleanup Project.

------ Register to get rid of these "In Post" ads! ------


Hiya Rob,

I've tried not to duplicate mztools stuff so I hope you're not dissapointed.

The code is there though for you to play with and a lot of mz things can be created if you like. If you stay in Excel then I guess you could use the add ins sheets to save stuff as well like code snippets and so on. Otherwise it would be a bunch of text files, ini files or the registry, all of which are viable.

Let me know how you get on please!

Hugs
Lisa

The Code Cage Advertisment
Advertisement
  #4  
Unread 17th October 2011, 09:00
maninweb's Avatar
maninweb maninweb is offline
Solution Centre Professional
MS Office Version: MS Office 2010 - 2016
MS Office Skill Level: Microsoft Excel MVP 2011 - 2016
 
Join Date: May 2011
Location: Aachen, Germany
Posts: 74
Posts Per Day: 0.03
My Top Tip Count: 0
Thanks to others: 3
Thanked 10 Times in 9 Posts
Reputation: 160 (7) maninweb has a spectacular aura aboutmaninweb has a spectacular aura about
Germany
Default Re: VBA Cleanup Project.

------ Register to get rid of these "In Post" ads! ------


Hi Lisa...

firstly, really cool project

I got an error when double clicking an entry of the Treeview, located here...
VBA CODE:
Private Sub tvProcedures_DblClick() 
     ' :
     ' :
    slLineNum = slSplitTreeItems(6) '<----
     ' :
     ' :
While debugging, I have seen, that the Index '6' does not exist in all cases as the length of the Treeview Key differs. So, I would like to suggest to replace this line by for allways getting the last entry...
VBA CODE:
slLineNum = slSplitTreeItems(UBound(slSplitTreeItems)) 
Best Regards :-)

Reply & Quote Reply


1 members found this post helpful.
  #5  
Unread 17th October 2011, 13:00
LisaGreen's Avatar
LisaGreen LisaGreen is offline
Solution Centre Professional
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Can use VBA
 
Join Date: Jun 2011
Location: Houten, Nederlands
Age: 65
Posts: 568
Posts Per Day: 0.27
My Top Tip Count: 1
Thanks to others: 9
Thanked 18 Times in 16 Posts
Reputation: 270 (13) LisaGreen is a jewel in the roughLisaGreen is a jewel in the roughLisaGreen is a jewel in the rough
1 Top Tips
Great_Britain
Default Re: VBA Cleanup Project.

------ Register to get rid of these "In Post" ads! ------


Done and uploaded mainweb.

... and you've highlited a different problem. When I tried to save the add in after making the changes I couldn't... said the file was read only.

I'd tried the problem first of course and the form was still active but couldn't be seen because hid the application in the form initialize.

I've removed that now.

Thank you Mainweb!!!!!!

  #6  
Unread 17th October 2011, 13:19
RoyUK's Avatar
RoyUK RoyUK is offline
Administrator
Solution Centre Professional
MS Office Version: MS Office 2003, 2007, 2010
MS Office Skill Level: Proficient with MS Office
 
Join Date: Nov 2008
Location: Derbyshire, UK
Posts: 2,697
Posts Per Day: 0.89
My Top Tip Count: 4
Thanks to others: 2
Thanked 195 Times in 193 Posts
Reputation: 3320 (68) RoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond repute
4 Top Tips
England
Default Re: VBA Cleanup Project.

------ Register to get rid of these "In Post" ads! ------


I noticed the making the application invisible. This needs a lot of care & you must be able to restore it.

First thing - do you really need to hide it?

If so try adding code to make it visible again in the Userform deactivate or queryclose events

Also, your error handling in Procedures should also handle this problem
__________________
Hope that helps, RoyUK
For tips & examples visit free excel help
DatabaseForm
About me

The Code Cage Advertisment
Advertisement
  #7  
Unread 17th October 2011, 13:26
maninweb's Avatar
maninweb maninweb is offline
Solution Centre Professional
MS Office Version: MS Office 2010 - 2016
MS Office Skill Level: Microsoft Excel MVP 2011 - 2016
 
Join Date: May 2011
Location: Aachen, Germany
Posts: 74
Posts Per Day: 0.03
My Top Tip Count: 0
Thanks to others: 3
Thanked 10 Times in 9 Posts
Reputation: 160 (7) maninweb has a spectacular aura aboutmaninweb has a spectacular aura about
Germany
Default Re: VBA Cleanup Project.

------ Register to get rid of these "In Post" ads! ------


Hi Lisa...

thanks

personally, I prefer to not hide the Excel Application Window in my apps.
While playing around with Clean Up App, I also experienced that, if the VBA Editor is closed and then the UserForm,
the Excel Process ist still active. So, hiding the Excel Main Window leads to more event handling checks.

Best Regards :-)

  #8  
Unread 17th October 2011, 13:33
LisaGreen's Avatar
LisaGreen LisaGreen is offline
Solution Centre Professional
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Can use VBA
 
Join Date: Jun 2011
Location: Houten, Nederlands
Age: 65
Posts: 568
Posts Per Day: 0.27
My Top Tip Count: 1
Thanks to others: 9
Thanked 18 Times in 16 Posts
Reputation: 270 (13) LisaGreen is a jewel in the roughLisaGreen is a jewel in the roughLisaGreen is a jewel in the rough
1 Top Tips
Great_Britain
Default Re: VBA Cleanup Project.

------ Register to get rid of these "In Post" ads! ------


Yeah... the whole point really was to try and stay in the VBE.

As I mention in the Improvements section on the main project page, I've found some code to spawn as a child of the VBE window but can't make it work! Still trying though...

Lisa

  #9  
Unread 18th October 2011, 17:57
broro183's Avatar
broro183 broro183 is offline
The Code Cage Staff
MS Office Version: Excel 2007 @ home & 2010 @ work
MS Office Skill Level: Can use VBA
 
Join Date: May 2009
Location: Waikato, New Zealand
Posts: 742
Posts Per Day: 0.26
My Top Tip Count: 0
Thanks to others: 15
Thanked 50 Times in 50 Posts
Reputation: 801 (23) broro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to behold
New_Zealand
Default Re: VBA Cleanup Project.

------ Register to get rid of these "In Post" ads! ------


Quote:
Originally Posted by LisaGreen View Post

Hiya Rob,

I've tried not to duplicate mztools stuff so I hope you're not dissapointed.
The code is there though for you to play with and a lot of mz things can be created if you like.

No, I'm not disappointed. When looking through your code I saw Stephen Bullen's VBEOnKey code which I hadn't seen before, so I'm getting my teeth into that to set up shortcuts that:
- toggle the Project/Immediate/Locals panes (without over-riding the existing accelerator keys)
- clear the Immediate pane.
- run my "RefreshAllXlAppSettings" when I bomb out of code as I am developing.

Once I've got them sussed I can post the code here...

-----------

I've only read through your code & haven't actually tried running any of it. It would take me a loooooooooong time to get near the level of detail you've already got so I can't make too many suggestions. However, a couple of things I wondered:
- can any of the "Redim Preserves..." be moved out of loops?
(since I havne't run any of it, I'm not sure how fast it is currently, but the above may help)

- In a similar vein to Maninweb's finding, I suggest using "Lbound(" & "Ubound(" in as many places as possible when dealing with arrays.

I've had a (possibly too complex) class module for timing code as WIP for a while now which is hopefully portable between different app's & uses conditional compilation to output (eg a recordset to an MS Access Table, or an array to an Excel range). I'll check it over & can post the conditional compilation snippet if you are interested...

thanks
Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...

The Code Cage Advertisment
Advertisement
  #10  
Unread 18th October 2011, 22:12
LisaGreen's Avatar
LisaGreen LisaGreen is offline
Solution Centre Professional
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Can use VBA
 
Join Date: Jun 2011
Location: Houten, Nederlands
Age: 65
Posts: 568
Posts Per Day: 0.27
My Top Tip Count: 1
Thanks to others: 9
Thanked 18 Times in 16 Posts
Reputation: 270 (13) LisaGreen is a jewel in the roughLisaGreen is a jewel in the roughLisaGreen is a jewel in the rough
1 Top Tips
Great_Britain
Default Re: VBA Cleanup Project.

------ Register to get rid of these "In Post" ads! ------


oooooo Yes!!!

I'll include it in the add in for timing if required! And of course you get credited! I generally use the GetTickCount api, but I'm drooling over getting someone elses timing code.. Yum! Very unlady like!!

You sod!! At least try to insert a debug!!! D/Click on a variable to select it.. Tools/Marcros and run the Insert Selection Proc!

Oh... and Stephens and Chips pages are well worth keeping an eye on after an initial crawl through them to download goodies!

I'm trying to work out why VBOnKey drops out if I do VBE stuff... though I suspect it has somat to do with the callback getting lost. I think I may ask Stephen actually!

What's RefreshAllXlAppSettings... sounds tasty as well!

Do you use any sort of naming convention at all Rob?

The Redim stuff is pretty well standard in a lot of code I've seen. The alternative I think would be at least two passes.. one to determine the "extent" of the redim and another to do it. I do do that sometimes if it's possible, but most time you have no idea of the number of elements you need... and also "preserve" don't play nice with multidim arrays very well. I don't really see any performance hit on that so far thoughhhhh.... maybe your timing stuff will light some things up!!

Your points about ubound and lbound ... well taken. I'll look at changing that.. and maybe use one of my procs to do it... ... or write one! :-)

Hugs and hugs... Lisa

The Code Cage Advertisment
Advertisement
New Reply
The Code Cage at Facebookhe Code Cage at Twitter


Tags
cleanup, project, vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

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:46.


Powered by vBulletin® Version 3.8.6
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
UA-8135493-1
Cultural Forum | Study at Malaysian University