Setting Cell Values from VBA

[Casteele/ShadowLord]

I'm using Excel 2003, and trying to figure out how to set a number of cells
(from VBA) in a worksheet to specific values based on the contents of another
cell. Specifically..

I have one column with the raw data values. The next few columns over, I
need to generate the results of some calculations on each row of the first
column. So far, that's the easy part.. The hard part is, the results can
include anywhere from 1 to 10 values.. So what I'm trying to do is write a
function that returns the first result in the cell with the function
(=myFunc(RC[-1])), but it also needs to write any remaining results to the
next 2 thru 10 columns.

Because the calculations are complex, and there's a couple thousand rows to
process, I'd rather not have to run the same computations repeatedly for each
column just to extract one value from the set.

So far, I've tried something like:

<code>
Function myFunc(nData As Integer, thisCell As Range) As Integer
Dim nResults(1 To 10) As Integer
Dim i, j

' (lengthy processing code snipped.. it basically puts the results in
the nResults array, and store the number of results generated in j)

myFunc = nResults(1)
For i = 2 To j
If nResults(i) > -1 Then
thisCell.Offset(0, i).Value = nResults(i)
Else
thisCell.Offset(0, i).Value = "#ERR"
End IF
Nexy i
End Function
</code>

When calling, the formula I'm using is "=myFunc(RC[-1],RC)", however, Excel
prints the "#VALUE" error value in any cell where there's more than one
result. What am I doing wrong?

Thanks,
C.

 

[Jon Peltier]

A user defined function can only change the value in the cell it's called
by.

You can return an array from a function, but you have to select enough
cells, and array-enter the function into the range of cells
(CTRL+SHIFT+ENTER).

Set up your UDF so it generates an array of 10 values, and leave the unused
ones blank. The code for the function should populate the array. Select a 10
column wide row of cells, type the formula, and hold CTRL and SHIFT while
pressing ENTER. If done correctly, Excel surrounds the formula in curly
brackets:

{=MYFUNC(RC[-1])}

If you type the curly brackets, it will not work.

- Jon