|
|
|
Jonatan Christensen
Posts: 53
Joined: 2012-10-09
|
I have a project with an addin module and an xll module.
In this project I have a functioning UDF which fetches data from a specified data source with a set of parameters.
I am interested in setting up the following user scenario:
1: User writes UDF formula in cell A1.
2: Cell A1 returns current result of UDF parameters.
3: User writes new value in the same cell (A1).
4: The result of the UDF is updated to be the newly inserted value.
I have seen this cell behavior before in other solutions, but have never implemented it myself, and I cannot seem to find any info on how to do it.
Is what I am looking for an already implemented tool in Excel, or will I have to somehow bind a ValueChanged event to the cell with the UDF? |
|
|
Posted 26 Feb, 2014 03:18:08
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18817
Joined: 2006-05-11
|
Hello Jonatan,
That is, there's a formula invoking the UDF and the UDF returns a value; the formula calculates the result and Excel puts it to the caller cell. When the user enters a value in that cell, you need to update the datasource or save the value in some other way so that the UDF returns the value just entered. Is this description correct?
When you enter a value to the cell containing a formula, this deletes the formula (Range.Formula). So you need to start with keeping track of all cells calling your UDF. You can do this in two ways: 1) get the caller cell address when the UDF is invoked and 2) use the SheetChange event in the COM add-in to analyze the formulas/values in the modified cell(s).
Now when the user enters a value, you can find out if the cell has had a formula referencing your UDF, save the new value and restore the formula.
Please note that the formula may contain other elements say =MyUdf+1.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 26 Feb, 2014 04:41:34
|
|
Top
|
|
|
Jonatan Christensen
Posts: 53
Joined: 2012-10-09
|
Hello Andrei
Your description is spot on.
Thank you very much for the inspiration! I suspect that I will be utilizing a combo of the 2 options above in my final solution.
It seems that there will be a lot of searching in lists involved in this. Thank goodness for Linq! |
|
|
Posted 26 Feb, 2014 05:12:05
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18817
Joined: 2006-05-11
|
Good luck with your project!
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 26 Feb, 2014 05:40:26
|
|
Top
|
|
|
nwein
Posts: 577
Joined: 2011-03-28
|
I Couldn't help noticing that you've mentioned linq with regards to lists. While this is all nice and good, when you do that in your COM addin (addinmodule) you should be careful and not use linq for anything COM related (i.e. Range, sheets, etc) as it will not release you COM object properly. You can definitely use linq for your own purposes, just don't iterate through COMs...
(Seems like recently I'm adding a lot of "my two cents" :) ) |
|
|
Posted 26 Feb, 2014 10:37:42
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18817
Joined: 2006-05-11
|
|
|
Posted 27 Feb, 2014 04:36:30
|
|
Top
|
|
|
Jonatan Christensen
Posts: 53
Joined: 2012-10-09
|
|
Andrei Smolin writes:
To start with, an Excel COM add-in not releasing COM objects can prevent Excel from closing, see Why doesn?Â?Ð?ét Excel quit?. Don't use foreach with COM collections, use for loops instead. Please find more about this in When to release COM objects in Office add-ins developed in .NET
I have already read that blog several times in the past =] And it came very much in handy for preventing Memory exceptions. And that is not the first time that info which I needed has been readily available on your website. Quite an amazing knowledge base you have here.
My plan was to maintain a list of cell addresses instead of the cells themselves. This should prevent problems related to unreleased com-objects. |
|
|
Posted 27 Feb, 2014 04:50:40
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18817
Joined: 2006-05-11
|
|
Jonatan Christensen writes:
My plan was to maintain a list of cell addresses instead of the cells themselves. This should prevent problems related to unreleased com-objects.
Great!
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 27 Feb, 2014 05:05:43
|
|
Top
|
|
Forums
Topics
Search
Help
Login
Register