none
Excel Crash on VSTO+VBA RRS feed

  • Question

  • My VSTO-AddIn Project built on .NET 3.5, Excel 2007. I got all updates windows/office. Project is only for 32-bit Excel.

    I have ~150 files which i need to open and make some changes. The Code starts from Studio and later uses few VBA functions as eg. I'm posting below. I tried about ~30 times in a row to Run my code on Client machine and 6 times Excel crashed.. On Developer PC this is almost same, only if i try to Debug it works fine and i cant get error, but if I'm not debugging I'm getting error: "Exception from HRESULT: 0x800A9C68" and Excel does not crash just throws error and continues. From Client Event Log posting 2 log Crashes because they are different and i got them not in sequence. Same crashes appears on Excel 2013.

    Sub OSUOpen(failas As String)
    Dim s, s1, nl, nl1 As String, nl2 As String
    On Error Resume Next
    s = Application.International(3)
    If s = "," Then s1 = "." Else s1 = ","
    nl1 = """0" & s1 & "00"""
    nl2 = """0" & s & "00"""
    opend failas
         Cells.Replace What:=nl1, Replacement:=nl2, lookat:=xlPart, _
         SearchOrder:=xlByRows, MatchCase:=False
    Range("S_P1").Offset(1, 1).Resize(Range("C1").Value, 1).Replace What:=s1, Replacement:=s, lookat:=xlPart, _
         SearchOrder:=xlByRows, MatchCase:=False
    Range("S_P1").Offset(1, 7).Resize(4, 1).Replace What:=s1, Replacement:=s, lookat:=xlPart, _
         SearchOrder:=xlByRows, MatchCase:=False
    End Sub

    Sub opend(ByVal path As String, Optional ll As String, Optional bb As Boolean)
    Application.DisplayAlerts = False
    Application.Workbooks.Open path, True, bb
    Application.DisplayAlerts = True
    End Sub

    Faulting application name: EXCEL.EXE, version: 16.0.6568.2025, time stamp: 0x56b3ae2c
    Faulting module name: ntdll.dll, version: 10.0.10586.103, time stamp: 0x56a853dc
    Exception code: 0xc0000374
    Fault offset: 0x000dc759
    Faulting process id: 0x45c
    Faulting application start time: 0x01d173b6475847c4
    Faulting application path: C:\Program Files (x86)\Microsoft Office\Root\Office16\EXCEL.EXE
    Faulting module path: C:\WINDOWS\SYSTEM32\ntdll.dll
    Report Id: 9763f81c-ec85-42d3-bb66-479a6f61f91d
    Faulting package full name: 
    Faulting package-relative application ID: 

    Faulting application name: EXCEL.EXE, version: 16.0.6568.2025, time stamp: 0x56b3ae2c
    Faulting module name: EXCEL.EXE, version: 16.0.6568.2025, time stamp: 0x56b3ae2c
    Exception code: 0xc0000005
    Fault offset: 0x000327e2
    Faulting process id: 0x23fc
    Faulting application start time: 0x01d173b8580020a6
    Faulting application path: C:\Program Files (x86)\Microsoft Office\Root\Office16\EXCEL.EXE
    Faulting module path: C:\Program Files (x86)\Microsoft Office\Root\Office16\EXCEL.EXE
    Report Id: da59454f-dfab-11e5-9c00-74867a53b6c5
    Faulting package full name: 
    Faulting package-relative application ID: 

    Tuesday, March 1, 2016 1:40 PM

