1
\$\begingroup\$

I have a simple app, which is something like a launcher for my macro enabled excel workbook. I made it for easier distribution, to be able to check if excel installed and also to make it look and behave like a standalone application.

I am using Office Interop which, according to MS, has many performance issues. But still, have you got any idea what I could improve to perform faster?

Imports System.Threading
Imports Microsoft.Win32
Imports Microsoft.Office.Interop.Excel

Module Module1
Public Sub Main()
    On Error GoTo ErrorHandler
    Dim singleInstance As Boolean = False
    Dim mutex As New Mutex(True, My.Application.Info.AssemblyName, 
    singleInstance)
    Dim excelKey As RegistryKey = Registry.ClassesRoot.OpenSubKey("Excel.Application")
    Dim excelInstalled As Boolean = If(excelKey Is Nothing, False, True)
    If excelInstalled = True And singleInstance = True Then
        SplashForm.Show()
        Dim MyAppPath As String = My.Application.Info.DirectoryPath
        Dim ExcelFilePath As String = IO.Path.Combine(My.Computer.FileSystem.SpecialDirectories.MyDocuments, "Správce rozpisů 2019\" & "Rozpis1.xlsb")

        If Not IO.File.Exists(ExcelFilePath) Then
            Dim ExcelfileBackupPath As String = IO.Path.Combine(MyAppPath, "bundle\backup\" & "Rozpis1.xlsb")
            If IO.File.Exists(ExcelfileBackupPath) Then
                My.Computer.FileSystem.CopyFile(ExcelfileBackupPath, ExcelFilePath, True)
                Call Open(ExcelFilePath)
            Else
                SplashForm.Close()
                ErrorForm.ShowDialog()
                End
            End If
        Else
            Call Open(ExcelFilePath)
        End If
        SplashForm.Close()
    ElseIf excelInstalled = False Then
        SplashForm.Close()
        CompatibilityErrorForm.ShowDialog()
    ElseIf singleInstance = False Then
        MsgBox("Správce rozpisů je již spuštěn.", vbInformation, "Aplikace je již spuštěna")
    End If
    Exit Sub
ErrorHandler:
    MsgBox("Při spouštění Správce rozpisů došlo k chybě.", vbCritical, "Došlo k chybě")
    Resume Next
End Sub

Sub Open(openThis As String)
    Dim xls As Application = New Application With {
        .WindowState = XlWindowState.xlMaximized
    }
    Dim workbook As Workbook = xls.Workbooks.Open(openThis)
    xls.Visible = True
End Sub
End Module
\$\endgroup\$
0
\$\begingroup\$

Interop is surely enough for this purpose. To manipulate workbooks, fill in 10.000th of cells then I usually use SpreadsheetLight (http://spreadsheetlight.com/), a free, MIT licensed .NET library that works on the XML level of the Excel files and is incredibly fast compared to the Application.Excel object.

From the point of code review, I think you could dive a bit more into VB​.NET, your code still has the feeling of being VB6 or VBA. Why don't you switch to try/catch error handling for example? I assure you, you are going to like it. And as a rule of thumb, never compile any path/file names into your application, put them into app.config (especially if this is a standalone application) or into start parameters (especially if this application is called from within an enterprise database application).

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.