slow vba running with xlsm files

Hey Diego,

There's nothing like that a command runs faster in xls and slower in xlsm. As we know, xls, xlsm, xlsb are just file extensions that tells us what kind of data is stored in those files. xls are basically older versions of Excel app. Since office 2007, the types are more specific in Excel which can clearly intimate what kind of data is available inside Excel app by just looking at it. From 2007 onwards, there has been a collaboration of Excel with Windows ZIP, thus, forming its structure as XLS + ZIP = XLSX. This was done in order to reduce the file size by compressing it. Microsoft has also adapted XML to form the base structure for the app thus making it more light and dynamic. XLSM is same as XLSX, but with an extra feature that it is specifically for allowing the macros to be stored within the file. And yeah, XLSX can't store macros.

The speed of macros actually depends on many things:
1. CPU
2. RAM
3. Application Settings
4. And last but not least, your code.

There are other things too but the above four are the major ones that has an effect on the speed of a macro. We can't help on the first two ones as those will involve costs but can manage the last two. Following is a code you can implement in your application to speed up by making sure no unnecessary steps get involved when you're running the codes.

Please Login or Register  to view this content.

The first ones, disables flickering of screen when macro runs. The second line makes sure sheet doesn't calculates when running macro. You could use Application.Calculate to calculate the cells when necessary. Third step stops prompting with unnecessary alerts like Allow-merging-Pop-ups. And last one makes sure no event gets triggered when the data is manipulated in the sheet. Enable it explicitly using Application.EnableEvents = True when you need the events to be triggered. I personally use this macro to make sure all of the settings are set to speed up my macro and I explicitly command if there special cases like calculations or triggering of an event.

On the last part, it depends on how you write code. There cannot be set instructions to use this or that when writing a code, because there's no restrictions to it. Its just our practices that makes a difference. Like for example, I try my best to avoid maximum number of loops as we all know, they eat a hell lot of memory, but in some cases they can be the best approach. There are always alternatives, so just find one for your own self.

If you post your codes, one or a few people in this forum, might be able to help find the part of the code that's making the processing slow. Cheers! Hope, you've got your answer.

Mm.. I believe that's because of the security issues. As xlsm stores code, the application first checks if the file has the authentication to allow the codes to run by the user. If the user has previously allowed the run then it is considered as authentic if macro settings is set to "Enable All Macros". For xlsx, there is no need of authentication as it doesn't allow storage of any codes when saved and so would always be safe to open it. The reason is because files can be designed with macros to steal the information from a client's system if the creator's intentions aren't good. So, this might be the case, I believe..

Open for thoughts!

Originally Posted by Dturazza

My macro involves basically opening file/ copy/paste /close file operations but the difference within the two formats (xlsx vs xlsm) is evident (about half a second with xlsx, about 5 seconds with xlsm)

I suspect you will be having any On Workbook Open Event on those .xlsm files which may be the root cause of this problem.

Yeah I found it!

The problem is that with the xlsm the excel interacts with the macro in some way, even if the macro does not calculate anything (I set calculation to manual...). I adopted the following code found at http://social.msdn.microsoft.com/For...cel-from-vbnet

Please Login or Register  to view this content.

I'm so happy!!
Thanks for the aid!
Diego

Glad you fixed it and thanks for the feedback

Also please select Thread Tools from the menu link above and mark this thread as SOLVED.