ImportantRemember to restore this functionality to its original state after your code executes.The following example shows the functionality that you can turn off while your VBA macro executes. ' Save the current state of Excel settings.screenUpdateState = Application.ScreenUpdatingstatusBarState = Application.DisplayStatusBarcalcState = Application.CalculationeventsState = Application.EnableEvents' Note: this is a sheet-level setting.displayPageBreakState = ActiveSheet.DisplayPageBreaks' Turn off Excel functionality to improve performance.Application.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.Calculation = xlCalculationManualApplication.EnableEvents = False' Note: this is a sheet-level setting.ActiveSheet.DisplayPageBreaks = False' Insert your code here.' Restore Excel settings to original state.Application.ScreenUpdating = screenUpdateStateApplication.DisplayStatusBar = statusBarStateApplication.Calculation = calcStateApplication.EnableEvents = eventsState' Note: this is a sheet-level settingActiveSheet.DisplayPageBreaks = displayPageBreaksStateRead and write large blocks of data in a single operationOptimize your code by explicitly reducing the number of times data is transferred between Excel and your code. Instead of looping through cells one at a time to get or set a value, get or set the values in the entire range of cells in one line, using a variant containing a two-dimensional array to store values as needed. The following code examples compare these two methods.The following code example shows non-optimized code that loops through cells one at a time to get and set the values of cells A1:C10000. These cells do not contain formulas.
![]() ![]()
Dim DataRange as RangeDim Irow as LongDim Icol as IntegerDim MyVar as DoubleSet DataRange=Range('A1:C10000')For Irow=1 to 10000For icol=1 to 3' Read the values from the Excel grid 30,000 times.MyVar=DataRange(Irow,Icol)If MyVar 0 then' Change the value.MyVar=MyVar.Myvar' Write the values back into the Excel grid 30,000 times.DataRange(Irow,Icol)=MyVarEnd IfNext IcolNext IrowThe following code example shows optimized code that uses an array to get and set the values of cells A1:C10000 all at the same time. These cells do not contain formulas. Dim DataRange As VariantDim Irow As LongDim Icol As IntegerDim MyVar As Double' Read all the values at once from the Excel grid and put them into an array.DataRange = Range('A1:C10000').Value2For Irow = 1 To 10000For Icol = 1 To 3MyVar = DataRange(Irow, Icol)If MyVar 0 Then' Change the values in the array.MyVar=MyVar.MyvarDataRange(Irow, Icol) = MyVarEnd IfNext IcolNext Irow' Write all the values back into the range at once.Range('A1:C10000').Value2 = DataRangeUse.Value2 rather than.Value or.Text when reading data from an Excel range.Text returns the formatted value of a cell.
This is slow, can return ### if the user zooms, and can lose precision.Value returns a VBA currency or VBA date variable if the range was formatted as Date or Currency. This is slow, can lose precision, and can cause errors when calling worksheet functions.Value2 is fast and does not alter the data being retrieved from Excel.Avoid selecting and activating objectsSelecting and activating objects is more processing intensive than referencing objects directly. By referencing an object such as a Range or a Shape directly, you can improve performance. The following code examples compare the two methods.The following code example shows non-optimized code that selects each Shape on the active sheet and changes the text to 'Hello'.
'Iteration is a powerful feature in Excel, but not one that is familiar to a large number of users.' I, for one, am not at all familiar with using iteration in Excel. That said, if I had to use it, I would Google 'Excel Iteration' and read some of the various articles on the subject.
For i = 0 To ActiveSheet.Shapes.CountActiveSheet.Shapes(i).SelectSelection.Text = 'Hello'Next iThe following code example shows optimized code that references each Shape directly and changes the text to 'Hello'. For i = 0 To ActiveSheet.Shapes.CountActiveSheet.Shapes(i).TextEffect.Text = 'Hello'Next iUse these additional VBA performance optimizationsThe following is a list of additional performance optimizations you can use in your VBA code:.Return results by assigning an array directly to a Range.Declare variables with explicit types to avoid the overhead of determining the data type, possibly multiple times in a loop, during code execution.For simple functions that you use frequently in your code, implement the functions yourself in VBA instead of using the WorksheetFunction object. For more information, see.Use the Range.SpecialCells method to scope down the number of cells with which your code interacts.Consider the performance gains if you implemented your functionality by using the C API in the XLL SDK. For more information, see the.Consider performance and size of Excel file formatsStarting in Excel 2007, Excel contains a wide variety of file formats compared to earlier versions. Ignoring the Macro, Template, Add-in, PDF, and XPS file format variations, the three main formats are XLS, XLSB, and XLSX.XLS formatThe XLS format is the same format as earlier versions. When you use this format, you are restricted to 256 columns and 65,536 rows. When you save an Excel 2007 or Excel 2010 workbook in XLS format, Excel runs a compatibility check.
File size is almost the same as earlier versions (some additional information may be stored), and performance is slightly slower than earlier versions. Any multi-threaded optimization Excel does with respect to cell calculation order is not saved in the XLS format. Therefore, calculation of a workbook can be slower after saving the workbook in the XLS format, closing, and re-opening the workbook.XLSB formatXLSB is the binary format starting in Excel 2007. It is structured as a compressed folder that contains many binary files. It is much more compact than the XLS format, but the amount of compression depends on the contents of the workbook. For example, ten workbooks show a size reduction factor ranging from two to eight with an average reduction factor of four. Starting in Excel 2007, opening and saving performance is only slightly slower than the XLS format.XLSX formatXLSX is the XML format starting in Excel 2007, and is the default format starting in Excel 2007.
The XLSX format is a compressed folder that contains many XML files (if you change the file name extension to.zip, you can open the compressed folder and examine its contents). Typically, the XLSX format creates larger files than the XLSB format (1.5 times larger on average), but they are still significantly smaller than the XLS files. You should expect opening and saving times to be slightly longer than for XLSB files.Open, close, and save workbooksYou may find that opening, closing, and saving workbooks is much slower than calculating them. Sometimes this is just because you have a large workbook, but there can also be other reasons.If one or more of your workbooks open and close more slowly than is reasonable, it might be caused by one of the following issues.Temporary filesTemporary files can accumulate in your WindowsTemp directory (in Windows 95, Windows 98, and Windows ME), or your Documents and SettingsUser NameLocal SettingsTemp directory (in Windows 2000 and Windows XP). Excel creates these files for the workbook and for controls that are used by open workbooks. Software installation programs also create temporary files.
If Excel stops responding for any reason, you might need to delete these files.Too many temporary files can cause problems, so you should occasionally clean them out. However, if you have installed software that requires that you restart your computer, and you have not yet done so, you should restart before deleting the temporary files.An easy way to open your temp directory is from the Windows Start menu: Click Start, and then click Run.
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |