Search code examples
excelcom-interopvba

How to map Excel cells to a VBA function returning table of data?

I have a dll exposed via COM to VBA macros in my Excel workbook. In the dll, the primary function takes as input a bunch of data from various cells in the worksheet, and produces a table of outputs to be displayed elsewhere on the same excel worksheet. I am trying to figure out how to have this function called any time one of the input cells change, populating all the output cells at the same time.

It has come to my attention that a VBA UDF cannot write to the speadsheet calling it (no side-effects)... it can only read from the spreadsheet and return values through the functions primary return value.

Is there a good way to map a large input set to a large output set without having to call the dll separately for each entry in the table?

Solution

  • Yes. If the function returns an array, you can select all the future result cells, enter the formula once,

    =TheFunction(a1,b2,c3)

    and press Ctrl+Shift+Enter.