To Optimize VBA Code for FASTER Macros_2

5. Hide Page breaks

When we run a Microsoft VBA macro in a later version of Microsoft Excel, the macro may take longer to complete than it does in earlier versions of Excel. For example, a macro that required several seconds to complete in an earlier version of Excel may require several minutes to complete in a later version of Excel. This problem may occur if the following conditions are true:

* The VBA macro modifies the properties of many rows or columns.
* An operation has been performed that forced Excel to calculate page breaks. Excel calculates page breaks when we perform any of the following operations:

o We display a print preview of your worksheet.
o In Microsoft Office Excel 2003 and in earlier versions of Excel, we click Page Setup on the File menu.
o We modify any of the properties of the PageSetup object in a VBA macro.

* In Excel 2003 and in earlier versions of Excel, we selected the Page breaks check box on the View tab of the Options dialog box.
Solution: is to disable Page breaks using ActiveSheet.DisplayPageBreaks = False

6. Use 'WITH' statement

If we have to access an object's properties and methods in several lines, we

must avoid using object's name or fully qualified object path again and again. It is annoying for VBA processor as it needs

to fully qualify the object each time. (Isn't it annoying for us too when some work or something is told us again and again?

Got it Guys !

SLOW 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

FAST MACRO:-

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

The point here to understand is minimum qualifying of an object by VBA processor. i.e. using minimum dots/periods(.) in the code. This concept tells us to use [A1] rather than Range("A1") and Range("StockRange")(3,4) rather than Range("StockRange").Cells(3,4)


7. Use vbNullString instead of ""


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.

8. Release memory of 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.