Dear all
I want make a function which can be used in excel cell just like "=aaaa(x,
y)", and the result of function is a matrix, maybe 1x10, 10x1, 10x20 etc.

I tried to use the left-up cell of the output range as the additional
input paramter, just like "=aaaa(x,y, F10)"

Unfortunately, I CANNOT write any number to the output range....but I can
read data from the input cell

Function aaaa(x as double, y as double, output as Range) as boolean
x = output.range("A1").value ' this will WORK
output.range("A1").value = 10 ' this will FAIL
End Function


How can I return a matrix??
thanks~~
Sincerely.

You assign the output to the function name - not as a parameter value.


You then have to do a multicell array entry of the formula in the worksheet

Function MyDumFunc(a as long)
redim v(1 to 10, 1 to 3)
for i = 1 to 10
for j = 1 to 3
v(i,j) = i * j * a
Next j
next i
MyDumFunc = v
End Function

Select A1:C10
in the formula bar put in

=MyDumFunc(25) and enter with Ctrl+Shift+Enter

--
Regards,
Tom Ogilvy



"Evaluate function parameter as VBA code" wrote:

> Dear all
> I want make a function which can be used in excel cell just like "=aaaa(x,
> y)", and the result of function is a matrix, maybe 1x10, 10x1, 10x20 etc.
>
> I tried to use the left-up cell of the output range as the additional
> input paramter, just like "=aaaa(x,y, F10)"
>
> Unfortunately, I CANNOT write any number to the output range....but I can
> read data from the input cell
>
> Function aaaa(x as double, y as double, output as Range) as boolean
> x = output.range("A1").value ' this will WORK
> output.range("A1").value = 10 ' this will FAIL
> End Function
>
>
> How can I return a matrix??
> thanks~~
> Sincerely.

thanks a lot~~~


"Tom Ogilvy" wrote:

> You assign the output to the function name - not as a parameter value.
>
>
> You then have to do a multicell array entry of the formula in the worksheet
>
> Function MyDumFunc(a as long)
> redim v(1 to 10, 1 to 3)
> for i = 1 to 10
> for j = 1 to 3
> v(i,j) = i * j * a
> Next j
> next i
> MyDumFunc = v
> End Function
>
> Select A1:C10
> in the formula bar put in
>
> =MyDumFunc(25) and enter with Ctrl+Shift+Enter
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Evaluate function parameter as VBA code" wrote:
>
> > Dear all
> > I want make a function which can be used in excel cell just like "=aaaa(x,
> > y)", and the result of function is a matrix, maybe 1x10, 10x1, 10x20 etc.
> >
> > I tried to use the left-up cell of the output range as the additional
> > input paramter, just like "=aaaa(x,y, F10)"
> >
> > Unfortunately, I CANNOT write any number to the output range....but I can
> > read data from the input cell
> >
> > Function aaaa(x as double, y as double, output as Range) as boolean
> > x = output.range("A1").value ' this will WORK
> > output.range("A1").value = 10 ' this will FAIL
> > End Function
> >
> >
> > How can I return a matrix??
> > thanks~~
> > Sincerely.