I have an Outlook Module that contains the VBA Code below. The VBA code opens an Excel Add-In called TMAPAddIn.xlam, which then displays a User Form. I exported the Module and gave it to other colleagues so that they can import it to their Outlook, this allowed them to make a custom button on their Outlook Quick Access Toolbar.

The code works great, takes about 5 seconds for the User Form to display and the code executes without a problem. The file is open as ReadOnly to ensure multiple users can run the add in at the same time, without ever having to worry about them modifying its contents. I would like to know if my code can be written better for speed / efficiency.

I've written this code only for purposes of ensuring everyone runs and opens the latest version of TMAPAddIn.xlam, unfortunately I don't have the resources or time to develop a COM Add-In.


Sub LaunchTMAP()
Dim ExApp As Object
Set ExApp = CreateObject("Excel.Application")

Dim informationalBox As UserForm2
Set informationalBox = New UserForm2
informationalBox.Show 'letting the user know the workbook is downloading so they dont think Outlook froze

Dim ExWbk As Workbook
Set ExWbk = ExApp.Workbooks.Open("Z/TMAPAddIn.xlam", ReadOnly:=True) 'Z is a network drive


ExWbk.Application.Run "Module1.example"

ExApp = Nothing
ExWbk = Nothing
End Sub

I would propose rearranging and simplifying the code down to this:

Sub LaunchTMAP()
    '' let user know the workbook is downloading so they dont think Outlook froze
    Dim infoBox As New UserForm2
    Call infoBox.Show

    ''  open excel, open the workbook, run the subroutine, then close excel
    Dim xl As New Excel.Application
    Dim wb As Excel.Workbook
    Set wb = ExApp.Workbooks.Open("Z/TMAPAddIn.xlam", ReadOnly:=True) 'Z is a network drive
    Call xl.Run("Module1.example")
    Call xl.Quit

    ''  hide the userform
    Call infoBox.Hide
End Sub

This should not make your code run any faster, but it should make it feel more responsive. By moving the infoBox dim and show to before the new excel window is openned, you make the infoBox appear earlier. This is because the 5 second delay comes from opening excel. I've added a quit call to make sure that you are closing out the excel window when oyu are done with it. I've also gone switched you away from having the CreateObject call, as you appear to already be using the "Microsoft Excel XX.0 Object Library" reference. I also used Dim ... As New .. notation where posible to reduce the number of lines.

| improve this answer | |

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.