LinkBack URL
About LinkBacks
I suspect the same. I have an existing ticket open w/ MS development team and will gladly post their response upon receipt later today. Thanks again for your help!
I suspect the same. I have an existing ticket open w/ MS development team and will gladly post their response upon receipt later today. Thanks again for your help!
No problem. I'll try and file it anyway - the more it gets reported, the more likely it may get fixed.
After a few days of working with MS developers, still haven't provided me with a definitive answer...
I did, however, come across this post on MS Community: "We are investigating, but it appears that the difference in protect performance in 2013 is due to some security measures the developers added in 2013."
http://answers.microsoft.com/en-us/o...f26fc7e?page=2
I just got this from the MS development team. If you are considering purchasing Excel 2013 and often use a macro to protect/unprotect many sheets at once I'd read the info below. In my case, in 2007 it takes about 2 seconds to lock 130 sheets in a Workbook Open event. In 2013, about 3 minutes. As for the suggestion in the last sentence, anyone have any suggestions on consolidating the number of pages and functionality relying on sheet protection?
From MS...we have verified with our product group that the behavior you’re experiencing is a result of the change in Office 2013 to use a more secure encryption algorithm that takes longer to run. As a result, it takes longer to encrypt the password for password protected sheets. For single sheet protection operations this isn’t noticeable, but when add-ins or macros unlock/lock sheets in bulk the result can be.
The product group has concluded that it’s something they don’t intend to fix in this version and unfortunately there are no workarounds other than to minimize the number of times you lock/unlock worksheets and only do it when they need to be modified. One suggestion is to consolidate the number of pages and functionality that relies on the sheet protected logic, so that fewer sheets and operations need to initiate sheet protection locks/unlocks.
I have been battling with this problem too.
Last edited by richard.briggs; 09-11-2013 at 04:16 PM.
I have removed the solution
Last edited by richard.briggs; 09-11-2013 at 04:16 PM.
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?
THFelmann,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
I've got a similar problem on this. I'm running a loop to open +- 250 .xlsx files, unprotect sheet with password, copy data from each file to a master file and then closing each file without saving.
I put a timer on the macro and the process takes +- 1 minute in excel 2010 but 28 minutes in excel 2013
found this relating to slow code for passwords
http://www.spreadsheet1.com/sheet-protection-2013.html
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.
Take a look to post#23 pls.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks