Close all Modules in the VBE

[Monty Norman]

Hello All,

I am running into an issue that I am seeing for the first time in my Excel career.

I have a large workbook ( about 30MB) with many modules (35 of them). The workbook was developed by someone who is no longer with us. That developer did not use best practices but such is life at times.

So, the workbook takes a bit longer than usual (sometimes up to 5 minutes) to open via code and I see it open and "unfold" all these modules.
Is there a way to collapse all the modules (something like when you press the + next to the module name in the VBE) before saving the workbook?

This:

vs this:

Images are for illustration these are not the actual workbooks involved.

The code that I am using is here:
With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = "C:\Users\" & Environ("username") & "\Desktop"
.InitialView = msoFileDialogViewDetails
.Filters.Add "Custom Excel Files", "*.xlsx, *.csv, *.xls, *.xls*"
.Title = "Choose the file"
.Show
End With

On Error Resume Next '/In case the User cancels the dialog.
sSelectedFile = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
If sSelectedFile = "" Then Exit Sub
On Error GoTo 0

Workbooks.Open Filename:=sSelectedFile
Set objSourceWrkBk = ActiveWorkbook
MsgBox "I made a refrence to this workbook: " & sSelectedFile

The Message Box is just to have an indication that the workbook was actually opened and the code continues. This will be removed after testing.

I am not sure if that is the reason why the workbook takes so long to open, I have it in the local drive, so network issues are eliminated, I have stopped all normal stuff (calculations, updating of links, screen updating and the likes) that happens when you want to speed up your code, I have code that resets the used range and clears all the empty cells, before saving, and I am not sure if the workbook is not corrupted, or, if it is, it is still being able to open and save and works as expected with no indications of trouble. This workbook is used daily and we have been using it for a while with no issues.

So that is why I think that opening these modules is the last thing to check. If you feel its might be something else, please I am all ears.

Thanks in advance!

Monty

 

[rlv01]

FYI, this may help.

VBA Editor - Collapse by Default?

In the Excel VBA editor window, is there a way to have it open with all projects collapsed by default? Every time I open the window to edit a macro, my Personal XLSB file shows fully expanded, which I don’t like because it takes up space and I have to manually collapse it to see the other...

www.mrexcel.com

 

[Skyybot]

Have you tried clearing the CACHE?