When a query is entered into the Bloomberg BDH function in Excel it automatically populates the selected cell, and cells below with the requested data.
I am trying to create a function that does something similar (albeit with a different source of data). How does the BDH function circumvent the typical restriction within a user defined function of modifying other cells when it fills down the queried data?
You need to do it in a two-stage process:
Your function calls another process to compute values and return only the 1st element of the array to be returned (could be "#NA #NA" or the date or price, etc.
The called process can be running in a separate RTD server (check out Real Time Data server) or a separate process which links to your spreadsheet via DDE. (Bloomberg uses DDE for some functions, RTD for the others, I think)
That way when you call a function, the first value is returned immediately (i.e. the answer, or "#NA Requesting Data..." and then when the RTD server fetches the required data the answer to this function changes to whatever the top-left array element is, and the rest of the cells are populated via DDE/RTD.
Simple examples of setting up a RTD server are found on Microsoft's knowledge base. Also, lots of examples using DDE (an older, but very popular technology for financial data exchange using Excel, even today) are available upon Googling.
A simple filler application can be built using C# or VB.net Interop (search again for examples).
Hope this helps.
Edit: You can also do this completely using VBA by using some clever combination of the OnTime functionality (see http://www.cpearson.com/excel/OnTime.aspx) and combining it with DDE driven from a separate spreadsheet (look up Tushar Mehta DDE clock, a very old example, but some things never change!)