0

I created a user defined function (UDF) in C# and it works almost fine. This UDF is displaying information on an Excel worksheet and when the UDF is trying to overwrite a cell where is placed another call of the same UDF (not necessary the same, I have several UDF's) Excel is crashing with message "Microsoft Excel has stopped working". I found that overwriting the original Excel function doesn't lead to any problem or crashing. Here is a sample of my UDF: 

[ExcelFunction(Name = "[functionName][1]", IsMacroType = true)]
public static string teGetHistorical(string cntry = "", string indctr = "", string startDate = "", string endDate = "")
{
    string key = "myKeyString";        
// 
//here some if statements defining my url string
//
    JArray o;
    using (WebClient wc = new WebClient())
    {
        wc.Encoding = System.Text.Encoding.UTF8;
        var json = wc.DownloadString(url);
        o = JArray.Parse(json);
    }
    if (o.Count == 0)
    {
        MessageBox.Show("No data provided for selected parameters");
    }
    else
    {

        Microsoft.Office.Interop.Excel.Application app = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
        Microsoft.Office.Interop.Excel.Range currentCell = app.ActiveCell;

        for (int i = 0; i < o.Count; i++)
        {
            for (int j = 0; j < names.Length; j++)
            {
                if (currentCell[i + 2, j + 1].HasFormula())
                {
             // here I'm detecting cell with formula and truing overpass this   issue. Although tried 
             // currentCell[i + 2, j + 1].Clear(); and
             // currentCell[i + 2, j + 1] = null;
                    currentCell[i + 2, j + 1].ClearFormats();
                    currentCell[i + 2, j + 1].ClearContents();
                    currentCell[i + 2, j + 1] = o[i][names[j]];
                }
                else
                {

                    currentCell[i + 2, j + 1].ClearFormats();
                    currentCell[i + 2, j + 1].ClearContents();
                    currentCell[1, j + 1].Font.Bold = true;

                    if (j > 0)
                    {
                        currentCell[1, j + 1] = names[j];
                    }
                    currentCell[2 + i, j + 1] = o[i][names[j]];
                }
            }
        }
    }                  
    return "Country";
}

Any suggestions how to handle this ?

Søren D. Ptæus
  • 4,176
  • 1
  • 26
  • 28
Olexandr Baturin
  • 3
  • 3
  • Can you check Event Viewer? Application crashes should at least generate an Info message there. – Alexei - check Codidact Jan 26 '17 at 10:19
  • 1
    UDFs [may not](http://stackoverflow.com/a/3622544/11683) change sheets. They may only return values. You can [work around](http://stackoverflow.com/q/23433096/11683) that, but you should not. – GSerg Jan 26 '17 at 10:22
  • @Alexei sorry but I 'm new in programming. I found Event Viewer and there is some error messages, what kind of information should I provide exactly? – Olexandr Baturin Jan 26 '17 at 10:34
  • Everything that is written in the general tab of the event information. It should look like this: `Event code: 3005 Event message: An unhandled exception has occurred. Event time: 1/26/2017 11:41:07 AM `, but I do not know what Excel crash is reporting. – Alexei - check Codidact Jan 26 '17 at 10:37
  • 1
    In addition to @GSerg, officially from the manufacturer of Excel: [Description of limitations of custom functions in Excel](https://support.microsoft.com/en-au/help/170787/description-of-limitations-of-custom-functions-in-excel) – Axel Richter Jan 26 '17 at 10:44
  • @Alexei only have this Faulting application name: EXCEL.EXE, version: 15.0.4885.1000, time stamp: 0x582ad757 Faulting module name: EXCEL.EXE, version: 15.0.4885.1000, time stamp: 0x582ad757 Exception code: 0xc0000005 Fault offset: 0x000000000032541f Faulting process id: 0x291c Faulting application start time: 0x01d277be5663c415 – Olexandr Baturin Jan 26 '17 at 10:45
  • @GSerg so why I can format and delete another values, like string, from cells ? I can overwrite, delete and format everything while a don't touch on cell with formula ? – Olexandr Baturin Jan 26 '17 at 10:52
  • 1
    @OlexandrBaturin If you are actually doing it from a function that was called by Excel from a cell, you should not be able to do that either. It's not supported and dangerous in principle and you should expect it to crash. Apparently you get more lucky because the cells you amend are not being referenced by other formulas. Because when you fiddle with the calculation tree you have it coming as I [have noted](http://stackoverflow.com/questions/41871168/excel-crushing-when-one-udf-is-overwriting-another-udf?noredirect=1#comment70924488_41871168). – GSerg Jan 26 '17 at 11:18

0 Answers0