"#NAME?" error ...

Add-in Express™ Support Service
That's what is more important than anything else

"#NAME?" error ...
 
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: 14104
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.

Regards from Belarus (GMT+3),

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: 14104
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.

Regards from Belarus (GMT+3),

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: 14104
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.

Regards from Belarus (GMT+3),

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: 14104
Joined: 2006-05-11
You are welcome!

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 13 Dec, 2011 09:26:53 Top