Using VSTO to expand a C# array (1D or 2D) into Excel following a UDF call

Question

Hi all,

I have some C# functions that I have exposed to Excel so that they can be called from Excel cells (UDFs). Some of these functions return arrays or even matrices. I have two questions in fact:

1- If I am to get the instance of the running Excel workbook, how can I get the cell from which the UDF call was made?
2- Is there some built-in functionality in VSTO to expand array results automatically in the cell where the UDF call was made?

Thanks!

Best Regards,
Fayssal

Answer 1

Hi Fayssal

VSTO has absolutely nothing to do with UDF. It does not support UDF in any way. You'll find alternate resources in the forum's Please Read First message where you can ask questions not related to VSTO.

FWIW, if your custom function returns an array and you want the array written to the selected set of cells, the user must press a certain keyboard combination (rather than Enter) to confirm entering an array. I think the combination is Ctrl+Enter (or maybe it's Shift+Enter - one of those, anyway). This will write the forumula with { braces } which indicates it's an array. And Excel will then take care of populating the cell selection with the array.

There was a discussion about this in the forum, some time back. If you search UDF and array you should turn it up and find the details.

---

Cindy Meister, VSTO/Word MVP

Answer 2

Thanks Cindy! Indeed, it seems this cannot be achieved using VSTO. More details could be found in this thread:

http://www.xtremedotnettalk.com/showthread.php?t=96044

BTW, the command to expand the results of a UDF is: Ctrl-Shift-Enter.
Do you have any idea why VSTO does not want to support UDFs for Document-level add-ins?

Anyways, I was trying to get access to the Excel instance from which the UDF call was initiated, and I ran into the dreadful COMException when trying to modify some cells from the active sheet. If somebody runs into this problem, this might be helpful:

http://support.microsoft.com/kb/316125/EN-US/

http://support.microsoft.com/kb/316126

 

Best Regards,
Fayssal