Bloomberg Formulas and Manual Calculation

Hello

I was wondering if there was anyway to get Bloomberg formulas to finish calculating on their own (without activesheet.calculate and the like) if Excel calculation is set to manual.

I tried events, but had no luck there.

The formulas I'd like to calculate are on one sheet, but the whole workbook has to be in manual. Is there a way to just have that sheet be set to automatic?

Originally Posted by MrShorty

I am optimistic that there is some way to do this.

As far as I know, the manual/automatic setting in Excel options is strictly global. I am not aware of a built in way to set a single sheet in a workbook to automatic.

As your "activesheet.calculate" example shows, the calculate method can apply not only to all open workbooks, but to individual sheets, and even specific ranges: https://msdn.microsoft.com/en-us/lib.../ff834613.aspx I would be curious what events you tried and how they failed. The generic approach I would expect would be to have code that invokes the calculate method on the desired sheet/range as part of a suitable event code procedure.

I've tried a while loop:

Please Login or Register  to view this content.

That didn't work and basically just kept trying to calculate.

I've also tried the same sort of thing with a "Do While" (which also got stuck) and a simple "If", but neither of them gave me anything good.

I tried adding "Application.Wait" to the original macro after the sheet calculate, that did nothing. I also tried adding it to the sheet events, and no luck there.

I tried adding a loop through each cell on a change event.

As it stands, I'm running out of ideas.

Originally Posted by shg

Considering the problem inside out, maybe ...

Please Login or Register  to view this content.

This has an interesting concept. I'll see if I can make it work.

Originally Posted by shg

You're welcome, good luck.

I'll give it a try. Thanks!

Unfortunately, there are processes that others use that require all of the sheets to have their EnableCalculation = True.

This means that I can't disable calculation on them because it would ruin other people's processes.

So that brings me back to the original problem: Is there anyway to let Bloomberg finish calculating its functions when the application is set to manual (other than sheet.calculate)?

Originally Posted by shg

So why can't calculation just be set to automatic?

It seems like you're chasing your tail.

The workbook's a bit massive. It will run way too slowly and/or crash if it's set to auto.