Search:

# How does a user defined function know its cell address?

Asked By: Lloyd    Date: Nov 28    Category: MS Office    Views: 2423

I would image this isn't to difficult, but umm... I don't know how to
do it. I am creating a user defined function that is dependent on its
relative position to another range, but I don't know how the function
can determine its location.

Can someone help?

Share:

### 10 Answers Found

Answer #1    Answered By: Amanda Carter     Answered On: Nov 28

Take a look at the application.caller object.

Answer #2    Answered By: Sallie Hill     Answered On: Nov 28

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?

Answer #3    Answered By: Abel Fischer     Answered On: Nov 28

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

Range) As Range

ColumnIndex) = 411

Can you get me going on the right path?

Answer #4    Answered By: Julia Silva     Answered On: Nov 28

Where are you planning that the

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.

Answer #5    Answered By: Grant Jones     Answered On: Nov 28

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.

Answer #6    Answered By: Phailin Jainukul     Answered On: Nov 28

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.

Answer #7    Answered By: Bonita Garcia     Answered On: Nov 28

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.

Answer #8    Answered By: Elmer Young     Answered On: Nov 28

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.

Answer #9    Answered By: Glenda Frazier     Answered On: Nov 28

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

Answer #10    Answered By: Vilmos Fischer     Answered On: Nov 28

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.

Didn't find what you were looking for? Find more on How does a user defined function know its cell address? Or get search suggestion and latest updates.