Nested Formulas
Arithmetic Operations(+,-,/,*)
Text Functions
CHAR
CLEAN
TRIM
LEFT
RIGHT
REPT
REPLACE
Mathematical Functions
ABS
FLOOR
CEILING
ROUND
INT
SIGN
ODD
EVEN
PI
TRUNC
Information Functions
ISBLANK
ISERR
ISERROR
ISEVEN
ISLOGICAL
ISNA
ISREF
ISTEXT
DATE/TIME Functions
SECOND
MINUTE
HOUR
DAY
MONTH
YEAR
TODAY
NOW
Statistical Formulas
SMALL
LARGE
MAX
MIN
MEDIAN
AVERAGE
Wednesday, February 8, 2012
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")
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")
To Optimize VBA Code for FASTER Macros_3
9. Reduce the number of lines using colon(:)
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.
10. Prefer constants
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
Since, its value is never changed so it will be evaluated once during compilation unlike variable which are evaluated many times during the run-time.
11. Avoid Unnecessary Copy and Paste
Instead of:-
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").PasteSpecial xlPasteFormulas
Application.CutCopyMode=False
'Clear Clipboard
Use this:-
'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.
12. Clear the Clipboard after Paste
Instead of:-
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").PasteSpecial
Application.CutCopyMode = False
'Clear Clipboard
Use this:-
'Bypass the Clipboard
Sheet1.Range("A1:A200").Copy Destination:= Sheet2.Range("B1")
13. Avoid '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.
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.
10. Prefer constants
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
Since, its value is never changed so it will be evaluated once during compilation unlike variable which are evaluated many times during the run-time.
11. Avoid Unnecessary Copy and Paste
Instead of:-
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").PasteSpecial xlPasteFormulas
Application.CutCopyMode=False
'Clear Clipboard
Use this:-
'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.
12. Clear the Clipboard after Paste
Instead of:-
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").PasteSpecial
Application.CutCopyMode = False
'Clear Clipboard
Use this:-
'Bypass the Clipboard
Sheet1.Range("A1:A200").Copy Destination:= Sheet2.Range("B1")
13. Avoid '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.
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.
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.
To Optimize VBA Code for FASTER Macros_1
1. Analyze the Logic :-
Before optimizing the syntax, pay more attention in optimizing the logic. Without a good logic, a good written VBA macro program has no value. So streamline your program logic and get the best performance of macros.
2. Turn off ScreenUpdating
Avoid 'Screen Flickering' or 'Screen Repainting': Use
Application.ScreenUpdating = False 'To Turn Off at the start of code.
Application.ScreenUpdating = False 'To Turn on at the end of the code.
The ScreenUpdating property controls most display changes on the monitor while a procedure is running. When screen updating is turned off, toolbars remain visible and Word still allows the procedure to display or retrieve information using status bar prompts, input boxes, dialog boxes, and message boxes. You can increase the speed of some procedures by keeping screen updating turned off. You must set the ScreenUpdating property to True when the procedure finishes or when it stops after an error.
3. Turn off 'Automatic Calculations'
Whenever content(s) of a cell or range of cells are changed, the formulas dependent on them and Volatile functions are recalculated.
You may turn off the automatic calculation using
Application.Calculation = xlCalculationManual 'To turn off the automatic calculation
Application.Calculation = xlCalculationAutomatic 'To turn On the automatic calculation
Now, whenever due to the program logic(that due to macros dependent on existing formulas) you need to calculate the formulas, you may use the following code accordingly.
ActiveSheet.Calculate ' To calculate the formulas of Active Worksheet
Application.Calculate ' To calculate the formulas of Active workbook or all workbooks in current application.
4. Disable Events
Use Application.EnableEvents to tell VBA processor whether to fire events or not. We rarely fire an event for each cell we're changing via code. Hence, turning off events will speed up our VBA code performance.
Before optimizing the syntax, pay more attention in optimizing the logic. Without a good logic, a good written VBA macro program has no value. So streamline your program logic and get the best performance of macros.
2. Turn off ScreenUpdating
Avoid 'Screen Flickering' or 'Screen Repainting': Use
Application.ScreenUpdating = False 'To Turn Off at the start of code.
Application.ScreenUpdating = False 'To Turn on at the end of the code.
The ScreenUpdating property controls most display changes on the monitor while a procedure is running. When screen updating is turned off, toolbars remain visible and Word still allows the procedure to display or retrieve information using status bar prompts, input boxes, dialog boxes, and message boxes. You can increase the speed of some procedures by keeping screen updating turned off. You must set the ScreenUpdating property to True when the procedure finishes or when it stops after an error.
3. Turn off 'Automatic Calculations'
Whenever content(s) of a cell or range of cells are changed, the formulas dependent on them and Volatile functions are recalculated.
You may turn off the automatic calculation using
Application.Calculation = xlCalculationManual 'To turn off the automatic calculation
Application.Calculation = xlCalculationAutomatic 'To turn On the automatic calculation
Now, whenever due to the program logic(that due to macros dependent on existing formulas) you need to calculate the formulas, you may use the following code accordingly.
ActiveSheet.Calculate ' To calculate the formulas of Active Worksheet
Application.Calculate ' To calculate the formulas of Active workbook or all workbooks in current application.
4. Disable Events
Use Application.EnableEvents to tell VBA processor whether to fire events or not. We rarely fire an event for each cell we're changing via code. Hence, turning off events will speed up our VBA code performance.
Tuesday, January 3, 2012
VBA Controls:-
This section is about communicating with users using controls or a Userform. Learn how to use these controls in Excel 2010,
Excel 2007 or Excel 2003. You can directly place controls on a sheet or place them on a Userform.
1) Textbox: A textbox is an empty field where the user can fill in a piece of text. Learn how to draw a textbox on your worksheet, how to refer to a textbox in your Excel VBA code, and how to clear a textbox.
2) Listbox: A listbox, is a drop down list from where the user can make a choice. Learn how to draw a listbox on your worksheet and how to add items to a listbox.
3) Combobox: A combobox is the same as a listbox but now the user can also fill in his/her own choice if it is not included in the list. Learn how to draw a combobox on your worksheet and how to add items to a combobox.
4) Checkbox: A checkbox is a field which can be checked to store information. Learn how to draw a checkbox on your worksheet and how to refer to a checkbox in your Excel VBA code.
5) Option Buttons: Option buttons are the same as checkboxes except that option buttons are dependent on each other while checkboxes are not. This means that when you check one option button the other option button will automatically uncheck.
6) Userform: This chapter teaches you how to create an Excel VBA Userform (also known as a dialog box). You can download the Userform on this page as well.
Excel 2007 or Excel 2003. You can directly place controls on a sheet or place them on a Userform.
1) Textbox: A textbox is an empty field where the user can fill in a piece of text. Learn how to draw a textbox on your worksheet, how to refer to a textbox in your Excel VBA code, and how to clear a textbox.
2) Listbox: A listbox, is a drop down list from where the user can make a choice. Learn how to draw a listbox on your worksheet and how to add items to a listbox.
3) Combobox: A combobox is the same as a listbox but now the user can also fill in his/her own choice if it is not included in the list. Learn how to draw a combobox on your worksheet and how to add items to a combobox.
4) Checkbox: A checkbox is a field which can be checked to store information. Learn how to draw a checkbox on your worksheet and how to refer to a checkbox in your Excel VBA code.
5) Option Buttons: Option buttons are the same as checkboxes except that option buttons are dependent on each other while checkboxes are not. This means that when you check one option button the other option button will automatically uncheck.
6) Userform: This chapter teaches you how to create an Excel VBA Userform (also known as a dialog box). You can download the Userform on this page as well.
VBA Programming 2:-
7) Logical Operators: Do you want to execute code in Excel Visual Basic when more conditions are met? Or just one? Or none?
Logical operators are what you need! Logical operators such as And, Or and Not are often used in Excel VBA.
8) Range: The Range object which is the representation of a cell (or cells) on your worksheet is the most important object of Excel VBA. It has many properties and methods and they are essential to manipulate the content of your Excel worksheet. In this chapter you will discover the most useful properties and methods of the Excel VBA Range object. They enable you to obtain control over your Excel worksheet.
9) Events: This chapter teaches you how to program workbook and worksheet events. Events are actions performed by users which trigger Excel VBA to execute a macro. For example, when you open a workbook or when you change something on an Excel worksheet, Excel VBA can automatically execute a macro.
10) Array: An Excel VBA array is a group of variables. You can refer to a specific variable (element) of an array by using the array name and the index number.
11) Date and Time: Dates and Times in Excel VBA can be manipulated in many ways. Easy examples are given in this chapter.
12) Function and Sub: The difference between a function and a sub in Excel VBA is that a function can return a value and a sub cannot. In this chapter we will look at an easy example of a function and a sub. Functions and subs become very useful as program size increases.
Logical operators are what you need! Logical operators such as And, Or and Not are often used in Excel VBA.
8) Range: The Range object which is the representation of a cell (or cells) on your worksheet is the most important object of Excel VBA. It has many properties and methods and they are essential to manipulate the content of your Excel worksheet. In this chapter you will discover the most useful properties and methods of the Excel VBA Range object. They enable you to obtain control over your Excel worksheet.
9) Events: This chapter teaches you how to program workbook and worksheet events. Events are actions performed by users which trigger Excel VBA to execute a macro. For example, when you open a workbook or when you change something on an Excel worksheet, Excel VBA can automatically execute a macro.
10) Array: An Excel VBA array is a group of variables. You can refer to a specific variable (element) of an array by using the array name and the index number.
11) Date and Time: Dates and Times in Excel VBA can be manipulated in many ways. Easy examples are given in this chapter.
12) Function and Sub: The difference between a function and a sub in Excel VBA is that a function can return a value and a sub cannot. In this chapter we will look at an easy example of a function and a sub. Functions and subs become very useful as program size increases.
Subscribe to:
Posts (Atom)