Custom Object attached to Cell

[TimFoley]

Hello all,

I have written a number of UDF's that return agregated data from a number of different sources. There is now a desire to provide drill-down capabilities for these values. I can successfully hyperlink them and send them to a custom macro as part of the UDF's. However, I have to parse the formula in the cell to derrive the appropriate parameters to fetch the underlying rows.

I would prefer to be able to have a custom object tied to the cell that I could query to retrieve the appropriate information, however I have not been able to determine how to attach a custom object to a cell.

Thanks in advance,
Tim

 

[TimFoley]

Anyone??

 

[RoryA]

It's not really clear what you mean by 'custom object'. Care to elaborate?

 

[TimFoley]

As an example, I have a function dbCountifs() that will preform a query of and return a count of the rows matching the query.

When the function is called, I also set the hyperlink address with Hyperlinks.Add Anchor:= Range(application.caller.address), Address:="", SubAddress:=application.caller.address.

I then have a sub Worksheet_FollowHyperlink on each page and use the Target.Range.Address to get the cell that was clicked.

Now from that I need to get the connection string and query used for the count to pull back the records and open them on a new sheet. This will provide equivilent functionality to pivot tables. (why not use pivot tables? because the data for a single table may come from multple sources and the data in general is a mess - but I have no control over the structure, or location of the backend databases).

My desire is to have this information available as additional properties of the cell object, or taken one step further, to have an additional method drillDown() in a class module of an .xlam that will retrieve the records then create and populate a new sheet.

 

[RoryA]

I'm not sure how you derive the parameters but perhaps you could simply store them in delimited form in a cell comment? If not, I think you'd have to somehow keep track of a collection of all the cells using the function, which would be prone to all kinds of problems.