When you open the VBA code editor from within Microsoft Excel (alt + F11), you’ll see a new window that’s just gray space with a pane on the left. In this pane on the left, there are two sections. One is labeled “Project – VBA Project” and you’ll see the name of the workbook you opened the editor from there (in the example screenshot, this is “VBA Starter Kit Example.xlsm”). This is the project level for VBA. All the code you write under this project will be contained in the workbook file you save.
You’ll also see any other open workbooks and all the sheets that are currently in each workbook as well as a “ThisWorkbook” object. These are under the folder Microsoft Excel Objects. Each of these is considered an object. You can reference them by coding in the object name which we will example later.
More important than the code you write, is knowing where you need to place your code to achieve the desired result. You can write code within any Excel Object in your project by double clicking the object you want to interact with in the left-hand pane. Something very important to note is that code you write in the Sheet1 object, cannot reference data directly from Sheet2. You are operating within the Sheet1 object and your scope is limited to the data contained there. If you’re writing code to manipulate data on a single tab, this is fine to write it there. However, if you need to reference other tabs of data, you need to write code elsewhere.
One option is the “ThisWorkbook” object. When you write code in this section, you can reference any of the sheet objects in the workbook and can access all the data stored there.
Writing code in both locations is fine when working only within the scope of the workbook. Often, developers only use these sections to handle events that occur there. For example, you might write code that initiates when a user changes the value of a cell, double-clicks a cell, or otherwise interacts with a certain sheet. This is done at the sheet object level. If your code starts there, but shouldn’t be written there, where should you write it? This is where Modules, Forms, & Class Modules come in.
Often, you will be writing your code within modules only. Modules can interact with the workbook they’re stored in, all the sheets within that workbook, any other workbook and collection of sheets, as well as other Microsoft Office applications (check out this post for an example of referencing outside workbooks through a module). They can do more, but this is an introductory course. Suffice it to say that you can become a very good VBA developer if you only wrote code in modules.
Forms are great tools when you need to have the user interact with your code. For example, you might to have a user select a date range for your program to select data from that date range and then perform some action with it. A user might need to select a name from a dropdown list to generate a report for a specific employee. These actions are all possible using forms within your code.
Lastly, we come to Class Modules. As a beginner, you probably will not use class modules ever. I was writing code for several years before I found the need to use a class module. A class serves as the rules for how you interact with objects. If you were creating a tool to manage an employee roster, you could use a class to define how you interact with the new employee object you’re creating. You might set properties that, when someone inputs a new employee name, the name can’t be a number. Setting classes can get complex and as such we won’t cover it in this article.
|
Tickets |