Serving the Quantitative Finance Community

 
User avatar
MaxCohen
Topic Author
Posts: 0
Joined: June 13th, 2007, 2:44 pm

Excel VBA Code Cleaning - Why?

February 15th, 2013, 9:18 am

Why do I need to do this. What is going on in the background. If I do not clean a project every few months the compiler will start showing odd behavior?
 
User avatar
PetePoplawski
Posts: 0
Joined: February 7th, 2013, 12:41 pm

Excel VBA Code Cleaning - Why?

February 15th, 2013, 1:09 pm

Hi,I don't know if it is the same case, but I've had a similar problem (so as some of my friends who use Reuters Eikon at work). The cause was the Eikon Excel Addin, it runs in the background occasionally changing active sheets etc. The longer the code the more likely it is that the Addin causes problems. This can also be caused by some other addins (Eikon is the one most aggresiveby my count) and the sollution might be the same.I fixed most of the issues by switching the addin off and working on named objects rather than active ones (instead of activesheet I'd use sheets(i) and so on).
 
User avatar
Hansi
Posts: 41
Joined: January 25th, 2010, 11:47 am

Excel VBA Code Cleaning - Why?

February 15th, 2013, 1:54 pm

I think I saw an explaination for this here sometime ago but don't remember what it was. I've had this happen and started using this thing to fix it: http://www.appspro.com/Utilities/CodeCleaner.htmI think it might be some weird caching mechanism.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel VBA Code Cleaning - Why?

February 15th, 2013, 5:16 pm

Sorry I don't get what you mean with "cleaning" a VBA project MaxCohen
 
User avatar
ashkar
Posts: 0
Joined: October 17th, 2011, 9:25 am

Excel VBA Code Cleaning - Why?

February 18th, 2013, 11:51 am

The addin saves compilation information (for optimisation probably) and then corrupts the addin over time. Seems to happen more often in large addins. 'Cleaning' it is really the only way to get around it. CodeCleaner is brilliant in doing this as Hansi pointed out.
 
User avatar
katastrofa
Posts: 7440
Joined: August 16th, 2007, 5:36 am
Location: Alpha Centauri

Excel VBA Code Cleaning - Why?

February 18th, 2013, 4:04 pm

I guess JPM didn't clean their VaR spreadsheets too often!
 
User avatar
MaxCohen
Topic Author
Posts: 0
Joined: June 13th, 2007, 2:44 pm

Excel VBA Code Cleaning - Why?

February 20th, 2013, 9:33 am

So does anybody know how to avoid or minimise having to code clean the project?Recently a certain chunk of a project has required cleaning regulary. Else lines of code with in that module will be skipped by the compiler?
 
User avatar
ashkar
Posts: 0
Joined: October 17th, 2011, 9:25 am

Excel VBA Code Cleaning - Why?

February 20th, 2013, 1:04 pm

I dont think you can ever be sure of when it requires cleaning. My team has a policy of cleaning the addin before checking in to source control. If you install code-cleaner, instead of pressing save button, get into habbit of pressing clean button (which saves it after cleaning).
 
User avatar
SpreadsheetGuru
Posts: 0
Joined: December 30th, 2014, 4:04 am

Excel VBA Code Cleaning - Why?

January 18th, 2015, 5:54 pm

There is a great article over on Spreadsheet1.com that spells out why you need to clean your VBA projects: VBA Project Code Cleaning
Last edited by SpreadsheetGuru on January 17th, 2015, 11:00 pm, edited 1 time in total.
 
User avatar
MaxCohen
Topic Author
Posts: 0
Joined: June 13th, 2007, 2:44 pm

Excel VBA Code Cleaning - Why?

January 21st, 2015, 8:51 am

Does anybody have either VBA code or an none install version of a code cleaner (i.e. not a .exe but an .xla)?