1. Speed up Excel cells reading/writing using Range.Resize()
There two ways to read Excel cells programatically. First method is to use loops. This method is simple and ... slow.
Here is sample code:
For i = 1 To 10
destination.Cells(i,1).value = source.Cells(i,1).value
Next
As you see there was 10 reads and 10 writes in loop.
You can use Resize() method of Range class to speed up cells reading and writing.. Resize method allows to read range of cells at once.
Sample code:
Dim arSales as Variant
arSales = source.Cells.Resize(10,1)
This method reads specified range to array almost instantly. In Excel 2003 you can get entire sheet (65536x256 cells) in seconds.
You can write data to Excel sheet in same way using prepared array.
Sample code:
destination.Cells.Resize(10,1) = arSales
In some cases you don't need to use Resize to get array of cells. Use Value property of Range if you want to get all Cells of range.
An example:
Set rng = Range("DATA")
arSales = rng.Value
2. Disable automatic calculation, screen update and events
Each change of worksheet invokes automatic calculation of entire workbook. This behavior is useful when you do some manual input but unacceptable for VBA methods.
You can use Application properties to disable unnecessary updates.
Sample code:
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
And to return settings back :
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
3. Conditional formatting
Sometime we need to format Excel sheet using VBA. MS Excel provides few ways to do this with VBA.
First one is to use Color and Font properties of Range class.
Sample code:
Set rng = Range("HEADER")
With rng.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
With rng.Font
.Font.Italic = True
.Font.Bold = True
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
This code works well but it have few disadvantages. It is slow when complex formatting required and incredibly hard to tune it later.
Second method is Conditional formatting usage. The idea is to format cells automatically if they contain some data.
1. Create Excel file with header and other static information;
2. Apply Conditional formatting to first row of range where data will be placed. Select font/colors and use condition like if A2<>"";
3. Place data into A2 cell using VBA. This will fire automatic formatting.
Benefits of this method is speed and flexibility. Color and font picker simplifies formatting and code.
Sample code:
rowscount = 20000 ' Rows number to format
colscount= 5 ' Columns number to format
onepass = 1024 ' Rows number to format at once
src_row = 1 ' Row number where conditional format is set
For i = src_row+1 To rowscount Step onepass
Rows(src_row).Resize(1, colscount).Copy ' Copy row format
If i + onepass > rowscount Then: onepass = rowscount - i
' Paste format
Rows(i).Resize(onepass, colscount).PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Next
