Wednesday, May 22, 2013

Returning dynamic array from VBA UDF into Excel

If you ever want to return non-scalar value from your VBA user-defined function (UDF)
into Excel worksheet, you have to use CTRL+SHIFT+ENTER combination to return all the values. This means,
that you have to know the exact dimensions of resulting array beforehand. Now, all Bloomberg users must have been wondering how Bloomberg has implemented its own worksheet functions, like BDH (for time-series data retrieving). When using that function, the user gives
all required function arguments just like in any Excel build-in worksheet function and function then returns resulting array, after pressing ENTER and not using
CTRL+SHIFT+ENTER combination. This means, that the user does not have to know the exact dimensions of the resulting array beforehand.

First attempt to create the corresponding functionality, would be to create some dummy worksheet UDF, which then calls separate sub routine to write an array of values into Excel worksheet. However, this attempt will fail, because a function called from worksheet is not able to modify any Excel objects (except message box). To get around this limitation (sub routine called inside UDF can not write data back to worksheet) you
can use Windows Timer API . First, timer is created at the beginning of a function call. Then, timer is killed as
soon as it calls desired sub procedure (which then writes data back
to worksheet).

Here's my own BDH mimic below, along with comments. It may not be the most elegant one, but
it should show the essence of this approach using Timer API. You can copy-paste the sample code
directly into a new VBA standard module and test it. The actual worksheet interface
function takes two arguments: value (which is going to be repeated), and
repetitions (which defines how many times the value will be repeated
below the cell, from which the function call has been made). With this approach, you could create your own UDF which takes any parameters and then retrieves data back to worksheet from database by using ADODB object inside your sub procedure what is going to be called by Timer API.More information about Windows Timer API can be found here: http://msdn.microsoft.com/en-us/library/windows/desktop/ms632592(v=vs.85).aspxThe code might sometimes look a bit odd within the frame below, but just copy-paste everything within the frame. I have tested the code and it should be working correctly. I hope you could get something out of this. Have a nice day.-Mike

The programs, which are presented in this blog, can be freely used, but without warranty or support of any kind. By using the programs presented in this blog, you accept to bear the entire risk, concerning quality or performance of any programs used. In no event, will I be liable to you for the damages, including any general, special, incidental or consequential damages arising out of the use or inability to use the programs presented in this blog. By using the programs presented in this blog, you are accepting the content of this disclaimer.