Bloomberg Functions won't Recalculate Automatically when Calculation set to Automatic?
In Excel, you can set auto-calculate ON (Automatic) or OFF (Manual). ON means the cells in the workbook will re-calculate automatically as anything change in the spreadsheet. OFF means when anything change in the spreadsheet, all related cells won't refresh until you manually tell Excel to refresh (e.g. press F9 or Shift+F9)
However, if you have a workbook which contains Bloomberg functions, those functions WON'T recalculate even you set calculation to Automatic. You still need to press F9 or shift+F9 to force the cells to refresh.
This is very annoying. But this is a fact, a reality. Bloomberg is a monopoly in the industry, and they don't care anyway. Accept this.
This has another implication in VBA development. In the macros, we need to ensure a "Caculate" statment is included to force the spreadsheet to re-calculate. For example:
However, in a lot of cases, even if such calculate statements are included, the macro may still encounter problems: It takes time for Bloomberg formulas to calculate. When an BBG formula calculates, it will show "#N/A Requesting Data..." (see picture below).
Please read the next tips "The 2 essential "correct" approaches to refresh Bloomberg formula in VBA" for standard approaches to refresh Bloomberg formulas in VBA macros.