Write an Excel function to write data to a worksheet

Suppose you want to write a function that will return an ARRAY of values to be written to a designated area of a worksheet. Excel does not (seem to) permit you to directly write to individual cells, one by one (which I tried: see my thread707-892318). The short answer is to use an ARRAY FORMULA.

For example, I have a RETIREMENT_FUND function that takes in a host of parameters about expected incomes, expenses, mortgage payoffs, etc., and it computes, month by month, the value of a starting fund up to some years in the future. I wanted to print out the yearly summaries for 13 components of the fund. I dimensioned an internal array for up to 100 years of data, Dim FUND(1 to 100, 1 to 13) As Variant, and I accumulated monthly results for a year and then wrote them to the next row of the array.

Here's the key: Once the function was written, I highlighted the part of the active worksheet in which I wanted to position my 100x13 FUND array, then typed in =RETIREMENT_FUND(...) with its 30-odd cell-reference arguments, then pressed [color red]Ctrl+Shift+Enter[/color], which causes the function to be entered in all 1300 cells as an "ARRAY FORMULA." As you change parameters, the function will write out different values to the array space.

You can, of course, write anything to the worksheet -- results of a game (e.g., battleship), a calendar, compiled text, etc.