C
Clark B
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
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