The VBA IDE

Now that we have written our first procedure, it is probably as good a time as any to take a look at the VBA integrated development environment (IDE). The VBA IDE is the place where you will type all of your VBA code, and if you have not seen it before it can appear quite daunting.

You will sometimes see the VBA IDE referred to simply as the VBE or Visual Basic Editor. Don't be confused ; they are just two different names for the same thing.

If you have used Access before, one of the first things that you may have noticed is that the VBA IDE is now in a separate window from Access itself. So, even though the code we are writing relates to Access objects, we enter the code in the VBA IDE window rather than within Access itself. Actually, the VBA IDE is now common to all of the VBA-enabled applications that make up Microsoft Office XP as well as to Visual Basic itself. So, whichever Office XP application you are in, if it supports VBA you will be able to use the VBA IDE to write VBA code.

In previous versions of Access, the code editor was integrated within Access itself. It might seem a little counter-intuitive to abstract the code development environment into a separate window, rather than retaining it within the application itself. However, the benefits - in terms of ease of learning and portability of code - of sharing a common, integrated development environment between all of the Office XP applications is, in the author's opinion, well worth the effort of occasionally having to Alt+Tab (or Alt+F11 ) between windows .

OK, so let's have a look in a little more detail at the VBA IDE.

Components of the VBA IDE

When we wrote our first event procedure in VBA earlier in this chapter, we concentrated on just one window, the code window. In fact, the VBA IDE is comprised of 7 different windows, each of which can be hidden or displayed according to the user 's preferences. The screenshot below shows what the VBA IDE looks like with all seven windows displayed:

Remember, if the VBA IDE isn't displayed, press Alt+F11, or select Code from the View menu when you're in Design view. Note also that when you call it up, it may not show all seven windows automatically.

We will look at how all of these windows can be used throughout this book, but before we go much further, it probably makes sense to familiarize ourselves with two of them in particular, the Project Explorer and the Code Window .

Project Explorer

All code within VBA must be stored within a project . As far as we are concerned , all of the code that we use within an Access database is stored within a single project. By default the name of the VBA project that holds our code is given the same name as the Access database to which the code relates. So, when we created our first event procedure in the IceCream.mdb database, Access created a new VBA project and called it IceCream .

Important 

Be careful not to confuse a VBA project with an Access project. A VBA project is the mechanism used by VBA to store all of the code that is associated with a specific database. By contrast, an Access Data Project (or .adp file) is a special type of lightweight database front-end to facilitate client-server development against databases such as SQL Server or Oracle.

Within a VBA project, code is arranged within a series of modules . These are simply a method of grouping together related chunks of code. If it makes it any easier, think of a filing cabinet at the office - you might use the top drawer for product detail files, the second for customer files and the third for invoices and sales details. The drawers of a filing cabinet allow you to store similar files together, but in a way that keeps them separate from each other. In the same way, a module allows you to store related chunks of code together. The Project Explorer window allows us to navigate our project and locate individual modules and individual chunks of code (we call them procedures, and will explain more about them in the next chapter ) within those modules. The three types of module that are displayed in the Project Explorer window are class modules, form and report modules, and standard modules.

If the Project Explorer window is not immediately visible in the VBA IDE, it might be hidden. To make it visible, just hit Ctrl+R.

Form and Report Modules

Every form and report can have a built-in module, called a class module , associated with it that contains all the code for that form or report. This code includes both event procedures - such as the cmdFirst_Click() event we wrote earlier - and other general procedures. The class module is tightly bound to the form or report object - so if you copy a form or report into another database and the form or report has a class module, the class module is automatically copied as well.

Note that new forms and reports do not automatically have a module associated with them. Instead, the module is only created if you decide to use VBA code in your form. This helps to reduce the size of the project and improves performance generally .

Standalone Class Modules

Class modules can also exist without an associated form or report. When created without an associated form or report, they are used to create custom objects. We will look at this use of class modules in more detail in Chapter 13.

Standard Modules

Standard modules always exist outside of forms or reports as objects in their own right, and are used for grouping together procedures that aren't associated with any one form or report in particular. These procedures can then be used by any form or report in the database. For example, you may have a standard module that contains specific functions that your company uses. These may carry out complex calculations such as wind loading in a construction company, or they may format and check the text of a site-visit report in line with your company's safety policy. These can also be included in code libraries. We discuss these in Chapter 15.

Working with Modules

The three types of module (class modules associated with Access forms or reports, standalone class modules and standard modules) are all displayed hierarchically in the Project Explorer window in the VBA IDE. In the example below, we can see how VBA groups together:

• Class modules associated with forms or reports (referred to as Microsoft Access Class Objects )

• Standard modules (referred to just as Modules )

• Standalone class modules (referred to as Class Modules )

  

In this example, Form_frmCompany is the class module associated with the frmCompany form, String Functions is a standard module, and clsDrive is a standalone class module.

The last two modules - String Functions and clsDrive - will not appear in IceCream.mdb , the database accompanying this book, but are shown here for illustrative purposes only.

To open a module from within the VBA IDE, simply select the module in the Project Explorer window, and then either double-click it or right-click it and select View Code from the popup menu. Alternatively, you could hit the View Code button on the toolbar.

If we were to select the Modules tab in the Database window in Access, we would see the standalone class module and the standard module displayed like this:

