Speed up Excel Application objects in your code easily!

[Casey W Little]

I do tons of coding in MS Excel and MS Access. When coding in Access, many times I find the need to use the excel application object in order to manipulate existing workbooks or to create new workbooks populated with formatted data from the database. I have found 1000's of % increase in code execution speed can be gained by modifying 3 settings in your excel application during runtime:

Application.EnableEvents
Application.ScreenUpdating
Application.Calculation

Here's a VBA example to get you going:

Code: [Select]

Sub ExcelAppExample
        Dim oXLApp as New Excel.Application 'Create a new Excel Application Object
        Dim oXLBook as Excel.Workbook

                With oXLApp       'Disable Events, Screen Updating, and Calculation
                                          'You can capture As-Found settings in variables if you find the need
                                          'then before closing out, reset the settings to original         
                     oXLApp.EnableEvents = False
                     oXLApp.ScreenUpdating = False
                     oXLApp.Calculation = -4135 'xlCalculationManual
                End With

        Set oXLBook = oXLApp.Workbooks.Open("Drive:\Workbook.xls")

' Add your code to manipulate the workbook as required....

                With oXLApp       'Enable Events, Screen Updating, and Calculation
                     oXLApp.EnableEvents = True
                     oXLApp.ScreenUpdating = True
                     oXLApp.Calculation = -4105 'xlCalculationAutomatic
                End With

            oXLBook.Save 'Save the workbook
            oXLBook.Close 'Close the workbook
            oXLApp.Quit 'Quit the Application

         Set oXLBook = Nothing 'Release the object from memory
         Set oXLApp = Nothing 'Release the object from memory
End SubIn one of my more complex access applications, I had to create several workbooks and many worksheets within each workbook. The worksheet was populated and formated as data was added. The difference this addition made was astounding!

The operation to create a worksheet, populate it, and format it averaged 2 minutes per sheet. I had 1950 worksheets to create (Divided among several workbooks). Modifying the settings as above decreased the operation time to 5 seconds per worksheet! Thats's an increase of around 2400%.

In terms of total time required:
Settings enabled = (1950 * 120)/3600 = 65 Hours
Settings DIsabled = (1950 * 5)/3600 = 2.7 Hours

Nuff Said.....