Hi!

I have a very tricky problem. It is rather complicated so I provide a short
description and a detailed description of my problem.

Short version:
In a User defined function I can use Application.Caller.Address to find out
which cell is calling the function.
How can I get info on which sheet and in which workbook this cell is at
runtime?
Is there not properties like
Application.Caller.Sheet.Name
and
Application.Caller.Workbook.Name ??


Long version
I retrieve data into excel using user defined formulas in an add-in. A .NET
dll connects to the SQL Server db and returns the requested data. So in a
cell I enter
=GetData("2003";"Q1";"Profit")

Now I do not want the program to fetch data directly, so in the VBA function
GetData there is no actual code for connecting to database. The retrieval is
done by another macro, triggered by a button click from user. The data
retrieved is stored in a array, and then distributed to cells containing the
GetData formula.

In short this is the process:
1. User enters the GetData formula in a cell
2. The user defined function in VBE is triggered and returns string "Value
not updated"
3. User clicks update button in customized toolbar
4. a Update macro finds all cells with GetData formula,
b stores the arguments in an array and also a reference to the calling
cell using Application.Caller.Address
c sends array to .NET component
d analyzes arguments
e create SQL
f gets data
g returns data back to Excel as an array
5. Update macro uses Application.CalculateFull
6. This triggers GetData formula, which use the data in the returned array
to populate cells.

This works splendid with only one worksheet, but with several worksheets and
workbooks I do no know on which worksheet or in which workbook the calling
calling cell is in, so I can't choose the correct data from the array. The
solutions is to include also worksheet name and workbook name in the array
building (step 4b). I need properties like
Application.Caller.Sheet.Name and
Application.Caller.Workbook.Name
but have not found anything like this.


Best Regards

Clark B

Clark,

This might do what your looking for!

Application.Caller.Activate
MyBook = ActiveWorkbook.Name
MySheet = ActiveSheet.Name

Dan E

"Clark B" wrote in message
...
Hi!

I have a very tricky problem. It is rather complicated so I provide a
short
description and a detailed description of my problem.

Short version:
In a User defined function I can use Application.Caller.Address to find
out
which cell is calling the function.
How can I get info on which sheet and in which workbook this cell is at
runtime?
Is there not properties like
Application.Caller.Sheet.Name
and
Application.Caller.Workbook.Name ??


Long version
I retrieve data into excel using user defined formulas in an add-in. A
..NET
dll connects to the SQL Server db and returns the requested data. So in a
cell I enter
=GetData("2003";"Q1";"Profit")

Now I do not want the program to fetch data directly, so in the VBA
function
GetData there is no actual code for connecting to database. The retrieval
is
done by another macro, triggered by a button click from user. The data
retrieved is stored in a array, and then distributed to cells containing
the
GetData formula.

In short this is the process:
1. User enters the GetData formula in a cell
2. The user defined function in VBE is triggered and returns string "Value
not updated"
3. User clicks update button in customized toolbar
4. a Update macro finds all cells with GetData formula,
b stores the arguments in an array and also a reference to the calling
cell using Application.Caller.Address
c sends array to .NET component
d analyzes arguments
e create SQL
f gets data
g returns data back to Excel as an array
5. Update macro uses Application.CalculateFull
6. This triggers GetData formula, which use the data in the returned array
to populate cells.

This works splendid with only one worksheet, but with several worksheets
and
workbooks I do no know on which worksheet or in which workbook the calling
calling cell is in, so I can't choose the correct data from the array. The
solutions is to include also worksheet name and workbook name in the array
building (step 4b). I need properties like
Application.Caller.Sheet.Name and
Application.Caller.Workbook.Name
but have not found anything like this.


Best Regards

Clark B

Splendid!

It works like a charm!

Thanks!

"Charles Williams" wrote in message
...
Hi Clark B,

assuming you are using application.caller inside the UDF then

sheet name is
Application.caller.parent.name
book name is
application.caller.parent.parent.name

hth
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com

"Clark B" wrote in message
...
Hi!

I have a very tricky problem. It is rather complicated so I provide a
short
description and a detailed description of my problem.

Short version:
In a User defined function I can use Application.Caller.Address to find
out
which cell is calling the function.
How can I get info on which sheet and in which workbook this cell is at
runtime?
Is there not properties like
Application.Caller.Sheet.Name
and
Application.Caller.Workbook.Name ??


Long version
I retrieve data into excel using user defined formulas in an add-in. A
.NET
dll connects to the SQL Server db and returns the requested data. So in
a
cell I enter
=GetData("2003";"Q1";"Profit")

Now I do not want the program to fetch data directly, so in the VBA
function
GetData there is no actual code for connecting to database. The
retrieval
is
done by another macro, triggered by a button click from user. The data
retrieved is stored in a array, and then distributed to cells containing
the
GetData formula.

In short this is the process:
1. User enters the GetData formula in a cell
2. The user defined function in VBE is triggered and returns string
"Value
not updated"
3. User clicks update button in customized toolbar
4. a Update macro finds all cells with GetData formula,
b stores the arguments in an array and also a reference to the
calling
cell using Application.Caller.Address
c sends array to .NET component
d analyzes arguments
e create SQL
f gets data
g returns data back to Excel as an array
5. Update macro uses Application.CalculateFull
6. This triggers GetData formula, which use the data in the returned
array
to populate cells.

This works splendid with only one worksheet, but with several worksheets
and
workbooks I do no know on which worksheet or in which workbook the
calling
calling cell is in, so I can't choose the correct data from the array.
The
solutions is to include also worksheet name and workbook name in the
array
building (step 4b). I need properties like
Application.Caller.Sheet.Name and
Application.Caller.Workbook.Name
but have not found anything like this.


Best Regards

Clark B