Identifying the worksheet and cell that contains a function call

  • Thread starter Thread starter Keith Howard
  • Start date Start date
K

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
 
Hi

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

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

Regards,
Per
 
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
Click to expand...

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
 
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.
Click to expand...

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
 
Back
Top