Excel 2013 is very slow in loading large workbooks, is there a way to open just the tab I want?

1) That's what I thought. You ARE protecting during the file open code.

 

2) If you can protect all the sheets, save them in the protected state, and take out the ProtectWorksheets code, the file should open fast.   

1)   Yes, you are right about that.

2)  You are right about that also. I protected programmatically, saved and commented out the reference to the ProtectWorksheets code. The file opens instantly.

However, there's one big inconvenience: I get many "Error 1004 during execution" messages after clicking command buttons: "The cell or chart you are trying to change is on a protected worksheet."

I didn't get those errors when protection was created during the file open code, due to "UserInterfaceOnly:=True" included in the protection code. Unfortunately this is not working anymore after saving the sheets in the protection state and taking out the protection code. To have UserinterfaceOnly:=True working obviously one has to protect the worksheets each time during the file open. Or do you know another way to achieve the same result, i.e. to avoid those "Error 1004" messages? 

Best regards

Jacques

Hi Jacque,

That's what I thought. You ARE protecting during the file open code. The extra time is not in open it's in the protecting itself. I protected 185 sheets programmatically, and then saved and closed the book. It opens quite fast. It does not take a long time to open a book with all the sheets protected.

The protection code itself took a long time. The reason is the stronger security in the protection, which is the same whether you do it programmatically or manually, but it isn't noticeable when you protect one sheet at a time manually. Protecting many of them programmatically compounds the problem. We have taken it to the developers already, and they say that they added the strong security to make sheet protection stronger, and will not remove it or give us a way to opt out of it because to do that would reduce that protection. I really cannot share the exact details of the changes.

If you can protect all the sheets, save them in the protected state, and take out the ProtectWorksheets code, the file should open fast. Then if you only unprotect the ones you need to, as you need to, you should not notice a huge perf issue. That is, you should not as long as you don't have to unprotect and reprotect a bunch of them at one time.

If anyone has workbooks that take much longer to open than they did in 2010, but do not have protection code running, it would be great if you could open a support case or get them to us somehow.

Thanks,

Anita

Anita

The problem of slow opening a (large) workbook is indeed happening when this workbook and its worksheets are protected programmatically (VBA code), but not when the protection was made manually I noticed.

Already I wrote this in my post of February 5, 2013 above:

"In 'ThisWorkbook', 'Workbook_Open' event, I had a reference to a module 'ProtectWorksheets' which password protects all sheets and the workbook itself when loading the workbook.

I have 'commented out' this reference and now the file is loading normally.

Of course the question remains why that reference to 'ProtectWorksheets' didn't cause any problem in Excel 2007 nor in Excel 2010."

So the problem of the slow opening seems indeed to be caused by the protection made programmatically, because when I eliminate the programmatic protection, the Workbook opens within a normal time and even when I had protected worksheets manually.

For my Workbook with 16 worksheets (protected programmatically) it takes 10 seconds after the splash screen before the first worksheet becomes available. During those 10 seconds nothing can be done (frozen) except wait until it becomes available. The little "circle" (hour glass) keeps turning and turning. As to me I think the program is just busy executing the VBA code to protect the 16 worksheets or something like that. In former versions of Office this did not happen. Notice that during those 10 seconds the Workbook is opened in Excel on the first worksheet, but nothing can be done.

Best regards

Jacques

Hi Jaques,

I'm sorry if I wasn't clear. We don't have a repro on slow opens. If you can help us get that, I would really appreciate it. You say that if your files are protected, it takes a long time to open them? I will try protecting 185 sheets and then see about opening the files and time the difference.

I thought you meant that you were protecting the sheets when you opened the books.  We have a repro on the slow times it takes to protect the files programmatically. That's what we debugged to find the root cause.

It could be that we have to check the hash when we open the files, but I'm just not sure. I'll check on that

As far as the strength of protection goes, it's exactly the same whether you protect manually or programmatically.

Regards,

Anita

Thank you, Anita

But as far as I could see (or I am missing something) that known issues list does not contain the problem of the slow loading of large workbooks protected via VBA code.

Anyway I understood the problem cannot be resolved for security reasons.

I think there's only one workaround: protect the worksheets manually and the workbook itself via VBA code (but this way the worksheet protection is not as good as via VBA code I think).

So, I'm hoping that somewhere in the future it will be possible to have security measures not affecting the loading speed of our workbooks and worksheets.

Best regards

Jacques