Encapsulating the process

You can use the Win32 API calls in this example any way you want and in any order you want. The ability to perform tasks any way you see fit makes the Win32 API both flexible and dangerous. Because you'll likely perform some tasks more than once, it pays to encapsulate the process you use into an easy-to-manage function. Listing 16-3 shows the code used for this example. It encapsulates the process of calling an external program and waiting for it to finish. The return value tells the caller...

Listing continued

Output Output + No Records Available GoTo SkipFields ' Check each field definition in the recordset. For Each CurField In CurRec.Fields ' Get the field name, type, and current value. Output Output + Name + CurField.Name + _ vbCrLf + vbTab + Type + _ CvtType(CurField.Type) + vbCrLf + vbTab + _ Value ' Some values are null. If IsNull(CurField.Value) Then Output Output + Null + vbCrLf Else Output Output + CStr(CurField.Value) + vbCrLf End If Next MsgBox Output, vblnformation, CurrentDB Information...

Authors Acknowledgments

Thanks to my wife, Rebecca, for working with me to get this book completed. I really don't know what I would have done without her help in researching and compiling some of the information that appears in this book. She also did a fine job of proofreading my rough draft. Russ Mullen deserves thanks for his technical edit of this book. He greatly added to the accuracy and depth of the material that you see here. I really appreciate the time he devoted to checking my code for accuracy. I also...

Reco Vering from an error

Recovering from an error is important. Adding recovery code means that you can count on your program to help you overcome problems with the system and keep your data safe. You can add two kinds of recovery code to your program t Code that recovers before the error happens t Code that recovers after the error happens It's always better to detect an error and recover from it before the error actually happens. You can always recover from a resource error, such as lack of hard drive space, before...

Other Products Use VBA

Don't assume that VBA is good only if you're using Microsoft Office or a few other Microsoft products. With VBA at your command, you can control a lot of different applications. Go to the Microsoft site http msdn.microsoft. com vba companies company.asp to see a list of companies that have licensed VBA. You'll be amazed at the number of applications that you can work with using VBA. Here are a few of my favorites 1 Corel products (http www.corel.com ) Corel makes WordPerfect and Draw....

The accompanying Web site

This book contains a lot of code, and you might not want to type it. Fortunately, you can find the source code for this book on the Dummies.com Web site at http www.dummies.com go vbafd5e. The source code is organized by chapter, and I always tell you about the example files in the text. The best way to work with a chapter is to download all the source code for it at one time. The Web site also has three Bonus Chapters one on FrontPage, a second on Visio, and a third on ten cool things that you...

Using the Label control to display text

You use the Label control to display text onscreen. A Label is for informational purposes. Although you can copy the content of a Label, you can't change it directly. The most common use for labels is to identify other controls and to provide accelerator key access to them. (See the upcoming Modifying the form and control properties section of this chapter for details on adding an accelerator key.) Labels also act as output for read-only text. Figure 7-3 shows a simple form with two labels. The...

Working with the DBEngine object

The DBEngine object is useful for performing several tasks. You can use it to access the Workspaces collection, as shown in the earlier section Defining your work area with the Workspaces collection. This section presents the most useful features of the DBEngine object. See the help topic at http HV05187151.asp for a complete reference. Vista strictly controls access to just about everything related to Access. You'll very likely run into problems with the User Account Control (UAC) feature of...

Using the Watches Window

The Watches window works similarly to the Locals window, but has a different purpose. The Locals window shows variables in their raw format and only the variables that you can see locally. You might want to see other variables, or you can use a function to change a variable before viewing it. The Watches window helps you perform these kinds of tasks, but it requires a little more work to use. You can display the Watches window by clicking the Watch Window button on the toolbar. Figure 6-6 shows...

Creating the Win API calls

The best way to work with the Win32 API in VBA is one step at a time. Define a call, the structures and enumerations to go with that call, and any required cleanup before you move on to the next call. Make sure you understand the sequence of steps you must perform to accomplish the task. Always create Win32 API calls in a separate module. Because the Win32 API calls are so flexible, you should concentrate on making them modular so that you can use them with other applications. In addition,...

Writing your own errorhandling code

Adding an error handler to your program is a two-part process. First, you must tell VBA that you've included an error handler. Otherwise, VBA uses the default error handler, even when error-handler code appears in the program. Second, you must provide the error-handling code. Listing 6-2 shows an example of a Sub that uses error handling. (You can find the source code for this example on the Dummies.com site at http www.dummies.com go vbafd5e.)

Using the SQL query

