"Cleaning" Excel's VBA code

[Deniall]

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".]

 

[SkipVought]

Hi,

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. ;-)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

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.

 

[Andrzejek]

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.

 

[DjangMan]

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.