Muutke küpsiste eelistusi

E-raamat: Excel 2010 Power Programming with VBA [Wiley Online]

(J-Walk and Associates, Inc., San Diego, CA)
  • Formaat: 1088 pages
  • Sari: Mr. Spreadsheet's Bookshelf
  • Ilmumisaeg: 11-May-2010
  • Kirjastus: John Wiley & Sons Inc
  • ISBN-10: 1118257618
  • ISBN-13: 9781118257616
Teised raamatud teemal:
  • Wiley Online
  • Hind: 52,85 €*
  • * hind, mis tagab piiramatu üheaegsete kasutajate arvuga ligipääsu piiramatuks ajaks
  • Formaat: 1088 pages
  • Sari: Mr. Spreadsheet's Bookshelf
  • Ilmumisaeg: 11-May-2010
  • Kirjastus: John Wiley & Sons Inc
  • ISBN-10: 1118257618
  • ISBN-13: 9781118257616
Teised raamatud teemal:
An overview of the spreadsheet application for power users explains how to customize Excel UserForms, develop new utilities, use VBA with charts and PivotTables, and facilitate interactions with other applications.

This unique memoir is about experiencing cancer, but it is also about so much more. Michael Murphy is an admired broadcaster, writer and psychoanalyst, and this is a pathbreaking book which has a very special relevance for all whose lives have been touched by cancer, but which also appeals to anyone who loves to read a moving story exceptionally well told.

As Mary Robinson writes in her Foreword: His book is unflinchingly honest, and he breaks down the taboo around men speaking the unspeakable...

What I have realised is that in writing about all aspects of the cancer theme - traumatic assault, mortality, endurance - Michael has created a personal myth to live by. His memoir graphically treats of themes which take enormous risks of courage to put into words, but through the exceptional quality of his writing, they have become a pillar of light which has enabled him to continue on in the warmth of its lustre, despite his loss and the limits that have now been reached.

Learn to use Visual Basic for Applications (VBA), and you can expand the already awesome power of Excel2010. John Walkenbach, aka Mr. Spreadsheet, shows you how to do it in this easy-to-follow guide.

If you're already an experienced Excel user, this book will make you an Excel master. You'll learn a new approach to Excel and the steps involved in developing a new spreadsheet application. You'll also discover how to develop VBA subroutines and functions, use advanced programming techniques, and more. And if you're switching to Excel 2010 from an earlier version, there's a section to get you up to speed on the new features.

No one can teach you more about Excel than Mr. Spreadsheet.

All the methods and tools you need to successfully program with Excel

John Walkenbach's name is synonymous with excellence in computer books that decipher complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows you how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf.

Featuring a complete introduction to Visual Basic for Applications and fully updated for the new features of Excel 2010, this essential reference includes an analysis of Excel application development and is packed with procedures, tips, and ideas for expanding Excel’s capabilities with VBA.

  • Offers an analysis of Excel application development and a complete introduction to Visual Basic for Applications (VBA)
  • Features invaluable advice from "Mr. Spreadsheet" himself (bestselling author John Walkenbach), who demonstrates all the techniques you need to create large and small Excel applications
  • Provides tips, tricks, and techniques for expanding Excel's capabilities with VBA that you won’t find anywhere else
  • Includes a CD with templates and worksheets from the book

