To Optimize VBA Code for FASTER Macros_4

14. Use 'For Each' 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
Next

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


15. Use 'Early Binding' rather 'Late Binding'

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")



16. Avoid using Variant

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

17. 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")