Automatic Caluation or Manual with Bloomberg links

I have spreadsheet that pulls in data from an external source (Bloomberg). The spreadsheet has about 2000 rows and grabs ~200 data points per row. It then puts these data points into arrays in order to percentile rank the data.

If I set Excel to automatically calculate formulas it crashes as there is so many calculations going on simultaneously. As a solution, I've created a macro to calculate one column at a time manually. That prevents Excel from crashing, but what I'm finding is that using Range.Calculate doesn't give Bloomberg enough time to return a data value and I get a bunch of "#N/A Requesting Data" errors. If you are running Bloomberg bdp links in Excel with Automatic calculation for a small set of data, the data values will eventually get populated, but because my spreadsheet is so large, I can't set formulas to automatic.

The solutions I'm think of would be to either a) tell Excel to wait a while for the manual calculation to complete or b) somehow set Excel to Automatic calculations, but do it for only one row at a time.

Does anyone know whether these are viable options and how to program it?

I'm not sure about 2003 version but in 2010 I can right click on any cell within the range of cells where the web query comes in. On that menu is the option "Data Range Properties". When that is clicked a window pops up and there's an option to "Enable background refresh". Removing the check mark from that option may solve your problem.

I think the easiest approach would just to put wait until calculation is complete at the end of your calculations - e.g.;

Please Login or Register  to view this content.

Nope, unforunately these didn't work. It's almost like Bloomberg has returned a value, even if it is "#N/A Requesting Data", so Excel thinks it has completed the calculation. I think I'm going to trying breaking up the spreadsheet into several different sheets. Then perhaps I can calculate one sheet at a time and piece them all back together using paste values.

What code can I use to set one sheet to Automatically calculate formulas and not the entire workbook?

the excel calculation doenst work for bbg links. You need to use the bloomberg refresh button. To incorporate this into your code go to WAPI <GO> on a bloomberg terminal for more info