A

A1 cells, selecting on all worksheets, 158-159 Abs function, 61, 365 Accelerator property (controls), 246, 249 Access databases connections, opening, 191 data sources, creating, adding, 205-206 deleting, 207-208 editing, 203-205 finding, 202-203 navigating, 199-201 recordsets to Excel, 210-212 opening with Open method, 194-196 opening with SELECT statement, 196-198 opening with tables, 193-194 overview, 192-193 references, creating, 190-191 retrieving data into Excel individual field values,...

Listing A Procedure That Saves Every Open Workbook

1 Run through all the open workbooks ' Has the workbook been saved before If wb.Path < > Then ' If not, display the Save As dialog box ' to get the workbook's path & filename newFilename .GetSaveAsFilename( _ FileFilter Microsoft Office & Excel Workbook & _ (*.xlsx), *.xlsx) ' If not, save the workbook using the ' specified path and filename wb.SaveAs fileName newFilename End If End If Next 'wb End Sub The main loop in the SaveAll macro uses the Workbooks collection and a For Each...

Contents

What Is a What Does VBA Have to Do with What You Should Know Before Reading This What's in the This Book's Special 1 Creating and Running Recorded Recording a VBA Recording a Word Recording an Excel Running a Recorded Using the Macro Name Assigning Shortcut Keys to Recorded Word Assigning Shortcut Keys to Recorded Excel Creating a Quick Access Toolbar Button for a Recorded From 2 Writing Your Own Activating the Ribbon's Developer Displaying the Visual Basic Touring the Visual Basic Creating a...

Accessing an Applications Builtin Dialog Boxes

Many VBA methods are known as dialog box equivalents because they let you select the same options that are available in an application's built-in dialog boxes. Using dialog box equivalents works fine if your procedure knows which options to select, but there are times when you might want the user to specify some of the dialog box options. For example, if your procedure prints a document (using the Application object's Printout method), you might need to know how many copies the user wants or...

Listing Procedures That Use the onKey Method to Set and Reset a Key Combination

Application.OnKey _ Key Del , _ Application.OnKey _ Key Del Running a Procedure at a Specific Time If you need to run a procedure at a specific time, use the OnTime method Application.OnTime(EarliestTime, Procedure , LatestTime , Schedule ) EarliestTime The time (and date, if necessary) you want the procedure to run. Enter a date time serial number. Procedure The name (entered as text) of the procedure to run when the LatestTime If Excel isn't ready to run the procedure at EarliestTime (in...

Creating a Gallery

The more choices you have to offer, the less attractive becomes the drop-down list control because the list just gets too long to navigate efficiently. A better solution for a large number of options is a gallery control, which displays multiple items in the number of rows and columns you specify. To create a gallery in RibbonX, begin with the < gallery> element label value imageMso value size normal large InsertAfterMso value InsertBeforeMso value onAction value enabled true false visible...

Using BuiltIn Constants

Many properties and methods have their own predefined constants. For Excel objects, these constants begin with the letters xl. For Word objects, the constants begin with wd. For VBA objects, the constants begin with vb. For example, Excel's Window object has a WindowState property that recognizes three built-in constants xlNormal to set a window in its normal state , xlMaximized to maximize a window , and xlMinimized to minimize a window . To maximize the active window, for example, you would...

Arithmetic Operators

VBA's arithmetic operators are similar to those you use to build Excel formulas. Table 4.1 lists each of the arithmetic operators you can use in your VBA statements. The Mod operator works like Excel's MOD worksheet function. In other words, it divides one number by another and returns the remainder. Here's the general form to use result dividend Mod divisor Here, dividend is the number being divided divisor is the number being divided into dividend and result is the remainder of the division....

Specifying the Number of Sheets in a New Workbook

When you create a new workbook in Excel, the file comes with three worksheets by default. Most people just use one worksheet, but leave the other two sheets in the workbook, just in case. If you use several sheets in many or all of your workbooks, you should consider increasing the default number of sheets that Excel includes in new workbooks. Follow these steps 1. Choose Office, Excel Options to open the Excel Options dialog box. 3. Use the Include This Many Sheets spin box to set the number...

Saving Application Settings in the Registry

In a VBA procedure, you use variables to store values you need to use while you're running the procedure. When the procedure finishes, the values of those variables are wiped from memory. What do you do if you have values that you want to preserve from one VBA session to another You could store the values somewhere in the document, but this isn't a great idea because those values could be easily changed or even deleted. A better idea is to use the Registry. Windows uses the Registry to store...

Accessing the File System Through VBA

If your applications need to work with the file system, VBA boasts quite a few features that make it easy. These features include a number of statements that return information about files and folders, as well as a number of functions with which you can manipulate files and folders. There are also powerful functions that give you direct access to files. This section examines all VBAs file-related statements and functions. Returning File and Folder Information If you need information about the...

Tab Strips and Multi Page Controls

I mentioned earlier that you can use frames to group related controls visually and help the user make sense of the form. However, there are two situations in which a frame falls down on the job. The first situation is when you need the form to show multiple sets of the same or similar data. For example, suppose you have a form that shows values for sales and expense categories. You might want the form to be capable of showing separate data for various company divisions. One solution would be to...