We've created an Excel addin (.xla) that has some user defined
functions. One of the functions returns a single result from a store
procedure on SQL Server. The user wants to be able to right click on
the cell that contains the function, and return a result set from a
query using the functions same argument list. In other words, the
function returns a total, and he may want to see the underlying detail
records. I got this to work fairly easily. On the Worksheets
Before_Right_Click event, I check to see the active cell contains the
required udf. If it does, then I parse the cell contents (my udf
formula) to retrieve the arguments that I then pass to a query.
However, a difficulty arises when, technically, there can be another
Excel Formula embedded within my function call.

ie: =MyFunc( 100, 100, #1/3/2003#, #1/30/2003# ) - This will work
fine
as I parse the argument list.

if instead they do this :
=MyFunc( 100, 200, Now()-60, Now()-30 ) - This is a pain in that
I have to evaluate the 3rd and 4th arguments before I pass them on to
the query. Excel already does this in their function calls. Does
anyone have any ideas how I might easily accomplish this. The 3rd and
4th arguments can technically be any valid date function call.

Thanks in advance,
-WATjr.

Hi Walter,

try using Application.evaluate or worksheet.evaluate

hth
Charles Williams
www.DecisionModels.com

"WATjr" wrote in message
om...
We've created an Excel addin (.xla) that has some user defined
functions. One of the functions returns a single result from a store
procedure on SQL Server. The user wants to be able to right click on
the cell that contains the function, and return a result set from a
query using the functions same argument list. In other words, the
function returns a total, and he may want to see the underlying detail
records. I got this to work fairly easily. On the Worksheets
Before_Right_Click event, I check to see the active cell contains the
required udf. If it does, then I parse the cell contents (my udf
formula) to retrieve the arguments that I then pass to a query.
However, a difficulty arises when, technically, there can be another
Excel Formula embedded within my function call.

ie: =MyFunc( 100, 100, #1/3/2003#, #1/30/2003# ) - This will work
fine
as I parse the argument list.

if instead they do this :
=MyFunc( 100, 200, Now()-60, Now()-30 ) - This is a pain in that
I have to evaluate the 3rd and 4th arguments before I pass them on to
the query. Excel already does this in their function calls. Does
anyone have any ideas how I might easily accomplish this. The 3rd and
4th arguments can technically be any valid date function call.

Thanks in advance,
-WATjr.