declaring New Microsoft.Office.Interop.Excel.Application outside of Sub makes the VSTO COM addin to freeze during loading

Question

I have created an Excel COM add in which opens a windows form from a ribbon button. I want to populate the combobox using data from an external excel spreadsheet as well as populate the textboxes based on the selected name.

The part of the form where this applies

when placing the block of code:

Dim ExcelApp = New Microsoft.Office.Interop.Excel.Application ' this is causing the issue
Dim ClientListBook = ExcelApp.Workbooks.Open("C:\Users\SoftwareDev\Desktop\ASSETS\MasterFile.xlsx")
Dim ClientListSheet = ClientListBook.Sheets(1)

below the Public Class Form1 as seen below:

Imports Microsoft.Office.Interop.Excel
Public Class Form1
    Dim ExcelApp = New Microsoft.Office.Interop.Excel.Application ' this is causing the issue
        Dim ClientListBook = ExcelApp.Workbooks.Open("C:\Users\SoftwareDev\Desktop\ASSETS\MasterFile.xlsx")
        Dim ClientListSheet = ClientListBook.Sheets(1)

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbClientName.SelectedIndexChanged

            End Sub

it causes the Add in to freeze

But when Placing the same block of code into the Sub Form1_Load and Sub ComboBox1 as seen below, the add in works although it is very slow and i suspect it is an incorrect implementation.

THIS IS THE WORKING FULL CODE FOR THE FORM:

Imports Microsoft.Office.Interop.Excel
Public Class Form1
    
    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbClientName.SelectedIndexChanged
        Dim ExcelApp = New Microsoft.Office.Interop.Excel.Application ' this is causing the issue
        Dim ClientListBook = ExcelApp.Workbooks.Open("C:\Users\SoftwareDev\Desktop\ASSETS\MasterFile.xlsx")
        Dim ClientListSheet = ClientListBook.Sheets(1)

        Dim iStartedRow As Integer
        Dim iTotalRows As Integer

        ' count the number of rows in the worksheet
        iTotalRows = ExcelApp.ActiveWorkbook.Sheets(1).Range("a1").CurrentRegion.Rows.Count

        ' populates the textboxes using clientList data

        For iStartedRow = 2 To iTotalRows
            If Me.cmbClientName.Text = ClientListSheet.Cells(iStartedRow, 1).text Then
                Me.txtSal.Text = ClientListSheet.Cells(iStartedRow, 4).text
                Me.txtInvMan.Text = ClientListSheet.Cells(iStartedRow, 5).text
            End If
        Next

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim ExcelApp = New Microsoft.Office.Interop.Excel.Application ' this is causing the issue
        Dim ClientListBook = ExcelApp.Workbooks.Open("C:\Users\SoftwareDev\Desktop\ASSETS\MasterFile.xlsx")
        Dim ClientListSheet = ClientListBook.Sheets(1)

        Dim StartedRow As Integer
        Dim TotalRows As Integer

        ' clear any existing data 
        Me.cmbClientName.Items.Clear()

        ' count the number of rows in the worksheet
        TotalRows = ExcelApp.ActiveWorkbook.Sheets(1).Range("a1").CurrentRegion.Rows.Count

        ' create a loop to add data into combobox
        For StartedRow = 2 To TotalRows
            Me.cmbClientName.Items.Add(ClientListSheet.Cells(StartedRow, 1).Text)
        Next

    End Sub

End Class

Please would you let me know how i can correctly implement this and if it would be better and faster to use Microsoft Access database instead. I am new to VSTO COM add ins and VB.Net, this is my first project so i understand that my implementation may be suboptimal and will appreciate all feedback.

Thank you

Answer 1

If you declare and initialize these variables on the class level, they are created when the constructor runs.

There is absolutely no reason to initialize them immediately - you can declare them on the class level, but create them only when needed

Moreover, if your code is running in a VSTO addin, there is absolutely no reason to create an instance of the Excel.Application object - it is already available to you.

Hardcoding the file path is obviously also a problem...

Public Class Form1
    Dim ExcelAppe
    Dim ClientListBook
    Dim ClientListSheet

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ExcelApp = Globals.ThisAddin.Application
        ClientListBook = ExcelApp.Workbooks.Open("C:\Users\SoftwareDev\Desktop\ASSETS\MasterFile.xlsx")
        ClientListSheet = ClientListBook.Sheets(1)