Solved: what is a code cleaner

[jazznaura]

Hi all,

Can someone please explain to me in simple terms what a VBA code cleaner does ?

What is "a build up of junk code" ?
If i don't write it where does it come from ?

I?m new to Vba (u may have guessed) and any help would be appreciated.

Thanks

[Bob Phillips]

VBA has a tendency to bloat, it just seems to grow for no apparent reason (nothing that has been added code-wise).

The solution to this problem is to export all of the code modules and re-import them. The re-import seems to refresh everything and remove that bloat.

Rob Bovey has written an indispensable addin that does it all automatically for you, http://www.appspro.com/Utilities/CodeCleaner.htm. I always clean my production code before releasing.

[Brandtrock]

VBA has a tendency to bloat, it just seems to grow for no apparent reason (nothing that has been added code-wise).

Since VBA is a smaller version of Visual Basic, does the same phenomenon occur in Visual Basic as well?

[jazznaura]

Does this bloating happen with all code, big and small?
I ask because I’ve not experienced any change to any of my code.
Is it something that is very rare?

[lucas]

You will need to run the code cleaner on your files and compare the filesize before and after running it.

[Bob Phillips]

Since VBA is a smaller version of Visual Basic, does the same phenomenon occur in Visual Basic as well?

That's a good question, and whilst I do not know for sure, I think I can take a good stab at it. As VB holds all of the project modules as separate files, I would think not. The bloat seems to be incurred by some interaction with VBA/Excel in the files, as they are conatined within the host application.

And I have never noticed it in VB.

[Bob Phillips]

Does this bloating happen with all code, big and small?
I ask because I?ve not experienced any change to any of my code.
Is it something that is very rare?

You won't notice any change to your code necessarily, it doesn't actually corrupt the code, it just bloats the file.

Test it and see. Save the workbook, and note its size. Then clean it and save it again, and see if it is smaller.

When I say it doesn't corrupt the file, it does occasionally mess it up. many times I have had inexplicable things happening, code doing things I knew it couldn't do, which were then solved by cleaning.

[Brandtrock]

That's a good question, and whilst I do not know for sure, I think I can take a good stab at it. As VB holds all of the project modules as separate files, I would think not. The bloat seems to be incurred by some interaction with VBA/Excel in the files, as they are conatined within the host application.

And I have never noticed it in VB.

Thanks for the reply Bob. You mention that some interaction between VBA/Excel seems to be the cause. Is this unique to Excel, or does it happen in the other MS applications that are VBA compatible? What about non MS products such as Corel Draw and AutoCAD? Have you, or anyone else, seen those files bloat as well?

Just curious mostly, I haven't got the time right now to do any research beyond reading the replies here. I appreciate the information that has been shared thus far. Thanks again.

Regards,

[jazznaura]

Thanks XLD for all the info.
I was concerned that additional code was generated but now I understand better.
Thanks,

[Paul_Hossler]

MHO:

I believe that the code bloat and the eventual instability comes from extensive editing on the VBA modules.

The VBA editor garbage collection/cleanup leaves a lot to be desired, and seems to eventually currupt the VBA by leaving pieces of deleted code around.

Many times one of my well behaved workbooks will start acting erratic (e.g. different results for the inputs) or even crashing Excel after one too many cut and pastes or other edits

Cleaning the code will usually fix this sort of thing, and get me back to just the usual kind of errors I make.

Paul