Problem opening an Excel file

[brucemc777]

Having a weird problem which just started within the last couple of months with an ongoing project that i keep adding onto.

The subject file is rather large by my standards, 4.36MB with something like 1,200 procedures and maybe 40,000 lines of code if i recall.

If i try to click on Excel and select the file from the opening file selection window, after a period of time Excel simply disappears, but if i start Excel, open a new blank workbook, and then go to File:Open and select the workbook, it will open (albeit though it takes a little time).

I tried dropping a code line of Stop in the workbook_open code, but it seems it doesn't get to that before it hangs and quits.

I do have a number of PUBLIC declarations, which now i must think should be my next step to investigate.

Any thoughts on what else i should look at? If i had a better idea of the mechanics of the Excel startup routines i might be able to figure out more on my own, but other than guessing that it might go after the public declarations prior to loading and running code, i'm just shooting in the dark-

I'd hate to have this thing, aptly named "Kraken", bomb out now. I've been adding on to it for the last three years! The sick thing is that i am now mainly working on trying to optimize the code...

Thank-you all for your thoughts, and MERRY CHRISTMAS!!!

[kersplash]

Start with a blank workbook and copy in your code, then your sheets one at a time. Try to open the workbook after each change to see if you can isolate the issue.

[scottiex]

How big is your largest module? I hear that can cause issues - you could try splitting the largest one up into two. And I think there is a limit on how many public declarations you can handle - you could try reducing that if you have a huge amount.

Maybe the forum can suggest some better (in this case more stable) ways to do what you are doing.

[brucemc777]

Dang. Must of been one of those Office 365 "improvements". Because the majority of my procedures use the same two listobjects, i publicly scoped them. This worked fine for a number of months. Can't do that anymore, at least not with this...

I just went on a search for every single procedure that used each of them and "Dim"ed and "Set" each into the procedure individually... All loads proper now.