Show Posts
1
VBA Programming / Speed up Excel Application objects in your code easily!
« on: July 11, 2012, 09:57:38 PM »
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:
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.....
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.....