Workbook opening very slowly

[Kilgore_elder]

I have a large and reasonably complicated workbook, which uses UDF and macros to run a series of processes for analysis purposes. The file itself is 9MB, and this is without any data in it. The purpose of the workbook is that the user pastes a number of comments in Column B of the opening dashboard and the workbook then analyses the comments looking for key words from an established library.
My problem is that it takes a very long time to undertake tasks and even calculates when the file is opened. I have provision for 3500 comments of around 600 characters, but before anything is pasted into the spreadsheet, i have to wait a good 10-15 minutes for the workbook to open because it is calculating. Is there a means by which I can either disable this process until there is data, or somehow speed it up?
The workbook is a xlsm file and doesn't use any macros or UDF until the data is available.
Any assistance would be greatly appreciated.
Thank you in advance.

 

[Derek Brown]

You could consider setting the calculation mode for the workbook to "manual" and then use F9 etc. to calculate when required.
The following may be of interest:
How the Excel Smart Recalculation Engine works - Decision Models
However, you will need to be aware of the potential problems regarding "manual" calculations:
https://support.microsoft.com/en-us/help/214395/how-excel-determines-the-current-mode-of-calculation

 

[Kilgore_elder]

Thank you Derek. From what I've read so far, this may do the trick. At present I have a stepped process that leads to an additional calculation, but if I can manually calculate the initial steps, that will help immensely.
Many thanks.
Regards, Kilgore_elder

 

[FDibbins]

Even with calcs set to manual, the file will still calc on Open or Save.

What kind of formulas do you have?
If you have ARRAY's, restrict them to just the range you need, avoid full column/row references
Take the same approach with any SUMPRODUCT formulas
keep CF to a minimum

 

[Kilgore_elder]

Thanks Ford. That's good advice. I do have a number of arrays and I'll review all of them.
Thanks again.

 

[Derek Brown]

When setting the calculation mode to manual, there is an option as to whether or not to recalculate before saving.
On opening, the workbook should not recalculate as long as you don't already have a workbook open that has set calculation mode to Automatic - the second link in my earlier email refers. It is very important to be aware of the way calculation mode is determined.

 

[Kilgore_elder]

Thanks Derek. Yes, I read that. I opened a dummy workbook, set the calculation to manual and then opened the larger workbook. No calculation! i am stepping through each calculation step to see where it is all slowing down. Many thanks for your assistance.

 

[Derek Brown]

You are very welcome.
The first link in my first reply has other pages of interest:
Importance of Excel Calculation Speed - Decision Models
Volatile Excel Functions -Decision Models

 

[Kilgore_elder]

Thanks again Derek.