Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


"Cleaning" Excel's VBA code

"Cleaning" Excel's VBA code

"Cleaning" Excel's VBA code

Every so often I come across articles that talk about how making heaps of modifications to VBA code in Excel spreadsheets can lead to a build-up of "rubbish" in the code, and how that rubbish can eventually lead to execution problems.  The solution, apparently, is to "clean" your VBA code every so often.  It seems there are products available that will do this cleaning for you.

Is there any truth in what these articles are saying?

If so, what product(s) would people recommend?

[I use Excel-2010 (32-bit version) on a 64-bit Windows-10 (home edition) operating system.  I develop and maintain spreadsheets in .XLS, .XLSM and .XLSB "formats".]

RE: "Cleaning" Excel's VBA code



now increasingly-fully-retired

Is that an asymptotic expression?

Sorry, I couldn’t help myself. I was impelled by a demonic redundant impulse.

Over the decades (well, two and change) (unlike you I am fully retired) I’ve rewritten some of mine and others’ inherited code. Never trusted the cleanup hype. I like my own style and rewriting can be therapeutic; polishing that apple. I can always do better as I learn.

Well my 2 cents plus $3.48 may get you a cup of coffee. winky smile


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: "Cleaning" Excel's VBA code

Definitely asymptotic:  here I am, seven-eighths of the way to infinity, but still "minutely-un-retired". (And, like you, still furiously busy and still learning things.)

Thanks for your views, which are appreciated as always.

Keep those impulses impelling you.

RE: "Cleaning" Excel's VBA code

The way I understand the expression: '"clean" your VBA code every so often' is to get rid of declared variables but not used any more, established Subs and Functions that used to be used, but they are not any more, etc.
You may have a Click event for a command button, you eliminate the control, but the Sub it still in the code. Doesn't bother anybody, but is not doing anything any more - why keep it?
For those tasks I use MSTools

---- Andy

There is a great need for a sarcasm font.

RE: "Cleaning" Excel's VBA code

MZ Tools does have a "clean" function which will export and then re-import your code. I write VBA that's in hosted environment that, every once in a while, starts acting "odd". To fix I open the macro add a blank line (or sometimes remove one) and resave. Problem gone. MZ Tools also has a dead code review that @Andrzejek was referencing. I highly recommend MZ Tools.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close