This power-user's guide is packed with procedures, tips, and ideas for expanding Excel's capabilities with VBA.

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Introduction 1(10)
Part I Some Essential Background
Chapter 1 Excel 2010: Where It Came From
11(12)
A Brief History of Spreadsheets
11(9)
It all started with VisiCalc
11(1)
Lotus 1-2-3
12(2)
Quattro Pro
14(1)
Microsoft Excel
15(5)
Current Competition
20(1)
Why Excel Is Great for Developers
20(2)
Excel's Role in Microsoft's Strategy
22(1)
Chapter 2 Excel in a Nutshell
23(30)
Thinking in Terms of Objects
23(1)
Workbooks
24(4)
Worksheets
24(2)
Chart sheets
26(1)
XLM macro sheets
26(2)
Excel 5/95 dialog sheets
28(1)
Excel's User Interface
28(9)
About the Ribbon
28(6)
Shortcut menus and the Mini Toolbar
34(1)
Dialog boxes
35(1)
Keyboard shortcuts
36(1)
Smart Tags
36(1)
Task pane
36(1)
Customizing the Display
37(1)
Data Entry
38(1)
Formulas, Functions, and Names
38(2)
Selecting Objects
40(1)
Formatting
40(2)
Protection Options
42(2)
Protecting formulas from being overwritten
42(1)
Protecting a workbook's structure
43(1)
Applying password protection to a workbook
43(1)
Protecting VBA code with a password
43(1)
Charts
44(1)
Shapes and SmartArt
45(1)
Database Access
46(1)
Worksheet databases
46(1)
External databases
47(1)
Internet Features
47(1)
Analysis Tools
48(2)
Add-Ins
50(1)
Macros and Programming
50(1)
File Format
50(1)
Excel's Help System
51(2)
Chapter 3 Formula Tricks and Techniques
53(24)
About Formulas
53(1)
Calculating Formulas
54(1)
Cell and Range References
55(3)
Why use references that aren't relative?
55(1)
About R1C1 notation
56(1)
Referencing other sheets or workbooks
57(1)
Using Names
58(7)
Naming cells and ranges
59(1)
Applying names to existing references
60(1)
Intersecting names
61(1)
Naming columns and rows
61(1)
Scoping names
61(1)
Naming constants
62(1)
Naming formulas
63(2)
Naming objects
65(1)
Formula Errors
65(1)
Array Formulas
66(3)
An array formula example
66(1)
An array formula calendar
67(1)
Array formula pros and cons
68(1)
Counting and Summing Techniques
69(2)
Counting formula examples
70(1)
Summing formula examples
70(1)
Other counting tools
71(1)
Working with Dates and Times
71(3)
Entering dates and times
72(1)
Using pre-1900 dates
73(1)
Creating Megaformulas
74(3)
Chapter 4 Understanding Excel Files
77(24)
Starting Excel
77(3)
File Types
80(3)
Excel file formats
80(1)
Text file formats
81(1)
Database file formats
81(1)
Other file formats
82(1)
Working with Template Files
83(4)
Viewing templates
83(1)
Creating templates
84(2)
Creating workbook templates
86(1)
Inside an Excel File
87(4)
Dissecting a file
87(4)
Why is the file format important?
91(1)
The OfficeUI File
91(1)
The XLB File
92(1)
Add-In Files
93(1)
Excel Settings in the Registry
94(7)
About the Registry
94(1)
Excel's settings
95(6)
Part II Excel Application Development
Chapter 5 What Is a Spreadsheet Application?
101(10)
Spreadsheet Applications
101(1)
The Developer and the End-User
102(3)
Who are developers? What do they do?
102(2)
Classifying spreadsheet users
104(1)
The audience for spreadsheet applications
104(1)
Solving Problems with Excel
105(1)
Basic Spreadsheet Types
106(5)
Quick-and-dirty spreadsheets
106(1)
For-your-eyes-only spreadsheets
107(1)
Single-user applications
107(1)
Spaghetti applications
107(1)
Utility applications
108(1)
Add-ins that contain worksheet functions
108(1)
Single-block budgets
109(1)
What-if models
109(1)
Data storage and access spreadsheets
109(1)
Database front ends
110(1)
Turnkey applications
110(1)
Chapter 6 Essentials of Spreadsheet Application Development
111(24)
Steps for Application Development
111(1)
Determining User Needs
112(1)
Planning an Application That Meets User Needs
113(2)
Determining the Most Appropriate User Interface
115(7)
Customizing the Ribbon
118(1)
Customizing shortcut menus
118(1)
Creating shortcut keys
119(1)
Creating custom dialog boxes
119(1)
Using ActiveX controls on a worksheet
120(2)
Executing the development effort
122(1)
Concerning Yourself with the End User
122(7)
Testing the application
122(2)
Making the application bulletproof
124(2)
Making the application aesthetically appealing and intuitive
126(1)
Creating a user Help system
127(1)
Documenting the development effort
128(1)
Distributing the application to the user
128(1)
Updating the application when necessary
129(1)
Other Development Issues
129(6)
The user's installed version of Excel
130(1)
Language issues
130(1)
System speed
130(1)
Video modes
131(4)
Part III Understanding Visual Basic for Applications
Chapter 7 Introducing Visual Basic for Applications
135(58)
Getting Some BASIC Background
135(1)
Delving in to VBA
136(1)
Object models
136(1)
VBA versus XLM
136(1)
Covering the Basics of VBA
137(3)
Introducing the Visual Basic Editor
140(3)
Displaying Excel's Developer tab
141(1)
Activating the VBE
141(1)
The VBE windows
142(1)
Working with the Project Explorer
143(2)
Adding a new VBA module
144(1)
Removing a VBA module
145(1)
Exporting and importing objects
145(1)
Working with Code Windows
145(8)
Minimizing and maximizing windows
146(1)
Storing VBA code
146(1)
Entering VBA code
147(6)
Customizing the VBE Environment
153(6)
Using the Editor tab
154(2)
Using the Editor Format tab
156(1)
Using the General tab
157(1)
Using the Docking tab
158(1)
The Macro Recorder
159(8)
What the macro recorder actually records
160(1)
Relative or absolute recording?
161(3)
Recording options
164(1)
Cleaning up recorded macros
165(2)
About Objects and Collections
167(2)
The object hierarchy
167(1)
About collections
168(1)
Referring to objects
168(1)
Properties and Methods
169(3)
Object properties
169(1)
Object methods
170(2)
The Comment Object: A Case Study
172(8)
Viewing Help for the Comment object
173(1)
Properties of a Comment object
174(1)
Methods of a Comment object
175(1)
The Comments collection
175(1)
About the Comment property
176(1)
Objects within a Comment object
177(1)
Determining whether a cell has a comment
178(1)
Adding a new Comment object
179(1)
Some Useful Application Properties
180(2)
Working with Range Objects
182(6)
The Range property
182(2)
The Cells property
184(3)
The Offset property
187(1)
Things to Know about Objects
188(5)
Essential concepts to remember
188(1)
Learning more about objects and, properties
189(4)
Chapter 8 VBA Programming Fundamentals
193(48)
VBA Language Elements: An Overview
193(2)
Comments
195(2)
Variables, Data Types, and Constants
197(13)
Defining data types
198(3)
Declaring variables
201(2)
Scoping variables
203(3)
Working with constants
206(3)
Working with strings
209(1)
Working with dates
209(1)
Assignment Statements
210(3)
Arrays
213(2)
Declaring arrays
213(1)
Declaring multidimensional arrays
214(1)
Declaring dynamic arrays
214(1)
Object Variables
215(1)
User-Defined Data Types
216(1)
Built-in Functions
217(3)
Manipulating Objects and Collections
220(3)
With-End With constructs
220(1)
For Each-Next constructs
221(2)
Controlling Code Execution
223(18)
GoTo statements
224(1)
If-Then constructs
224(5)
Select Case constructs
229(3)
Looping blocks of instructions
232(9)
Chapter 9 Working with VBA Sub Procedures
241(40)
About Procedures
241(3)
Declaring a Sub procedure
242(1)
Scoping a procedure
243(1)
Executing Sub Procedures
244(11)
Executing a procedure with the Run Sub/UserForm command
245(1)
Executing a procedure from the Macro dialog box
245(1)
Executing a procedure with a Ctrl+shortcut key combination
246(1)
Executing a procedure from the Ribbon
247(1)
Executing a procedure from a customized shortcut menu
247(1)
Executing a procedure from another procedure
248(5)
Executing a procedure by clicking an object
253(1)
Executing a procedure when an event occurs
254(1)
Executing a procedure from the Immediate window
254(1)
Passing Arguments to Procedures
255(4)
Error-Handling Techniques
259(5)
Trapping errors
259(2)
Error-handling examples
261(3)
A Realistic Example That Uses Sub Procedures
264(17)
The goal
264(1)
Project requirements
264(1)
What you know
265(1)
The approach
265(1)
What you need to know
266(1)
Some preliminary recording
266(2)
Initial setup
268(1)
Code writing
269(1)
Writing the Sort procedure
270(4)
More testing
274(1)
Fixing the problems
275(4)
Utility availability
279(1)
Evaluating the project
279(2)
Chapter 10 Creating Function Procedures
281(44)
Sub Procedures versus Function Procedures
281(1)
Why Create Custom Functions?
282(1)
An Introductory Function Example
282(5)
Using the function in a worksheet
283(1)
Using the function in a VBA procedure
284(1)
Analyzing the custom function
285(2)
Function Procedures
287(5)
A function's scope
288(1)
Executing function procedures
288(4)
Function Arguments
292(1)
Function Examples
293(15)
Functions with no argument
293(2)
A function with one argument
295(3)
A function with two arguments
298(1)
A function with an array argument
299(1)
A function with optional arguments
300(2)
A function that returns a VBA array
302(3)
A function that returns an error value
305(2)
A function with an indefinite number of arguments
307(1)
Emulating Excel's SUM function
308(3)
Extended Date Functions
311(2)
Debugging Functions
313(1)
Dealing with the Insert Function Dialog Box
314(5)
Using the MacroOptions method
315(2)
Specifying a function category
317(1)
Adding a function description manually
318(1)
Using Add-ins to Store Custom Functions
319(1)
Using the Windows API
320(5)
Windows API examples
321(1)
Determining the Windows directory
321(1)
Detecting the Shift key
322(1)
Learning more about API functions
323(2)
Chapter 11 VBA Programming Examples and Techniques
325(74)
Learning by Example
325(1)
Working with Ranges
326(27)
Copying a range
326(2)
Moving a range
328(1)
Copying a variably sized range
328(2)
Selecting or otherwise identifying various types of ranges
330(2)
Prompting for a cell value
332(1)
Entering a value in the next empty cell
333(1)
Pausing a macro to get a user-selected range
334(2)
Counting selected cells
336(1)
Determining the type of selected range
337(2)
Looping through a selected range efficiently
339(3)
Deleting all empty rows
342(1)
Duplicating rows a variable number of times
342(2)
Determining whether a range is contained in another range
344(1)
Determining a cell's data type
345(1)
Reading and writing ranges
346(1)
A better way to write to a range
347(2)
Transferring one-dimensional arrays
349(1)
Transferring a range to a variant array
349(1)
Selecting cells by value
350(2)
Copying a noncontiguous range
352(1)
Working with Workbooks and Sheets
353(4)
Saving all workbooks
354(1)
Saving and closing all workbooks
354(1)
Hiding all but the selection
354(2)
Synchronizing worksheets
356(1)
VBA Techniques
357(8)
Toggling a Boolean property
357(1)
Determining the number of printed pages
358(1)
Displaying the date and time
358(2)
Getting a list of fonts
360(2)
Sorting an array
362(1)
Processing a series of files
363(2)
Some Useful Functions for Use in Your Code
365(5)
The FileExists function
365(1)
The FileNameOnly function
365(1)
The PathExists function
366(1)
The RangeNameExists function
366(2)
The SheetExists function
368(1)
The WorkbooklsOpen function
368(1)
Retrieving a value from a closed workbook
368(2)
Some Useful Worksheet Functions
370(16)
Returning cell formatting information
370(2)
A talking worksheet
372(1)
Displaying the date when a file was saved or printed
372(1)
Understanding object parents
373(1)
Counting cells between two values
374(1)
Determining the last non-empty cell in a column or row
375(2)
Does a string match a pattern?
377(1)
Extracting the nth element from a string
378(1)
Spelling out a number
379(1)
A multifunctional function
380(1)
The SheetOffset function
381(1)
Returning the maximum value across all worksheets
381(2)
Returning an array of nonduplicated random integers
383(1)
Randomizing a range
384(2)
Windows API Calls
386(13)
Determining file associations
386(1)
Determining disk drive information
387(1)
Determining default printer information
388(1)
Determining video display information
389(1)
Adding sound to your applications
390(2)
Reading from and writing to the Registry
392(7)
Part IV Working with UserForms
Chapter 12 Custom Dialog Box Alternatives
399(20)
Before You Create That UserForm
399(1)
Using an Input Box
399(5)
The VBA InputBox function
400(2)
The Excel InputBox method
402(2)
The VBA MsgBox Function
404(5)
The Excel GetOpenFilename Method
409(3)
The Excel GetSaveAsFilename Method
412(1)
Prompting for a Directory
413(1)
Displaying Excel's Built-in Dialog Boxes
413(3)
Displaying a Data Form
416(3)
Making the data form accessible
416(2)
Displaying a data form by using VBA
418(1)
Chapter 13 Introducing UserForms
419(36)
How Excel Handles Custom Dialog Boxes
419(1)
Inserting a New UserForm
420(1)
Adding Controls to a UserForm
421(1)
Toolbox Controls
422(4)
CheckBox
423(1)
ComboBox
423(1)
CommandButton
423(1)
Frame
423(1)
Image
423(1)
Label
423(1)
ListBox
424(1)
MultiPage
424(1)
OptionButton
424(1)
RefEdit
424(1)
ScrollBar
424(1)
SpinButton
424(1)
TabStrip
425(1)
TextBox
425(1)
ToggleButton
426(1)
Adjusting UserForm Controls
426(1)
Adjusting a Control's Properties
426(6)
Using the Properties window
428(1)
Common properties
429(1)
Accommodating keyboard users
430(2)
Displaying a UserForm
432(2)
Displaying a modeless UserForm
433(1)
Displaying a UserForm based on a variable
433(1)
Loading a UserForm
433(1)
About event-handler procedures
433(1)
Closing a UserForm
434(1)
Creating a UserForm: An Example
435(7)
Creating the UserForm
436(2)
Writing code to display the dialog box
438(1)
Testing the dialog box
439(1)
Adding event-handler procedures
440(1)
Validating the data
441(1)
The finished dialog box
442(1)
Understanding UserForm Events
442(6)
Learning about events
442(1)
UserForm events
443(1)
SpinButton events
444(2)
Pairing a SpinButton with a TextBox
446(2)
Referencing UserForm Controls
448(2)
Customizing the Toolbox
450(2)
Adding new pages to the Toolbox
450(1)
Customizing or combining controls
450(1)
Adding new ActiveX controls
451(1)
Creating UserForm Templates
452(1)
A UserForm Checklist
453(2)
Chapter 14 UserForm Examples
455(38)
Creating a UserForm "Menu"
455(2)
Using CommandButtons in a UserForm
455(1)
Using a ListBox in a UserForm
456(1)
Selecting Ranges from a UserForm
457(2)
Creating a Splash Screen
459(2)
Disabling a UserForm's Close Button
461(1)
Changing a UserForm's Size
462(2)
Zooming and Scrolling a Sheet from a UserForm
464(2)
ListBox Techniques
466(19)
Adding items to a ListBox control
467(5)
Determining the selected item in a ListBox
472(1)
Determining multiple selections in a ListBox
472(2)
Multiple lists in a single ListBox
474(1)
ListBox item transfer
474(2)
Moving items in a ListBox
476(2)
Working with multicolumn ListBox controls
478(2)
Using a ListBox to select worksheet rows
480(2)
Using a ListBox to activate a sheet
482(3)
Using the MultiPage Control in a UserForm
485(1)
Using an External Control
486(3)
Animating a Label
489(4)
Chapter 15 Advanced UserForm Techniques
493(50)
A Modeless Dialog Box
493(4)
Displaying a Progress Indicator
497(10)
Creating a stand-alone progress indicator
498(4)
Showing a progress indicator by using a MultiPage control
502(3)
Showing a progress indicator without using a MultiPage control
505(2)
Creating Wizards
507(6)
Setting up the MultiPage control for the wizard
508(1)
Adding the buttons to the wizard's UserForm
508(1)
Programming the wizard's buttons
508(2)
Programming dependencies in a wizard
510(2)
Performing the task with the wizard
512(1)
Emulating the MsgBox Function
513(4)
MsgBox emulation: MyMsgBox code
514(1)
How the MyMsgBox function works
515(1)
Using the MyMsgBox function
516(1)
A UserForm with Movable Controls
517(1)
A UserForm with No Title Bar
518(1)
Simulating a Toolbar with a UserForm
519(2)
A Resizable UserForm
521(5)
Handling Multiple UserForm Controls with One Event Handler
526(3)
Selecting a Color in a UserForm
529(2)
Displaying a Chart in a UserForm
531(1)
Saving a chart as a GIF file
532(1)
Changing the Image control Picture property
532(1)
Making a UserForm Semitransparent
532(2)
An Enhanced Data Form
534(3)
About the Enhanced Data Form
536(1)
Installing the Enhanced Data Form add-in
537(1)
A Puzzle on a UserForm
537(1)
Video Poker on a UserForm
538(5)
Part V Advanced Programming Techniques
Chapter 16 Developing Excel Utilities with VBA
543(22)
About Excel Utilities
543(1)
Using VBA to Develop Utilities
544(1)
What Makes a Good Utility?
545(1)
Text Tools: The Anatomy of a Utility
545(18)
Background for Text Tools
546(1)
Project goals for Text Tools
547(1)
The Text Tools workbook
547(1)
How the Text Tools utility works
548(1)
The UserForm for the Text Tools utility
548(2)
The Module1 VBA module
550(2)
The UserForm1 code module
552(2)
Making the Text Tools utility efficient
554(1)
Saving the Text Tools utility settings
555(2)
Implementing Undo
557(2)
Displaying the Help file
559(1)
Adding the RibbonX code
560(2)
Post-mortem of the project
562(1)
Understand the Text Tools utility
562(1)
More about Excel Utilities
563(2)
Chapter 17 Working with Pivot Tables
565(18)
An Introductory Pivot Table Example
565(6)
Creating a pivot table
566(2)
Examining the recorded code for the pivot table
568(1)
Cleaning up the recorded pivot table code
568(3)
Creating a More Complex Pivot Table
571(5)
The code that created the pivot table
573(1)
How the more complex pivot table works
574(2)
Creating Multiple Pivot Tables
576(3)
Creating a Reverse Pivot Table
579(4)
Chapter 18 Working with Charts
583(56)
Getting the Inside Scoop on Charts
583(3)
Chart locations
584(1)
The macro recorder and charts
584(1)
The Chart object model
585(1)
Creating an Embedded Chart
586(2)
Creating a Chart on a Chart Sheet
588(1)
Using VBA to Activate a Chart
589(1)
Moving a Chart
590(1)
Using VBA to Deactivate a Chart
591(1)
Determining Whether a Chart Is Activated
592(1)
Deleting from the ChartObjects or Charts Collection
593(1)
Looping through All Charts
594(2)
Sizing and Aligning ChartObjects
596(2)
Exporting a Chart
598(2)
Exporting all graphics
599(1)
Changing the Data Used in a Chart
600(6)
Changing chart data based on the active cell
601(2)
Using VBA to determine the ranges used in a chart
603(3)
Using VBA to Display Arbitrary Data Labels on a Chart
606(3)
Displaying a Chart in a UserForm
609(2)
Understanding Chart Events
611(7)
An example of using Chart events
612(3)
Enabling events for an embedded chart
615(1)
Example: Using Chart events with an embedded chart
616(2)
Discovering VBA Charting Tricks
618(7)
Printing embedded charts on a full page
619(1)
Hiding series by hiding columns
619(2)
Creating unlinked charts
621(1)
Displaying text with the MouseOver event
622(3)
Animating Charts
625(6)
Scrolling a chart
626(2)
Creating a hypocycloid chart
628(1)
Creating a "clock" chart
629(2)
Creating an Interactive Chart without VBA
631(4)
Getting the data to create an interactive chart
632(1)
Creating the Option Button controls for an interactive chart
632(1)
Creating the city lists for the interactive chart
632(1)
Creating the interactive chart data range
633(1)
Creating the interactive chart
634(1)
Working with Sparkline Charts
635(4)
Chapter 19 Understanding Excel's Events
639(38)
What You Should Know about Events
639(7)
Understanding event sequences
640(1)
Where to put event-handler procedures
640(2)
Disabling events
642(1)
Entering event-handler code
643(1)
Event-handler procedures that use arguments
644(2)
Getting Acquainted with Workbook-Level Events
646(8)
The Open event
647(1)
The Activate event
648(1)
The SheetActivate event
648(1)
The NewSheet event
649(1)
The BeforeSave event
649(1)
The Deactivate event
650(1)
The BeforePrint event
650(2)
The BeforeClose event
652(2)
Examining Worksheet Events
654(8)
The Change event
654(1)
Monitoring a specific range for changes
655(5)
The SelectionChange event
660(1)
The BeforeDoubleClick event
661(1)
The BeforeRightClick event
662(1)
Checking Out Chart Events
662(2)
Monitoring with Application Events
664(5)
Enabling Application-level events
666(1)
Determining when a workbook is opened
666(2)
Monitoring Application-level events
668(1)
Using UserForm Events
669(1)
Accessing Events Not Associated with an Object
670(7)
The OnTime event
670(2)
The OnKey event
672(5)
Chapter 20 Interacting with Other Applications
677(26)
Starting an Application from Excel
677(4)
Using the VBA Shell function
677(3)
Using the Windows ShellExecute API function
680(1)
Activating an Application with Excel
681(2)
Using AppActivate
681(1)
Activating a Microsoft Office application
682(1)
Running Control Panel Dialog Boxes
683(1)
Using Automation in Excel
684(11)
Working with foreign objects using automation
685(1)
Early versus late binding
685(3)
A simple example of late binding
688(1)
Controlling Word from Excel
689(3)
Controlling Excel from another application
692(3)
Sending Personalized E-Mail via Outlook
695(3)
Sending E-Mail Attachments from Excel
698(3)
Using SendKeys
701(2)
Chapter 21 Creating and Using Add-Ins
703(30)
What Is an Add-In?
703(3)
Comparing an add-in with a standard workbook
703(1)
Why create add-ins?
704(2)
Understanding Excel's Add-In Manager
706(1)
Creating an Add-In
707(1)
An Add-In Example
708(6)
Adding descriptive information for the example add-in
709(1)
Creating an add-in
710(1)
Installing an add-in
710(2)
Testing the add-in
712(1)
Distributing an add-in
712(1)
Modifying an add-in
713(1)
Comparing XLAM and XLSM Files
714(7)
XLAM file VBA collection membership
714(1)
Visibility of XLSM and XLAM files
715(1)
Worksheets and chart sheets in XLSM and XLAM files
716(1)
Accessing VBA procedures in an add-in
717(4)
Manipulating Add-Ins with VBA
721(5)
AddIn object properties
722(3)
Accessing an add-in as a workbook
725(1)
AddIn object events
726(1)
Optimizing the Performance of Add-ins
726(1)
Special Problems with Add-Ins
727(6)
Ensuring that an add-in is installed
727(2)
Referencing other files from an add-in
729(1)
Detecting the proper Excel version for your add-in
730(3)
Part VI Developing Applications
Chapter 22 Working with the Ribbon
733(36)
Ribbon Basics
733(4)
Using VBA with the Ribbon
737(6)
Accessing a Ribbon control
738(2)
Working with the Ribbon
740(2)
Activating a tab
742(1)
Customizing the Ribbon
743(21)
A simple RibbonX example
743(3)
A simple Ribbon example, take 2
746(5)
Another RibbonX example
751(3)
Ribbon controls demo
754(7)
A DynamicMenu Control Example
761(2)
More on Ribbon customization
763(1)
Creating an Old-Style Toolbar
764(5)
Limitations of old-style toolbars in Excel 2010
765(1)
Code to create a toolbar
765(4)
Chapter 23 Working with Shortcut Menus
769(20)
CommandBar Overview
769(8)
CommandBar types
770(1)
Listing shortcut menus
770(1)
Referring to CommandBars
771(1)
Referring to controls in a CommandBar
772(1)
Properties of CommandBar controls
773(1)
Displaying all shortcut menu items
774(3)
Using VBA to Customize Shortcut Menus
777(6)
Resetting a shortcut menu
777(1)
Disabling a Shortcut Menu
778(1)
Disabling shortcut menu items
778(1)
Adding a new item to the Cell shortcut menu
779(2)
Adding a submenu to a shortcut menu
781(2)
Shortcut Menus and Events
783(6)
Adding and deleting menus automatically
784(1)
Disabling or hiding shortcut menu items
785(1)
Creating a context-sensitive shortcut menu
785(4)
Chapter 24 Providing Help for Your Applications
789(20)
Help for Your Excel Applications
789(1)
Help Systems That Use Excel Components
790(9)
Using cell comments for help
792(1)
Using a text box for help
793(1)
Using a worksheet to display help text
794(1)
Displaying help in a UserForm
795(4)
Displaying Help in a Web Browser
799(2)
Using HTML files
799(1)
Using an MHTML file
800(1)
Using the HTML Help System
801(4)
Using the Help method to display HTML Help
804(1)
Associating a Help File with Your Application
805(4)
Associating a Help topic with a VBA function
805(4)
Chapter 25 Developing User-Oriented Applications
809(16)
What is a User-Oriented Application?
809(1)
The Loan Amortization Wizard
809(11)
Using the Loan Amortization Wizard
810(2)
The Loan Amortization Wizard workbook structure
812(1)
How the Loan Amortization Wizard works
813(7)
Potential enhancements for the Loan Amortization Wizard
820(1)
Application Development Concepts
820(5)
Part VII Other Topics
Chapter 26 Compatibility Issues
825(14)
What Is Compatibility?
825(1)
Types of Compatibility Problems
826(1)
Avoid Using New Features
827(1)
But Will It Work on a Mac?
828(2)
Dealing with 64-bit Excel
830(1)
Creating an International Application
831(8)
Multilanguage applications
832(2)
VBA language considerations
834(1)
Using local properties
834(1)
Identifying system settings
834(3)
Date and time settings
837(2)
Chapter 27 Manipulating Files with VBA
839(32)
Performing Common File Operations
839(9)
Using VBA file-related statements
840(5)
Using the FileSystemObject object
845(3)
Displaying Extended File Information
848(2)
Working with Text Files
850(4)
Opening a text file
851(1)
Reading a text file
852(1)
Writing a text file
852(1)
Getting a file number
852(1)
Determining or setting the file position
853(1)
Statements for reading and writing
853(1)
Text File Manipulation Examples
854(11)
Importing data in a text file
854(2)
Exporting a range to a text file
856(1)
Importing a text file to a range
857(1)
Logging Excel usage
858(1)
Filtering a text file
859(1)
Exporting a range to HTML format
859(4)
Exporting a range to an XML file
863(2)
Zipping and Unzipping Files
865(3)
Zipping files
865(2)
Unzipping a File
867(1)
Working with ADO
868(3)
Chapter 28 Manipulating Visual Basic Components
871(24)
Introducing the IDE
871(2)
The IDE Object Model
873(3)
The VBProjects collection
874(2)
Displaying All Components in a VBA Project
876(1)
Listing All VBA Procedures in a Workbook
877(2)
Replacing a Module with an Updated Version
879(2)
Using VBA to Write VBA Code
881(2)
Adding Controls to a UserForm at Design Time
883(3)
Design-time versus runtime UserForm manipulations
884(1)
Adding 100 CommandButtons at design time
885(1)
Creating UserForms Programmatically
886(9)
A simple runtime UserForm example
887(1)
A useful (but not so simple) dynamic UserForm example
888(7)
Chapter 29 Understanding Class Modules
895(16)
What is a Class Module?
895(1)
Example: Creating a NumLock Class
896(5)
Inserting a class module
897(1)
Adding VBA code to the class module
897(3)
Using the NumLockClass class
900(1)
More about Class Modules
901(3)
Programming properties of objects
901(2)
Programming methods for objects
903(1)
Class module events
904(1)
Example: A CSV File Class
904(7)
Class module---level variables for the CSVFileClass
905(1)
Property procedures for the CSVFileClass
905(1)
Method procedures for the CSVFileClass
905(2)
Using the CSVFileClass object
907(4)
Chapter 30 Working with Colors
911(26)
Specifying Colors
911(5)
The RGB color system
912(1)
The HSL color system
913(1)
Converting colors
913(3)
Understanding Grayscale
916(3)
Converting colors to gray
916(2)
Viewing charts as grayscale
918(1)
Experimenting with Colors
919(2)
Understanding Document Themes
921(6)
About document themes
921(1)
Understanding document theme colors
921(4)
Displaying all theme colors
925(2)
Working with Shape Objects
927(6)
A shape's background color
928(2)
Shapes and theme colors
930(2)
Shape examples
932(1)
Modifying Chart Colors
933(4)
Chapter 31 Frequently Asked Questions about Excel Programming
937(32)
Getting the Scoop on FAQs
937(1)
General Excel Questions
938(4)
The Visual Basic Editor
942(2)
Procedures
944(4)
Functions
948(3)
Objects, Properties, Methods, and Events
951(8)
UserForms
959(3)
Add-Ins
962(2)
User Interface
964(5)
Part VIII Appendixes
Appendix A Excel Resources Online
969(8)
The Excel Help System
969(1)
Microsoft Technical Support
969(1)
Support options
970(1)
Microsoft Knowledge Base
970(1)
Microsoft Excel home page
970(1)
Microsoft Office home page
970(1)
Internet Newsgroups
971(1)
Accessing newsgroups by using a newsreader
971(1)
Accessing newsgroups by using a Web browser
971(1)
Searching newsgroups
972(1)
Internet Web sites
973(1)
The Spreadsheet Page
973(1)
Daily Dose of Excel
974(1)
Jon Peltier's Excel Page
974(1)
Pearson Software Consulting
974(1)
Contextures
974(1)
Pointy Haired Dilbert
974(1)
David McRitchie's Excel Pages
975(1)
Mr. Excel
975(2)
Appendix B VBA Statements and Functions Reference
977(8)
Invoking Excel functions in VBA instructions
980(5)
Appendix C VBA Error Codes
985(4)
Appendix D What's on the CD-ROM
989(18)
System Requirements
989(1)
Using the CD
989(1)
Files and Software on the CD
990(1)
Applications
990(1)
eBook version of Excel 2010 Power Programming with VBA
990(1)
Sample files for Excel 2010 Power Programming with VBA
990(15)
Troubleshooting
1005(2)
Index 1007(46)
End-User License Agreement 1053
John Walkenbach, arguably the foremost authority on Excel, has written hundreds of articles and created the award-winning Power Utility Pak. His 50-plus books include John Walkenbach's Favorite Excel 2010 Tips & Tricks, Excel 2010 Formulas, and the bestselling Excel Bible, all published by Wiley. Visit his popular Spreadsheet Page at spreadsheetpage.com.