| 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) |
|
|
|
12 | (2) |
|
|
|
14 | (1) |
|
|
|
15 | (5) |
|
|
|
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) |
|
|
|
24 | (4) |
|
|
|
24 | (2) |
|
|
|
26 | (1) |
|
|
|
26 | (2) |
|
|
|
28 | (1) |
|
|
|
28 | (9) |
|
|
|
28 | (6) |
|
Shortcut menus and the Mini Toolbar |
|
|
34 | (1) |
|
|
|
35 | (1) |
|
|
|
36 | (1) |
|
|
|
36 | (1) |
|
|
|
36 | (1) |
|
|
|
37 | (1) |
|
|
|
38 | (1) |
|
Formulas, Functions, and Names |
|
|
38 | (2) |
|
|
|
40 | (1) |
|
|
|
40 | (2) |
|
|
|
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) |
|
|
|
44 | (1) |
|
|
|
45 | (1) |
|
|
|
46 | (1) |
|
|
|
46 | (1) |
|
|
|
47 | (1) |
|
|
|
47 | (1) |
|
|
|
48 | (2) |
|
|
|
50 | (1) |
|
|
|
50 | (1) |
|
|
|
50 | (1) |
|
|
|
51 | (2) |
|
Chapter 3 Formula Tricks and Techniques |
|
|
53 | (24) |
|
|
|
53 | (1) |
|
|
|
54 | (1) |
|
Cell and Range References |
|
|
55 | (3) |
|
Why use references that aren't relative? |
|
|
55 | (1) |
|
|
|
56 | (1) |
|
Referencing other sheets or workbooks |
|
|
57 | (1) |
|
|
|
58 | (7) |
|
|
|
59 | (1) |
|
Applying names to existing references |
|
|
60 | (1) |
|
|
|
61 | (1) |
|
|
|
61 | (1) |
|
|
|
61 | (1) |
|
|
|
62 | (1) |
|
|
|
63 | (2) |
|
|
|
65 | (1) |
|
|
|
65 | (1) |
|
|
|
66 | (3) |
|
|
|
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) |
|
|
|
70 | (1) |
|
|
|
71 | (1) |
|
Working with Dates and Times |
|
|
71 | (3) |
|
|
|
72 | (1) |
|
|
|
73 | (1) |
|
|
|
74 | (3) |
|
Chapter 4 Understanding Excel Files |
|
|
77 | (24) |
|
|
|
77 | (3) |
|
|
|
80 | (3) |
|
|
|
80 | (1) |
|
|
|
81 | (1) |
|
|
|
81 | (1) |
|
|
|
82 | (1) |
|
Working with Template Files |
|
|
83 | (4) |
|
|
|
83 | (1) |
|
|
|
84 | (2) |
|
Creating workbook templates |
|
|
86 | (1) |
|
|
|
87 | (4) |
|
|
|
87 | (4) |
|
Why is the file format important? |
|
|
91 | (1) |
|
|
|
91 | (1) |
|
|
|
92 | (1) |
|
|
|
93 | (1) |
|
Excel Settings in the Registry |
|
|
94 | (7) |
|
|
|
94 | (1) |
|
|
|
95 | (6) |
|
Part II Excel Application Development |
|
|
|
Chapter 5 What Is a Spreadsheet Application? |
|
|
101 | (10) |
|
|
|
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) |
|
|
|
106 | (5) |
|
Quick-and-dirty spreadsheets |
|
|
106 | (1) |
|
For-your-eyes-only spreadsheets |
|
|
107 | (1) |
|
|
|
107 | (1) |
|
|
|
107 | (1) |
|
|
|
108 | (1) |
|
Add-ins that contain worksheet functions |
|
|
108 | (1) |
|
|
|
109 | (1) |
|
|
|
109 | (1) |
|
Data storage and access spreadsheets |
|
|
109 | (1) |
|
|
|
110 | (1) |
|
|
|
110 | (1) |
|
Chapter 6 Essentials of Spreadsheet Application Development |
|
|
111 | (24) |
|
Steps for Application Development |
|
|
111 | (1) |
|
|
|
112 | (1) |
|
Planning an Application That Meets User Needs |
|
|
113 | (2) |
|
Determining the Most Appropriate User Interface |
|
|
115 | (7) |
|
|
|
118 | (1) |
|
Customizing shortcut menus |
|
|
118 | (1) |
|
|
|
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) |
|
|
|
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) |
|
|
|
129 | (6) |
|
The user's installed version of Excel |
|
|
130 | (1) |
|
|
|
130 | (1) |
|
|
|
130 | (1) |
|
|
|
131 | (4) |
|
Part III Understanding Visual Basic for Applications |
|
|
|
Chapter 7 Introducing Visual Basic for Applications |
|
|
135 | (58) |
|
Getting Some BASIC Background |
|
|
135 | (1) |
|
|
|
136 | (1) |
|
|
|
136 | (1) |
|
|
|
136 | (1) |
|
Covering the Basics of VBA |
|
|
137 | (3) |
|
Introducing the Visual Basic Editor |
|
|
140 | (3) |
|
Displaying Excel's Developer tab |
|
|
141 | (1) |
|
|
|
141 | (1) |
|
|
|
142 | (1) |
|
Working with the Project Explorer |
|
|
143 | (2) |
|
|
|
144 | (1) |
|
|
|
145 | (1) |
|
Exporting and importing objects |
|
|
145 | (1) |
|
Working with Code Windows |
|
|
145 | (8) |
|
Minimizing and maximizing windows |
|
|
146 | (1) |
|
|
|
146 | (1) |
|
|
|
147 | (6) |
|
Customizing the VBE Environment |
|
|
153 | (6) |
|
|
|
154 | (2) |
|
Using the Editor Format tab |
|
|
156 | (1) |
|
|
|
157 | (1) |
|
|
|
158 | (1) |
|
|
|
159 | (8) |
|
What the macro recorder actually records |
|
|
160 | (1) |
|
Relative or absolute recording? |
|
|
161 | (3) |
|
|
|
164 | (1) |
|
Cleaning up recorded macros |
|
|
165 | (2) |
|
About Objects and Collections |
|
|
167 | (2) |
|
|
|
167 | (1) |
|
|
|
168 | (1) |
|
|
|
168 | (1) |
|
|
|
169 | (3) |
|
|
|
169 | (1) |
|
|
|
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) |
|
|
|
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) |
|
|
|
182 | (2) |
|
|
|
184 | (3) |
|
|
|
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) |
|
|
|
195 | (2) |
|
Variables, Data Types, and Constants |
|
|
197 | (13) |
|
|
|
198 | (3) |
|
|
|
201 | (2) |
|
|
|
203 | (3) |
|
|
|
206 | (3) |
|
|
|
209 | (1) |
|
|
|
209 | (1) |
|
|
|
210 | (3) |
|
|
|
213 | (2) |
|
|
|
213 | (1) |
|
Declaring multidimensional arrays |
|
|
214 | (1) |
|
|
|
214 | (1) |
|
|
|
215 | (1) |
|
|
|
216 | (1) |
|
|
|
217 | (3) |
|
Manipulating Objects and Collections |
|
|
220 | (3) |
|
|
|
220 | (1) |
|
|
|
221 | (2) |
|
Controlling Code Execution |
|
|
223 | (18) |
|
|
|
224 | (1) |
|
|
|
224 | (5) |
|
|
|
229 | (3) |
|
Looping blocks of instructions |
|
|
232 | (9) |
|
Chapter 9 Working with VBA Sub Procedures |
|
|
241 | (40) |
|
|
|
241 | (3) |
|
Declaring a Sub procedure |
|
|
242 | (1) |
|
|
|
243 | (1) |
|
|
|
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) |
|
|
|
259 | (2) |
|
|
|
261 | (3) |
|
A Realistic Example That Uses Sub Procedures |
|
|
264 | (17) |
|
|
|
264 | (1) |
|
|
|
264 | (1) |
|
|
|
265 | (1) |
|
|
|
265 | (1) |
|
|
|
266 | (1) |
|
Some preliminary recording |
|
|
266 | (2) |
|
|
|
268 | (1) |
|
|
|
269 | (1) |
|
Writing the Sort procedure |
|
|
270 | (4) |
|
|
|
274 | (1) |
|
|
|
275 | (4) |
|
|
|
279 | (1) |
|
|
|
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) |
|
|
|
287 | (5) |
|
|
|
288 | (1) |
|
Executing function procedures |
|
|
288 | (4) |
|
|
|
292 | (1) |
|
|
|
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) |
|
|
|
311 | (2) |
|
|
|
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) |
|
|
|
320 | (5) |
|
|
|
321 | (1) |
|
Determining the Windows directory |
|
|
321 | (1) |
|
|
|
322 | (1) |
|
Learning more about API functions |
|
|
323 | (2) |
|
Chapter 11 VBA Programming Examples and Techniques |
|
|
325 | (74) |
|
|
|
325 | (1) |
|
|
|
326 | (27) |
|
|
|
326 | (2) |
|
|
|
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) |
|
|
|
336 | (1) |
|
Determining the type of selected range |
|
|
337 | (2) |
|
Looping through a selected range efficiently |
|
|
339 | (3) |
|
|
|
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) |
|
|
|
350 | (2) |
|
Copying a noncontiguous range |
|
|
352 | (1) |
|
Working with Workbooks and Sheets |
|
|
353 | (4) |
|
|
|
354 | (1) |
|
Saving and closing all workbooks |
|
|
354 | (1) |
|
Hiding all but the selection |
|
|
354 | (2) |
|
|
|
356 | (1) |
|
|
|
357 | (8) |
|
Toggling a Boolean property |
|
|
357 | (1) |
|
Determining the number of printed pages |
|
|
358 | (1) |
|
Displaying the date and time |
|
|
358 | (2) |
|
|
|
360 | (2) |
|
|
|
362 | (1) |
|
Processing a series of files |
|
|
363 | (2) |
|
Some Useful Functions for Use in Your Code |
|
|
365 | (5) |
|
|
|
365 | (1) |
|
The FileNameOnly function |
|
|
365 | (1) |
|
|
|
366 | (1) |
|
The RangeNameExists function |
|
|
366 | (2) |
|
|
|
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) |
|
|
|
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) |
|
|
|
379 | (1) |
|
A multifunctional function |
|
|
380 | (1) |
|
|
|
381 | (1) |
|
Returning the maximum value across all worksheets |
|
|
381 | (2) |
|
Returning an array of nonduplicated random integers |
|
|
383 | (1) |
|
|
|
384 | (2) |
|
|
|
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) |
|
|
|
399 | (5) |
|
The VBA InputBox function |
|
|
400 | (2) |
|
The Excel InputBox method |
|
|
402 | (2) |
|
|
|
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) |
|
|
|
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) |
|
|
|
420 | (1) |
|
Adding Controls to a UserForm |
|
|
421 | (1) |
|
|
|
422 | (4) |
|
|
|
423 | (1) |
|
|
|
423 | (1) |
|
|
|
423 | (1) |
|
|
|
423 | (1) |
|
|
|
423 | (1) |
|
|
|
423 | (1) |
|
|
|
424 | (1) |
|
|
|
424 | (1) |
|
|
|
424 | (1) |
|
|
|
424 | (1) |
|
|
|
424 | (1) |
|
|
|
424 | (1) |
|
|
|
425 | (1) |
|
|
|
425 | (1) |
|
|
|
426 | (1) |
|
Adjusting UserForm Controls |
|
|
426 | (1) |
|
Adjusting a Control's Properties |
|
|
426 | (6) |
|
Using the Properties window |
|
|
428 | (1) |
|
|
|
429 | (1) |
|
Accommodating keyboard users |
|
|
430 | (2) |
|
|
|
432 | (2) |
|
Displaying a modeless UserForm |
|
|
433 | (1) |
|
Displaying a UserForm based on a variable |
|
|
433 | (1) |
|
|
|
433 | (1) |
|
About event-handler procedures |
|
|
433 | (1) |
|
|
|
434 | (1) |
|
Creating a UserForm: An Example |
|
|
435 | (7) |
|
|
|
436 | (2) |
|
Writing code to display the dialog box |
|
|
438 | (1) |
|
|
|
439 | (1) |
|
Adding event-handler procedures |
|
|
440 | (1) |
|
|
|
441 | (1) |
|
|
|
442 | (1) |
|
Understanding UserForm Events |
|
|
442 | (6) |
|
|
|
442 | (1) |
|
|
|
443 | (1) |
|
|
|
444 | (2) |
|
Pairing a SpinButton with a TextBox |
|
|
446 | (2) |
|
Referencing UserForm Controls |
|
|
448 | (2) |
|
|
|
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) |
|
|
|
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) |
|
|
|
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) |
|
|
|
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) |
|
|
|
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) |
|
|
|
489 | (4) |
|
Chapter 15 Advanced UserForm Techniques |
|
|
493 | (50) |
|
|
|
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) |
|
|
|
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) |
|
|
|
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) |
|
|
|
534 | (3) |
|
About the Enhanced Data Form |
|
|
536 | (1) |
|
Installing the Enhanced Data Form add-in |
|
|
537 | (1) |
|
|
|
537 | (1) |
|
Video Poker on a UserForm |
|
|
538 | (5) |
|
Part V Advanced Programming Techniques |
|
|
|
Chapter 16 Developing Excel Utilities with VBA |
|
|
543 | (22) |
|
|
|
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) |
|
|
|
547 | (1) |
|
How the Text Tools utility works |
|
|
548 | (1) |
|
The UserForm for the Text Tools utility |
|
|
548 | (2) |
|
|
|
550 | (2) |
|
The UserForm1 code module |
|
|
552 | (2) |
|
Making the Text Tools utility efficient |
|
|
554 | (1) |
|
Saving the Text Tools utility settings |
|
|
555 | (2) |
|
|
|
557 | (2) |
|
|
|
559 | (1) |
|
|
|
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) |
|
|
|
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) |
|
|
|
584 | (1) |
|
The macro recorder and charts |
|
|
584 | (1) |
|
|
|
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) |
|
|
|
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) |
|
|
|
598 | (2) |
|
|
|
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) |
|
|
|
621 | (1) |
|
Displaying text with the MouseOver event |
|
|
622 | (3) |
|
|
|
625 | (6) |
|
|
|
626 | (2) |
|
Creating a hypocycloid chart |
|
|
628 | (1) |
|
|
|
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) |
|
|
|
642 | (1) |
|
Entering event-handler code |
|
|
643 | (1) |
|
Event-handler procedures that use arguments |
|
|
644 | (2) |
|
Getting Acquainted with Workbook-Level Events |
|
|
646 | (8) |
|
|
|
647 | (1) |
|
|
|
648 | (1) |
|
|
|
648 | (1) |
|
|
|
649 | (1) |
|
|
|
649 | (1) |
|
|
|
650 | (1) |
|
|
|
650 | (2) |
|
|
|
652 | (2) |
|
Examining Worksheet Events |
|
|
654 | (8) |
|
|
|
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) |
|
|
|
669 | (1) |
|
Accessing Events Not Associated with an Object |
|
|
670 | (7) |
|
|
|
670 | (2) |
|
|
|
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) |
|
|
|
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) |
|
|
|
701 | (2) |
|
Chapter 21 Creating and Using Add-Ins |
|
|
703 | (30) |
|
|
|
703 | (3) |
|
Comparing an add-in with a standard workbook |
|
|
703 | (1) |
|
|
|
704 | (2) |
|
Understanding Excel's Add-In Manager |
|
|
706 | (1) |
|
|
|
707 | (1) |
|
|
|
708 | (6) |
|
Adding descriptive information for the example add-in |
|
|
709 | (1) |
|
|
|
710 | (1) |
|
|
|
710 | (2) |
|
|
|
712 | (1) |
|
|
|
712 | (1) |
|
|
|
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) |
|
|
|
722 | (3) |
|
Accessing an add-in as a workbook |
|
|
725 | (1) |
|
|
|
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) |
|
|
|
733 | (4) |
|
Using VBA with the Ribbon |
|
|
737 | (6) |
|
Accessing a Ribbon control |
|
|
738 | (2) |
|
|
|
740 | (2) |
|
|
|
742 | (1) |
|
|
|
743 | (21) |
|
|
|
743 | (3) |
|
A simple Ribbon example, take 2 |
|
|
746 | (5) |
|
|
|
751 | (3) |
|
|
|
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) |
|
|
|
765 | (4) |
|
Chapter 23 Working with Shortcut Menus |
|
|
769 | (20) |
|
|
|
769 | (8) |
|
|
|
770 | (1) |
|
|
|
770 | (1) |
|
|
|
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) |
|
|
|
799 | (1) |
|
|
|
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) |
|
|
|
|
Chapter 26 Compatibility Issues |
|
|
825 | (14) |
|
|
|
825 | (1) |
|
Types of Compatibility Problems |
|
|
826 | (1) |
|
|
|
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) |
|
|
|
834 | (1) |
|
Identifying system settings |
|
|
834 | (3) |
|
|
|
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) |
|
|
|
850 | (4) |
|
|
|
851 | (1) |
|
|
|
852 | (1) |
|
|
|
852 | (1) |
|
|
|
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) |
|
|
|
858 | (1) |
|
|
|
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) |
|
|
|
865 | (2) |
|
|
|
867 | (1) |
|
|
|
868 | (3) |
|
Chapter 28 Manipulating Visual Basic Components |
|
|
871 | (24) |
|
|
|
871 | (2) |
|
|
|
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) |
|
|
|
895 | (1) |
|
Example: Creating a NumLock Class |
|
|
896 | (5) |
|
|
|
897 | (1) |
|
Adding VBA code to the class module |
|
|
897 | (3) |
|
Using the NumLockClass class |
|
|
900 | (1) |
|
|
|
901 | (3) |
|
Programming properties of objects |
|
|
901 | (2) |
|
Programming methods for objects |
|
|
903 | (1) |
|
|
|
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) |
|
|
|
911 | (5) |
|
|
|
912 | (1) |
|
|
|
913 | (1) |
|
|
|
913 | (3) |
|
|
|
916 | (3) |
|
Converting colors to gray |
|
|
916 | (2) |
|
Viewing charts as grayscale |
|
|
918 | (1) |
|
Experimenting with Colors |
|
|
919 | (2) |
|
Understanding Document Themes |
|
|
921 | (6) |
|
|
|
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) |
|
|
|
930 | (2) |
|
|
|
932 | (1) |
|
|
|
933 | (4) |
|
Chapter 31 Frequently Asked Questions about Excel Programming |
|
|
937 | (32) |
|
Getting the Scoop on FAQs |
|
|
937 | (1) |
|
|
|
938 | (4) |
|
|
|
942 | (2) |
|
|
|
944 | (4) |
|
|
|
948 | (3) |
|
Objects, Properties, Methods, and Events |
|
|
951 | (8) |
|
|
|
959 | (3) |
|
|
|
962 | (2) |
|
|
|
964 | (5) |
|
|
|
|
Appendix A Excel Resources Online |
|
|
969 | (8) |
|
|
|
969 | (1) |
|
Microsoft Technical Support |
|
|
969 | (1) |
|
|
|
970 | (1) |
|
|
|
970 | (1) |
|
Microsoft Excel home page |
|
|
970 | (1) |
|
Microsoft Office home page |
|
|
970 | (1) |
|
|
|
971 | (1) |
|
Accessing newsgroups by using a newsreader |
|
|
971 | (1) |
|
Accessing newsgroups by using a Web browser |
|
|
971 | (1) |
|
|
|
972 | (1) |
|
|
|
973 | (1) |
|
|
|
973 | (1) |
|
|
|
974 | (1) |
|
|
|
974 | (1) |
|
Pearson Software Consulting |
|
|
974 | (1) |
|
|
|
974 | (1) |
|
|
|
974 | (1) |
|
David McRitchie's Excel Pages |
|
|
975 | (1) |
|
|
|
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) |
|
|
|
989 | (1) |
|
|
|
989 | (1) |
|
Files and Software on the CD |
|
|
990 | (1) |
|
|
|
990 | (1) |
|
eBook version of Excel 2010 Power Programming with VBA |
|
|
990 | (1) |
|
Sample files for Excel 2010 Power Programming with VBA |
|
|
990 | (15) |
|
|
|
1005 | (2) |
| Index |
|
1007 | (46) |
| End-User License Agreement |
|
1053 | |