VBA Shortcut issue

[CelestialMind7]

Hi guys,

I've searched for an answer to my question but don't seem to have any luck since this is somewhat of a unique problem.

I have a very extensive vba program that I've designed for my company. I figured that before I'd start adding even more to it that it would be a good idea to simplify what I've already built by replacing some of the code with what I've read online "should speed up the total vba run. To use one instance for example, I've replaced:

Columns("L:L").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

with

Columns("L:L").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove


I've also done this for several copy/paste commands and many other. My problem is - that instead of these one liners speeding up the program, its actually running SLOWER. I've tested a smaller scale run up until the point to which I've made the changes to and original ran in 11:20 seconds and the "improved" code ran in 15:40. That's a huge difference and completely deceives the purpose of these so called "shortcuts". Does anyone have any thoughts as to why this could be happening?

Thanks,

Chris

 

[mole999]

did you copy out your code, delete the modules and then remake them posting the code back in ?

 

[CelestialMind7]

did you copy out your code, delete the modules and then remake them posting the code back in ?

Yes, I actually read online that any "Application." functions slow it down too so I created 3 new modules and merged 12 into 3 to cut down on that time as well. You think that has anything to do with it? Maybe I should redo/recopy the merges into fresher modules? Thoughts?

 

[mole999]

I hear (can't say I can prove it) that excel caches information at some level and that a full fresh recompiled code is beneficial

 

[CelestialMind7]

I hear (can't say I can prove it) that excel caches information at some level and that a full fresh recompiled code is beneficial

I created new modules and copied the code into them and timed it - 20:30

 

[mole999]

strange, someone will know the best steps

 

[CelestialMind7]

strange, someone will know the best steps

I hope so mole 'cause I'm running out of ideas. I have no idea why it would be running twice as slow. I even copied the old code into the new modules from the older template and it checks out at 11:20 so I have no idea why the "new and improved" code would be twice as slow. Hopefully someone can chime in with some ideas

 

[CelestialMind7]

bump. thoughts anyone?

 

[CelestialMind7]

another shameless bump

 

[Repush]

can't explain the difference, other than that every time you run it your code makes your file larger by adding cloumns
I assume you already put this in your code:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual