Your VBA Toolkit

Organize with Office 365 Course

Organize With Office 365

Get Instant Access

In This Chapter

^ Using the Visual Basic Editor ^ Understanding references and object libraries ^ Using the Object Browser

1\ s we discuss in Chapter 1, any time you want to work with Access VBA code, you need to open (or create) a module. As soon as you open one, you're taken to a program window that's separate from the Access program window. The program that opens and allows you to create or edit VBA code is the Visual Basic Editor (also called the VBA Editor).

It might seem strange that a whole separate program window opens each time you want to write or edit VBA code, but there's a reason: VBA is the programming language for all the programs in Microsoft Office. Whenever you want to create or edit VBA code in any Microsoft Office program window, you use the same Visual Basic Editor. Read through this chapter for all the buzzwords and skills needed to work in the Visual Basic Editor.

Using the Visual Basic Editor

The Visual Basic Editor — where you write, edit, and test your VBA code — contains lots of optional tools and panes. There are so many of them, in fact, that we can't even tell you exactly how the editor will look on your screen the first time you open it. However, it will likely contain at least some of the components shown in Figure 2-1.

Figure 2-1:

Some of the Visual Basic Editor optional panes.

Figure 2-1:

Some of the Visual Basic Editor optional panes.

Standard toolbar

Properties window Immediate window Code window Project Explorer

Standard toolbar

Like most program windows, the Visual Basic Editor has a title bar and menu bar at the top. Optional toolbars appear under the menu bar. You can hide or show any toolbar at any time by choosing ViewOToolbars from the menu bar. Select the check box for the toolbar you want to show; deselect the check box to hide that toolbar.

The View menu also provides options for making the various panes shown in Figure 2-1 visible. For example, if you don't see the Immediate window, choose ViewOImmediate Window from the menu bar to make it visible. To close an open pane or window inside the VBA Editor, click the Close (X) button in the upper-right corner of the pane that you want to close.

In Figure 2-1, the optional panes are docked (attached) to the VBA Editor program window. You can undock any pane and change it to a free-floating window. Just drag the item's title bar toward the center of the program window and release the mouse button. For example, Figure 2-2 shows the Project Explorer pane still docked and the Properties window undocked. The title bar for each item is also pointed out in the figure.

View Code View Object Toggle Folders

Figure 2-2:

Examples of docked and undocked panes.

Docked Title bars Undocked

If you undock a pane, you can generally re-dock it by dragging it back to any edge of the VBA Editor program window. If the pane refuses to dock, try right-clicking within the pane and choosing Dockable from the contextual menu that appears. Then drag the pane to an edge or border if it doesn't dock right on the spot.

You can size any pane (or free-floating window) by dragging any edge of the item. For example, when both the Project Explorer and Properties panes are docked, you can widen or narrow them both by dragging the right edge of one of those panes. Drag the bottom edge of a pane to make it taller or shorter.

Whether you really need all the panes open depends on what you're doing at the moment within the VBA Editor. You'll probably spend the vast majority of your time in the Code window. Before we discuss that window, take a quick look at the optional Project Explorer and Properties windows.

Using Project Explorer

Project Explorer provides a list of all the modules contained in the current database (which is whatever database happens to be open in Access at the moment). The Toggle Folders button on the Project Explorer toolbar determines

Figure 2-2:

Examples of docked and undocked panes.

Docked Title bars Undocked how the module names are displayed. When the Toggle Folders button is turned on, module names are shown in these three separate folders:

^ Microsoft Office Access Class Objects: Lists the names of all form and report class modules in the current database. The name of the class module is the same as the form or report name, preceded by Form_ or Report_.

^ Modules: Lists the names of all standard modules in the current database.

^ Class Modules: Lists the names of class modules that appear in the Navigation pane of the current database.

If a folder has a plus sign (+) next to its name, you can click that + to view objects within the folder. Conversely, clicking the minus sign (-) next to either folder name collapses the folder and hides its contents.

To open a module in the VBA Editor, just double-click its name in Project Explorer. Each module that you double-click opens within its own Code window (described a little later, in the section "Using the Code window").

For form and report class modules, Project Explorer also provides quick access to the form or report to which the module is attached. Just right-click any class module name and choose View Object. The form or report opens in Design view in Access. The VBA Editor might then be covered by the Access window. However, the editor is still open, so you can get back to it by clicking its taskbar button.

The buttons to the left of the Toggle Folders button — View Code and View Object — also provide a means of switching between a class module and the object to which it's attached. Press Alt+F11 to switch back and forth between the Access and VBA Editor program windows.

Using the Properties window

The Properties window in the VBA Editor can be quite perplexing because it displays the properties of whatever object is selected in Access. If nothing is selected in Access, the Properties window might show nothing. That's often the case when you're working with standard modules because standard modules aren't tied to any particular object or event.

To illustrate how things tie together, Figure 2-3 shows a portion of a form, in Design view, in Access. A subform on the form is selected. In the VBA Editor window, which also appears in Figure 2-3, the properties for that selected subform appear in the VBA Editor Properties window.

In that same figure, you see an example of how Project Explorer might look in a database that already contains some modules. The modules whose names begin with the word Form_ are all class modules that are attached to forms in that database.

Perhaps the most important thing to remember about Project Explorer and the Properties window is that they're optional, and you really don't need them taking up space in your VBA Editor when you're not using them. Most of the time, you probably won't use them, so feel free to close those panes and forget about them if they just get in the way and confuse matters for you.

VBA Project Explorer lists all modules in the current database

Figure 2-3:

A sample Properties window and Project Explorer.

VBA Project Explorer lists all modules in the current database

Figure 2-3:

A sample Properties window and Project Explorer.

VBA Properties window shows properties of selected Access object

Selected object on Access form

Using the Immediate window

The Immediate window in the Visual Basic Editor allows you to run code at any time, right on the spot. This window is sometimes referred to as the debug window because it's mainly used for testing and debugging (removing errors from) code. If the Immediate window isn't open in the Visual Basic Editor, you can bring it out of hiding at any time by choosing ViewO Immediate Window from the editor's menu bar.

When the Immediate window is open, you can anchor it to the bottom of the Visual Basic Editor by dragging its title bar to the bottom of the window. You can optionally make the Immediate window free-floating by dragging its title bar up and away from the bottom of the Visual Basic Editor's program window. You can also dock and undock the Immediate window by right-clicking within the Immediate window and choosing Dockable.

The Immediate window allows you to test expressions, run VBA procedures you created, and more. You see practical examples throughout this book. To get your feet wet, test this simple expression in the Immediate window. Just bear in mind that an Access expression is any formula. For example, the simplest expression in the world is probably 1 + 1, which (as just about everyone knows) results in 2.

To test an expression in the Immediate window, do the following:

1. Click inside the Immediate window.

You need your cursor in that pane.

2. Type a question mark (?) followed by a space and the expression you want to test; then press Enter.

For example, click in the Immediate window and then type ? 1+1.

The Immediate window immediately shows you the result — 2 — as shown in Figure 2-4.

Figure 2-4:

Testing a simple expression in the Immediate window.

Figure 2-4:

Testing a simple expression in the Immediate window.

You might think of the ? character at the start of the line as asking the Immediate window "What is?" For example, if you think of ? 1 + 1 as meaning "What is one plus one?", it stands to reason that the Immediate window would return 2. After all, 1 + 1 is 2!

When you start actually writing VBA code, you'll use the Immediate window to test and debug your code. For now, just know that the Immediate window is another optional pane in the Visual Basic Editor that you can show and hide as needed.

Using the Code window

The VBA Editor's Code window is where you write, edit, and view VBA code. The Code window is similar to a word processor or text editor in that it supports all the standard Windows text-editing techniques. For example, you can type text and use the Backspace or Delete keys to delete text. And just like in Word, you can press the Tab key to indent text, select text by dragging the mouse pointer through it, and copy and paste text (to and from the Code window). In short, the Code window is a text editor.

Like all panes in the Visual Basic Editor, the Code window can be docked or undocked. Choosing one view or the other is just a matter of personal preference and doesn't affect how you write and edit VBA code. You can easily switch between docked and undocked views.

When the Code window is undocked, it has its own title bar and can be moved and sized independently. To dock an undocked Code window, click the Code window's Maximize button, as shown in Figure 2-5.

When the Code window is docked, it fills the available space in the VBA Editor window, and its Minimize, Restore, and Close buttons appear near the upper-right corner of the VBA Editor's program window. Clicking the Code window's Restore Window button (also shown in Figure 2-5) undocks the Code window and allows it to float freely.

As we mention earlier, the Code window is really a small word processor or text editor. But word processors tend to be oriented around paragraphs of text, whereas the Code window is built for typing individual lines of code. Unlike a word processor — where you don't press Enter until you get to the end of a paragraph — in the Code window, you press Enter at the end of each line you type.


-Ins Window


Type d question for help

J- "'J 1 #

Ln 4, Col 1


I VBA Dummies - FormNumWord Demo Form (Code) QË3Q |

! ClickHereButton

I VBA Dummies - FormNumWord Demo Form (Code) QË3Q |

! ClickHereButton

Option Compare Database

Private Sub CiickHereButtonJ^icUCtJ. Ile . COnvertedMuniber . Visible = True He . COnvertedMuniber . Value = NuniUord [Wunibe End Sub

-Undocked Code window

Figure 2-5:

The Code window Restore Window and Maximize buttons.

Word Demo Form (Code)]

Add-Ins Window Help



- Restore Window

Qption Compare Database

Private-.-Sub ClicfcHereButton_Click [ )

He. ConvertedNuitiber-. Visible = True He. ConvertedNuitiber-. VSiue = WÙKiWbrd [Number) End Sub

— Docked Code window

When you type a line of VBA code and press Enter, the Visual Basic Editor compiles that line of code. For now, you can think of compiling as testing the line of code to see whether it will work. If you just type some line at random in the Code window — or even if you try to type a legitimate line of VBA code but make a mistake — you see a compile error message, as shown in Figure 2-6.

Figure 2-6:

A compile error in the Code window.

Figure 2-6:

A compile error in the Code window.

We talk about ways of dealing with compile errors when we really get into telling you how to write code in Chapter 3. For now, just realize that if you type anything other than a valid line of VBA code into the Code window, you see a compile error message as soon as you press Enter. So don't waste your time trying to type text at random into the Code window.

Referring to Objects from VBA

VBA is able to control objects in Access (and other programs in Microsoft Office) because of Automation (with a capital A) technology. The idea behind Automation is this: A program, database, document, or special capability exposes (makes available) its objects through an object library. The object library contains an organized set of names that VBA can refer to when it wants to manipulate an object.

Think of an object library as sort of a steering wheel that's sticking out of some database or some program. When the steering wheel isn't available, VBA can't manipulate objects in the program. However, when the steering wheel is exposed, VBA can manipulate objects inside that program. As we discuss in the following section, you control which steering wheels are available by setting references to object libraries.

Figure 2-7 shows a hypothetical example where the Access and Excel object models (steering wheels) are exposed. VBA can therefore manipulate objects in those programs. In the figure, Word and PowerPoint aren't exposing their objects, so VBA can't manipulate objects in those programs.

Object libraries

Figure 2-7:

Object libraries expose objects to VBA.

Object libraries

Figure 2-7:

Object libraries expose objects to VBA.

Microsoft Word

Microsoft Word

Not all object libraries expose objects in specific Office programs. Some object libraries expose programs; some object libraries expose documents; still others expose technologies that simply help you bridge the gaps between programs. Access, by itself, offers several object models. The important point is, though, that before you start writing VBA code, you need to know what object libraries are available to you.

Setting References to Object Libraries

To manipulate the objects in an object model through VBA, you need to set a reference to the appropriate object library. That part is easy because you just have to put a check mark next to the appropriate object library's name in the References dialog box. To open the References dialog box and choose your object libraries, follow these steps (in the Visual Basic Editor program window):

1. Choose ToolsOReferences from the Visual Basic Editor menu bar.

The References dialog box, as shown in Figure 2-8, opens.

Figure 2-8:

Set object library references here.

References - Northwind 2007

Available References:

0 Visual Basic For Applications 0 Microsoft Access 2007 Object Library 0 OLE Automation

0 Microsoft Office 2007 Access database engine Objed 0 Microsoft Office 12.0 Object Library 0 Microsoft ActiveX Data Objects 2.3 Library ] +

□ IAS Heiper COM Component 1,0 Type Library

□ IAS RADIUS Protocol 1.0 Type Library Priority D Acrobat Access 3.0 Type Library

□ AcroIEHelper 1,0 Type Library +

□ Active D5 115 Extension Dll

□ Active D5 115 Namespace Provider

□ Active D5 Type Library ,— F1 Active Setuo Control Library ll<l ■■ I_

Microsoft ActiveX Data Objects 2.8 Library

Location: C: \Program FilesKommon Files\System\ado\msado 15.dll Language: Standard

2. To set a reference to an object library, select its check box.

Some object libraries are already selected (checked), as shown in Figure 2-8. The selected object libraries shown in Figure 2-8 are typical and are a good starting point for any Access VBA programming.

3. When all the object libraries you need are selected, click OK to close the dialog box.

Setting references to object libraries exposes objects to VBA immediately, but it doesn't expose anything to you — at least, not in a way that's readily apparent on-screen. To find out what objects are available to VBA (and you) at the moment — and get help with them all — you need to use the Object Browser.

Using the Object Browser

Every object library provides VBA with a very large set of names that represent objects that VBA can manipulate — so many names that we doubt anybody would even attempt to remember them all. To make it easy to find names of things on an as-needed basis, VBA provides the Object Browser tool.

^H-Sr^ In this context, browser has nothing to do with the Internet or the World Wide Web. Rather, the Object Browser is a tool for browsing the contents of all avail-W ) able object libraries. And those object libraries have no connection to the Internet.

While you're in the Visual Basic Editor, you can do any of the following to open the Object Browser:

^ Choose ViewOObject Browser from the Visual Basic Editor menu bar. ^ Press F2.

^ Click the Object Browser button on the VBA Editor's Standard toolbar.

When the Object Browser opens, it doesn't look like it's any big help, but there will be plenty of times when you need to use it. Now is a good time to become familiar with how you work that darn thing. Figure 2-9 points out the names of various tools within the Object Browser.

Figure 2-9:

The Object Browser.

Project/Library list

Search box

Q Object Browser

(All Libraries)


<globals> ES: AccessObject

AccessObjectProper ££¡1 AccessObjectProper AcCloseSave AcColorlndex ip AcCommand t=p AcControlType ip AcCurrentView dp AcCursorOnHover =? AcDataObjectType ip AcDataTransferType ip AcDefReportView ip AcDefView ip AcDisplayAs a

Members of AccessObject' ni1 CurrentView li? DateCreated eS" DateModified

FullName & GeiDependencylnfo IsDependentUpon nl? IsLoaded Ei1 Name EÜ1 Parent El? Properties EÜ1 Type

Class AccessObject

Member of Access

Classes list

Split bars Members list

Details pane

Here's a brief description of each tool:

1 Project/Library list: From here, you choose either a single object library to browse or <All Libraries> (where All Libraries means all object libraries that are selected in the References dialog box).

1 Search box: Here you type or choose a name to search for.

1 Classes list: This list shows the names of all classes in the selected object library or all available libraries if <All Libraries> is selected in the Project/Library list. A class is any class or group of objects, such as AllForms (all the forms in the current database).

i Members list: When you click a name in the Classes list, this pane shows the members (objects, properties, methods, events, functions, and objects) that belong to that class.

i Details pane: When you click a member name in the Members list, the Details pane shows the syntax (rules) for using the item that's selected in the Members list, as well as the name of the library to which the member belongs.

1 Split bars: Drag the split bars left or right (or up and down) to adjust the size of the panes. (Drag any edge or corner of the Object Browser window to size the window as a whole.)

Clicking the Project/Library drop-down list displays the names of all currently loaded object libraries (all the object libraries to which you've set a reference in the References dialog box; refer to Figure 2-8). This list describes the object libraries:

1 Access: The Microsoft Access 2007 Object Library lets you control the Access program programmatically.

1 ADODB: The Microsoft ActiveX Data Objects 2.8 Library allows you to access all data in your database as well as data from outside databases.

1 DAO: The Microsoft Office 2007 Access database engine Object Library is the primary method for working with the Microsoft Jet database engine from code.

1 Office: The Microsoft Office 12.0 Object Library lets you control aspects of Access that are common across all Microsoft Office programs.

1 stdole: The OLE Automation object library (where stdole is short for standard OLE) provides programmable access to objects that use object-linking and -embedding technologies, such as pictures in tables.

1 VBA: The Visual Basic for Applications object library contains programmable access to objects built into the VBA programming language, such as functions for doing math with dates, times, and dollar amounts.

In addition to listing the names of object libraries selected in the References dialog box, the Project/Library list offers the name of the database you're working in. Consider the name of the current database to be the project on the Project/Library drop-down list. You don't need to set a reference to that object library because it's built into the database that's open in Access.

Searching the Object Library

The real beauty of the Object Browser lies in its ability to help you find information about an object as you need it. Because you probably don't know what library an object resides in, choose <All Libraries> from the Project/Library drop-down list before you begin a search. Then you need to know what name you're searching for.

For example, as you discover a little later in this book, Access offers a DoCmd (do command) object that lets VBA perform a variety of actions, from opening forms to setting the values of controls. Suppose you're writing some code and need some quick information about that object. You could get that information by following these steps to search the Object Browser:

1. In the Search box in the Object Browser, type the word you're searching for.

For example, to search for information on the DoCmd object, type DoCmd as the word to search for.

2. Click the Search button (the binoculars) next to the Search box.

The results of your search appear in the Search Results pane under the Search box.

3. To get help with an item in the Search Results pane, click a name there and then click the Help button (the question mark) on the Object Browser toolbar.

The Help text appears in a separate Help window, as shown in the example in Figure 2-10.

Admittedly, the Help text is technical documentation, written more for programmers than for VBA beginners. But you won't be a beginner for long, and knowing how to search the Object Browser will soon become a valuable skill.

As with other tools that we describe in this chapter, you can close the Object Browser (as well as any open Help window) at any time by clicking its Close (X) button.

Help button

Figure 2-10:

Search the Object Browser for help.

Help button

Figure 2-10:

Search the Object Browser for help.

Search Results pane Help window

Search Results pane Help window ft

We suppose right about now that you're wondering how any of the tools in this chapter will make your life easier. We're working up to that. For now, just being aware of the various panes and windows in the Visual Basic Editor is a good start. Knowing that VBA works by manipulating objects in object libraries is a good thing too. Even just being aware that the Object Browser and Help windows exist is valuable as you start writing code.

Writing code is the actual programming part of VBA. You write VBA code to automate activities, and you automate activities by manipulating objects via object libraries. VBA has a lot of fancy buzzwords, but if you just think of object libraries as steering wheels that VBA can grab onto and steer, you're ahead of the game. Hop to Chapter 3 to start writing code.

Chapter 3

Was this article helpful?

0 0

Post a comment