How can I output VBA function results (array's for example) to spreadsheet

[ww.childers]

I write custom functions that often collect data from an excel
spreadsheet in the open workbook, do a bunch of other calcs with the
data, and return a 2-d array results. The problem is that I want to
output the array results to some new or existing spreadsheet in the
workbook but don't want to use use array formula's (i.e.
Ctrl-Shift-Enter) to place the resulting output result where the array
formula's entered.

In past I've used a work-around by putting all the functions that
return results I want to put into the spreadsheet under a sub(), then
call another sub() to display results in the worksheet of interest by
looping thru the appropriate rows/col's (ie. Cells(r,c).value = x(i)).


But isn't there a way to get the results to be displayed in the
appropriate worksheet cells I chose from within a function() instead of
having to go put everything under a sub()?

For example, the following doesn't work to get data placed in the
requested cells (or anywhere on a the active spreadsheet).

e.g. Assume data is 1-d array of unknown number of elements
function output(data as variant)

dim cnt as integer, i as integer, r as integer c as integer
cnt = Ubound(data)
'assume r & c are given initial values from functions rowstart() &
colstart() that finds someplace on the spreadsheet of interest to start
placing the data.
r = rowstart()
c = colstart()

'place data in column c
for i = 0 to cnt-1
Cells(r,c).value = data(i)
r=r+1
next i

End function

On the other hand, if I replace function routine output(data as
variant) with sub routine sub(data as variant) everything works just
fine.

Is there something I have to put into or call from within the function
to get it to work as I'd like or what am I missing?

 

[John Bundy]

That looks to be fine as long as R and C are valid and the array was set-up
properly. You do need to give sheet names somewhere, i'm assuming you have
this function in a module.
This is how I did it, I don't like names like data either, it will be bad
too often.
For i = 0 To cnt - 1
Cells(r, c) = myData(i, 1)
r = r + 1
Next

 

[bill]

John, is your sample code in function or sub?

That looks to be fine as long as R and C are valid and the array was set-up
properly. You do need to give sheet names somewhere, i'm assuming you have
this function in a module.
This is how I did it, I don't like names like data either, it will be bad
too often.
For i = 0 To cnt - 1
Cells(r, c) = myData(i, 1)
r = r + 1
Next

--