Tutorial by Examples

In the VBA Editor window, from the Tools menu select "Options": Then in the "Editor" tab, make sure that "Require Variable Declaration" is checked: Selecting this option will automatically put Option Explicit at the top of every VBA module. Small note: This is ...

Office Blog - Excel VBA Performance Coding Best Practices Often, best performance is achieved by avoiding the use of Range as much as possible. In this example we read in an entire Range object into an array, square each number in the array, and then return the array back to the Range. This accesse...

If MsgBox("Click OK") = vbOK Then can be used in place of If MsgBox("Click OK") = 1 Then in order to improve readability. Use Object Browser to find available VB constants. View → Object Browser or F2 from VB Editor. Enter class to search View members available ...

Descriptive names and structure in your code help make comments unnecessary Dim ductWidth As Double Dim ductHeight As Double Dim ductArea As Double ductArea = ductWidth * ductHeight is better than Dim a, w, h a = w * h This is especially helpful when you are copying data from one ...

Good error handling prevents end users from seeing VBA runtime errors and helps the developer easily diagnose and correct errors. There are three main methods of Error Handling in VBA, two of which should be avoided for distributed programs unless specifically required in the code. On Error GoTo 0...

It's good practice to document your work for later use, especially if you are coding for a dynamic workload. Good comments should explain why the code is doing something, not what the code is doing. Function Bonus(EmployeeTitle as String) as Double If EmployeeTitle = "Sales" Then ...

It is best practice in any programming language to avoid premature optimization. However, if testing reveals that your code is running too slowly, you may gain some speed by switching off some of the application’s properties while it runs. Add this code to a standard module: Public Sub SpeedUp( _ ...

Using ActiveCell or ActiveSheet can be source of mistakes if (for any reason) the code is executed in the wrong place. ActiveCell.Value = "Hello" 'will place "Hello" in the cell that is currently selected Cells(1, 1).Value = "Hello" 'will always place "Hello&...

Even when all your work is directed at a single worksheet, it's still a very good practice to explicitly specify the worksheet in your code. This habit makes it much easier to expand your code later, or to lift parts (or all) of a Sub or Function to be re-used someplace else. Many developers establi...

It is very rare that you'll ever want to use Select or Activate in your code, but some Excel methods do require a worksheet or workbook to be activated before they'll work as expected. If you're just starting to learn VBA, you'll often be suggested to record your actions using the macro recorder, t...

When working with multiple open Workbooks, each of which may have multiple Sheets, it’s safest to define and set reference to all Workbooks and Sheets. Don't rely on ActiveWorkbook or ActiveSheet as they might be changed by the user. The following code example demonstrates how to copy a range from...

VBA is compiled in run-time, which has a huge negative impact on it's performance, everything built-in will be faster, try to use them. As an example I'm comparing SUM and COUNTIF functions, but you can use if for anything you can solve with WorkSheetFunctions. A first attempt for those would be t...

It is generally not considered 'best practice' to re-purpose the reserved names of Properties or Methods as the name(s) of your own procedures and variables. Bad Form - While the following is (strictly speaking) legal, working code the re-purposing of the Find method as well as the Row, Column and ...