Stef's Blog: 2012

Optimize VBA Code

Avoid 'Screen Flickering' or 'Screen Repainting': Use
Application.ScreenUpdating = False 'To Turn Off at the start of code.
Application.ScreenUpdating = True 'To Turn on at the end of the code.

Turn off automatic calculations:

Application.Calculation = xlCalculationManual 'To turn off the automatic calculation
Application.Calculation = xlCalculationAutomatic 'To turn On the automatic calculation
ActiveSheet.Calculate ' To calculate the formulas of Active Worksheet
Application.Calculate ' To calculate the formulas of Active workbook or all workbooks in current application.

http://msdn.microsoft.com/en-us/library/aa173689%28v=office.11%29.aspx

Stop Events:

ActiveSheet.DisplayPageBreaks = False
http://support.microsoft.com/kb/199505

Use 'WITH' statement when working with objects:

SLOW MACRO	FAST MACRO
Sheets(1).Range("A1:E1").Font.Italic = True Sheets(1).Range("A1:E1").Font.Interior.Color = vbRed Sheets(1).Range("A1:E1").MergeCells = True	With Sheets(1).Range("A1:E1") .Font.Italic = True .Font.Interior.Color = vbRed .MergeCells = True End With

Use vbNullString instead of ""(2 double quotes) :

vbNullString is slightly faster than "", since vbNullString is not actually a string, but a constant set to 0 bytes, whereas "" is a string consuming at least 4-6 bytes for just existence.
For example: Instead of strVariable = ""

use strVariable = vbNullString.

Release memory from object variables:

Whenever we create an object in VBA, we actually create two things -- an object, and a pointer (called an object reference). We might say, "VB does not use pointers", but it is not true. "VB does not let you manipulate pointers" is more precise. Behind the scenes, VB still makes extensive use of pointers. To destroy an object in VB, you set it to Nothing. But wait a minute. If all we ever use are object pointers, how can we set the object itself to Nothing? The answer is: We can't.
When we set a reference to Nothing, something called the garbage collector kicks in. This little piece of software tries to determine whether or not an object should be destroyed. There are many ways to implement a garbage collector, but Visual Basic uses what is called the reference count method.
When VB interprets the last line(where we generally sets our objects to Nothing), it will remove the existing reference. At this point, if the object has no more references, the garbage collector will destroy the object and deallocate all its resources. If any other references point to the same object, the object will not be destroyed.

Reduce the number of Lines: Avoid multiple statements especially when they can be clubbed into one line. For example - See these 2 macros

SLOW MACRO

    With Selection
        .WrapText = True
        .ShrinkToFit = False

End With

FAST MACRO

With Selection
.WrapText = True: .ShrinkToFit = False

End With

As you can see, you can club multiple statements into one using colon character(:). When you do this with multiple statements, it will decrease the readability but will increase the speed.

Declare variable as Variable and constant as Constant:

Seems, obvious ! But many of us don't follow it. Like
      Dim Pi As Double
      Pi = 3.14159
instead use
      Const Pi As Double
      Pi = 3.14159

Avoid Unnecessary Copy and Paste:

Instead of	Use this:
Sheet1.Range("A1:A200").Copy Sheet2.Range("B1").PasteSpecial Application.CutCopyMode = False 'Clear Clipboard	'Bypass the Clipboard Sheet1.Range("A1:A200").Copy Destination:= Sheet2.Range("B1")
Sheet1.Range("A1:A200").Copy Sheet2.Range("B1").PasteSpecial xlPasteValues Application.CutCopyMode=False 'Clear Clipboard	'Bypass the Clipboard if only values are required Sheet2.Range("B1:B200").Value = Sheet1.Range("A1:A200").Value
Sheet1.Range("A1:A200").Copy Sheet2.Range("B1").PasteSpecial xlPasteFormulas Application.CutCopyMode=False 'Clear Clipboard	'Bypass the Clipboard if only formulas are required Sheet2.Range("B1:B200").Formula = Sheet1.Range("A1:A200").Formula 'Same can be done with FormulaR1C1 and Array Formulas.

Use Worksheet Functions rather developing own logic:

By using Application.WorkSheetFunction, we tell VBA processor to use native code rather than interpreted code as VBA understands the worksheet functions better than your algorithm. So, for example use
      mProduct = Application.WorkSheetFunction.Product(Range("C5:C10"))
rather than defining your own logic like this:
      mProduct = 1
      For i = 5 to 10
            mProduct = mProduct * Cells(3,i)

Use 'For Each' rather than 'indexed For':

We can avoid using Indexed For when looping through collections. For example, take the code just before this tip. It can be modified to:
For Each myCell in Range("C5:C10")
mProduct = mProduct * myCell.Value

This is in relation to qualifying object again and again as using "WITH" statements.

Avoid using 'Macro Recorder' style code:

The code will look genius and eventually perform like Genius too ! You'll better catch it with example, so use:
      [A1].Interior.Color = vbRed
rather than
      Range("A1").Select
      Selection.Interior.Color = vbRed
Using too many Select and Selection effects the performance drastically. Ask yourself why to go in Cell and then change the properties? or rather ask why to go pizza shop when you can enjoy it at your home ;)

Avoid using Variant and Object in declaration statements:

Think about better logic and get rid of them. i.e. do not use Dim i As Variant or Dim mCell As Object. By trying to be specific,we will save a lot of system memory this way, particularly in case of large projects. We may not remember which has been declared variant above and misuse a variable assigning any value to it which will be type-casted without errors. A variant's descriptor is 16 bytes long while double is 8 bytes, long is 4 bytes and integer is 2 bytes. Hence use Variant cautiously. As an example, use:
      Dim i As Long rather than Dim i As Variant
Similarly use:
      Dim mCell As Range 'or
      Dim mSheet As Worksheet
rather than
      Dim mCell As Object 'or
      Dim mSheet As Object

Declare OLE objects directly:

Declaring and defining the OLE objects in declaration statement is called 'Early Binding' but declaring an object and defining it later is called 'Late Binding'. Always prefer 'Early Binding' over 'Late Binding'. Now for example use:
      Dim oXL As Excel.Application
rather than
      Dim oXL As Object
      Set oXL = CreateObject("Excel.Application")

Stef's Blog

Senin, 27 Februari 2012

Kamis, 23 Februari 2012