|
|
|
Dragon Fly
Posts: 76
Joined: 2011-07-27
|
Hi,
My add-in consists of a COM Add-In (for the GUI) and an XLL Add-In (for the UDFs). Everything works nicely but a customer complained about the scenario described below. I understand that it's not an Add-In Express problem but I'm hoping that you guys would have some ideas on how to solve this problem. Here's the scenario:
1) A company purchases 1 copy of my add-in. That add-in is installed on an analyst's machine. The analyst uses the add-in to create UDFs. For example, cell A1 contains a UDF that produces the value 1.23. Everything is fine up to this point.
2) The analyst sends the workbook to his team members. His team members open the workbook and see a "#NAME?" error in cell A1. The reason is because the add-in is NOT installed on his team members' machines and therefore the UDF is undefined.
Our customer's IT department does NOT want to install the add-in on multiple machines. Is there a solution for this problem? Thank you. |
|
|
Posted 12 Dec, 2011 11:12:21
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18817
Joined: 2006-05-11
|
Hello,
The analyst should Paste Special the value returned by the UDF (the value is 1.23), not the formula which produces 1.23 when calculated. In this way, you tore 1.23 from the formula that calculates it and you can use that value with no add-in installed.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 13 Dec, 2011 00:37:38
|
|
Top
|
|
|
Dragon Fly
Posts: 76
Joined: 2011-07-27
|
Hi Andrei,
I was aware of the Paste Special feature but it's not too practical in my scenario. Let me expand on my example. The analyst would create in excess of 1,000 UDFs in multiple worksheets. In addition, the analyst would hit the Refresh button in my add-in daily to refresh the values of the UDFs. By using Paste Special, the UDFs are effectively removed from the workbook and therefore the Refresh will no longer work. Any other suggestions? Thanks. |
|
|
Posted 13 Dec, 2011 06:43:38
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18817
Joined: 2006-05-11
|
If not installing the add-in is a must, then the users without the add-in must be supplied with workbooks that do not reference the add-in and accordingly the analyst must use Paste Special.
You can add an option to your COM add-in letting the analyst to create such a workbook programmatically.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 13 Dec, 2011 07:23:52
|
|
Top
|
|
|
Dragon Fly
Posts: 76
Joined: 2011-07-27
|
> You can add an option to your COM add-in letting the analyst to create such a workbook programmatically.
Since the original workbook must not be modified (because of the daily refresh), would I programmatically make a copy of the workbook first and then programmatically execute the Paste Special command on my UDFs? Which functions in the Excel object model would help me implement this option? |
|
|
Posted 13 Dec, 2011 07:49:57
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18817
Joined: 2006-05-11
|
You can create a workbook (Workbooks.Add), copy worksheets to a new workbook (WorkSheet.Copy), then use Worksheet.UsedRange.Copy and then Worksheet.UsedRange.PasteSpecial. This requires restoring Clipboard.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 13 Dec, 2011 08:23:02
|
|
Top
|
|
|
Dragon Fly
Posts: 76
Joined: 2011-07-27
|
|
Thanks for your help. I might have more questions when I work on it in a few weeks. |
|
|
Posted 13 Dec, 2011 09:20:02
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18817
Joined: 2006-05-11
|
You are welcome!
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 13 Dec, 2011 09:26:53
|
|
Top
|
|
Forums
Topics
Search
Help
Login
Register