none
Need help understanding how to make a VSTO excel add-in. The attached code says start-up and shutdown event cannot be found RRS feed

  • Question

  • I am missing something basic.  I created a Excel 2013 Add-in project in Visual Studio (VB module).  My code is posted below.  At first it compiled; but then I changed the project name and now it says start-up and shutdown even cannot be found.  Also when it did compile, I could not load it to Excel.  It gave a run-time error.  Any help would be greatly appreciated.  This is my first attempt at making a VSTO add-in.  VB part of the code has no reported errors.  Thanks.

    Raja

    Imports Microsoft.VisualStudio.Tools.Applications
    Public Class ThisAddIn
        Private Sub ThisAddIn_Startup() Handles Me.Startup
            System.Windows.Forms.MessageBox.Show("Add-in has successfully loaded")
            CFMList()
            

        End Sub

        Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

        End Sub
        Public Sub CFMList()

            'Declaration
            '   PublicEnumerationXlThemeColor()
            '
            ' This macro makes a sorted CFM list from a IP11B exported Excel workbook
            '

            '
            ' Worksheets.Add(After:=Worksheets(1)).Name = "CFM_Map"

            ' Sheets.Add After:=Sheets(Sheets.Count)

            Dim excelapp As New Excel.Application
            Dim sht1 As Worksheet

            excelapp = CreateObject("Excel.Application")
            excelapp.Visible = True
            'book1 = excelapp.Workbooks.Add
            sht1 = Globals.CFMList.Application.ActiveWorkbook.Sheets("MAX UFBC (Cell Friction Metric)")
            excelapp.Visible = True
            sht1.Select()

            ' For i = 0 To caltip.GetUpperBound(0)
            'For j = 0 To caltip.GetUpperBound(1)
            'sht1.Cells(i + 2, j + 1).value = caltip(i, j)
            'Next
            'Next

            'Sheets("MAX UFBC (Cell Friction Metric)").Select()
            sht1.Cells("A50") = "Rod#"
            sht1.Cells("B50") = "GE-i"
            sht1.Cells("C50") = "GE-j"
            sht1.Cells("D50") = "PNPS-xx"
            sht1.Cells("E50") = "PNPS-yy"
            sht1.Cells("G50") = "CFM"
            sht1.Cells("F50") = "Site Rod"
            sht1.Cells("h50") = "Max Node"

            Dim Csize As Long


            sht1.Cells("K1") = "max i"
            sht1.Cells("L1") = "=MAX(R[5]C[-11]:R[5]C[42])"
            Csize = sht1.Cells("L1")

            Dim GNFi(Csize) As Long
            Dim GNFj(Csize) As Long
            Dim PNPSxx(Csize) As Long
            Dim PNPSyy(Csize) As Long
            Dim CFM(Csize, Csize) As Long
            Dim max_node(Csize, Csize) As Long
            Dim n As Long

            n = 0
            For i = 1 To Csize
                GNFi(i) = sht1.Cells(6, i + 1)
                PNPSxx(i) = 2 * GNFi(i)

                For j = 1 To Csize
                    GNFj(j) = sht1.Cells(j + 6, 1)
                    PNPSyy(j) = 2 * Csize + 1 - 2 * GNFj(j)
                    CFM(i, j) = sht1.Cells(j + 6, i + 1)
                    max_node(i, j) = sht1.Cells(j + 6, i + 1)
                    If (CFM(i, j) <> 0) Then
                        n = n + 1
                        sht1.Cells(50 + n, 1) = n
                        sht1.Cells(50 + n, 2) = GNFi(i)
                        sht1.Cells(50 + n, 3) = GNFj(j)
                        sht1.Cells(50 + n, 4) = PNPSxx(i)
                        sht1.Cells(50 + n, 5) = PNPSyy(j)
                        sht1.Cells(50 + n, 6) = PNPSxx(i) & " " & PNPSyy(j)
                        sht1.Cells(50 + n, 7) = CFM(i, j)
                        sht1.Cells(50 + n, 8) = max_node(i, j)
                    End If

                Next j
            Next i

            sht1.Range("B51").Select()
            sht1.Range("B51").End(Excel.XlDirection.xlDown).End(Excel.XlDirection.xlToRight).Select()


            Dim xlSortOnValues As Long
            Dim xlDescending As Long
            Dim xlSortNormal As Long
            xlSortOnValues = 0
            xlDescending = 1
            xlSortNormal = 0
            sht1.Sort.SortFields.Clear()
            sht1.Sort.SortFields.Add(Key:=sht1.Range("G51").End(Excel.XlDirection.xlDown), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal)


            With sht1.Sort
                .SetRange(sht1.Range("B50").End(Excel.XlDirection.xlDown))
                .Header = Excel.XlYesNoGuess.xlNo
                .MatchCase = False
                .Orientation = Excel.XlSortOrientation.xlSortRows
                .SortMethod = Excel.XlSortMethod.xlPinYin
                .Apply()
            End With
            Dim r1 As NamedRange

            r1 = sht1.Range("G51").Select()
            r1 = sht1.Range("G51").End(Excel.XlDirection.xlDown).Select()

            r1.FormatConditions.Add(Type:=Excel.XlFormatConditionType.xlCellValue, Operator:=Excel.XlFormatConditionOperator.xlGreater, _
                Formula1:="=339.5")
            r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
            With r1.FormatConditions(1).Interior
                .PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                .Color = 255
                .TintAndShade = 0
            End With
            r1.FormatConditions(1).StopIfTrue = False
            r1.FormatConditions.Add(Type:=Excel.XlFormatConditionType.xlCellValue, Operator:=Excel.XlFormatConditionOperator.xlBetween, _
                Formula1:="=99.5", Formula2:="=159.5")
            r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
            With r1.FormatConditions(1).Interior
                .PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                .Color = 65535
                .TintAndShade = 0
            End With
            r1.FormatConditions(1).StopIfTrue = False
            r1.FormatConditions.Add(Type:=Excel.XlFormatConditionType.xlCellValue, Operator:=Excel.XlFormatConditionOperator.xlGreater, _
                Formula1:="=159.5")
            r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
            With r1.FormatConditions(1).Font
                .Color = -16383844
                .TintAndShade = 0
            End With
            With r1.FormatConditions(1).Interior
                .PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                .Color = 13551615
                .TintAndShade = 0
            End With
            r1.FormatConditions(1).StopIfTrue = False

            sht1.Range("F51").Select()
            r1 = sht1.Range("F51").End(Excel.XlDirection.xlDown).Select()
            r1.FormatConditions.Delete()
            r1 = sht1.Range("G51").End(Excel.XlDirection.xlDown).Select()
            sht1.Range("F51").Activate()
            r1.FormatConditions.Add(Type:=Excel.XlFormatConditionType.xlCellValue, Operator:=Excel.XlFormatConditionOperator.xlGreater, _
                Formula1:="=339.5")
            r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
            With r1.FormatConditions(1).Interior
                .PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                .Color = 255
                .TintAndShade = 0
            End With
            r1.FormatConditions(1).StopIfTrue = False
            r1 = sht1.Range("G51").End(Excel.XlDirection.xlDown).Select()
            sht1.Range("F51").Activate()
            r1.FormatConditions.Add(Type:=Excel.XlFormatConditionType.xlCellValue, Operator:=Excel.XlFormatConditionOperator.xlBetween, _
                Formula1:="=99.5", Formula2:="=159.5")
            r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
            With r1.FormatConditions(1).Interior
                .PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                .Color = 65535
                .TintAndShade = 0
            End With
            r1.FormatConditions(1).StopIfTrue = False

            r1.FormatConditions.Add(Type:=Excel.XlFormatConditionType.xlCellValue, Operator:=Excel.XlFormatConditionOperator.xlGreater, _
                Formula1:="=159.5")
            r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
            With r1.FormatConditions(1).Font
                .Color = -16383844
                .TintAndShade = 0
            End With
            With r1.FormatConditions(1).Interior
                .PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                .Color = 13551615
                .TintAndShade = 0
            End With
            r1.FormatConditions(1).StopIfTrue = False


            r1 = sht1.Range("F51").End(Excel.XlDirection.xlDown).Select()
            r1.FormatConditions.Add(Type:=Excel.XlFormatConditionType.xlExpression, Formula1:= _
                "=IF(G51:G134>159.5,TRUE,FALSE)")
            r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
            With r1.FormatConditions(1).Font
                .Color = -16776961
                .TintAndShade = 0
            End With
            With r1.FormatConditions(1).Interior
                .PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                .ThemeColor = Excel.XlThemeColor.xlThemeColorAccent6
                .TintAndShade = 0.799981688894314
            End With
            r1.FormatConditions(1).StopIfTrue = False

            r1.FormatConditions.Delete()
            r1 = sht1.Range("G51").End(Excel.XlDirection.xlDown).Select()
            sht1.Range("F51").Activate()
            r1.FormatConditions.Add(Type:=Excel.XlFormatConditionType.xlCellValue, Operator:=Excel.XlFormatConditionOperator.xlGreater, _
                Formula1:="=339.5")
            r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
            With r1.FormatConditions(1).Interior
                .PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                .Color = 255
                .TintAndShade = 0
            End With
            r1.FormatConditions(1).StopIfTrue = False
            r1.FormatConditions.Add(Type:=Excel.XlFormatConditionType.xlCellValue, Operator:=Excel.XlFormatConditionOperator.xlBetween, _
                Formula1:="=99.5", Formula2:="=159.5")
            r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
            With r1.FormatConditions(1).Interior
                .PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                .Color = 65535
                .TintAndShade = 0
            End With
            r1.FormatConditions(1).StopIfTrue = False
            r1.FormatConditions.Add(Type:=Excel.XlFormatConditionType.xlCellValue, Operator:=Excel.XlFormatConditionOperator.xlGreater, _
                Formula1:="=159.5")
            r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
            With r1.FormatConditions(1).Font
                .Color = -16383844
                .TintAndShade = 0
            End With
            With r1.FormatConditions(1).Interior
                .PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                .Color = 13551615
                .TintAndShade = 0
            End With
            r1.FormatConditions(1).StopIfTrue = False
            r1 = sht1.Range("F51").End(Excel.XlDirection.xlDown).Select()

            r1.FormatConditions.Add(Type:=Excel.XlFormatConditionType.xlExpression, Formula1:= _
                "=IF(G51:G134>159.5,TRUE,FALSE)")
            r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
            With r1.FormatConditions(1).Font
                .Color = -16776961
                .TintAndShade = 0
            End With
            With r1.FormatConditions(1).Interior
                .PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                .ThemeColor = Excel.XlThemeColor.xlThemeColorAccent6
                .TintAndShade = 0.799981688894314
            End With
            r1.FormatConditions(1).StopIfTrue = False
            r1.FormatConditions.Add(Type:=Excel.XlFormatConditionType.xlExpression, Formula1:= _
                "=IF(G51:G134>99.5,IF(G51:G134<159.5,TRUE,FALSE),FALSE)")
            r1.FormatConditions(r1.FormatConditions.Count).SetFirstPriority()
            With r1.FormatConditions(1).Interior
                .PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic
                .Color = 65535
                .TintAndShade = 0
            End With
            r1.FormatConditions(1).StopIfTrue = False

            r1 = sht1.Range("A50:H50").Select()
            With r1
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                '     .ReadingOrder = xlContext
                .MergeCells = False
            End With
            With r1
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                '   .ReadingOrder = xlContext
                .MergeCells = False
            End With
            r1.Font.Bold = True
            r1 = sht1.Range("A50").End(Excel.XlDirection.xlToRight).End(Excel.XlDirection.xlDown).Select()

            r1.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone
            r1.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone
            With r1.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThin
            End With
            With r1.Borders(Excel.XlBordersIndex.xlEdgeTop)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThin
            End With
            With r1.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThin
            End With
            With r1.Borders(Excel.XlBordersIndex.xlEdgeRight)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThin
            End With
            With r1.Borders(Excel.XlBordersIndex.xlInsideVertical)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThin
            End With
            With r1.Borders(Excel.XlBordersIndex.xlInsideHorizontal)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThin
            End With
            r1 = sht1.Range("A50").End(Excel.XlDirection.xlDown).End(Excel.XlDirection.xlToRight).Select

            sht1.PageSetup.PrintArea = sht1.Range("A50").End(Excel.XlDirection.xlDown).End(Excel.XlDirection.xlToRight).Select

            sht1.ExportAsFixedFormat(Type:=Excel.XlFixedFormatType.xlTypePDF, Filename:= _
                "make-CFM-list.pdf", Quality:=Excel.XlFixedFormatQuality.xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                False)
            Globals.CFMList.Application.ActiveWorkbook.SaveAs()

        End Sub

    End Class


    Sunday, July 20, 2014 6:52 PM

Answers

  • Hello Raja,

    > At first it compiled; but then I changed the project name

    You need to revert the changes back or rename all other parts of the projects. Try to search for the old name in the project.

    Also please make sure that you unregistered the add-in before doing any changes in the add-in (use the Clean option in Visual Studio for unregistering).

    Monday, July 21, 2014 9:41 AM
  • Hi Raja

    Based on personal experience, it's a very BAD idea to change a project name, once it's been created. I think it will be a lot less frustrating and time-consuing to create a new project - with the right name - and copy across any code you've written...


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, July 21, 2014 2:28 PM
    Moderator

All replies

  • Hello Raja,

    > At first it compiled; but then I changed the project name

    You need to revert the changes back or rename all other parts of the projects. Try to search for the old name in the project.

    Also please make sure that you unregistered the add-in before doing any changes in the add-in (use the Clean option in Visual Studio for unregistering).

    Monday, July 21, 2014 9:41 AM
  • Hi Raja

    Based on personal experience, it's a very BAD idea to change a project name, once it's been created. I think it will be a lot less frustrating and time-consuing to create a new project - with the right name - and copy across any code you've written...


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, July 21, 2014 2:28 PM
    Moderator