My experience is from working on Excel-DNA, which is a .NET library for making Excel add-ins that also talks to Excel. Since Excel-DNA is also based on the Excel C API, the interaction with Excel is the same as from a native C/C++ add-in. The discussion here is also related to a blog post from a few years ago about making array functions that automatically resize to the right result size.Normally, an Excel function called from a worksheet calculation will not be able to alter the values on the sheet (Excel prevents this completely). So this is why SetContents cannot work from inside a function called from a worksheet formula. If your query might return different sizes of resultsets, and you want to return these from your function, then you are going beyond the standard Excel function behaviour. You need to run a macro to set values on the sheet.One approach to get the hybrid function/macro behaviour you ask about might be this:* When the function gets called, get the resultset, and then check the caller (using xlfCaller). If the caller is a reference of a size matching the resultset, just return the results as an array.* If the caller is not sized correctly, store the caller information, result size and optionally a cached version of the results in some internal structure. * Then enqueue some 'fix-up' macro to run after calculation is complete.* In the 'fix-up' macro, check the mismatched size, and change the formula to be an array formula of the right size. Setting the formula will force a recalculation.* The recalculation will now be from an array formula of the right size, so the fix-up macro need not run again.The main problem is to get the fix-up macro to run reliably. In the Excel-DNA example, I call the COM API function Application.Run(...) to run the fix-up macro. This needs to be done carefully (with good error handling) to be reliable. Recent Excel-DNA versions actually install a dedicated message window on the main thread to ensure that this kind of call happens on the main thread. But it's still tricky.There are some reasons to update the formula instead of just dumping the results to the sheet. Having a proper array formula leaves the dependency graph in a consistent state, so you can see which cells were populated by your query function, and hence how they might depend on other cells. Also, the query function will update only when it's inputs change (unless you make it volatile). And one can't accidentally delete, modify or leave behind some data that depends on the function when casually editing the sheet.Changing the formula to an array formula of the right size from that fix-up macro is also a bit tricky. Using the C API there are some issues with setting a formula - you need to select the right sheet, and take care of A1/R1C1 naming styles etc. But the Excel-DNA show how you might go about that. This aspect works one once one gets past all the Excel quirks.If you have to do this kind of thing, which clearly goes against the functionality Excel supports, it can be done. And I think the above is the only safe and sensible approach.But Excel certainly does not make it easy.All of that said - I know of at least one user who has made a general query function like this, and he swears by it. You can have the SQL string and parameters clearly visible inside your sheet, and as you change things that query is rerun and you can based further calculations on the results. He has a whole library of 'SQL' sheets with different examples of queries that he just modifies and combines as he needs to. Instead of the SQL being hidden in some dialog box, it's all on the sheet. So it's a style that works for some people, and not an entirely crazy idea
-GovertExcel-DNA - Free and easy .NET for Excel