The Visual Basic Editor
The Visual Basic Editor is where VBA code is stored:
You can access the Visual Basic Editor in the Developer Ribbon:
or with the shortcut ALT + F11.
If you don’t see the Developer Ribbon, you will need to add it (directions in the next section).
In the Visual Basic Editor you will find the Project Window. This window contains a list of the “modules” for all open workbooks. Modules are where the code is stored. There are three module types:
- Standard Module:
-
- Generic — Most of your code will go in the Modules folder. Unless you have a good reason to put it elsewhere, place your code here.
- ThisWorkbook — Store workbook-level code. Typically use this for workbook “events” that trigger code to run (workbook open, save, etc.)
- Sheet-level — Store sheet-level code. Typically use this for worksheet “events” that trigger code to run (worksheet change, worksheet activate, etc.)
- Form Module — Form Modules store code for UserForms.
- Class Module — The Class Modules are for advanced VBA programmers. They involve Object Oriented Programming. We will not cover Class Modules in this tutorial.
To insert a module, go to the Insert Menu (ALT > I).
You can (and should) rename Modules in the Properties Window. Renaming modules makes it easier to organize your code. If your VBA project contains more than one module, you should rename the modules.
Record a Macro
The Macro Recorder allows you to “record” your actions to VBA code. Even if you don’t know one bit of VBA code, you can use the Macro Recorder to create VBA procedures that repeat a series of actions.
Recording a Macro is easy:
- Go to Developer > Record a Macro
- Do your actions
- Go to Developer > Stop Recording
When Recording a Macro there are two options: ‘Use Relative References’ or not.
This setting changes how your code is recorded when selecting different cells. With ‘Use Relative References’ turned off, the Macro Recorder will record the actual cell ranges that you select. With it turned on, when using the keyboard to navigate, the Macro Recorder will record «offsets» from the activecell. Here is an example: Suppose you start in cell A1 and press the right arrow. With ‘Use Relative References’ turned off, cell B1 is recorded. With it turned on, Activecell.Offset(1,0) is recorded instead. This makes your code flexible based on whatever cell is selected when you run the Macro:
Chapter 10: Arrays
In broad programming terms, an Array is a series of objects of the same type. Usually arrays are used to store sets of data. Think of arrays in a similar way to a series of cells in an Excel worksheet. You can return values in an array by referencing its position in the array, similar to how you can reference a cell based on its column and row numbers.
Arrays are at the core of every programming language, but when working with Excel, arrays aren’t necessary because you can store information within ranges of cells.
The primary advantage to using arrays is processing speed. Reading and writing to ranges of cells in Excel is relatively very time-consuming. Reading and writing to arrays is much faster.
