none
Macro runs quicker in excel 2010 than excel 2014? RRS feed

  • Question

  • I have a package I developed with numerious macros and pivot tables.

    When i open it, it performs some actions using an "open workbook" macro- all the code, tables etc were created using excel 2010- but when i open it using excel 2014 it is considerably slower- are there known differences in the conctruction of VB code or refreshing of pivot tables that could cause this.

    Anyone else experienced this?

    Thanks

    David

    Monday, April 13, 2015 2:29 PM

All replies

  • Are you talking about Excel 2013?

    Did you have a chance to compare add-ins running in both versions of Excel?

    Finally, did you try to measure the time spent on a particular task in Excel?

    Monday, April 13, 2015 2:35 PM
  • Sorry- it is 2013

    running one query in 2010 takes < 1 sec- in 2013 about 15sec's

    I have split the process that occurs, a relatively small bit seems to drag - there are a number of these in the process

    Sheets("sheet name").Unprotect "XYZ"

    if I run

    Name = ActiveWorkbook.Name
    Workbooks(Name).Sheets("sheet name").Unprotect "XYZ" it seems to run quicker-

    is there some change in construction of code in 2013 as apposed to 2010? if so is there a list of code changes that should be made?

    D

    Tuesday, April 14, 2015 10:11 AM
  • Indeed un/protecting sheets is significantly slower in 2013 because of a new and stronger hashing algorithm (SHA-512). There's no workaround except to minimise un/protect changes where possible without impacting on security.
    Tuesday, April 14, 2015 12:28 PM
    Moderator
  • Peter

    Is the new protection the rolls royce version?- Is there a way to use the "Compact car" version like in 2010? or is there a way of getting VB code to refresh a pivot table without unprotecting?

    Note- when workbook opens i use

    Sheets("sheet name").Protect Password:="XYZ", UserInterFaceOnly:=True

    which allows VB work on a protected sheet but don't know how to refesh pivot table (currently use ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh)

    without unprotecting it first

    Thanks for help

    David

    Tuesday, April 14, 2015 12:58 PM
  • Have you tried myPivotTable.RefreshTable

    This of course would only work on an unprotected sheet or protected with UserInterfaceOnly
    Tuesday, April 14, 2015 3:19 PM
    Moderator
  • Peter

    I have a number of pivot tables that use the same sheet as their source- I can get one pivottable to refresh using code below- but if I have 2 pivots on different sheets (both being protected) it won't work as it looks for all sheets to be unprotected first- is there some change to the code to enablePivotTables on all sheets and refresh all pivots at same time (ThisWorkbook.RefreshAll)

    Thanks

    D

    Sheets("sheet2").Protect Password:="xyz", UserInterfaceOnly:=True
    Sheets("sheet2").Select
    ActiveSheet.EnablePivotTable = True
    ActiveSheet.PivotTables(1).RefreshTable

    Wednesday, April 15, 2015 6:16 PM
  • I haven't double checked but AFAIK all relevant sheets should be unprotected or protected with UserInterface:=true
    Wednesday, April 15, 2015 8:03 PM
    Moderator