LinkBack URL
About LinkBacks
It does seem to be. I'll try and get time to file the bug later.
It does seem to be. I'll try and get time to file the bug later.
Re: Macro(VBA) works too much slowly in excel 2013
I ran into the same problem this week, after migrating from Excel 2010 to 2013, and found this thread really helpful.
However, my solution was a little different: I now do a
Sheet.Protect Password:="", UserInterfaceOnly:=True
when activating the Workbook, for each sheet once, and omit any real password. This is not so safe but in my case just good enough, and much faster, since the password is an empty string. Performance remains still a problem even after removing all
Sheet.Unprotect/.Protect
like I did in my workbook (that uses Drawings for an interactive graphical user interface); it seems that re-protecting the worksheet still consumes a lot of processing power after each change of value in a cell or moving some graphics around.
The UserInterfaceOnly:=True method is definitely much better than wrapping code in .Unprotect/.Protect; everything such as inserting/deleting rows, columns, validation lists etc. works from VBA but sheets are protected against accidental damage by users. However, the security model of Excel seems less than appropriate. Why isn't authorization not simply a detached process managing the user access profile instead of hacking the SMA-512 hash each time when running through some VBA code? Well, it seems to me, Excel 2013 still isn't Enterprise-ready, even if I never saw an Enterprise not relying heavily upon Excel Workbooks...
Any better solution?
I got the same problem. My Macro is mainly calculating stuff and updating the Worksheet. I got 2010 and 2013 on my computer 2010 needs about 1.5s and 2013 needs about 8.5s for exactly the same task. Somehow yesterday I managed to get 2013 to do the task as fast as 2010 then I saved and closed the file and restarted Excel today and it is back to 8.5s. Yesterday I cleaned up the complete code and made it "Option Explicit" (that is supposed to make it a bit faster). The code is still clean, but 2013 is again slow.
What made is suspicious is that the time is almost exactly as slow as it was before I made some changed in an earlier version to prevent redundant calculations: Instead of doing the calculations again and again I just do them once in the Worksheet and look them up by VBA. That worked quite well.
I turned off the automatic recalculation of the worksheet and then suddenly the Macro was quick again!
In conclusion: Excel2013 seems to recalculate your sheets whenever you make changes to the sheet even if they do not affect the cells which are recalculated. This means that you have 2 options to make Excel fast again:
1. Do all calculations before you copy the results to your sheet (quite annoying).
2. Put parts of your code in between:
Application.Calculation = xlCalculationManual
and
Application.Calculation = -4105
And make sure that there are no relevant calculations carried out in the worksheet within that code block i.e. make sure that the information you paste into the worksheet is not correlated to information you read out from the worksheet within such a code block.
My Code even got faster now and just takes 0.7s.
P.S.: to check your running time use:
Start = Timer
'Your Code here
Debug.Print Timer-Start
And you get the running time in the Immediate Window of VBA.
Last edited by blablubbb; 11-13-2014 at 07:39 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks