The Fundamentals Roadmap

Before you start building your solution, you need a general plan of what you want to accomplish, one that takes into consideration where to start, what tools are required, and what your customer scenarios are. Once you know the job you need to accomplish, the following Fundamentals Roadmap diagram, the description of this book's six parts, and a list of sample scenarios should help you find the information you need to build your custom programs.

This book has six parts, described below. Depending on what you need to learn, you can begin with any one of them.

This part includes a description of the Visual Basic Editor and explains how to use it to write code. It will familiarize you with the Visual Basic for Applications language; teach you about objects, methods, properties, and events; and show you how to create custom dialog boxes and wizards. It will also introduce you to the concepts of Visual Basic in Office applications and show you how to use the Visual Basic for Applications programming language and development environment to write code.

This part describes how to use code to create new documents; to open, save, print and close them; and to find where they are on a file system. It also tells you how to write code to trap events when a document is created, opened, saved, printed, or closed. Finally, it discusses how to insert and manipulate content in a document once it's loaded into an Office application.

Scenarios

• Find files to open and folders where you can save files.
• Create content invisibly and then display it to the user on-screen.
• Batch print from selected files in a folder.



• Set and retrieve document properties, like Author and Title, as well as determine if a specific document property is set before a document is saved, printed, or closed.
• Add header and footer information to documents that are opened, closed, or saved to a specific file location.
• Insert content—like text, tables, shapes—into a document and manipulate it.
• Insert or remove a sheet in a workbook or a slide in a presentation.
• Find and replace text.

If you need to retrieve content from one source for use in creating a new Office document, this part describes how you can do that. Your code can start any Office application and create documents based on content from another data source. For example, you can create a report in Word along with a supporting presentation in PowerPoint, which is based on data retrieved from a Microsoft Access database or any other database.

Scenarios

• Use Visual Basic code to start an application or access an instance of an application already running.
• Use an Office application invisibly to create documents and content.
• Exchange information between Office applications.
• Create a custom form in Access that provides options to create a Word report, an Excel worksheet for data analysis, a PowerPoint presentation for meetings, and to send documents attached to an e-mail message sent using Microsoft Outlook.

When you select a word in a document and click a toolbar button to apply formatting, the Office application determines the selection's content type and applies the appropriate formatting. This part describes how to determine the type of content in a selection and apply formatting based on the selection. It also tells you how to update menus and toolbars based on the content type and how to find content on the user's screen.

Scenarios

• Retrieve content or the active selection and format it.
• Trap double-click or right-click events and display your own dialog box or shortcut menu.
• Position a dialog box specifically around a text selection.
• Navigate to a specific content item like a slide in a presentation, a sheet in a workbook, and a paragraph in a document, or find specific text.

This part describes how to create custom menus and toolbars. You'll learn how to use the CommandBars object model to mimic the state and behavior of built-in controls such as the Bold, Zoom, and Ruler commands. You'll also learn how to create custom balloons for the Office Assistant. You can use a custom balloon to display a list of choices, gather information from a user, or display a message to a user.

Scenarios

• Click a toolbar button to display a dialog box and format the current selection.
• Create a zoom drop-down list or a menu drop-down list.
• Create a top-level menu or a submenu like the Macro submenu on the Tools menu.
• Create a depressed button (like the Bold button) or add a check mark beside a menu item like the Ruler menu item on the View menu in Word and PowerPoint.
• Change an icon or caption of a button to toggle the state of the button.
• Display another toolbar when a button is clicked—for example, click the Drawing button on the Standard toolbar in Word and Excel and have it display the Drawing toolbar.
• Based on the visible state of the Office Assistant, determine when to display information to the user through the Office Assistant or a standard message box.

The last section has two chapters on the COM add-in model, which is introduced in Office 2000 as Office's first consistent add-in model. This model allows you to create a single add-in file targeted for more than one Office application. It also gives you the ability to create add-ins in any Microsoft development environment (although only the use of Microsoft Visual Basic 6.0 is discussed). Chapter 13 provides detailed instructions on how to create a COM add-in as well as a pointer to finding the COM add-in template for getting started in minutes. The last chapter describes the COM add-in model in-depth.

Scenarios

• Find a menu or toolbar control when an add-in is loaded and add commands as necessary.
• Remove a control when an add-in unloads.
• Load an add-in when the Office application starts.

When writing Visual Basic code for a business solution that incorporates Office, you can store your code in one of three general types of Office projects: a document, a template, or an add-in or wizard. How do you know which one to use? The first thing to decide is what type of solution you want to deliver, based on the projects' characteristics.

Document is a collective term used to represent all types of Office documents: Word documents, Excel workbooks, PowerPoint presentations, and Access databases. The Visual Basic code you write in the Visual Basic for Applications project of a document commonly provides customizations specific to the document's contents.

Code in a document project is stored within the same file as all other content of the document. Thus, when the document file location changes, the project moves with the file. In an example in Chapter 8, the code that drives the automatic generation of the Office documents is added to an Access database. In this case, you create a document project since the code is stored in the Access database and should move with the database if its file location changes.

Template is a collective term used for all types of Office templates, including Word, Excel, and PowerPoint templates. Writing code in a template is essentially the same as writing code in a document project because the code provides customizations to a document. The difference is that when you apply a template in Excel or PowerPoint, the code is copied into the workbook or the presentation. In Word, when you attach a template to a document, you make the code, along with other Word-specific content such as text styles and AutoText, readily available to the Word document.

Add-ins are tools that you can create to customize and extend the functionality of Word, Excel, PowerPoint, or Access themselves. An add-in is a supplemental program that adds custom commands and specialized features to any Office application. For example, you can write an add-in program for Word, Excel, and PowerPoint that displays a list of contacts retrieved from your Outlook Contacts folder. You can create an add-in in the form of a wizard that steps a user through a series of tasks. Most add-ins are accessed through a menu command or toolbar button. Chapter 3 shows you how to create a custom wizard. Chapters 13 and 14 describe how to develop add-ins for any Office application.