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

  • Thread starter Thread starter Andy Chan
  • Start date Start date
A

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
 
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
 
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
 
Back
Top