Excel User Defined Function issues with Excel Template

I am working with a product based on Excel Document Level Customization. The project type is “Excel 2013 Template” in Visual Studio.

Recently I have developed a User Defined Function which will be used to evaluate our own function. I have developed the User Defined Function based on the article https://blogs.msdn.microsoft.com/eric_carter/2004/12/01/writing-user-defined-functions-for-excel-in-net/

The User Defined Function works fine with the Excel Template of our product but it fails for one test case.

1. Open a new Excel template of the product . [ex: Excel template 1]
2. Add the User Defined Function to a cell. [Evaluates the result successfully]
3. Open another Excel Template of the product. [ex: Excel template 2]
4. Add the User Defined Function to a cell. [Evaluates the result successfully]
5. Close the template opened in Step 1. [ie: Excel template 1)]
6. Add the User Defined Function to a cell of other template [ie: Excel template 2]

Now I can notice the added function won’t get evaluated and End up with “#NAME?” error.

I have checked the User Defined Function from the Excel’s Add-in list. It is active. Any way I have re-installed the add-in programmatically in the workbook’s ActivateEvent, but no use.

This problem will only occur if I close the template that was opened first. Further this issue will not be there for saved work books.

When I debugged, I found one issue. In our product we have our own set of commandbars added to the Excel’s context menu when our customization get opened. The below sample code will add a command bar button in to the context menu of the cell.

object oMissing = System.Type.Missing;
 try
 {
    commandBars = (Office.CommandBars)Application.GetType().InvokeMember("CommandBars", BindingFlags.GetProperty, null, Application, new object[] { });
    commandBarSheetMenu = commandBars["Cell"];                
 }
 catch (Exception ex)
 {
    MessageBox.Show(ex.Message);
 }
 commandBarButtonTest = (Office.CommandBarButton)commandBarSheetMenu.Controls.Add(1, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 commandBarButtonTest.BeginGroup = true;
 commandBarButtonTest.Caption = "Test Me";
 commandBarButtonTest.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(commandBarButtonSheetCopy_Click);

When I remove/ comment the above code the User Defined Function start working even If I close the first opened template.

Is it a known problem with the Excel Template and User Defined Function when it is used with Command bars?

It would be great if someone can comment on the issue and suggest a solution to this issue.