You can open one of these modules in number of ways. For example, you can select it and hit the Code button on the toolbar, or you can simply double-click it. Other ways of opening the module include right-clicking it and selecting Design View or selecting it and choosing Code from the View menu.

Note that the class module associated with the frmCompany form is not displayed in the Database window. To open that class module from within Access, you should select the form in the Database window or open it in design view and then either hit the Code button or choose Code from the View menu.

If you want to toggle between the Access window and the Visual Basic IDE, you can just hit Alt+F11.

Code Window

When we open up a module, whether it's a class module associated with a form or report, a standalone class module or a standard modules, the Code Window is displayed. As its name suggests, the code window is where we type our VBA code:

Each separate code window contains all of the code within a module, so opening a new module will open a new code window. You can view the code in a module one procedure at a time or you can decide that the procedures should be shown continuously. To toggle between full module view and single procedure view, simply click the appropriate View Selection button in the lower left corner of the code window.

You can determine whether the code window will default to full module view or single procedure view by checking or unchecking the Default to Full Module View checkbox on the Editor tab of the dialog displayed when you select Options from the Tools menu.

If you do decide to use Full Module View, you can use the Procedure Separator checkbox to determine whether or not procedure separators (thin gray lines) should be displayed in the code window to demarcate where one procedure ends and another starts.

At the top of the code window you will notice two drop-down lists, referred to as the object listbox and the procedure listbox . The object listbox is used to select the object whose code you want to look at. For standard modules, the only option listed is (General) as standard modules do not contain objects, only procedures. For class modules associated with forms or reports (also called form or report modules), the object listbox will also contain in alphabetical order a list of all of the objects (such as command buttons ) contained on the form or report.

The procedure listbox details the events associated with the specific object selected in the object listbox. For example, if we opened the form module for our frmCompany form and selected the cmdFirst button in the object listbox, the procedure listbox would display all of the event procedures that we could write for that object. If we have actually chosen to handle one of these events by entering code in the event handler, the name of the event will appear in bold in the procedure listbox:

It is also possible to display code from two different procedures, or parts of procedures, from the same module in the same window. To do this select Window Split from the main menu:

The code window then splits in two. Initially these views show exactly the same thing but you can scroll them individually or use the object and procedure listboxes to jump to the desired procedure. It is also possible to move the split up or down by clicking and dragging the split bar in the middle.

Using split windows can be very useful for cutting and pasting between procedures or perhaps viewing variable declarations alongside the code where they are used (see below).

The (General) Object

The (General) object isn't really an object at all, but it's where everything goes that relates to the module as a whole. So, in a form or report module, you could put procedures here that aren't necessarily event procedures tied to a particular object, but are procedures that are general to the form. In standard modules, all procedures go in this section. This is also the place where you declare module-level variables ( Private ) or global ( Public ) variables.

Options

You also set Options in the (General) section of a module. Every module has certain options that can be set using statements commencing with the Option keyword.

Option Explicit

This is the most widely used of the options. If it's set, it means that all variables have to be declared before being used. You will see why this is a good idea in later chapters. You can turn on Option Explicit by default in new modules by selecting Options from the Tools menu (in the VBA IDE) and checking Require Variable Declaration on the Editor page.

Option Base

This statement allows you to set the default lower limit for arrays. This is normally . Arrays are explained later on in the book, so you don't need to worry about this at the moment.

Option Compare

This is one statement that you will frequently see. It determines how VBA compares strings (text values). Normally, this is set to Database , but it can be Binary or Text instead.

When Option Compare Binary is set, VBA will use the internal binary representation of characters when comparing them in that module. This means that it will regard lower and upper case versions of the same letter as different. It also means that when VBA sorts values it will place all upper-case letters before all lower-case letters , so whereas a word beginning with a upper-case Z is placed after one starting with an uppercase Y, a word beginning with a lower-case a is placed after both of these.

Option Compare Database , which is the default setting, causes VBA to use the Access database's sort order when comparing strings in that module. The sort order of the database is determined by what the setting of the New Database Sort Order option on the General tab of the Tools/Options dialog (in Access) was when the database was created.

If the value in the New Database Sort Order drop-down box is General , then the database will be created with a sort order defined by the system locale. We can change the system locale by using the Regional Settings utility in the Control Panel. As well as defining the sort order, the system locale also affects other features such as the way that dates are formatted or how currency values are displayed. Alternatively, if we want to use a sort order different from that specified by the system locale, we can select a different sort order in the New Database Sort Order drop-down box.

Changes we make to the New Database Sort Order drop-down box are only reflected in new databases that are created after we change the setting. Note, however, that when we tell Access to compact a database, it physically creates a new database into which it compacts the old. This means that the new database that is created will have the new sort order. In fact, this is the recommended way of changing the sort order of a database. Option Compare Text uses the system locale to determine the sort order, but is always case insensitive.

Make sure you have good reasons for changing the default sort order as this affects every table in the database.

Option Private

This makes the whole module private, so that none of the code within it can be accessed from another module. This saves you having to use the Private keyword on each procedure. Again, we'll explain these concepts in more detail in later chapters, so don't worry too much about them for now.

You will not normally need to modify any of these Option settings in day-to-day use. The only one to keep an eye on is Option Explicit . We'll look at the reasons for that in Chapter 4.

Declarations

As well as setting options, the (General) section can also be used to declare external functions, variables, and constants that apply to the whole module. We'll look at this subject in more detail in the next chapter.