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

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

"Andy Chan" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

"kcc" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>