Closed Thread
Results 1 to 27 of 27

Macro(VBA) works too much slowly in excel 2013

  1. #1
    Registered User
    Join Date
    12-06-2012
    Location
    taukuba, japan
    MS-Off Ver
    Excel 2013
    Posts
    3

    Macro(VBA) works too much slowly in excel 2013

    Hello,

    I am working on a project that is based on Excel macro (VBA).

    Now company need to upgrade the excel version to 2013. So I downloaded trial version and testing all the developed tool on excel 2013.

    The problem I am facing is that macro is running very slowly,especially when it changes content of cell or change color of it.

    I tried running .xls file and also tried after upgrading the .xls file to .xlsm

    For the process which was taking 1 sec to complete in excel 2003 is taking more then 1 min in excel 2013

    means there can be any problem in environment setting or else

    Some Information about excel tools

    1. The excel macro contents connection to postgre database. For this I am using OBDC connection
    2. The excel tool contains pivot tables for showing the reports. The pivot tables are refreshed by VBA coding

    Looking for help

    thanks and best regards.

  2. #2
    Registered User
    Join Date
    12-06-2012
    Location
    taukuba, japan
    MS-Off Ver
    Excel 2013
    Posts
    3

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

    Hello

    I searched more on this problem and I came to know that the code I am using for protect and unprotect the sheet is taking very long time

    My code is like this

    To Unprotect the Sheet
    Sheet.Unprotect Password:=SHEET_PASSWORD

    To Protect the Sheet
    formSheet.Protect Password:=SHEET_PASSWORD


    In Excel 2003 protecting a sheet and unprotect it again 10000 times in loop is taking 2 sec, While the same process 100 times in loop is taking 1 min in excel 2013.

    means it work around 3000 time slower in 2013. What can be reason of this problem, please tell if any one knows.

    Thanks in advance...

  3. #3
    Registered User
    Join Date
    12-27-2012
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    1

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

    Hi,

    Did you get a resolution to this issue? I'm running the latest version (15.0.4420.1017) and the problem is still there.

    Thanks in advance.

    Rob

  4. #4
    Registered User
    Join Date
    12-06-2012
    Location
    taukuba, japan
    MS-Off Ver
    Excel 2013
    Posts
    3

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

    I was protecting the sheet for restricting the user to change the cell that are not allowed.

    I could not find the solution but I changed my logic of protecting like:
    MySheet.Protect userinterfaceonly:=True Password:=SHEET_PASSWORD

    I protect the sheet only once when file opens, This allows me to change the cell content by VBA(Macro) but dont allow user to change.

    Now I only change the Locked property of cell for changing the permission to user.

    This works faster but not compared to Excel 2003.

    Still looking for better solution.

  5. #5
    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

    Exact same issue here. Was a solution ever found? Does anyone else know of a solution? Not much info online about Excel 2013. Thanks

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

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

    I can't replicate that problem - protecting and unprotecting a sheet 10,000 times takes about half a second for me in 2013.

  7. #7
    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 have 100 sheets and it takes 3+ minutes to protect/unprotect all at once. Can you share the macro that you are using? Thanks

  8. #8
    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

    Here is the macro that I am using:

    Please Login or Register  to view this content.

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

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

    I just ran a simple test:
    Please Login or Register  to view this content.
    Last edited by romperstomper; 02-14-2013 at 08:48 AM.

  10. #10
    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

    Thanks romperstomper. Your macro worked in about a second for me too using the same workbook that I'm having trouble protecting all sheets. Any idea what the issue could be?

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

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

    It seems to be a lot slower adding in all those options. I'll see if I can narrow down whether it is a particular one that causes the slowdown.

  12. #12
    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

    This unprotect all sheets macro also takes minutes to perform:

    Please Login or Register  to view this content.

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

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

    Yes - I just noticed that in copy/pasting the protect line, I forgot to change it to unprotect on the second pass - that seems to take a long time, even without the additional protection options.

    The slow protection options seem to be:
    DrawingObjects
    AllowSorting
    AllowFiltering

    none of the others seem to really affect it.

  14. #14
    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

    Do you think that this is a bug in Excel?

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

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

    It does seem to be. I'll try and get time to file the bug later.

  16. #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!

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

    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.

  18. #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

  19. #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.

  20. #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.

  21. #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.

  22. #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?

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

    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]

  24. #24
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    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

  25. #25
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    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

  26. #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.

  27. #27
    Forum Expert 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,744

    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

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