UDF returning a 2 dimentional array

[Alan]

I would like to write a user defined function that would return a 2
dimentional array of values based on the value of a supplied
parameter ... can anyone tell me how to do it?

Intuitively, although I know this is wrong, I would like to define a
function something like -

Public Function Arr(2,10)(byval Param as long) as variant

Where (2,10) are the dimensions of the array and Param is the supplied
decision parameter. If the syntax is corrected, can this be done??

As a supplementary, if this can be done can Arr be redimensioned
within the function to return a bespoke array without blank fields or
must he original size of Arr be generic and oversized to cover all
cases?

Regards,

Alan

 

[Jim Thomlinson]

A UDF used within a spreadsheet can only return a value to a single cell. It
can not put values into cells that it is not in. So how do you intend to use
this UDF in a spreadsheet... or do you?

In code a function can take an array as an input and return an array. Maybe
I just don't understand what you are trying to do???

 

[Charlie]

Public Function Grid(nRow As Long, nCol As Long) As String()
'
Dim i As Long
Dim j As Long
Dim Temp() As String
'
ReDim Temp(nRow, nCol)
'
For i = 1 To nRow
For j = 1 To nCol
Temp(i, j) = "Hello"
Next j
Next i
'
Grid = Temp
'
End Function

 

[Dana DeLouis]

Any ideas here that can help?

Function TestIt()
Dim M
M = Fx(2, 10, 3, 4)
[A1].Resize(2, 10).FormulaArray = M
End Function

Function Fx(NumR, NumC, ParamArray v()) As Variant
Dim r As Long
Dim c As Long
Dim M() As Variant
Dim t As Double

'Your function next...
t = WorksheetFunction.Sum(v)

ReDim M(1 To NumR, 1 To NumC)
For r = 1 To NumR
For c = 1 To NumC
M(r, c) = r * c + t
Next c
Next r
Fx = M
End Function

 

[Tim Williams]

A UDF used within a spreadsheet can only return a value to a single cell.
It
can not put values into cells that it is not in. So how do you intend to
use
this UDF in a spreadsheet... or do you?

Jim,

Can't a UDF could be used as an array formula via ctrl+alt+enter ??

Tim

 

[Jim Thomlinson]

Yes a UDF can be an array formula but ultimately it can only return a single
value to the cell that it is in. It can take one or more arrays as input but
it can not return an array because the cell it is in can only accept a value
and not an array of values.

SumIf, CountIf, SumProduct all take arrays of values as their inputs, but
they only return a single value to the cell that they are in.

 

[Peter T]

Hi Jim

...but it [a udf] can not return an array

A UDF can return an array. Indeed it can be highly efficient for a UDF to
return an array as it calculates only once to return multiple values into
the cells it has been array entered.

Regards,
Peter T

 

[Dave Peterson]

But you can select your multicelled range first, then array enter your UDF.

A do-nothing UDF:

Option Explicit
Function testarr() As Variant
testarr = Array(1, 2, 3, 4)
End Function

Select A1:A4
and array enter
=testarr()

and a1:a4 will each get one of the values.

 

[Dave Peterson]

Your UDF is gonna return something to each of the cells that you selected when
you array entered the formula.

I think the answer to your question is that you have to be careful when you
select that range and enter the array formula.

When I know that I want a variable amount of rows or columns, I'll use an
oversized (or an exact size) selection (to make sure) and then make sure that
the cells I don't want to use look empty.

 

[Alan]

Sorry guys, my system or setup is really playing up. I couldn't see
this thread and have only just stumbled upon it because I tried to
post another message with the same title and it magically appeared in
the frame to the right of the screen but not in the main frame! Can
anyone suggest why 'cause this is driving me nuts!

Thanks to all of you who have responded. In answer to Jim: I don't
want to paste values into a spreadsheet I want to hold the values in
memory. If I can get this function working, and hopefully with help
from the gurus in this group I will, the function will be held in an
add-in and be interrogated from and the results passed back to a user
workbook. If I can populate the array the values will be picked off
and used one by one there.

Thanks Charlie but you function doesn't seem to pass the parameter
that I need to decide the array's size and how it should be should be
populated.

Dana, your input looks really promising but I am unsure ... I must try
it out with real data when I get back into the office on Monday. Could
you clarify a couple of points please.

Function Fx(NumR, NumC, ParamArray v()) As Variant

*** Can ParamArray v() be a single value rather than an array or am I
misinterpretting here? Would a single value have to be supplied in
"array" format?***

Dim r As Long
Dim c As Long
Dim M() As Variant
Dim t As Double


'Your function next...
t = WorksheetFunction.Sum(v)


ReDim M(1 To NumR, 1 To NumC)

*** Can M be redimensioned to values other than NumR and NumC. NumR
and NumC would be the user supplied default values but in some cases
will need to change dependent on the value of the Parameter supplied.
***

For r = 1 To NumR
For c = 1 To NumC
M(r, c) = r * c + t
Next c
Next r
Fx = M
End Function

Thanks again,

Alan

 

[Dana DeLouis]

* Can ParamArray v() be a single value rather than an array...

Hi. I was guessing that when you used "Param" in your original post, you
were trying to do ParamArray.
I can't really follow what you want, but are there any ideas here that can
help?

Function Fx(x, y, MSize) As Variant
Dim r As Long
Dim c As Long
Dim M() As Variant

ReDim M(1 To 2, 1 To MSize)
'Do stuff
'Now, change dimensions again.
ReDim Preserve M(1 To 4, 1 To (2 * MSize))
'More stuff
Fx = M
End Function

 

[Alan]

Thanks Dana, whilst it would appear that my description of what I am
trying to do is clearly lacking in clarity I think that you have given
me a really good steer as to what I need to do. I think that in my own
original attempt to move things along I was making things too
complicated because I was being too logical (if that is possible!).
You have very much helped to unscramble my thought processes.

I have another go at writing the code as soon as I am back in the
office.

Regards,

Alan