Answers

  • This is VSTO-AddIn. In main function i do not release com objects. 

    I have solved this by rewriting this functions:

    public double fun_viso(ref Excel.Worksheet ws)

    public string fun_tiesiog1(ref Excel.Worksheet wb)

    public double fun_PVM1(ref Excel.Worksheet ws)

    into more simple 1 function which finds all info i need:

    for (row = 30; row >= 1; row += -1)
                        {
                            kas0 = wb.Range["S_P1"].Offset[c1 + row, 0].Value.ToStr();

                            if (kas0 == "")
                                continue;
                            if (kas0.Substring(0, 10).ToLower() == "iš viso #5")
                            {
                                ((Excel.Worksheet)_with27.Workbooks[osuatl].Sheets["OSUATL"]).Range["J4"].Value = wb.Range["S_P1"].Offset[c1 + row, 7 + m].Value.ToDbl();
                                ((Excel.Worksheet)_with27.Workbooks[osuatl].Sheets["OSUATL"]).Range["J3"].Value = wb.Range["S_P1"].Offset[c1 + row - 1, 7 + m].Value.ToDbl();
                                a = true;
                            }
                            if (kas0.Substring(0, 10).ToLower() == "po indeksa")
                            {
                                ((Excel.Worksheet)_with27.Workbooks[osuatl].Sheets["OSUATL"]).Range["G2"].Value = wb.Range["S_P1"].Offset[c1 + row, 8 + m].Value.ToDbl();
                                ((Excel.Worksheet)_with27.Workbooks[osuatl].Sheets["OSUATL"]).Range["H2"].Value = wb.Range["S_P1"].Offset[c1 + row, 9 + m].Value.ToDbl();
                                ((Excel.Worksheet)_with27.Workbooks[osuatl].Sheets["OSUATL"]).Range["I2"].Value = wb.Range["S_P1"].Offset[c1 + row, 10 + m].Value.ToDbl();
                                ((Excel.Worksheet)_with27.Workbooks[osuatl].Sheets["OSUATL"]).Range["J2"].Value = wb.Range["S_P1"].Offset[c1 + row, 7 + m].Value.ToDbl();
                                b = true;
                            }
                            if (a && b)
                            {
                                break;
                            }
                        }

    It's sad that old code works fine on less data. It's like Excel can't handle all work... :)

    Tuesday, March 8, 2016 10:10 AM

