none
Slow VB Code RRS feed

  • Question

  • HI

    I know there are issues with sha1 encryption that MS don't seem too bothered with, but can someone explain this

    If I open a spreadsheet and run

    Sheets("Sheet1").Protect Password:="mypassword", UserInterFaceOnly:=True

    it takes ages- but after doing it the first time- if I re-run it, it's like I'm back in 2010 (simpler days!)

    I need my code to update sheets that I need to be protected- anyone any idea how to speed it up?

    Thanks for help

    David

    Friday, June 10, 2016 4:31 PM

Answers

All replies

  • Sheets("Sheet1").Protect Password:="mypassword", UserInterFaceOnly:=True

    I need my code to update sheets that I need to be protected- anyone any idea how to speed it up?

    There is no way to speed that up.

    Andreas.

    Saturday, June 11, 2016 10:32 AM
  • Hi David_1234,

    I agree with Andreas Killer. there is no way to do it. we don't have any control on that.

    you had mentioned that it takes much time. I want to ask you how much time it consumes to protect a sheet?

    when I try to run above line it protect it immediately.

    do you have a very long or complex code? maybe because of that or some other calculations are taking much time and you are thinking that the line above takes much time.

    I recommend you to run this line in newly created workbook and note the time again. did it take same or less then the time then your current workbook.

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 13, 2016 12:32 AM
    Moderator
  • Deepak,

    using excel 2010- it takes no time. <1 sec

    Currently I have 2013- once I open a spreadsheet it takes a number of seconds to do this for about 15 tabs- (say 20 seconds)

    If I run the same piece of code again without closing the file it takes < 1 sec

    I know SHA1 is used to encrypt a sheet has the speed of this been addressed in excel 2016?

    D

    Monday, June 13, 2016 9:57 AM
  • Hi David_1234,

    it looks like when you run it for first time it takes some time to complete the process and when you try to run again it will execute immediately because some steps are already executed.

    yes you can try this using Excel 2016 I had test with only 1 tab it executes immediately  but I did not tested it with 15 tabs so you can try.

    you had mentioned regarding SHA1.

    do you want to implement it?

    Regards

    Deepak

    Tuesday, June 14, 2016 5:46 AM
    Moderator
  • My file actually has 45 tabs- but a user only sees ones relevant to them

    It takes over 30 seconds to run the enclosed when it's run the 1st time

    If I run it a 2nd it takes < 1 second!

    I don't have 2016 can you possible see what time for 45 blank tabs on that?

    Cheers

    D

    Private Sub Workbook_Open()
    Dim wSheet As Worksheet
    For Each wSheet In Worksheets
    wSheet.Protect Password:="password", _
    UserInterFaceOnly:=True
    Next wSheet
    End Sub


    Wednesday, June 15, 2016 10:07 AM
  • What performance does your computer have? If low on memory and an old i3 or AMD CPU it might explain the results. Can you test run on a better computer with an i7 processor and at least 8 GB RAM?

    My test run was immediately finished.


    Best regards, George

    Wednesday, June 15, 2016 1:17 PM
  • Running Excel 2013

    I7processor 8Gig memory 500 gig  SSD HD- so should be plenty quick- I know it takes no time on excel 2010- did you run it on 2016?

    cheers

    D

    Wednesday, June 15, 2016 1:28 PM
  • Yep, 2016

    Best regards, George

    Wednesday, June 15, 2016 1:29 PM
  • My file actually has 45 tabs- but a user only sees ones relevant to them

    It takes over 30 seconds to run the enclosed when it's run the 1st time

    If I run it a 2nd it takes < 1 second!

    I don't have 2016 can you possible see what time for 45 blank tabs on that?



    I've created an empty file, used this code to create the sheets:

    Sub Setup()
      Dim i As Long
      For i = Worksheets.Count To 45
        Worksheets.Add After:=Worksheets(Worksheets.Count)
      Next
    End Sub

    Here are the timings for different versions of Excel inside virtual machines, i5-4570 3.2Ghz, any machine has 4-8Gb RAM, access to 2 CPU cores, main machine with 24Gb RAM, means more then enough for all machines.

    Version Seconds
    2007 0.00701
    2010 0.01081
    2013 4.80537
    2016 4.58542

    My machines are faster, because the sheets in my test are empty. But it is obvious that the protection needs a lot of time in 2013 and above. As I said: You can't make it faster, I now what I'm talking about.

    Do you believe me now? ;-)

    Andreas.
    Wednesday, June 15, 2016 3:08 PM
  • Cheers for that

    Anyone- even microsoft able to explain why if i run it a 2nd time it takes no time at all

    Andreas does you time improve if you run a second time as well?

    thks

    D

    Wednesday, June 15, 2016 3:15 PM
  • Hi David_1234,

    I try to run your above code in Excel 2016 with 45 Tabs.

    it takes 10 secs when I run the code for 1st time.

    it take no time when I run the code after that.

    if you have data in sheets probably it will take some more time.

    and we don't have any control on this to reduce the time to execute the code. This how it works and it will take this much time.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, June 16, 2016 6:25 AM
    Moderator
  • Anyone- even microsoft able to explain why if i run it a 2nd time it takes no time at all

    For me it's logic, isn't it? When you run a code that protect the sheets, but they are already protected, it skips the internal code for the protection.

    When you unprotect the sheets and protect them again, you'll get the same timings.

    Version On Off On 2nd Off 2nd
    2007 0.00285539 0.26921746 0.00255535 0.23186215
    2010 0.00480809 0.24413517 0.00214106 0.24830312
    2013 4.73761261 4.82445864 4.73194086 4.81967562
    2016 4.56229882 5.06268082 4.48379263 4.97844107

    Here's my test file:
    https://dl.dropboxusercontent.com/u/35239054/Samples/6ec73a79-bd80-45ce-9db3-b788205f020d.xlsm

    Andreas.

    Thursday, June 16, 2016 1:18 PM
  • Hi David_1234,

    so now Andreas Killer had proved that why its working like that.

    hope you got your answer from Andreas Killer 's suggestion.

    If you think that suggestion given by Andreas Killer is an answer for your question then I would recommend you to mark the suggestion as an Answer.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 17, 2016 5:04 AM
    Moderator