Hello Govert,
I am trying to copy defined names from one workbook to another, but i am running into problems with formula and strings. This is my code (a bit simplified):
```
public void onButtonPressed(IRibbonControl control)
{
// Setup application.
Excel.Application excelApp = (Excel.Application)ExcelDnaUtil.Application;
Excel.Workbook thisWorkbook = excelApp.ActiveWorkbook;
Excel.Workbook fromWorkbook = ... i get this workbook by using some forms and more...
foreach (Excel.Name definedName in fromWorkbook.Names)
{
if (definedName.Visible == false)
{
continue;
}
var newDefinedName = thisWorkbook.Names.Add(
Name: definedName.Name,
RefersTo: definedName.RefersTo
);
newDefinedName.Comment = definedName.Comment;
}
}
```
I get the following error when trying to add a defined name to "thisWorkbook":
```
System.Runtime.InteropServices.COMException: 'There's a problem with this formula.
Not trying to type a formula?
When the first character is an equal ("=") or minus ("-") sign, Excel thinks it's a formula:
- you type =1+1, cell shows: 2,
To get around this, type an apostrophe ( ' ) first:
- you type: '=1+1, cell shows =1+1
```
I have both LAMBDA functions and simple constants with decimals that i want to copy over. When i try to insert the "RefersTo" of the defined name into a cell, it is formatted correctly, and i can copy the text and create a defined name with the Excel UI.
I tried this with VBA in the Excel Developer, and there it worked! But it does not work when working with C#. I want this function to be within the add-in i am creating.
Can you help me? :) Thank you in advance!