How to Clean 64 bit Excel VBA all modules (If Code Clenaer not work on 64)?

[Chirag R Raval]

Dear Sir,

in 64 bit Excel, Code cleaner not work.
I have just 1 way to clean Excel VBA project, through Debug->>Compile Vba Project.
but must step by step. till this option become grayout (means all clear, no remains to clean).

if ,prefer Open Source Tools ..

I found some link about Vba
VBA CODE DE COMPILER AND COMPACTOR
http://cpap.com.br/orlando/VBADecompilerMore.asp?IdC=Download1#Download1

OR

https://www.spreadsheet1.com/vba-project-code-cleaner-for-access-excel-powerpoint-word.html

I already start study here...
https://www.reddit.com/r/excel/comments/4trjrq/vba_essentials_writing_clean_code/

& Here
https://www.linkedin.com/pulse/why-clean-excel-vba-macros-here-petros-chatzipantazis

Can it help to clean VBA project?
OR what is the best way to clean 64 bit VBA projects?
OR there are any macro to run for to do this job?
OR any other Trips & Tricks to do this?

Please Help

Regards,

Chirag Raval

 

[Debaser]

Debug-Compile does not clean projects. You should export the code modules to text files, remove the existing ones and then re-import them.

 

[Chirag R Raval]

Dear Sir @Debaser

Thank you very much,

I also found VBADecompiler at here for specially working on 64 bit..which do automatic same as manually export all modules & import back. with little user interface.

http://cpap.com.br/orlando/VBADecompilerMore.asp (Free -Open Source)

Also

https://www.vb-decompiler.org/ (paid)

but before decompile, it must be in compiled form. (In VBA Tools, Compile VBA project & completed that compilation till its 9that button) become gray-out , means nothing further for compilation..

Please read instructions there (Free Open Source site) , its very important & very knowledgeable about compile & de compile for )

but if you want to release all variables before "End Sub" tricks found at here

http://www.vbforums.com/showthread.php?353302-Clear-all-variables-Please

What the Word "End" do when put before "End Sub" do please spread some knowledge.

Hope i can also find some best solutions

Regards,

Chirag Raval

 

[Debaser]

End stops all code and clears all variables. It should be used with care, as it's a little like shutting your computer down by just unplugging it.

 

[Chirag R Raval]

Dear Sir @Debaser

If all variables distroy before end sub then vba & projects
Can be maintain cleaned.(may be code cleaner do this as it's routine?)

Some times, for some errors, vba exit us from sub in middle ,so declared many types
Of variables, mainly objects which should need to "nothing" at end of sub , due to errors, code can not reach to that
Statement, & hence they remains in memory then how can we distroy them before do anything further?

I read about garbedge collector cleans variable after end of sub but for objects variable can not say precisely.

If " nothing " keyword just empty the object variable meansm variable it self remains in memory
How to distroy each declared variables it's self from memory without using unsafe "End" statement?

Objects=nothing, string=vbnullstring, array=erase, then how can empty other
Types of variables? (It's just empty? Not distroy variable it self before end sub?)

Can there are any code available that checks in memory for any instance of variable before
Run any sub , if found then clear it first?

Regards,
Chirag Raval

 

[Debaser]

The short answer is no. If your code ends abruptly, all references to your object variables will be cleared anyway due to state loss, and there's no way for you to get them back (short of storing every memory address location and then using APIs to try and clear that memory, which would be insane).

It seems to me that you are worrying about the wrong thing here - fix the code so that it can't (as far as that is possible) terminate abruptly, rather than worrying about what you can do after that happens.

By the way, that is not really what code cleaner is about. It's for removing the p-code from your project, not anything to do with memory usage and variables.

 

[Chirag R Raval]

Dear Sir @Debaser ,

Thank you very much for your kind attention.

I found macro from site of respected Sir c.pearon
That export all subs , forms, classes, , in your desired, perticular folder,
(Project file must be unhide before export) from project,
Create new file, start macro recording, do record something, close file,
Prompt for save your recorded macro before close, click on yes ,excel name itself it " personnel.xlsb",
Go to there, ( on newly created personel.xlsb stored) it if hidden, open it, first import, that .bas file in which this export_ import code, import all your exported modules back through their import Sub, re-reference all your necessity reference from tools, reference, in VBA editor, re-hide personel.xlsb
Your , fresh, clean personel.xlsb ready now.

I want to ask here , we must re-reference (tools, reference), from start
In this newly created personnel.xlsb, is there any trick to retrieve previously
Referenced settings before export from original?
Regards,
Chirag Raval