Lesson 1 : Introduction To Excel VBA

In this Lesson, you will learn …

why use Excel VBA

the Visual Basic Editor

The purpose of this course is to show Excel users how to use the Visual Basic For Application programming language that comes free with MS Excel. This tool will help you to significantly enhance the power of the world’s most popular spreadsheet. Using VBA, however, involves programming, simple but very powerful.

Nowadays computer programming has become much easier, and even so-called normal people now engage in this activity. Programming simply means developing instructions that the computer automatically carries out.

Excel programming means that you can instruct Excel to automatically do things that you normally do manually — saving you lots of time resources.

Since you’re reading this, it’s a safe bet that you need to become an Excel programmer. This could be something you initiated or you want to make yourself more marketable. In this online course, we will learn about Excel programming so that by the time you finish this course, you can honestly say, “Oh, Excel programming is so easy.”

At the end of this course, we will also learn develop a few simple applications with Excel VBA.

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.

1) What is Visual Basic Editor?

You have use workbooks, formulas, charts, and other Excel goodies most of the time. Now it’s time to expand your horizons and explore an entirely new aspect of Excel: the Visual Basic Editor (VBE). In this chapter, you find out how to work with the VBE, and get down to the task of writing some VBA code.

The Visual Basic Editor is a separate application where you write and edit your VBA macros. It works seamlessly with Excel. You can activate the VBE by pressing Alt+F11 when Excel is active or by using the Tool-Macro-Visual Basic Editor command.(Fig-1.1)

To return to Excel, press Alt+F11 again.

(Fig 1.1- click for larger image)

(a) Menu Bar

Just like any other program menu bar that you have used, the VBE menu bar contains various commands and components. You can use this commands and components in the VBE to do things. Many of the menu commands have shortcut keys associated with them.

Fig 1.2 - Click for larger image

Figure 1.2 is the VBE program window. There are a few components on this window.


(b) Project Explorer

Fig 1.3 - Click for larger image

The Project Explorer window displays a tree diagram that shows every workbook currently open in Excel (including add-ins and hidden workbooks). When you’re working in the VBE, each Excel workbook and add-in that’s open is a project. You can think of a project as a collection of objects arranged as an outline. Click on the plus sign (+) at the left of the project’s name in the Project Explorer window to expand a project.

(c) Code Window

The VBA code is contains in the Code window (Figure 1.4, sometimes known as a Module window). Every object in a project has an associated Code window. Double-click the object in the Project Explorer window to bring up the Code Window. For example, to view the Code window for the Sheet1 object, double-click Sheet1 in the Project Explorer window. Unless you’ve added some VBA code, the Code window will be empty.

Fig 1.4 - Click for larger image

(d) Object Browser

The VBE includes another tool, known as the Object Browser (Figure 1.5). This tool allows you browse through the objects available to you. To access the Object Browser, press F2 when the VBE is active (or choose View ➪ Object Browser). You see a window like the one shown in Figure 1.5.

The drop-down list at the top contains a list of all currently available object libraries. Figure 1.5 shows All Libraries. If you want to browse through Excel’s objects, select Excel from the drop-down list.

Fig 1.5 - Click for larger image

(e) Properties Window

Fig 1.6 - Click for larger image

Figure 1.6 show the Properties window of the CommandButton1.For example when you change the Caption property to of CommandButton1 to “Click Me” and then notice how the new caption is displayed on the control. You can also play with some of the other properties such as Font, Height, BackColor, Width, and Height to change the appearance of CommandButton1.

2) Summary:

I hope that in this lesson, you did get a basic introduction to the VBA programming environment. So, you’ve learn how to access the VBA IDE and how to view and use some of its major components.