Identifying the worksheet and cell that contains a function call

[Keith Howard]

Hello.
I am tring to write a function that I would call from many different sheets
and cells in a workbook. The answer that the function returns is relative to
the location of the cell containing the function call.

I was thinking that I need to pass the row and column of the call, like this:
=NextTaskDueDate(ROW(FG69),COLUMN(FG69))

This works well and gives the correct result. However, I also need to pass
in the name of the sheet, because I make the function call from within many
different sheets.

Is there a way to create a 3rd parameter, which is the name of the worksheet
holding the cell containing function call?

Or, even better, is there a more effective way of identifying, within the
code within the receiving function, the location (sheet and cell reference)
of the function call?

Thanks.

Keith

 

[Per Jessen]

Hi

Use theese statements to get the desired information in your function.

shName = ActiveSheet.Name
cRow = ActiveCell.Row
cCol = ActiveCell.Column

Regards,
Per

 

[Ron Rosenfeld]

Hello.
I am tring to write a function that I would call from many different sheets
and cells in a workbook. The answer that the function returns is relative to
the location of the cell containing the function call.

I was thinking that I need to pass the row and column of the call, like this:
=NextTaskDueDate(ROW(FG69),COLUMN(FG69))

This works well and gives the correct result. However, I also need to pass
in the name of the sheet, because I make the function call from within many
different sheets.

Is there a way to create a 3rd parameter, which is the name of the worksheet
holding the cell containing function call?

Or, even better, is there a more effective way of identifying, within the
code within the receiving function, the location (sheet and cell reference)
of the function call?

Thanks.

Keith

You could use the Caller property of the application object.

e.g.

===============================
Function YourUDF()
Dim sCellLoc As String
Dim r As Range, ws As Worksheet
sCellLoc = Application.Caller.Worksheet.Name & "!" & _
Application.Caller.Address

MsgBox (sCellLoc)

'or, if you want to set up range/worksheet objects
Set r = Application.Caller
Set ws = r.Worksheet

....
rest of your code
....

End Function
=======================================
--ron

 

[Ron Rosenfeld]

Hello.
I am tring to write a function that I would call from many different sheets
and cells in a workbook. The answer that the function returns is relative to
the location of the cell containing the function call.

Perhaps I misunderstood your question. How does the answer vary dependent on
function location?

Maybe all you need to do is specify the precedent cells?

A better explanation of what you are trying to do would be helpful.
--ron