(For more resources related to this topic, see here.)

What kind of things can you do with it?

Once you have pushed your experience using the Office application to the limits and you can no longer get your job done due to a lack of built-in tools, using VBA will help avert frustrations you may encounter along the way. VBA enables you to build custom functions, also called User-defined Functions (UDFs), and you can automate tedious tasks such as defining and cleaning formats, manipulate system objects such as files and folders, as well as work together with Windows as a combined system, through its Application Programming Interface (API), and other applications by referencing their object libraries or Dynamic-link Libraries (DLLs).

Of course you can also use VBA to manipulate the Office application that hosts your code. For example, you can customize the user interface in order to facilitate the work you and others do.

An important thing to remember, though, is that the VBA code that you create is used within the host application. In our case, the code will run within Excel. Such VBA programs are not standalone, that is, they cannot run by themselves; they need the host application in order to operate correctly.

How can you use this technology within your existing projects?

You can use VBA in two different ways. The first, and most common way is to code directly into your VBA project. For example, you may have an Excel workbook with some custom functions that calculate commissions. You can add modules to this workbook and code the UDFs in this module.

Another option would be to save the workbook as an Addin. An Addin is a specialized document that hosts the code and makes it available to other workbooks. This is very useful when you need to share the solutions you develop with other workbooks and co-workers.

Recording a macro, adding modules, browsing objects, and variables

Before you get your hands “dirty” with coding in VBA, there are a few things you need to know. These things will help when it comes to coding. In this section, you will learn how to:

• Record a macro

• Add modules

• Browse objects

• Get some background on declaring variables

We will start with macro recording, a feature which is available in most Office applications.

Recording a macro

A macro, in Office applications, is a synonym for VBA code. In Excel, we can record almost any action we perform (such as mouse clicks and typing), which in turn is registered as VBA code. This can come in handy when we need to discover properties and methods related to an object. Let us now have a look at some ways you can record a macro in Excel. There are two options:

1. Recording a macro from the status bar.

2. Recording from the Developer tab.

Option 1 — Recording a macro from the status bar

From the status bar, click on the Record Macro button. If the button is not visible, right-click on the status bar and from the pop-up menu, choose the Macro Recording option, as shown in the following screenshot:

Option 2 — Recording from the Developer tab

Now that you know how to record a macro from the status bar, let us check another option. This option requires that you activate the Developer tab. In order to activate it, assuming it is not active yet, follow these steps:

1. Go to File | Excel Options | Customize Ribbon.

2. Under Main Tabs check the Developer checkbox, as shown in the following screenshot :

   

3. Next, activate the Developer tab and click on Record Macro, as shown in the following screenshot:

   

4. Once the macro recording process starts, you will be prompted to enter some basic information about the macro such as the macro name, the shortcut key, location where the macro should be stored, and its description. The following screenshot shows these options filled out:

   

5. Once the macro has been recorded, you can access its container module by pressing, simultaneously, the Alt + F11 keys. Alternatively, you can click on the Visual Basic button in the Developer tab. This button is to the left of the Record Macro button introduced previously. This will open the Visual Basic Editor (VBE), where all the VBA code is kept.

   The VBE is the tool we use to create, modify, and maintain any code we write or record. The following screenshot shows the VBE window with the project explorer, properties, and code window visible:

   

6. If upon opening the VBE, the VBA project explorer window is not visible, then follow these steps:

   1. Go to View | Project Explorer.

   2. Alternatively, press the Ctrl + R keys simultaneously.

7. If, on the other hand, the VBA project explorer is visible, but the code window is not, you can choose which code window to show.

8. Suppose you are interested in the content of the module you’ve recorded from the project explorer window, follow these step to show the module window:

   1. Click on View | Code.

   2. Alternatively, press F7.

Summary

In this article, you have learned some basic stuff about VBA. These included macro recording, adding modules, and browsing objects.

Resources for Article :

Further resources on this subject:

• Understanding ShapeSheet™ in Microsoft Visio 2010 [Article]
• Excel 2010 Financials: Using Graphs for Analysis [Article]
• Excel 2010 Financials: Adding Animations to Excel Graphs [Article]