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

Question

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?

GSerg · Accepted Answer · 2010-11-12 22:29:59Z

1

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.

answered Nov 12, 2010 at 22:29

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related