How to output an array in Excel with a user-defined function?

[Andy Chan]

Dear all,

I want to write a user-defined function to output an array. But I don't
know its codes. For example, if I want to define a function MyFunction(A,B)
where A, B are positive integers which generates an array such that the
entry in the x-th row, y-th column is x+y, what codes should I write? Should
it be like this:

Function MyFunction (A As Integer, B As Integer) As Range
dim R As Range
dim X As Integer
dim Y As Integer
redim R(A,B)
For X = 1 to A
For Y = 1 to B
R.Cells(X,Y)=X+Y
Next Y
Next X
MyFunction = R
End Function

Thanks in advance.

Best Regards,
Andy

 

[kcc]

You've got something that looks somewhere being a function
to return an array and a macro to write to a range.
Assuming you want the first, here are a few corrections.
Your returning an array but defining it as a range
dim for an array should include ().
Index for an array starts at 0, not 1. Unless you set "option base 1".

Try this
Function MyFunction (A As Integer, B As Integer)
dim R() As Integer
dim X As Integer
dim Y As Integer
redim R(A-1,B-1)
For X = 1 to A
For Y = 1 to B
R(X-1,Y-1)=X+Y
Next Y
Next X
MyFunction = R
End Function

Since it returns an array, it either should be entered as an array formula
or processed further with something like sumproduct that knows how to use
arrays.
kcc

 

[Andy Chan]

Yes, I want a function. Thanks a lot!

But I heard that if the data type of the output is not declared when the
function is defined, the program will be slower. Can I declare the data type
of the function as

Function MyFunction (A As Integer, B As Integer) As Range

Best Regards,
Andy