After you create a SQL query, you can use it in a program to get information from the database. Listing 15-8 shows a short example that uses the test query to get a list of words with acronyms that begin with the letter C. (You can find the source code for this example on the Dummies.com site at SQL isn't something that you can understand in a few short lessons. You don't need to spend a lot of time learning SQL to create simple commands, such as retrieving information from a single table, but...

Batteries Included VBA Comes with Office

N Iflf A good many people have written to ask me whether VBA really does come with Office. The answer is yes. All Office products support VBA, and you can use VBA to perform a wealth of tasks, many of which will seem impossible now. Older versions of Office provide a convenient method for accessing the VBA editor. Simply use the ToolsOMacroOVisual Basic Editor command to display the VBA editor where you type your VBA commands and store them for later use. One of the reasons for this section is...

Modifying the toolbar or menu content

Toolbars and menus have a lot of properties that you can change. Anything that you can change with the Customize feature of the Office application is also available from code. For example, you might want to provide better tooltips for your program or change the wording of the prompt. Here's an example of code that modifies a ToolTip associated with the B (bold) button on the Formatting toolbar. (You can find the source code for this example on the Dummies.com site at http www.dummies.com go...

Defining the parts of a program

A program is the highest level of physical structure. It contains everything needed to perform a given task. A program can cross module, class module, and form boundaries. (Modules, class modules, and forms are special containers for holding program code. You can save them as individual files for access later, but Office embeds them within an application document or template for use.) The concept of a program comes from the earliest use of computers. A program acts as a container for the code...

Table of Contents

About This Conventions Used in This What You Should What You Don't Have to Foolish How This Book Is Part I An Overview of Part II Learning the Part III Expanding Your VBA Part IV Programming for Part V The Part of The accompanying Web Icons Used in This Where to Go from Part I An Overview of Chapter 1 Getting to Know Batteries Included VBA Comes with VBA It's Not Just for Automating Customizing an application's Performing Getting stuff from a Adding new application Making special Having things...

Defining methods

IBEfl Methods help you interact with an object by defining the form of interaction. The example in this chapter has only one method, Show. After you configure a message box for use, you want to show it onscreen, so the name is appropriate. The UserForm control also uses this name for the same purpose. A method can rely on a Public Sub if it doesn't return a value, or a Public Function if it does. You call a method by using the same technique that you would with a Sub or Function. The difference...

Listing Adding a Comment by Using an Attribute

Public Function Pythagoras(Side1 As Double, _ Side2 As Double) As Double Attribute Pythagoras.VB_Description Returns the value of the hypotenuse. Pythagoras Math.Sqr((Side1 * Sidel) + (Side2 * Side2)) End Function Notice the way that the description appears in the file. You must type the keyword Attribute followed by the name of the function and the keyword VB_Description. Type the text that you want to appear as a description for the function in the Insert Function dialog box. Save the file....

Setting macro security for One Note Publisher Visio Project and all older versions of Office

Depending on which version of Microsoft Office you use and how you set it up at the beginning, the macro security feature might be set too high to allow you to use the examples in this book. To change the macro security level, use the following procedure. 1. Choose the ToolsOOptions command. The Microsoft Office application displays the Options dialog box. The Microsoft Office application displays the Security dialog box. 4. Select the Security Level tab and choose the Low option. 5. Click OK...

Designing a Basic Class

Because a class is a blueprint for an object, you need to have some idea of what you want to build. The best classes are ones that answer specific needs that you have. A class requires a specific look and feel. VBA includes this look and feel as part of the objects that it creates from the class. It helps to look at existing classes when you think about your own class, to see what other people have done. All the controls provided with VBA make a good starting point because you can play with...

List servers that you access through email and Web sites

List servers have several advantages over newsgroups. For one thing, the noise level (unwanted postings) is much lower. These groups are very focused, which means that you normally get good information. A moderator keeps discussions on track, so there's less chance of seeing a discussion entitled Great New VBA Trick that's actually about Uncle Al's birthday party. In many cases, you subscribe by sending the list owner or another special e-mail address an e-mail with the word Subscribe as the...

Deciding how to perform the task

Before you begin working with the Win32 API, you must know which call to use. Accessing an external program is called creating a process. Consequently, you must use the CreateProcess() call. However, you must know more. The CreateProcess() call comes in two forms one for ASCII text named CreateProcessA() and another for Unicode text named CreateProcessW(). In general, you'll find that using ASCII text is much easier in VBA, so this section uses the CreateProcessA() call. When you create a...

Free electronic newsletters

Free newsletters often contain short articles, tips, links, and the occasional coding example. (You might see them listed as e-newsletters or eNewsletters.) They're a good source of continuous information for building your VBA knowledge base. Here are a few of the better examples i Office Watch http office-watch.com i Eli Journals i The Office Experts http www.theofficeexperts.com newsletter.htm i Microsoft Office Tips http www.worldstart.com msofficetips.htm 1 DevSource http www.devsource.com...

Listing Working with Flagged Outlook Messages

' Create the Outlook application reference. Dim OutlookApp As Outlook.Application Set OutlookApp ' Create the MAPI namespace reference. Dim MAPI_NS As Outlook.NameSpace Set MAPI_NS ' Create a reference to the Inbox. Dim Inbox As Outlook.MAPIFolder Set Inbox ' Holds the current message. Dim CurMsg As Outlook.MailItem ' Look at all of the messages. For Each CurMsg In Inbox.Items ' Determine whether this message requires processing. If CurMsg.FlagStatus olFlagMarked And _ CurMsg.FlagIcon...

Starting and Stopping External Applications

VBA may not seem particularly powerful when it comes to performing some tasks, such as interacting with external programs, and the truth is that you do need to perform more work than normal to accomplish some tasks. However, VBA offers access to the entire Win32 Application Programming Interface (API) as long as you're willing to create the required prototypes (descriptions of the information required for the call you want). Consequently, anything you see listed on the Microsoft MSDN Web site...

Accessing any Registry locations in Office

ji& Wfa Unfortunately, SaveSetting and GetSetting functions don't provide access to the rest of the Registry. In addition, these functions affect only the current user. The example in the upcoming section Selecting Objects in a Word Document shows why this is a problem. You need access to the entire Registry to write functional programs. Consequently, you have to augment the functionality that VBA provides by accessing the Windows 32-bit Application Programming Interface (Win32 API)...

Adding and removing toolbars and menus

jj& Wfa You might want to add custom toolbars and menus to hold entries for your programs. Using custom setups means that you can show or hide custom programs as needed. This technique also keeps your code separate from the entries that Microsoft has made to ensure that there's little chance of compatibility problems. Listing 12-2 shows one technique for adding a new toolbar. (You can find the source code for this example on the Dummies.com site at http www.dummies.com go vbafd5e.)

Learning the Ropes

Listing 4-17 Designing an Excel Report ' Create some output variables. Dim DataSum As Integer Dim Output As String ' Begin by adding data to the report. Sheet1.Cells(3, 2) 1 Sheet1.Cells(4, 2) 2 Sheet1.Cells(5, 2) 3 ' Create a sum of the cell content. DataSum Sheet1.Cells(3, 2) + _ Sheet1.Cells(4, 2) + _ Sheet1.Cells(5, 2) Output The sum of the three numbers is CStr(DataSum) The code begins by declaring two variables. The first, DataSum, holds the sum of the three data entries that the code...

Microsoftspecific newsgroups

Microsoft provides a number of VBA newsgroups. In fact, you can probably find in this one place everything that you need in order to talk with other VBA users. Most ISPs carry the Microsoft newsgroups. However, you can also access the Microsoft newsgroups directly by using its server at news.microsoft.com. (If you want to use your browser, you type news for example however, it's usually easier to find the newsgroup in your newsgroup reader.) Here are some VBA newsgroups that you can visit for...

Storing and Modifying Information

Using variables in programs Using constants in programs Working with various data types Changing data by using operators Designing a report for Excel n this chapter, I refine the concept of a variable by describing variable types and how you can modify their content. Understanding how a computer stores information is very important. Computers don't see information the same way that you do many methods of representing information that seem obvious to you are invisible to the computer. As far as...

Creating a Simple Word XML Document

Word automates the process of linking an XSD file to your document by adding this feature through the document templates. When you create a document by using a properly designed template, the XML tags are automatically added according to the XSD schema that you create. You might want to create searchable letters. One way to do this is to export the letters as XML and to use a standard search engine. Start by creating a letter template file. The example includes the usual features, such as To...

Interacting with the User

Using forms to interact with the user Creating forms by using controls Designing forms for specific Microsoft Office applications BA utility programs can usually perform their jobs without much input. However, a VBA program that helps you perform a task usually needs some type of input. A form provides fields where you can enter or read additional information, objects (such as labels) that display information, and controls (such as command buttons) that help you interact with the form. You...

Defining the project

You may never need to define more than one project for a program. This is especially true of Access and Excel projects, in which everything needed to hold the data appears in one file in most cases. You can also place everything that you need into a single Word template if this is the only template that you need and the program is appropriate for use with more than one document. However, think about Word for a moment, and you discover something about projects. Figure 3-9 shows a typical Project...

Applying What You Know to Design an Excel Report

All the examples up to this point in the book have worked with abstract (or demonstration) code. Abstract code is practical for presenting concepts, but you wouldn't actually add it to a document because it doesn't perform useful work. The reason that you use VBA is to accomplish useful work. The example in this section relies on code to create an Excel report. You begin by typing the entries shown in Figure 4-5. These entries serve as a basis for the report. The report contains data entries....

Listing Defining a Custom Error Handler

' The variable that receives the input. Dim InNumber As Byte ' Tell VBA about the error handler. On Error GoTo MyHandler InNumber InputBox(Type a number between 1 and + _ 10., Numeric Input, 1) ' Determine whether the input is correct. If (InNumber < 1) Or (InNumber > 10) Then ' If the input is incorrect, then raise an error. Err.Raise vbObjectError + 1, _ Incorrect Numeric Input. The number + must be between 1 and 10. MsgBox The Number You Typed + CStr(InNumber), vbOKOnly Or vbInformation,...

Using the Charts collection

One of the most useful purposes of the Charts collection is building a custom chart whenever you need one. The advantage of creating uncommon charts by using code is that they take up less space. In addition, you can create variations on a theme without a lot of work. Listing 14-5 shows an example of the Charts collection in action. (You can find the source code for this example on the Dummies.com site at http www.dummies.com go vbafd5e.)

Defining properties

A property describes a feature of the object, such as its color, the caption, a method of presentation (such as sunken or etched), or some other characteristic. Don't assume that a property has to describe a physical characteristic of the object. A filename is a perfectly acceptable property. A property is a special kind of object-specific data. VBA provides three kinds of construction methods for properties. You can choose to use one or all three methods. A property requires at least one of...

Listing Using the Debug Object

' The variable that receives the input. Dim InNumber As Byte InNumber InputBox(Type a number between 1 and + _ 10., Numeric Input, 1) ' Print the value of InNumber to the Immediate window. Debug.Print InNumber + CStr(InNumber) ' Stop program execution if InNumber is not in the ' correct range. MsgBox The Number You Typed + Notice how this sample uses the Debug.Print and the Debug.Assert methods in combination. The Debug.Print method outputs the current values to the Immediate window, and the...

Listing Changing Datasheets with the For Next Statement

Dim ActiveRows As Integer ' Number of active rows. Dim Counter As Integer ' Current row in process. ' Select the first data cell in the worksheet. Range(A5).Select ' Use SendKeys to select all of the cells in the ' column. SendKeys +A DOWN , True ' Get the number of rows to process. ActiveRows ' Reset the cell pointer. Range(C5).Select ' Keep processing the cells until complete. For Counter 5 To ActiveRows + 5 ' Call the Sub created to change a single cell. MakeChoice3 ' Move to the next cell....

Creating an application plan

In Chapter 2, I describe how to use pseudo-code to write a program. The program in that chapter uses only one module because it's quite simple. Not every program that you create is that simple. Sometimes a program performs complex tasks, and you need to create an overview, or application plan, first. Think of the Lego approach again Each block is separate, but you don't get the whole picture until you put the blocks together. The application plan shows how to put the blocks together to get a...

Understanding collection usage

If you've followed the book to this point, you've used collections in previous chapters. For example, the Recovering from an error section of Chapter 6 relies on a collection to retrieve information from a specific hard drive. The FileSystemObject object contains a collection of Drive objects. The example in the Designing a form for your application section of Chapter 7 also relies on collections. This example adds control objects to the Controls collection of the UserForm object. VBA uses a...

Executing a break

Whenever you open your program in the VBA IDE and execute it from there, the program is in debug mode. Using debug mode lets you stop the program and see what it's doing at any particular moment. Stopping the program can help you not only find bugs but also discover more about VBA and how to use it. You can use this feature to view other people's code to see how they perform programming tasks. Before you can stop execution of your program, you need to tell VBA where to stop. A stop within a VBA...

Adding comments to your functions

When you open the Insert Function dialog box, Excel displays a message saying that no help is available for your function. You can use one of two techniques to add comments to your functions. Use the following procedure to add the comment with the first technique 1. Click the Developer tab and then click Macros. 2. Type the name of your function in the Macro Name field. Notice that VBA doesn't highlight the Create button because it knows that the function exists even though the function doesn't...

Getting Tools to Make Programming Easier

When you build more programs, you begin to notice that VBA doesn't always provide all the features that you need. In some cases, you might find that the VBA IDE lacks functionality. You might think that Microsoft hasn't really addressed every need and that some tasks require too many steps to complete. Rather than reinvent the wheel and write the addition yourself, consider using one of these third-party products I MZ-Tools (http www.mztools.com ) An interesting addition to the VBA Integrated...

Step Implement the design

The wording for this step is just a fancy way of saying that you need to write some code. You often hear programmers use the phrase implement the design, and it also appears as commonly used jargon in many magazines. Before you can write some code, you need to open the Visual Basic Editor in older versions of Office by using the ToolsOMacroOVisual Basic Editor command. When working with Office 2007, all you need to do is click Visual Basic the Developer tab on the Ribbon. I'm using Excel for...

Making choices with option buttons and frames

You never use the OptionButton control alone. This control always appears in a group. A group of OptionButton controls lets you make one selection from a list of selections. Only one OptionButton control is selected at any given time, so selecting one automatically deselects all the other buttons. Because the OptionButton controls work in a group, you have to tell VBA which controls belong to which group, especially if a form contains more than one group. You can create groups in one of two...

Step Design the program

In the Deciding What to Do section, earlier in this chapter, I tell you how to plan your program. I also tell you what questions you should ask in preparation for writing the program. This step is actually part of the design phase, but most developers make it a separate step. Thinking through your application before you commit something to paper is important. After you plan your application, you can use any of a number of techniques to design the program. Some people use the flowcharting...

Using Object Browser

VBA provides access to a lot of objects, more than you'll use for any one program. With all the objects that you have at your disposal, you might forget the name of one or more of them at some time. Object Browser helps you find the objects that you need. In fact, you can use it to find new objects that could be useful for your next project. Use the ViewOObject Browser command to display Object Browser, as shown in Figure 1-14. Normally, you need to filter the information in some way. View the...

Sending EMail Messages with Outlook

Creating e-mail with Outlook is straightforward. If you don't like the formatting capabilities of Outlook, you can always create a message in Word and use the Word document as content for the e-mail. However, these two methods of message creation assume that a list of people will receive precisely the same message. When you want to create every message with the same general content and some messages with special content, you need a program. I often need to send customized announcements by using...

Understanding the VBA programming blocks

A VBA program consists of building blocks. In fact, because programming is abstract, people tend to use physical examples to explain how things work. You still need to know about the abstract elements of VBA programming, or else you can't write a program. This section explains the basic constructs of VBA programming. In the upcoming Taking the Lego Approach section of this chapter, I use a physical example as a means of describing these abstract elements in detail. Consider only the four...

Using the Do While Loop statement

A Do While Loop statement keeps performing a task until a certain condition is true. The loop checks the expression first and then executes the code within the structure if the expression is true. You use this loop to perform processing zero or more times. A Do While Loop works especially well if you can't determine the number of times that the loop should execute when you design your program. One example of a file that could require zero or more changes is a Word document. You might need to...

Using the Registry with VBA

Microsoft assumes that you always want to access the Registry to store settings for your program, so it created special functions that let you store settings under one specific key, HKEY_CURRENT_USER Software VBA. If you want to store program settings, simply use the SaveSetting function. Likewise, the GetSetting and GetAllSettings functions retrieve information from the Registry. These functions are always safe to use, even when you have User Account Control (UAC) enabled under Vista.

Writing the data to disk

The easiest way to export your data to disk is to save it as an XML document by using the SaveAs method. Here's an example of this method ' Use the standard SaveAs method to export XML. Sheetl.SaveAs ExcelXML.XML, xlXMLSpreadsheet End Sub You can also accomplish this task by choosing the FileOSave As command. The advantage of this method is that you can concentrate on a single worksheet, a set of records, or an area of a document rather than save the whole document. The first argument is the...

Executing the VBA program automatically

In rare cases, you need to write a VBA program that does something when you start the associated application or perform some other task, such as open a document. You should use this method of executing your programs automatically only if it serves a very special purpose. Using this technique to perform a setup that you only use once in a while doesn't make sense. If you set up something every day, it usually pays to make it a permanent part of the application configuration rather than run a...

Contents at a Glance

Part I An Overview of Chapter 1 Getting to Know Chapter 2 Your First VBA Part II Learning the Chapter 3 Writing Structured VBA Chapter 4 Storing and Modifying Chapter 5 Creating Structured Chapter 6 Trapping Errors and Squashing Chapter 7 Interacting with the Part III Expanding Your VBA Horizons 179 Chapter 8 Object-Oriented Chapter 9 Working with Arrays and Chapter 10 Working with Disk Chapter 11 VBA Programming with Part IV Programming for Chapter 12 VBA Programming in Chapter 13 VBA...

In this part

M Mse Chapter 17 to discover new resources that you can use to build your own VBA library. This chapter contains ten types of resources that you can use to make your VBA programming experience better, easier, faster, or more fun. VBA is a language that lets you decide what you want your host application to do. The VBA resources in this chapter give you ideas of what's possible. Chapter 18 is a special chapter for anyone upgrading their applications. You'll find that the changes Microsoft made...

Using the quicklaunch methods

It isn't always convenient or efficient to open the Macro dialog box to run the programs that you design. If you use the same program several times a day, opening the Macro dialog box can become a time-wasting event. What you need is a quick-launch method a way to start the program that doesn't require you to open the Macro dialog box. 2Nll t The Macro dialog box, shown in Figure 2-5, has one other use that helps you create quick-launch programs. Open the Macro dialog box by using the...

Knowing the Enemy

Users look at bugs as non-entities devoid of any characteristics. All a user knows is that a bug causes the program to crash and lose data. You can't afford to have that perspective. Bugs have personalities, in that they vary by S Other factors that you include in your personal classification system Locating a bug means knowing about its personality so that you can find it quickly. It helps to classify the bug by type. Each bug type has a different method for prevention and troubleshooting. You...

VBA Its Not Just for Programmers

One of the things that you should think about is why you want to use VBA. I know that some of you are probably just interested in using VBA, but most of you need a good reason for taking time out of your busy schedules. It's important to think about what tasks you can use VBA to do. It won't take out the garbage or fold your laundry, but you can use it to write some types of letters automatically. With this in mind, you find out about a few things in this section that I've done with VBA....

Understanding Variables and Constants

VBA provides many levels of data interpretation. Some data interpretations help you make your program reliable, others make it run faster, still others provide accuracy, and a few make the data easier to interpret. One of the two big distinctions is between variables and constants. You can modify a variable anytime the program can actually access it. A constant, however, retains the same value all the time. You use variables as storage containers for data that changes, and you use constants for...

Understanding the Access Related Objects

Microsoft Access provides more objects than just about any other Office application that you'll use. You can create a database, populate it with information, and print it out without ever using the user interface. In addition, these objects are interesting because they perform so many tasks well. Access even has a special object (see the upcoming Accessing special commands with the DoCmd object section) that performs little utility tasks. You need to know about a variety of collections and...

Locating Just the Right Code

A number of Web sites cater to the VBA user by proving example code that you can use anywhere in your own code. Most of these sites copyright their code so that you can't use it for profit or as part of a magazine article. The developer provides the code as is, so you might need to debug it a little or modify it to meet your needs. The point is that the code helps you better understand a programming concept, which makes writing your own version of the example much faster. Use these links as...

Designing the envelope and abet form

ji& Wfa The example in this section uses a single form for either envelopes or labels. It includes fields for name, address, city, state, and zip code. I purposely kept the form simple so that you can concentrate on technique rather than on customization. The form itself isn't all that special. It includes two command buttons one for creating the output and another for canceling it. Each of the required fields is a text box. (You can find the source code for this example on the Dummies.com...

Using RSS to Obtain the Latest Information

One of the biggest news items today is Rich Site Summary (RSS) (some sources call it Really Simple Syndication and others call it RDF Site Summary all three terms mean the same thing). The RSS technology sends short descriptions of articles to a special application on your machine, called a reader, at regular intervals. The summary lets you quickly decide whether you want to read the entire article. All you have to do is click the supplied link when you want to review the material. Using RSS,...

Asking Others About a

Many people have a tendency to think that they're the only ones experiencing a particular problem. In other cases, the pressures of fixing a problem now, not five minutes from now, keep people from looking for solutions that might help them. However, when it comes to VBA, you have a whole world of people to ask. Unless you're very unlucky, someone out there has experienced a problem that's similar to yours. It may not be precisely the same problem, but the help is there if you want it. The...

Setting macro security for Word Excel Power Point and Access

Office 2007 sets the security bar very high. It's unlikely that you'll be able to run most of the macros in this book without changing your security settings. The following steps help you make the required changes 1. Select the Developer or Database Tools tab on the Ribbon. You see the Trust Center dialog box, shown in Figure 1-5. Use the Trust Center to adjust the security settings for your Office product. Use the Trust Center to adjust the security settings for your Office product. 3. Select...

Making the declaration

All data in a VBA program looks like a series of bits to the computer. The computer doesn't understand the difference between a constant and a variable VBA provides this distinction for your benefit. You use different methods to mark variables and constants in your program. Variables have a scope and a data type. See the Defining the Data Types section, later in this chapter, for data type descriptions and how to use them. See the Getting the Scoop on Scope section of Chapter 3 for scope...

Importing a module in a program

The library that you build while you write VBA programs is an important resource because it reduces the need to write new code. Every time that you need to reuse existing code, you can import the module by using the following procedure 1. Right-click anywhere in the Project Explorer window and then choose Import File from the context menu that appears. 2. Locate the BAS, FRM, or CLS file that you want to import. 3. Highlight the file and then click Open. The module appears in the Project...

Understanding Sql A Quick Overview

SQL (pronounced SEE-quel) is a specialized language used by many database management systems (DBMSes) to manipulate data. You normally don't need to use SQL in Access programs because you can create a query by using the Access Query Designer. The Query Designer is one of the first tools that you learn to use because you need it to link tables and create complex data sets. None of the techniques that you use for designing a database changes when you write VBA programs you can still create...

Using the lfThen Elself statement

When making multiple comparisons, you can use the If Then ElseIf statement to make the code easier to read. Using this format can also reduce the number of decisions that VBA must make, which ensures that your code runs as quickly as possible. Listing 5-3 shows an alternative form of the example in the preceding Using the If Then Else statement section. (You can find the source code for this example on the Dummies.com site at http www.dummies.com go vbafd5e.)

Using the Templates collection

Word users know that templates provide the information required to give a set of documents the same look and feel. For example, a letter template could include special formatting for the heading and body text. It might include all the text that is the same for each letter, such as your name and address. Specialized templates often include automatic setups so that the initial document is as close as possible to the final product. Templates can use the DOT, DOTX, or DOTM file extensions. Only the...

Using the Case Else clause

A Select Case statement should normally contain the optional Case Else clause to ensure that you handle all cases, even those that you don't expect when you write the program. Adding this clause requires little time and adds an important error-trapping feature to your program. You could easily change the program from the previous section to include a Select Case statement like the one shown in Listing 5-6. (You can find the source code for this example on the Dummies.com site at http...

Adding controls to the Toolbox

You might find that you need other controls in order to create your program. Windows provides a wealth of controls that you can use. The Toolbox that VBA provides has only a few essential controls in it you can always add more by using the following procedure 1. Right-click anywhere on the Controls page of the Toolbox, and then choose Additional Controls from the context menu that appears. You see the Additional Controls dialog box, shown in Figure 7-6. Add controls to your Toolbox by using the...

Processing Outlook EMail Messages with Word

You can use it to get your personal mail or to view newsgroups online. However, you might run into situations when the formatting features that Outlook provides are less than useful. That's the purpose of the example in this case. Word requests the messages that you mark in Outlook and formats them in preparation for printing or just viewing. I use this program to create quick reference resources from my e-mail. The marking feature of this program is...

Downloading ActiveX Controls and Third Party Components

A developer can never have too many controls and components. You don't necessarily have to use them all every time that you write a program. A well-stocked toolbox of controls and components simply makes it easier to find just the tool that you need. Use the following sites as a starting point to building your own control and component toolbox 1 VBA Store at ComponentSource http www2.componentsource. com Marketplace 1 ActiveX.COM http www.active-x.com 1 c net Download.com http download.com.com...

Using strings for text

The first data type that I discuss in this chapter is one that you've already seen in the message box examples the string. When you create a message box, you use a string as input. The string is the most useful and most-often used data type in VBA. In this chapter, I discuss only the essential string functions. VBA provides a rich set of string manipulation functions. You can discover more about these functions in the Working with Strings help topic. (Always access VBA help topics from within...

Using the Code window

The Code window is where you write your application code. It works like any other editor that you've used, except that you type in a special language VBA. Figure 1-10 shows a typical example of a Code window with some code loaded. Notice that the Project Explorer window and the Properties window are gone you can display them again by using the ViewOProject Explorer Use the Code window to modify your program. Use the Code window to modify your program. and ViewOProperties Window commands. As an...

Modifying existing tabs

You can modify any existing element as long as you know the required identifier. Microsoft provides these identifiers as a download at http 43 29d9e9-4d11-46a5-89 8d-23e4f331e9ae. You can also view the identifiers by using a special feature of the Office products with the Ribbon interface. The following steps describe how 1. Select the Office application's button to display its menu. 2. Click the Options button (such as Word Options in Word). You see the Options dialog box for that application....

Adding Form Related Applications

The example database, queries, data, and associated form in this chapter all come from 1 of about 15 databases that I use personally. This word list is handy because it contains all the words that I've ever had to define in a book. A personal word list is one of the handier database programs that you can maintain because finding the meaning of words (especially jargon related to a specific industry) can prove difficult. In other examples in this chapter, I show you bits and pieces of the tables...

Using the GoTo statement correctly

The GoTo statement provides the essential service of helping you redirect program flow. Before you use the GoTo statement, ask yourself whether there's some other means of performing the redirection, such as using a loop. If there isn't any other way to perform the programming task efficiently, using a GoTo statement is acceptable. Listing 5-9 shows an example of correct GoTo usage. It's an update of the example shown in the earlier Using the Select Case statement section of this chapter. (You...

Using the lfThen Else statement

The If Then Else statement makes one of two choices. If the expression controlling the statement is true, VBA executes the first set of statements. On the other hand, if the expression is false, VBA executes the second set of statements. The example in this section determines whether two numbers are equal or whether one is greater than the other. To test the If Then Else statement, create a new Excel worksheet. The code relies on input from the worksheet for test purposes. Figure 5-1 shows the...

Selecting Objects within Excel

You can place any object that you want in an Excel worksheet, including pictures and sounds. These kinds of objects work very much the same in Excel as they do in Word. (See the Selecting Objects in a Word Document section of Chapter 13.) The main difference is that you use the OLEObjects collection of the sheet that holds the object. However, Excel can also embed Chart objects in a worksheet. Because this is such a special feature, I show you in this section how to work with embedded Chart...

Sending Notes from Word to Excel

You might find the need to create a utility simply because a task becomes unwieldy in the host program. For example, many of my worksheets contain extensive notes. Editing those notes in Excel can become a real problem after the note gets to a certain size. However, Word provides a great editor, and I can make the notes as long as needed. The example shown in Listing 16-7 includes part of a program that I created for moving notes between Word and Excel so that they're easier to edit. (You can...

Understanding the Excel Related Objects

Excel relies on some essential objects to provide access to the various data elements. Because of the nature of worksheets, you'll find that each of these objects can actually perform multiple duties. For example, you don't have to access a worksheet through a Workbook object unless you don't know the name of the worksheet. Most objects also include links to objects above and below the current position in the hierarchy so that you can use the object of convenience to access a data element. You...

VBA Programming in Excel

Working with Excel-related objects Accessing and using Excel objects Changing the contents of individual cells Creating your own functions to use in Excel Adding pizzazz to your Excel worksheet Defining your own special-purpose worksheets icrosoft Excel provides a wealth of opportunities for custom VBA programs. You can do everything from creating special equations to designing eye-grabbing presentations. With some effort, you can create detailed graphs and charts on the fly. It's also...

Listing Using the Workbooks Collection

Public Sub WorkbookDemo() ' Holds the output data. Dim Output As String ' Get the test workbook. Dim CurrBook As Workbook ' Get the workbook name and location. Output Name + CurrBook.Name + vbCrLf + _ Full Name + CurrBook.FullName + vbCrLf + _ Path + CurrBook.Path + vbCrLf + vbCrLf ' Holds the current sheet. Dim CurrSheet As Worksheet Output Worksheet List + vbCrLf For Each CurrSheet In CurrBook.Worksheets Output Output + CurrSheet.Name + vbCrLf Next ' Holds the current chart. Dim CurrChart As...

Developing Custom Functions in Excel

You can't find too many worksheets that are devoid of formulas because you need formulas to figure out new values based on your existing data. Microsoft includes a wealth of standard formulas in Excel. In fact, it's possible that you'll never need anything more than the Microsoft formulas. However, formulas are extremely important, so it's handy to know how to create one of your own. All formulas in Excel rely on functions. If you want to create a special...

Manipulating Toolbars and Menus

Office 2007 now has two interfaces, which means that Microsoft has made it more difficult for users of all types. The toolbar-and-menu interface used by previous versions of Office appears in products such as Visio and Outlook, and the new Ribbon interface appears in the core office applications, such as Word and Excel. This section of the chapter works mainly with the older toolbar and menu interface. The Working with the New Ribbon Interface section, later in this chapter, describes the newer...

Using numbers for calculations

Numbers form the basis for a lot of the information computers store. You use numbers to perform tasks in a spreadsheet, to express quantities in a database, and to show the current page in a document. Programs also use numbers to count things such as loops, to determine the position of items such as characters in a string, and to check the truth value of a statement. Finally, VBA uses numbers in myriad ways, such as determining which character to display onscreen or how to interact with your...

Using the Windows collection

Excel records one Window object in the Windows collection for every file that you open. Consequently, the Windows collection doesn't tell you much about the data except at a very high level. For example, you could use the Window object to determine the names of files that you have opened. The Window object can also determine the active Sheet object (see the section Using the Sheets collection, earlier in this chapter, for details) and tell you about general settings, such as whether Excel...

Listing Moving Notes from Word to Excel

Public Sheet As Integer Public Row As String Public Column As String ' Go to the beginning of the document. .GoTo What wdGoToLine, Which wdGoToFirst .EndOf Unit wdSection, Extend wdExtend ' Get the text. NoteText .Text End With ' Create a file dialog. Dim GetFile As FileDialog Set GetFile GetFile.AllowMultiSelect False GetFile.Filters.Clear GetFile.Filters.Add Excel Files, *.XL* Set TheBook Excel.Workbooks.Open(Filename) ' Request the sheet and cell number for the note. NoteSelect.Show...

Listing Using the Registry to Work with Objects

Dim AObj As InlineShape ' Holds the BMP file class. Dim BMPClass As String ' Holds picture statistical data. Dim Output As String ' Holds the Registry key reference. Dim RegKeyRef As Long ' Holds the length of the Registry data. Dim RegLength As Long ' Get the BMP file class. ' Open the Registry key. RegOpenKey ROOT_KEYS.HKEY_CLASSES_ROOT, _ .bmp, RegKeyRef ' Determine whether the key exists. If RegKeyRef 0 Then MsgBox Couldn't open BMP file Registry setting., _...

Using the Select Case statement

The Select Case structure begins with the Select Case statement and ends with an End Case statement. You provide a variable that the Select Case statement can use for selection. Within the Select Case structure are Case clauses, or values that the Select Case structure uses for comparison. When the value of a clause matches the value of the input variable, the Select Case structure performs all tasks required by that clause. In this section, I use examples of the Select Case statement to make a...

Listing Using the Sheets Collection

Public Sub ListSheets ' An individual entry. Dim ThisEntry As Variant ' Holds the output data. Dim Output As String ' Get the current number of worksheets. Output Sheet Count _ For Each ThisEntry In Application.Sheets ' Verify there is a sheet to work with. If ThisEntry.Type XlSheetType.xlWorksheet Then Output Output vbCrLf ThisEntry.Name End If Next ' Display the result. MsgBox Output, _ vblnformation or vbOKOnly, _ Worksheet List The code for this example begins by creating a Variant to hold...

Using the Sheets collection

The Sheets collection is the easiest method for accessing worksheets in many situations. You don't have to drill down through the Excel object hierarchy to find the worksheet that you want. However, accessing the worksheets at the top of the hierarchy means that you don't have the objects that exist at lower levels available either, so this technique is a tradeoff. You can use the Sheets collection to access all kinds of sheets, not just worksheets. Any standalone Chart objects also appear in...

Listing Retrieving a Specific Sentence from a Document

Dim CurrPane As Pane ' Holds a specific paragraph on the page. Dim CurrPara As Paragraph ' Holds a single sentence. Dim CurrSent As String ' Holds the output. Dim Output As String ' Acts as a loop counter. Dim Counter As Integer ' Locate the window based on the expected name. Dim LocateWindow As Window ' The current window. Dim WinString As String ' The window caption. For Each LocateWindow In Application.Windows If Left LocateWindow.Caption, 15 _ WordObjects.doc Then...

Performing tasks when the Ribbon loads

You have access to a wealth of information about the Ribbon. However, most of this information comes from callbacks. One of the most important callbacks concerns Ribbon loading. You use the onLoad attribute for the lt customUI gt element, as shown here onLoad RibbonLoaded gt This callback looks for a RibbonLoaded Sub in your VBA code. As with button and other control events, you can ask the Custom UI Editor to generate the required Sub code for you automatically. All you need to do then is fill...

VBA Programming with XML

Defining the features of Word Markup Language WordML Designing your first XML document Working with XML data Creating an XML document Using eXtensible Style Language Transformation XSLT to modify presentation 7he eXtensible Markup Language XML is a special way of marking text so that it contains both information and context. Using this technique means that the recipient knows not only the information but also the meaning behind the information. For example, if you see 12.99 in a text file, you...

Security under Vista

Nlfii Vista places extra security constraints on Office products. The User Access Control UAC makes it impossible to run some macros that would ordinarily work under previous versions of Windows. Even setting the macro security won't help, in some cases, depending on the security policies set by the administrator, your personal security settings, and the task the macro performs. In general, you want to sign your macros before you use them under Vista. See the Adding a Digital Signature to Your...

Writing Your First

Most Microsoft Office products provide a Properties dialog box, similar to the one shown in Figure 3-10, that contains a Summary tab for documents. You can also find a variation of this Properties dialog box for most third-party products. The Summary tab can provide a lot of valuable information for your programs. You can find out basic statistics, such as the author's name and the company that created the document. The document information also includes statistics, such as the number of words...