Closed Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 27 of 27

Macro(VBA) works too much slowly in excel 2013

  1. #16
    Registered User
    Join Date
    08-23-2012
    Location
    Charotte,NC
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Macro(VBA) works too much slowly in excel 2013

    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!

  2. #17
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,300

    Re: Macro(VBA) works too much slowly in excel 2013

    No problem. I'll try and file it anyway - the more it gets reported, the more likely it may get fixed.

  3. #18
    Registered User
    Join Date
    08-23-2012
    Location
    Charotte,NC
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Macro(VBA) works too much slowly in excel 2013

    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

  4. #19
    Registered User
    Join Date
    08-23-2012
    Location
    Charotte,NC
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Macro(VBA) works too much slowly in excel 2013

    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.

  5. #20
    Registered User
    Join Date
    06-06-2012
    Location
    Leeds, United Kingdom
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    40

    Re: Macro(VBA) works too much slowly in excel 2013

    I have been battling with this problem too.
    Last edited by richard.briggs; 09-11-2013 at 04:16 PM.

  6. #21
    Registered User
    Join Date
    06-06-2012
    Location
    Leeds, United Kingdom
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    40

    Re: Macro(VBA) works too much slowly in excel 2013

    I have removed the solution
    Last edited by richard.briggs; 09-11-2013 at 04:16 PM.

  7. #22
    Registered User
    Join Date
    01-10-2014
    Location
    Zurich
    MS-Off Ver
    Excel 2013
    Posts
    1

    Cool 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?

  8. #23
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,139

    Re: Macro(VBA) works too much slowly in excel 2013

    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]

  9. #24
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    421

    Re: Macro(VBA) works too much slowly in excel 2013

    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

  10. #25
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    421

    Re: Macro(VBA) works too much slowly in excel 2013

    found this relating to slow code for passwords
    http://www.spreadsheet1.com/sheet-protection-2013.html

  11. #26
    Registered User
    Join Date
    05-20-2014
    Posts
    3

    Re: Macro(VBA) works too much slowly in excel 2013

    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.

  12. #27
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,740

    Re: Macro(VBA) works too much slowly in excel 2013

    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.

Closed Thread
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1