The VBA Integrated Development Environment IDE

Before learning how to program in VBA, you have to learn how to use the software required for creating your projects. The VBA development software is included with each component of the Microsoft Office suite of programs, including Excel. Starting the VBA development software places you in the VBA programming environment IDE, which provides you with a number of tools for use in the development of your project.

In tHe ReAL WoRLd

An IDE is software used by programmers for rapid application development (RAD). IDE's are available for numerous programming languages and are often quite expensive to purchase (several hundred dollars or more for a single license). The price is worth it because IDE's provide tools that enable programmers to develop applications quickly, saving them considerable time and money. Yet, the most important component of any development software is the compiler, which for many languages can be obtained at no cost. The compiler converts your program into the binary code your computer understands. If you have the compiler, all you really need to create an application—albeit with considerably more effort—is a text editor. Excel comes with its own IDE and VBA compiler, thus making it more of a value than you may realize.

Getting to the IDE from Excel

Before you begin creating projects with VBA you must know your way around the IDE. You can access the IDE from Excel in a couple of different ways. In Excel: select Tools, Macro, Visual Basic Editor (as shown in Figure 1.3); or use the keystroke Alt + F11.

Alternatively, select the Visual Basic toolbar from the View/Toolbars menu item in Excel. When the toolbar is displayed, select the Visual Basic Editor icon found in the middle of the toolbar (see Figure 1.4).

Components of the IDE

After opening the VBA IDE you may find yourself looking at a window similar to what is shown in Figure 1.5. This figure shows the VBA IDE and some of the tools that can be used to create projects.

Accessing the VBA IDE from the Tools menu in Excel.

Accessing the VBA IDE from the Tools menu in Excel.

The View/Toolbars menu item

Accessing the VBA IDE from the Visual Basic toolbar.

available toolbars

The Visual Basic editor icon The Visual Basic toolbar available toolbars

Accessing the VBA IDE from the Visual Basic toolbar.

The Visual Basic editor icon The Visual Basic toolbar

The menu bar

The Standard' toolbar

The Project -Explorer window

An Object -Code window

The Properties window

The VBA IDE.

The menu bar

The Standard' toolbar

The Project -Explorer window

An Object -Code window

The Properties window

The VBA IDE.

Like in most applications, there is a menu bar across the top of the window. You may only recognize a few items that exist within this menu, but don't worry. I'll show you the function of most of these items as we proceed through the book.

The Standard toolbar is one of four toolbars available from the IDE. Like any toolbar, its function is to give the user fast access to common tools available within the application. Again, I will explain the use of many of these functions, as well as the use of other toolbars, as we proceed through the book.

Of particular importance is the Project Explorer window, shown in the upper left corner of the IDE window in Figure 1.5. The Project Explorer lists all projects currently open, including those opened by Excel upon startup. The Project Explorer also lists the components of any opened projects. For example, Figure 1.5 shows that there is currently one project, called Bookl, open, and that this project contains four Excel objects: Sheetl, Sheet2, Sheet3, and ThisWorkbook. I will discuss Excel objects in detail in Chapter 5. For right now, recognize that these objects represent familiar components from Excel (the workbook and worksheets it contains).

If I open more workbooks in Excel, or add more worksheets to a currently open workbook in Excel, then their names will appear on the object list in the Project Explorer window.

Just below the Project Explorer window in Figure 1.5 is the Properties window. The Properties window displays a list of attributes or properties of the currently selected object in the Project Explorer window. These properties are used to manipulate the behavior and appearance of the object to which they belong. The properties of Sheetl are displayed in Figure 1.5 because it has been selected in the Project Explorer. Choosing a different object will result in a different properties list in the Properties window, as not all objects have the same properties. As a simple example in manipulating the properties of a worksheet, open a new workbook in Excel, note the name of your workbook and any worksheets it contains (do not change any names), then open the VBA IDE. Once in the IDE, display the Project Explorer and Properties windows. If the Project Explorer and Properties windows are not already displayed you can access them through the View menu item (see Figure 1.6). You can also use the keystrokes Ctrl+R and F4 to access the Project Explorer and Properties windows, respectively.

Accessing the Project Explorer and Properties windows.

Accessing the Project Explorer and Properties windows.

Once the Project Explorer window is displayed, find the project that represents the workbook you opened while in Excel (probably Bookl or Book2). If the components of the workbook you opened in Excel are not displayed, click the + sign next to the Microsoft Excel Objects folder directly underneath the project name. Next, find the object labeled Sheetl, select it with your mouse and then turn your attention to the Properties window. Scroll down the Properties window until you come to the Name property (the one without the parentheses around it). Delete the text entered to the right of the Name property and enter MySheet. Figure 1.7 illustrates how to find the Name property.

The View Object icon The View Code icon

The View Object icon The View Code icon

The Name property.

Accessing the Name property of a worksheet.

The Name property.

Accessing the Name property of a worksheet.

Toggle back to Excel by pressing Alt+F11, or select it from the taskbar in Windows. You will note that the name of Sheetl has now been replaced with MySheet in your Excel workbook, as shown in Figure 1.8.

The worksheet name

An edited worksheet name in Excel.

See how easy it is to alter the properties of a worksheet in Excel using VBA? As VBA developers, however, we will seldom, if ever, alter the properties of a workbook or worksheet at design time. The bulk of the work affecting workbooks and worksheets will occur at run time; however, we will alter properties of ActiveX controls at design time.

Design time refers to project development and the manipulation of object properties using the VBA IDE prior to running any code. Conversely run time will refer to the manipulation of object properties using a program; thus, the properties of the object do not change until the code is executed.

23 "X.1 | | | 1,1 * i M MyShi'i't Sheet2 / sheets /

Ready

Finally, I will show you one more component of the VBA IDE. If you look back at Figure 1.5 you will also see a standard code window. Windows such as these are used as containers for your program(s). This is where you type in the code for your program, so these windows are essentially text editors very similar to Notepad. You must be aware that there are pre-defined code windows for specific Excel objects, namely the workbook (for example, ThisWorkbook) and the worksheets (for example, Sheetl). The code window displayed in Figure 1.5 represents Sheetl contained within the workbook Bookl.

You will also be able to add components to your project and they will have their own code windows. I will explain how to use code windows more thoroughly as we proceed through this book. For now, know that you can open a code window by double clicking on any object listed in the Project Explorer. You can also select the object in the Project Explorer and click on the View Code icon at the top left of the window (refer to Figure 1.7), select Code from the tools menu, or press F7 (refer to Figure 1.6). Note that you can also view the selected object in Excel by selecting the appropriate item from these same locations (refer to Figures 1.7 and 1.8).

There are, of course, more components to the VBA IDE, but I've shown you enough to get you started for now. As the need arises, I will introduce more tools from the IDE that will aid in the development of various projects.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Responses

Post a comment