Take a look at the application.caller object.

If you know all the cell  addresses, couldn't you use ROW() and COLUMN()
and just do math to figure out where the active cell is?

I seem to be going in circles with trying to write an excel function. I'm
trying to create a function  that takes in a set of business rules and scheduling
constraints and outputs the results to a range  of cells but I’m unsure of the
structure of the formula.

I would like to create the public function

BusinessResults (ByVal BusinessRules As Range, ByVal BusinessConstraints As
Range) As Range

…

BusinessResults.Range(application.caller.address).offset(rowIndex,
ColumnIndex) = 411

…

Can you get me going on the right path?

Where are you planning that the

BusinessResults.Range(application.caller.address).offset(rowIndex,
ColumnIndex) = 411

line should go?

It can't go inside the BusinessResults function. Nor does it have the
required parameters to allow it to be used within another function.

It's pretty unusual to return a range  from a function, anyway. What
specifically are you trying to do?

To return a range from the function, you would need a statement like

Set BusinessResults = <<< some calculation that returns a range >>>

Just reading a bit more carefully ...

> ... write an excel function  ... outputs the results to a range of cells

A function cannot output the results to a range of cells. A function
returns a single value to whatever calls it. E.g. in

Angle = Sin (2 * PI)

Sin is a function that returns a number.

If you want your routine to store information into cells, then it needs to
be a Sub, not a Function. Seeing it is a Sub, it can just set the cells
from statements within itself. You can provide the destination range as a
parameter in the call to the function if you wish.

Excel does have a set of matrix functions like MMULT that takes as
parameters two arrays and outputs the results as another array, which
is what I am attempting to create with my function.

I also explored using a subroutine but it required the user  to invoke
a procedure call or the subroutine needed to be event driven. It also
seemed to require some designated area of the worksheet to hold the
parameters read by the subroutine or the parameters tables needed to
be in a predefined position. This was too restrictive for the
functions planned use.

I've done a little more research and my current thinking is to not
use a range  as the function  return data type but to use an array.

The issue was that I could not predetermine where a user  would place the user
defined function  relative to another dataset. The application.caller.address is
the address  of the cell  where the function was called from and thus solves that
problem.

I don't think it solves your problem.

Where the user  places the call to the UDF is where the data gets
returned. A UDF can't alter anything in the spreadsheet directly.

I think I'm close to a workable solution now. The function  is
assigned the value of the output array but the user  must select the
destination range  and press <ctrl><Shift><Enter> when entering the
formula.

For instance:

Public Function myFunction()
Dim arr(1, 0)
arr(0, 0) = 3.14159
arr(1, 0) = 2222
myFunction = arr
End Function

Will output the correct result table.

MMULT will only return multiple values if it's array-entered into multiple
cells, AFAIK. But it will be returning an array, not a range. Excel will
select the correct item from the array for the particular instance of the
formula that is in the respective cell.

From what I can see (I've not written any myself), functions that work with
array-entry are fairly specialised beasts.

I'm sure there's lots of tutorial information out there on the Internet, but
this reference www.cpearson.com/excel/WritingFunctionsInVBA.aspx
covers array entered functions in what looks like a nice manner. Give it a
read.

My initial issue with calculating the line items for a financial
statement based on a set of business rules and constraints was solved
by writing array functions for the spreadsheet. The only remaining
drawback is that array functions require the user  to select the
destination cells, which is fine for the financial statements where
the number of cells are known, but will not be acceptable when the
functions need to return values from a database that can have any
number of rows and columns.

I found one solution that relied on a bit of trickery that was
dependent on intimate knowledge of Excel's operations and a speedy computer so
the user wouldn't notice the multiple iterations it required to create the
results and a DLL add-in.