My function works perfectly on debug mode but does not recognized by Excel in regular mode

Olexandr Baturin

unread,

Dec 26, 2016, 7:40:08 AM12/26/16

to Excel-DNA

Hello, I created Add-In in Visual Studio 2015 (in C# language) and its run perfectly in Debug mode, but when I open excel normally (clicking on excel icon) my functions are not recognized by excel. Any guess? Here is an example of function:

[ExcelFunction(Name = "TEGetCalendar", IsMacroType = true, IsHidden = false)]
public static string teGetCalendar(string host, string key, string startDate, string endDate, string selectedCntry = "", string selectedIndic = "")
{
string url;
var reference = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
Excel.Range currentFormulaCell = Tools.ReferenceToRange(reference);
int row = currentFormulaCell.Row;
int col = currentFormulaCell.Column;

if (selectedCntry.Length == 0
&& selectedIndic.Length == 0
&& startDate.Length == 0
&& endDate.Length == 0)
{
url = host + "calendar?c=" + key;
}
else if (selectedCntry.Length == 0)
{
url = host + "calendar" + "/indicator/" + selectedIndic + "/" + startDate + "/" + endDate + "?c=" + key;
}
else if (selectedIndic.Length == 0)
{
url = host + "calendar/country/" + selectedCntry + "/" + startDate + "/" + endDate + "?c=" + key;
}
else
{
url = host + "calendar/country/" + selectedCntry + "/indicator/" + selectedIndic + "/" + startDate + "/" + endDate + "?c=" + key;
}

//Debug.WriteLine(url);
//MessageBox.Show(url);
using (WebClient wc = new WebClient())
{
var json = wc.DownloadString(url);

JArray o = JArray.Parse(json);
//Debug.WriteLine(o.Count);
//for (int i = 0; i < o.Count; i++)
//{
// Debug.WriteLine(o[i]);
//}
if (o.Count == 0)
{
MessageBox.Show("No data provided for selected parameters");
}
else
{
for (int i = 0; i < o.Count; i++)
{
for (int j = 0; j < helperClass.calendNames.Length; j++)
{
currentFormulaCell[i + 2, 1 + j].Font.Bold = false;
currentFormulaCell[1, 1 + j].Font.Bold = true;
if (j > 0)
{
currentFormulaCell[1, 1 + j] = helperClass.calendNames[j];
}
currentFormulaCell[i + 2, 1 + j] = o[i][helperClass.calendNames[j]];
}
}
}
}
return "Date";
}

Govert van Drimmelen

unread,

Dec 26, 2016, 7:59:46 AM12/26/16

to exce...@googlegroups.com

Most likely this relates to some project or other properties that are set in Debug build and not the Release build.

Easiest is to start with a clean and very simple project, and check that everything works there.

Once there, you can build it out by adding the rest of your code, or compare with the problem project.

Follow these instructions:

1. Create a new C# "Class Library" project (not an add-in project or anything like that).

2. Install the "ExcelDna.AddIn" package from NuGet.

3. In the class, add your function (the optional values with defaults aren't supported, so it will be clearer if you leave that out.):

[ExcelFunction(Name = "TEGetCalendar")]

public static string teGetCalendar(string host, string key, string startDate, string endDate, string selectedCntry, string selectedIndic)

{

return "Helo from the TEGetCalendar function";

}

4. Now build and run and check that everything works right.

That should at least sort out the debug / release issues.

-----

You function has a few other red flags that I'll mention, though that's not related to your question about the debug vs release build.

* It's not really supported to use the COM object model (like the COM Range type) from within a UDF. It might work or might not or might make Excel unstable.

* Showing a message box from a UDF (during the calculation) as a form of validation can be very confusing. For example, a user might copy a formula with a mistake into many cells, and the message boxes will pop up and ruin his Excel session.

* Writing to other cells from a UDF can make Excel unstable, breaks Excel's calculation model and makes dependency tracking impossible. Returning an array from your function, and using a utility or helper like the ArrayResizer sample is sometimes a better approach, though async functions are then not supported.

These are just some comments based on the function you've shown, that might be useful to you or other users.

-Govert

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Olexandr Baturin [olexandr...@tradingeconomics.com]
Sent: 26 December 2016 02:36 PM
To: Excel-DNA
Subject: [ExcelDna] My function works perfectly on debug mode but does not recognized by Excel in regular mode

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

Olexandr Baturin

unread,

Dec 26, 2016, 9:18:02 AM12/26/16

to Excel-DNA

Hello Govert,

First of all thank you for your help.

I tried to follow your steps and I got this message, when excel was opening, "The file format and extension of "testClass-AddIn.xll" don't match...".

to fix this I went to Properties > Debug > Command line arguments and changed "testClass-AddIn.xll" to "testClass-AddIn64.xll" and get the same situation as before, function works when excel is opened from VS2015 and does note when excel is opened normally.

Note:

In case of my add-in. If I install my add in from executable file or directly from VS and than If I add .xll file from excel definitions like another add-in everything works fine.

But what I need to do its to put everything work from executable file.

With my best regards,

Olexandr

To post to this group, send email to exc...@googlegroups.com.

Govert van Drimmelen

unread,

Dec 26, 2016, 9:28:15 AM12/26/16

to exce...@googlegroups.com

I don't understand what you mean by "to put everything work from executable file".

Do I understand right that the Excel add-in works when you load it in Excel directly (either by File->Open or by adding it as an add-in)?

Are you building some kind of installer?

Is that where the problem is?

-Govert

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Olexandr Baturin [olexandr...@tradingeconomics.com]

Sent: 26 December 2016 04:18 PM
To: Excel-DNA
Subject: Re: [ExcelDna] My function works perfectly on debug mode but does not recognized by Excel in regular mode

To post to this group, send email to exce...@googlegroups.com.

Olexandr Baturin

unread,

Dec 26, 2016, 9:41:32 AM12/26/16

to exce...@googlegroups.com

Yes, I'm building an installer.

And to put add-in works I need to run an installer and besides I need to open Excel , File > Options > AddIn and add my .xll file from the project as another add-in.

With my best regards,

Olexandr

On Mon, Dec 26, 2016 at 2:28 PM, Govert van Drimmelen <gov...@icon.co.za> wrote:

I don't understand what you mean by "to put everything work from executable file".

Do I understand right that the Excel add-in works when you load it in Excel directly (either by File->Open or by adding it as an add-in)?

Are you building some kind of installer?

Is that where the problem is?

-Govert

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Olexandr Baturin [olexandr.baturin@tradingeconomics.com]

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+unsubscribe@googlegroups.com.

To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/9UDBJmDFBko/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+unsubscribe@googlegroups.com.

Govert van Drimmelen

unread,

Dec 26, 2016, 9:48:52 AM12/26/16

to exce...@googlegroups.com

The only installer advice I have is to look at the WiXInstaller project https://github.com/Excel-DNA/wixinstaller

In particular, the custom action writes to the registry to install the add-in: https://github.com/Excel-DNA/WiXInstaller/blob/master/Source/InstallerCA/CustomAction.cs

-Govert

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Olexandr Baturin [olexandr...@tradingeconomics.com]

Sent: 26 December 2016 04:41 PM
To: exce...@googlegroups.com

Reply all

Reply to author

Forward