All replies

  • Hi Julius,

    Do you call VBA code in VSTO? You may remove the logical (using simple code) and check the result.

    Please provide the detail code of VSTO.

    On the other hand, I suggest that you could achieve that (VBA) in VSTO project directly and check whether the excel will crash.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 2, 2016 7:49 AM
    Moderator
  • Okey... i did some deeper research..

    I've found new error message: "Attempted to read or write protected memory. This is often an indication that other memory is corrupt. ". Also i want to say i tried more than 100+ times already run my code with same files, and crashes are on random files. If i try to go through ~20 files, it works fine, but if i try with ~150.. after few tries Excel just crashes..

    in All cases Excel crashes + restarts on these my functions/Code below, also to make it clear how i found it i wrote:

    File.AppendAllText(@"C:\Ex Crash\F3.txt", "File xxxxx.xlsx .. Start fun_PVM1-> " + "\n");

    File.AppendAllText(@"C:\Ex Crash\F3.txt", "File xxxxx.xlsx .. End fun_PVM1-> " + "\n");

    public double fun_PVM1(ref Excel.Worksheet ws)
                {
                    double functionReturnValue = 0;
                    int row = 0;
                    string toliau = null;
                    string kitas = null;
                    Excel.Worksheet ew = ws;
                    int c1 = ws.Range["C1"].Value.ToInt();
                    try
                    {
                        for (row = 30; row >= 1; row += -1)
                        {
                            if (string.IsNullOrEmpty(ew.Range["S_P1"].Offset[c1 + row, 1].Value.ToStr()))
                                goto bj7g;
                            if (Strings.InStr(1, ew.Range["S_P1"].Offset[c1 + row, 1].Value.ToStr(), "PVM") == 0)
                                goto bj7g;
                            functionReturnValue = ew.Range["S_P1"].Offset[c1 + row, 7].Value.ToDbl();
                            ew = null;
                            return functionReturnValue;
                        bj7g:
                            toliau = "";
                        }
                        kitas = toliau;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToStr() + Klaida(ex));
                    }
                    functionReturnValue = 0;
                    ew = null;
                    return functionReturnValue;
                }

                public string fun_tiesiog1(ref Excel.Worksheet wb)
                {
                    string functionReturnValue = null;
                    int row = 0;
                    string nm = "";
                    string toliau = null;
                    Excel.Worksheet ew = wb;
                    int c1 = ew.Range["C1"].Value.ToInt();
                    try
                    {
                        nm = wb.Name;
                        for (row = 30; row >= 1; row += -1)
                        {
                            if (string.IsNullOrEmpty(ew.Range["S_P1"].Offset[c1 + row, 1].Value.ToStr()))
                                goto bh8g;
                            if (Strings.InStr(1, ew.Range["S_P1"].Offset[c1 + row, 1].Value.ToStr(), "ndeks") == 0)
                                goto bh8g;
                            if (Strings.InStr(1, ew.Range["S_P1"].Offset[c1 + row + 1, 0].Value.ToStr(), "viso") == 0)
                                goto bh9g;
                            functionReturnValue = ew.Range["S_P1"].Offset[c1 + row + 1, 8].Address + ":" + ew.Range["S_P1"].Offset[c1 + row + 1, 10].Address;
                            ew = null;
                            return functionReturnValue;
                        bh8g:
                            toliau = "a";
                        }
                    bh9g:
                        toliau = "a";
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Failas:" + nm + " Klaida:" + Klaida(ex));
                    }
                    nm = toliau;
                    functionReturnValue = ew.Range["S_P1"].Offset[c1 + 1, 8].Address + ":" + ew.Range["S_P1"].Offset[c1 + 1, 10].Address;
                    ew = null;
                    return functionReturnValue;
                }

    public double fun_viso(ref Excel.Worksheet ws)
                {
                    double functionReturnValue = 0;
                    int row = 0;
                    string toliau = null;
                    string kitas = null;
                    Excel.Worksheet ew = ws;
                    int c1 = ew.Range["C1"].Value.ToInt();
                    try
                    {
                        for (row = 30; row >= 1; row += -1)
                        {
                            try
                            {
                                if (string.IsNullOrEmpty(ew.Range["S_P1"].Offset[c1 + row, 0].Value.ToStr()))
                                    goto bi9g;
                            }
                            catch
                            {
                                goto bi9g;
                            }
                            if (Strings.InStr(1, ew.Range["S_P1"].Offset[c1 + row, 0].Value.ToStr(), "viso") == 0)
                                goto bi7g;
                            functionReturnValue = ew.Range["S_P1"].Offset[c1 + row, 7].Value.ToDbl();
                            ew = null;
                            return functionReturnValue;
                        bi7g:
                            if (Strings.InStr(1, ew.Range["S_P1"].Offset[c1 + row, 0].Value.ToStr(), "Viso") == 0)
                                goto bi9g;
                            functionReturnValue = ew.Range["S_P1"].Offset[c1 + row, 7].Value.ToDbl();
                            ew = null;
                            return functionReturnValue;
                        bi9g:
                            toliau = "";
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToStr() + Klaida(ex));
                    }
                    kitas = toliau;
                    functionReturnValue = 0;
                    ew = null;
                    return functionReturnValue;
                }

    On this "Numer()" function Excel does not crash, he is hanging in and doing something inside - Task Manager showing that Excel is working, and CPU works on 20-40%, but not always.

    public void Numer()
                {
                    try
                    {
                        int eil = 0;
                        int row = 0;
                        int c1;
                        Excel.Application _with16 = Globals.ThisAddIn.Application;
                        _with16.Range["C1"].Value = _with16.Range["Viso"].Row - 11;
                        eil = 1;
                        c1 = _with16.Range["C1"].Value.ToInt();

                        for (row = 1; row <= c1; row++)
                        {
                            if (!string.IsNullOrEmpty(_with16.Range["M_P1"].Offset[row, -3].Value.ToStr()))
                            {
                                _with16.Range["M_P1"].Offset[row, -3].Value = eil;
                                eil = eil + 1;
                            }
                        }
                        _with16 = null;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToStr() + Klaida(ex));
                    }
                }

    and few times:

    ....

    Excel.Font _with14 = _with11.Range["D" + _with11.ActiveCell.Row + ":J" + _with11.ActiveCell.Row].Font;
                            _with14.Name = a;
                            _with14.Size = 10;
                            _with14.Bold = false;
                            _with14.Italic = true;
                            _with14.Strikethrough = false;
                            _with14.Underline = -4142;
                            _with14.ColorIndex = -4105;

    ....

    Adding extensions:

    public static double ToDbl(object strO)
            {
                double functionReturnValue = 0;
                if (strO == DBNull.Value | strO == null | string.IsNullOrEmpty(ToStr(strO)))
                    return 0;

                if (double.TryParse(strO.ToString(), out functionReturnValue))
                {
                    return functionReturnValue;
                }

                string a = ToStr(strO);
                int b = a.ToLower().IndexOf("€"); //lt
                if (b > 0)
                {
                    a = a.Substring(0, b - 1);
                    if (a.Contains(" "))
                        a = a.Replace(" ", "");

                    try
                    {
                        if (double.TryParse(a, out functionReturnValue))
                            return double.Parse(a, System.Globalization.NumberStyles.Any);
                        functionReturnValue = 0;
                    }
                    catch
                    {
                        functionReturnValue = 0;
                    }
                }
                else
                {
                    try
                    {
                        if (double.TryParse(Convert.ToString(strO), System.Globalization.NumberStyles.Any, CultureInfo.CreateSpecificCulture("lt-LT"), out functionReturnValue))
                            return double.Parse(strO.ToString(), System.Globalization.NumberStyles.Any);
                        functionReturnValue = 0;
                    }
                    catch
                    {
                        functionReturnValue = 0;
                    }
                }

                return functionReturnValue;
            }

            public static int ToInt(object strO)
            {

                int functionReturnValue = 0;
                if (strO == DBNull.Value | strO == null | string.IsNullOrEmpty(ToStr(strO)))
                    return 0;
                try
                {
                    if (Int32.TryParse(strO.ToString(), out functionReturnValue))
                    {
                        return functionReturnValue;
                    }
                    string c = null;
                    string a = ToStr(strO).Replace(".", ",");
                    int b = a.IndexOf(",");
                    if (b > 0)
                    {
                        c = a.Substring(0, b);
                    }
                    else
                    {
                        c = a;
                    }
                    functionReturnValue = Convert.ToInt32(c);
                }
                catch
                {
                    functionReturnValue = 0;
                }
                return functionReturnValue;
            }

            public static string ToStr(object strO)
            {
                string functionReturnValue = null;
                if (strO == DBNull.Value | strO == null)
                    return "";
                try
                {
                    return Convert.ToString(strO).Trim();
                }
                catch
                {
                    functionReturnValue = "";
                }
                return functionReturnValue;
            }

    public static string Klaida(Exception ex)
            {
                string functionReturnValue = null;
                try
                {
                    string f = ThisAddIn.ServisasKat() + "klaidos.txt";
                    try
                    {
                        File.AppendAllText(f, string.Format("{0}{1}", DateTime.Now.ToString() + " " + ex.Message + " " + ex.StackTrace, Environment.NewLine));
                    }
                    catch { }

                    int trace = ex.StackTrace.IndexOf("line", 0);
                    if (trace > 0)
                    {
                        string KlaidEil = ex.StackTrace.Substring(trace + 5);

                        functionReturnValue = KlaidEil;
                        if (KlaidEil.Count() > 5)
                        {
                            int i = KlaidEil.IndexOf(" ", 0);
                            functionReturnValue = KlaidEil.Substring(i - 1);
                        }
                    }
                }
                catch
                {
                    functionReturnValue = "";
                }
                return functionReturnValue;
            }


    • Edited by Julius Bank Thursday, March 3, 2016 1:40 PM
    Thursday, March 3, 2016 1:38 PM
  • Hi Julius,

    These methods are independent, how/when/where you call them?

    What’s the purpose that you use ref in the parameter?

    How/when/where you release the com object (e.g. range, sheet(s), workbook(s), application)?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, March 4, 2016 2:12 AM
    Moderator
  • This is VSTO-AddIn. In main function i do not release com objects. 

    I have solved this by rewriting this functions:

    public double fun_viso(ref Excel.Worksheet ws)

    public string fun_tiesiog1(ref Excel.Worksheet wb)

    public double fun_PVM1(ref Excel.Worksheet ws)

    into more simple 1 function which finds all info i need:

    for (row = 30; row >= 1; row += -1)
                        {
                            kas0 = wb.Range["S_P1"].Offset[c1 + row, 0].Value.ToStr();

                            if (kas0 == "")
                                continue;
                            if (kas0.Substring(0, 10).ToLower() == "iš viso #5")
                            {
                                ((Excel.Worksheet)_with27.Workbooks[osuatl].Sheets["OSUATL"]).Range["J4"].Value = wb.Range["S_P1"].Offset[c1 + row, 7 + m].Value.ToDbl();
                                ((Excel.Worksheet)_with27.Workbooks[osuatl].Sheets["OSUATL"]).Range["J3"].Value = wb.Range["S_P1"].Offset[c1 + row - 1, 7 + m].Value.ToDbl();
                                a = true;
                            }
                            if (kas0.Substring(0, 10).ToLower() == "po indeksa")
                            {
                                ((Excel.Worksheet)_with27.Workbooks[osuatl].Sheets["OSUATL"]).Range["G2"].Value = wb.Range["S_P1"].Offset[c1 + row, 8 + m].Value.ToDbl();
                                ((Excel.Worksheet)_with27.Workbooks[osuatl].Sheets["OSUATL"]).Range["H2"].Value = wb.Range["S_P1"].Offset[c1 + row, 9 + m].Value.ToDbl();
                                ((Excel.Worksheet)_with27.Workbooks[osuatl].Sheets["OSUATL"]).Range["I2"].Value = wb.Range["S_P1"].Offset[c1 + row, 10 + m].Value.ToDbl();
                                ((Excel.Worksheet)_with27.Workbooks[osuatl].Sheets["OSUATL"]).Range["J2"].Value = wb.Range["S_P1"].Offset[c1 + row, 7 + m].Value.ToDbl();
                                b = true;
                            }
                            if (a && b)
                            {
                                break;
                            }
                        }

    It's sad that old code works fine on less data. It's like Excel can't handle all work... :)

    Tuesday, March 8, 2016 10:10 AM