Can I write a function like matrix function?
 

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.

Can I write a function like matrix function?
 

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.

Can I write a function like matrix function?
 

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.