| Introduction |
|
xxvii | |
| Part I: Introduction to Excel VBA |
|
|
Chapter 1 Essentials of Spreadsheet Application Development |
|
|
3 | (16) |
|
What Is a Spreadsheet Application? |
|
|
3 | (1) |
|
Steps for Application Development |
|
|
4 | (1) |
|
|
|
5 | (1) |
|
Planning an Application That Meets User Needs |
|
|
6 | (2) |
|
Determining the Most Appropriate User Interface |
|
|
8 | (4) |
|
|
|
8 | (1) |
|
Customizing shortcut menus |
|
|
8 | (1) |
|
|
|
9 | (1) |
|
Creating custom dialog boxes |
|
|
9 | (1) |
|
Using ActiveX controls on a worksheet |
|
|
10 | (1) |
|
Executing the development effort |
|
|
11 | (1) |
|
Concerning Yourself with the End User |
|
|
12 | (5) |
|
|
|
12 | (1) |
|
Making the application bulletproof |
|
|
13 | (2) |
|
Making the application aesthetically appealing and intuitive |
|
|
15 | (1) |
|
Creating a user Help system |
|
|
16 | (1) |
|
Documenting the development effort |
|
|
16 | (1) |
|
Distributing the application to the user |
|
|
16 | (1) |
|
Updating the application when necessary |
|
|
17 | (1) |
|
|
|
17 | (2) |
|
The user's installed version of Excel |
|
|
17 | (1) |
|
|
|
17 | (1) |
|
|
|
18 | (1) |
|
|
|
18 | (1) |
|
Chapter 2 Introducing Visual Basic for Applications |
|
|
19 | (42) |
|
Getting a Head Start with the Macro Recorder |
|
|
19 | (13) |
|
Creating your first macro |
|
|
20 | (3) |
|
Comparing absolute and relative macro recording |
|
|
23 | (4) |
|
Other macro recording concepts |
|
|
27 | (5) |
|
Working with the Visual Basic Editor |
|
|
32 | (10) |
|
Understanding VBE components |
|
|
32 | (1) |
|
Working with the Project window |
|
|
33 | (3) |
|
Working with a Code window |
|
|
36 | (2) |
|
Customizing the VBA environment |
|
|
38 | (2) |
|
|
|
40 | (1) |
|
|
|
41 | (1) |
|
|
|
41 | (1) |
|
|
|
42 | (5) |
|
|
|
43 | (1) |
|
Understanding collections |
|
|
43 | (1) |
|
|
|
44 | (3) |
|
Deep Dive: Working with Range Objects |
|
|
47 | (6) |
|
Finding the properties of the Range object |
|
|
48 | (1) |
|
|
|
48 | (1) |
|
|
|
49 | (3) |
|
|
|
52 | (1) |
|
Essential Concepts to Remember |
|
|
53 | (2) |
|
Don't Panic-You Are Not Alone |
|
|
55 | (6) |
|
Read the rest of the book |
|
|
55 | (1) |
|
Let Excel help write your macro |
|
|
55 | (1) |
|
|
|
55 | (1) |
|
|
|
56 | (1) |
|
Pilfer code from the Internet |
|
|
57 | (1) |
|
|
|
57 | (1) |
|
|
|
58 | (1) |
|
Mine YouTube for video training |
|
|
58 | (1) |
|
Learn from the Microsoft Office Dev Center |
|
|
59 | (1) |
|
Dissect the other Excel files in your organization |
|
|
59 | (1) |
|
Ask your local Excel genius |
|
|
59 | (2) |
|
Chapter 3 VBA Programming Fundamentals |
|
|
61 | (44) |
|
VBA Language Elements: An Overview |
|
|
61 | (2) |
|
|
|
63 | (1) |
|
Variables, Data Types, and Constants |
|
|
64 | (11) |
|
|
|
65 | (2) |
|
|
|
67 | (2) |
|
|
|
69 | (3) |
|
|
|
72 | (1) |
|
|
|
73 | (1) |
|
|
|
74 | (1) |
|
|
|
75 | (2) |
|
|
|
77 | (2) |
|
|
|
77 | (1) |
|
Declaring multidimensional arrays |
|
|
78 | (1) |
|
|
|
79 | (1) |
|
|
|
79 | (1) |
|
|
|
80 | (1) |
|
|
|
81 | (3) |
|
Manipulating Objects and Collections |
|
|
84 | (3) |
|
|
|
84 | (1) |
|
|
|
85 | (2) |
|
Controlling Code Execution |
|
|
87 | (18) |
|
|
|
88 | (1) |
|
|
|
88 | (5) |
|
|
|
93 | (3) |
|
Looping blocks of instructions |
|
|
96 | (9) |
|
Chapter 4 Working with VBA Sub Procedures |
|
|
105 | (38) |
|
|
|
105 | (3) |
|
Declaring a Sub procedure |
|
|
106 | (1) |
|
|
|
107 | (1) |
|
|
|
108 | (11) |
|
Executing a procedure with the Run Sub/UserForm command |
|
|
109 | (1) |
|
Executing a procedure from the Macro dialog box |
|
|
109 | (1) |
|
Executing a procedure with a Ctrl+shortcut key combination |
|
|
110 | (1) |
|
Executing a procedure from the Ribbon |
|
|
111 | (1) |
|
Executing a procedure from a customized shortcut menu |
|
|
111 | (1) |
|
Executing a procedure from another procedure |
|
|
112 | (4) |
|
Executing a procedure by clicking an object |
|
|
116 | (2) |
|
Executing a procedure when an event occurs |
|
|
118 | (1) |
|
Executing a procedure from the Immediate window |
|
|
118 | (1) |
|
Passing Arguments to Procedures |
|
|
119 | (4) |
|
Error-Handling Techniques |
|
|
123 | (4) |
|
|
|
123 | (1) |
|
|
|
124 | (3) |
|
A Realistic Example That Uses Sub Procedures |
|
|
127 | (16) |
|
|
|
128 | (1) |
|
|
|
128 | (1) |
|
|
|
128 | (1) |
|
|
|
129 | (1) |
|
Some preliminary recording |
|
|
129 | (2) |
|
|
|
131 | (1) |
|
|
|
132 | (1) |
|
Writing the Sort procedure |
|
|
133 | (4) |
|
|
|
137 | (1) |
|
|
|
138 | (3) |
|
|
|
141 | (1) |
|
|
|
142 | (1) |
|
Chapter 5 Creating Function Procedures |
|
|
143 | (40) |
|
Sub Procedures versus Function Procedures |
|
|
143 | (1) |
|
Why Create Custom Functions? |
|
|
144 | (1) |
|
An Introductory Function Example |
|
|
144 | (4) |
|
Using the function in a worksheet |
|
|
145 | (1) |
|
Using the function in a VBA procedure |
|
|
146 | (1) |
|
Analyzing the custom function |
|
|
146 | (2) |
|
|
|
148 | (5) |
|
|
|
150 | (1) |
|
Executing function procedures |
|
|
150 | (3) |
|
|
|
153 | (1) |
|
|
|
153 | (1) |
|
Functions with no argument |
|
|
153 | (15) |
|
A function with one argument |
|
|
156 | (3) |
|
A function with two arguments |
|
|
159 | (1) |
|
A function with an array argument |
|
|
159 | (1) |
|
A function with optional arguments |
|
|
160 | (2) |
|
A function that returns a VBA array |
|
|
162 | (3) |
|
A function that returns an error value |
|
|
165 | (1) |
|
A function with an indefinite number of arguments |
|
|
166 | (2) |
|
Emulating Excel's SUM Function |
|
|
168 | (3) |
|
|
|
171 | (2) |
|
|
|
173 | (1) |
|
Dealing with the Insert Function Dialog Box |
|
|
174 | (4) |
|
Using the MacroOptions method |
|
|
174 | (2) |
|
Specifying a function category |
|
|
176 | (1) |
|
Adding a function description manually |
|
|
177 | (1) |
|
Using Add-Ins to Store Custom Functions |
|
|
178 | (1) |
|
|
|
179 | (4) |
|
|
|
179 | (1) |
|
Determining the Windows directory |
|
|
180 | (1) |
|
|
|
181 | (1) |
|
Learning more about API functions |
|
|
182 | (1) |
|
Chapter 6 Understanding Excel's Events |
|
|
183 | (34) |
|
What You Should Know about Events |
|
|
183 | (7) |
|
Understanding event sequences |
|
|
184 | (1) |
|
Where to put event-handler procedures |
|
|
184 | (2) |
|
|
|
186 | (1) |
|
Entering event-handler code |
|
|
187 | (1) |
|
Event-handler procedures that use arguments |
|
|
188 | (2) |
|
Getting Acquainted with Workbook-Level Events |
|
|
190 | (7) |
|
|
|
191 | (1) |
|
|
|
192 | (1) |
|
|
|
192 | (1) |
|
|
|
193 | (1) |
|
|
|
193 | (1) |
|
|
|
193 | (1) |
|
|
|
194 | (1) |
|
|
|
195 | (2) |
|
Examining Worksheet Events |
|
|
197 | (9) |
|
|
|
198 | (1) |
|
Monitoring a specific range for changes |
|
|
199 | (4) |
|
The SelectionChange event |
|
|
203 | (1) |
|
The BeforeDoubleClick event |
|
|
204 | (1) |
|
The BeforeRightClick event |
|
|
205 | (1) |
|
Monitoring with Application Events |
|
|
206 | (4) |
|
Enabling Application-level events |
|
|
207 | (1) |
|
Determining when a workbook is opened |
|
|
208 | (1) |
|
Monitoring Application-level events |
|
|
209 | (1) |
|
Accessing Events Not Associated with an Object |
|
|
210 | (7) |
|
|
|
210 | (2) |
|
|
|
212 | (5) |
|
Chapter 7 VBA Programming Examples and Techniques |
|
|
217 | (74) |
|
|
|
217 | (1) |
|
|
|
218 | (28) |
|
|
|
218 | (1) |
|
|
|
219 | (1) |
|
Copying a variably sized range |
|
|
220 | (1) |
|
Selecting or otherwise identifying various types of ranges |
|
|
221 | (2) |
|
|
|
223 | (1) |
|
Prompting for a cell value |
|
|
224 | (1) |
|
Entering a value in the next empty cell |
|
|
225 | (1) |
|
Pausing a macro to get a user-selected range |
|
|
226 | (2) |
|
|
|
228 | (1) |
|
Determining the type of selected range |
|
|
229 | (2) |
|
Looping through a selected range efficiently |
|
|
231 | (2) |
|
|
|
233 | (1) |
|
Duplicating rows a variable number of times |
|
|
234 | (2) |
|
Determining whether a range is contained in another range |
|
|
236 | (1) |
|
Determining a cell's data type |
|
|
237 | (1) |
|
Reading and writing ranges |
|
|
238 | (2) |
|
A better way to write to a range |
|
|
240 | (2) |
|
Transferring one-dimensional arrays |
|
|
242 | (1) |
|
Transferring a range to a variant array |
|
|
242 | (1) |
|
|
|
243 | (1) |
|
Copying a noncontiguous range |
|
|
244 | (2) |
|
Working with Workbooks and Sheets |
|
|
246 | (5) |
|
|
|
246 | (1) |
|
Saving and closing all workbooks |
|
|
247 | (1) |
|
Hiding all but the selection |
|
|
247 | (2) |
|
Creating a hyperlink table of contents |
|
|
249 | (1) |
|
|
|
250 | (1) |
|
|
|
251 | (8) |
|
Toggling a Boolean property |
|
|
251 | (1) |
|
Displaying the date and time |
|
|
251 | (2) |
|
|
|
253 | (1) |
|
|
|
254 | (2) |
|
|
|
256 | (1) |
|
Processing a series of files |
|
|
257 | (2) |
|
Some Useful Functions for Use in Your Code |
|
|
259 | (5) |
|
|
|
259 | (1) |
|
The FileNameOnly function |
|
|
259 | (1) |
|
|
|
260 | (1) |
|
The RangeNameExists function |
|
|
260 | (1) |
|
|
|
261 | (1) |
|
The WorkbooklsOpen function |
|
|
261 | (1) |
|
Retrieving a value from a closed workbook |
|
|
262 | (2) |
|
Some Useful Worksheet Functions |
|
|
264 | (16) |
|
Returning cell formatting information |
|
|
264 | (2) |
|
|
|
266 | (1) |
|
Displaying the date when a file was saved or printed |
|
|
266 | (1) |
|
Understanding object parents |
|
|
267 | (1) |
|
Counting cells between two values |
|
|
268 | (1) |
|
Determining the last nonempty cell in a column or row |
|
|
269 | (1) |
|
Does a string match a pattern? |
|
|
270 | (2) |
|
Extracting the nth element from a string |
|
|
272 | (1) |
|
|
|
272 | (1) |
|
A multifunctional function |
|
|
273 | (1) |
|
|
|
274 | (1) |
|
Returning the maximum value across all worksheets |
|
|
275 | (1) |
|
Returning an array of nonduplicated random integers |
|
|
276 | (2) |
|
|
|
278 | (1) |
|
|
|
279 | (1) |
|
|
|
280 | (11) |
|
Understanding API Declarations |
|
|
280 | (1) |
|
Determining file associations |
|
|
281 | (1) |
|
Determining default printer information |
|
|
282 | (1) |
|
Determining video display information |
|
|
283 | (1) |
|
Reading from and writing to the Registry |
|
|
284 | (7) |
| Part II: Advanced VBA Techniques |
|
|
Chapter 8 Working with Pivot Tables |
|
|
291 | (16) |
|
An Introductory Pivot Table Example |
|
|
291 | (6) |
|
|
|
292 | (2) |
|
Examining the recorded code for the pivot table |
|
|
294 | (1) |
|
Cleaning up the recorded pivot table code |
|
|
294 | (3) |
|
Creating a More Complex Pivot Table |
|
|
297 | (4) |
|
The code that created the pivot table |
|
|
298 | (1) |
|
How the more complex pivot table works |
|
|
299 | (2) |
|
Creating Multiple Pivot Tables |
|
|
301 | (3) |
|
Creating a Reverse Pivot Table |
|
|
304 | (3) |
|
Chapter 9 Working with Charts |
|
|
307 | (48) |
|
Getting the Inside Scoop on Charts |
|
|
307 | (3) |
|
|
|
307 | (1) |
|
The macro recorder and charts |
|
|
308 | (1) |
|
|
|
308 | (2) |
|
Creating an Embedded Chart |
|
|
310 | (1) |
|
Creating a Chart on a Chart Sheet |
|
|
311 | (1) |
|
|
|
312 | (1) |
|
Using VBA to Activate a Chart |
|
|
313 | (1) |
|
|
|
314 | (1) |
|
Using VBA to Deactivate a Chart |
|
|
315 | (1) |
|
Determining Whether a Chart Is Activated |
|
|
316 | (1) |
|
Deleting from the ChartObjects or Charts Collection |
|
|
316 | (2) |
|
Looping through All Charts |
|
|
318 | (2) |
|
Sizing and Aligning ChartObjects |
|
|
320 | (1) |
|
|
|
321 | (3) |
|
|
|
324 | (2) |
|
|
|
325 | (1) |
|
Changing the Data Used in a Chart |
|
|
326 | (5) |
|
Changing chart data based on the active cell |
|
|
327 | (2) |
|
Using VBA to determine the ranges used in a chart |
|
|
329 | (2) |
|
Using VBA to Display Arbitrary Data Labels on a Chart |
|
|
331 | (4) |
|
Displaying a Chart in a UserForm |
|
|
335 | (2) |
|
Understanding Chart Events |
|
|
337 | (7) |
|
An example of using Chart events |
|
|
338 | (2) |
|
Enabling events for an embedded chart |
|
|
340 | (2) |
|
Example: Using Chart events with an embedded chart |
|
|
342 | (2) |
|
Discovering VBA Charting Tricks |
|
|
344 | (7) |
|
Printing embedded charts on a full page |
|
|
344 | (1) |
|
|
|
344 | (2) |
|
Displaying text with the MouseOver event |
|
|
346 | (3) |
|
|
|
349 | (2) |
|
Working with Sparkline Charts |
|
|
351 | (4) |
|
Chapter 10 Interacting with Other Applications |
|
|
355 | (24) |
|
Understanding Microsoft Office Automation |
|
|
355 | (4) |
|
Understanding the concept of binding |
|
|
356 | (2) |
|
A simple automation example |
|
|
358 | (1) |
|
Automating Access from Excel |
|
|
359 | (2) |
|
Running an Access Query from Excel |
|
|
359 | (1) |
|
Running an Access Macro from Excel |
|
|
360 | (1) |
|
Automating Word from Excel |
|
|
361 | (3) |
|
Sending Excel data to a Word document |
|
|
361 | (1) |
|
Simulating mail merge with a Word document |
|
|
362 | (2) |
|
Automating PowerPoint from Excel |
|
|
364 | (5) |
|
Sending Excel data to a PowerPoint presentation |
|
|
365 | (1) |
|
Sending all Excel charts to a PowerPoint presentation |
|
|
366 | (1) |
|
Convert a workbook into a PowerPoint presentation |
|
|
367 | (2) |
|
Automating Outlook from Excel |
|
|
369 | (4) |
|
Mailing the Active Workbook as an Attachment |
|
|
369 | (1) |
|
Mailing a Specific Range as an Attachment |
|
|
370 | (1) |
|
Mailing a Single Sheet as an Attachment |
|
|
371 | (1) |
|
Mailing All Email Addresses in Your Contact List |
|
|
372 | (1) |
|
Starting Other Applications from Excel |
|
|
373 | (6) |
|
Using the VBA Shell function |
|
|
373 | (3) |
|
Using the Windows ShellExecute API function |
|
|
376 | (1) |
|
|
|
377 | (1) |
|
Running Control Panel dialog boxes |
|
|
378 | (1) |
|
Chapter 11 Working with External Data and Files |
|
|
379 | (36) |
|
Working with External Data Connections |
|
|
379 | (5) |
|
Manually creating a connection |
|
|
379 | (4) |
|
Manually editing data connections |
|
|
383 | (1) |
|
Using VBA to create dynamic connections |
|
|
384 | (10) |
|
Iterating through all connections in a workbook |
|
|
386 | (1) |
|
Using ADO and VBA to Pull External Data |
|
|
387 | (1) |
|
|
|
388 | (1) |
|
|
|
389 | (1) |
|
Referencing the ADO object library |
|
|
390 | (1) |
|
Putting it all together in code |
|
|
391 | (1) |
|
Using ADO with the active workbook |
|
|
392 | (2) |
|
|
|
394 | (4) |
|
|
|
395 | (1) |
|
|
|
396 | (1) |
|
|
|
396 | (1) |
|
|
|
396 | (1) |
|
Determining or setting the file position |
|
|
397 | (1) |
|
Statements for reading and writing |
|
|
397 | (1) |
|
Text File Manipulation Examples |
|
|
398 | (4) |
|
Importing data in a text file |
|
|
398 | (1) |
|
Exporting a range to a text file |
|
|
398 | (1) |
|
Importing a text file to a range |
|
|
399 | (1) |
|
|
|
400 | (1) |
|
|
|
401 | (1) |
|
Performing Common File Operations |
|
|
402 | (8) |
|
Using VBA file-related statements |
|
|
402 | (5) |
|
Using the FileSystemObject object |
|
|
407 | (3) |
|
Zipping and Unzipping Files |
|
|
410 | (5) |
|
|
|
410 | (1) |
|
|
|
411 | (4) |
| Part III: Working with UserForms |
|
|
Chapter 12 Leveraging Custom Dialog Boxes |
|
|
415 | (20) |
|
Before You Create That UserForm |
|
|
415 | (1) |
|
|
|
415 | (6) |
|
The VBA InputBox function |
|
|
416 | (2) |
|
The ApplicationinputBox method |
|
|
418 | (3) |
|
|
|
421 | (5) |
|
The Excel GetOpenFilename Method |
|
|
426 | (3) |
|
The Excel GetSaveAsFilename Method |
|
|
429 | (1) |
|
Prompting for a Directory |
|
|
430 | (1) |
|
Displaying Excel's Built-In Dialog Boxes |
|
|
430 | (3) |
|
|
|
433 | (2) |
|
Making the data form accessible |
|
|
434 | (1) |
|
Displaying a data form by using VBA |
|
|
434 | (1) |
|
Chapter 13 Introducing UserForms |
|
|
435 | (36) |
|
How Excel Handles Custom Dialog Boxes |
|
|
435 | (1) |
|
|
|
436 | (1) |
|
Adding Controls to a UserForm |
|
|
437 | (1) |
|
|
|
437 | (5) |
|
|
|
438 | (1) |
|
|
|
438 | (1) |
|
|
|
438 | (1) |
|
|
|
439 | (1) |
|
|
|
439 | (1) |
|
|
|
439 | (1) |
|
|
|
439 | (1) |
|
|
|
439 | (1) |
|
|
|
439 | (1) |
|
|
|
440 | (1) |
|
|
|
440 | (1) |
|
|
|
440 | (1) |
|
|
|
440 | (1) |
|
|
|
440 | (1) |
|
|
|
440 | (2) |
|
Adjusting UserForm Controls |
|
|
442 | (1) |
|
Adjusting a Control's Properties |
|
|
443 | (6) |
|
Using the Properties window |
|
|
443 | (2) |
|
|
|
445 | (2) |
|
Accommodating keyboard users |
|
|
447 | (2) |
|
|
|
449 | (2) |
|
Adjusting the display position |
|
|
449 | (1) |
|
Displaying a modeless UserForm |
|
|
450 | (1) |
|
Displaying a UserForm based on a variable |
|
|
450 | (1) |
|
|
|
450 | (1) |
|
About event-handler procedures |
|
|
451 | (1) |
|
|
|
451 | (2) |
|
Creating a UserForm: An Example |
|
|
453 | (6) |
|
|
|
453 | (2) |
|
Writing code to display the dialog box |
|
|
455 | (1) |
|
|
|
456 | (1) |
|
Adding event-handler procedures |
|
|
457 | (2) |
|
|
|
459 | (1) |
|
Understanding UserForm Events |
|
|
459 | (6) |
|
|
|
459 | (1) |
|
|
|
460 | (1) |
|
|
|
461 | (1) |
|
Pairing a SpinButton with a TextBox |
|
|
462 | (3) |
|
Referencing UserForm Controls |
|
|
465 | (1) |
|
|
|
466 | (3) |
|
Adding new pages to the Toolbox |
|
|
467 | (1) |
|
Customizing or combining controls |
|
|
467 | (1) |
|
Adding new ActiveX controls |
|
|
468 | (1) |
|
Creating UserForm Templates |
|
|
469 | (1) |
|
|
|
469 | (2) |
|
Chapter 14 UserForm Examples |
|
|
471 | (40) |
|
Creating a UserForm "Menu" |
|
|
471 | (3) |
|
Using CommandButtons in a UserForm |
|
|
471 | (1) |
|
Using a ListBox in a UserForm |
|
|
472 | (2) |
|
Selecting Ranges from a UserForm |
|
|
474 | (2) |
|
|
|
476 | (2) |
|
Disabling a UserForm's Close Button |
|
|
478 | (1) |
|
Changing a UserForm's Size |
|
|
479 | (1) |
|
Zooming and Scrolling a Sheet from a UserForm |
|
|
480 | (2) |
|
|
|
482 | (21) |
|
Adding items to a ListBox control |
|
|
483 | (4) |
|
Determining the selected item in a ListBox |
|
|
487 | (1) |
|
Determining multiple selections in a ListBox |
|
|
488 | (1) |
|
Multiple lists in a single ListBox |
|
|
489 | (1) |
|
|
|
490 | (2) |
|
Moving items in a ListBox |
|
|
492 | (2) |
|
Working with multicolumn ListBox controls |
|
|
494 | (2) |
|
Using a ListBox to select worksheet rows |
|
|
496 | (2) |
|
Using a ListBox to activate a sheet |
|
|
498 | (3) |
|
Filtering a ListBox from a TextBox |
|
|
501 | (2) |
|
Using the MultiPage Control in a UserForm |
|
|
503 | (1) |
|
Using an External Control |
|
|
504 | (2) |
|
|
|
506 | (5) |
|
Chapter 15 Advanced UserForm Techniques |
|
|
511 | (50) |
|
|
|
512 | (4) |
|
Displaying a Progress Indicator |
|
|
516 | (11) |
|
Creating a stand-alone progress indicator |
|
|
517 | (3) |
|
Showing a progress indicator that's integrated into a UserForm |
|
|
520 | (4) |
|
Creating a non-graphical progress indicator |
|
|
524 | (3) |
|
|
|
527 | (6) |
|
Setting up the MultiPage control for the wizard |
|
|
528 | (1) |
|
Adding the buttons to the wizard's UserForm |
|
|
528 | (1) |
|
Programming the wizard's buttons |
|
|
528 | (2) |
|
Programming dependencies in a wizard |
|
|
530 | (2) |
|
Performing the task with the wizard |
|
|
532 | (1) |
|
Emulating the MsgBox Function |
|
|
533 | (4) |
|
MsgBox emulation: MyMsgBox code |
|
|
533 | (2) |
|
How the MyMsgBox function works |
|
|
535 | (2) |
|
Using the MyMsgBox function |
|
|
537 | (1) |
|
A UserForm with Movable Controls |
|
|
537 | (1) |
|
A UserForm with No Title Bar |
|
|
538 | (2) |
|
Simulating a Toolbar with a UserForm |
|
|
540 | (2) |
|
Emulating a Task Pane with a UserForm |
|
|
542 | (1) |
|
|
|
543 | (4) |
|
Handling Multiple UserForm Controls with One Event Handler |
|
|
547 | (3) |
|
Selecting a Color in a UserForm |
|
|
550 | (2) |
|
Displaying a Chart in a UserForm |
|
|
552 | (2) |
|
Saving a chart as a GIF file |
|
|
553 | (1) |
|
Changing the Image control's Picture property |
|
|
554 | (1) |
|
Making a UserForm Semitransparent |
|
|
554 | (2) |
|
|
|
556 | (1) |
|
Video Poker on a UserForm |
|
|
557 | (4) |
| Part IV: Developing Excel Applications |
|
|
Chapter 16 Creating and Using Add-Ins |
|
|
561 | (28) |
|
|
|
561 | (3) |
|
Comparing an add-in with a standard workbook |
|
|
561 | (1) |
|
|
|
562 | (2) |
|
Understanding Excel's Add-In Manager |
|
|
564 | (2) |
|
|
|
566 | (1) |
|
|
|
567 | (5) |
|
Adding descriptive information for the example add-in |
|
|
568 | (1) |
|
|
|
568 | (2) |
|
|
|
570 | (1) |
|
|
|
571 | (1) |
|
|
|
571 | (1) |
|
|
|
571 | (1) |
|
Comparing XLAM and XLSM Files |
|
|
572 | (6) |
|
XLAM file VBA collection membership |
|
|
573 | (1) |
|
Visibility of XLSM and XLAM files |
|
|
573 | (1) |
|
Worksheets and chart sheets in XLSM and XLAM files |
|
|
574 | (1) |
|
Accessing VBA procedures in an add-in |
|
|
575 | (3) |
|
Manipulating Add-Ins with VBA |
|
|
578 | (6) |
|
Adding an item to the AddIns collection |
|
|
579 | (1) |
|
Removing an item from the AddIns collection |
|
|
580 | (1) |
|
|
|
580 | (3) |
|
Accessing an add-in as a workbook |
|
|
583 | (1) |
|
|
|
584 | (1) |
|
Optimizing the Performance of Add-Ins |
|
|
584 | (5) |
|
Special Problems with Add-Ins |
|
|
585 | (1) |
|
Ensuring that an add-in is installed |
|
|
585 | (2) |
|
Referencing other files from an add-in |
|
|
587 | (1) |
|
Detecting the proper Excel version for your add-in |
|
|
588 | (1) |
|
Chapter 17 Working with the Ribbon |
|
|
589 | (36) |
|
|
|
589 | (2) |
|
|
|
591 | (5) |
|
Adding a button to the Ribbon |
|
|
591 | (3) |
|
Adding a button to the Quick Access Toolbar |
|
|
594 | (1) |
|
Understanding the limitations of Ribbon customization |
|
|
595 | (1) |
|
|
|
596 | (21) |
|
Adding a button to an existing tab |
|
|
596 | (6) |
|
Adding a check box to an existing tab |
|
|
602 | (3) |
|
|
|
605 | (8) |
|
A dynamicMenu control example |
|
|
613 | (3) |
|
More on Ribbon customization |
|
|
616 | (1) |
|
Using VBA with the Ribbon |
|
|
617 | (4) |
|
Accessing a Ribbon control |
|
|
617 | (2) |
|
|
|
619 | (2) |
|
|
|
621 | (1) |
|
Creating an Old-Style Toolbar |
|
|
621 | (4) |
|
Limitations of old-style toolbars in Excel 2007 and later |
|
|
622 | (1) |
|
|
|
622 | (3) |
|
Chapter 18 Working with Shortcut Menus |
|
|
625 | (22) |
|
|
|
625 | (7) |
|
|
|
626 | (1) |
|
|
|
626 | (1) |
|
|
|
627 | (1) |
|
Referring to controls in a CommandBar |
|
|
628 | (2) |
|
Properties of CommandBar controls |
|
|
630 | (1) |
|
Displaying all shortcut menu items |
|
|
630 | (2) |
|
Using VBA to Customize Shortcut Menus |
|
|
632 | (10) |
|
Shortcut menu and the single-document interface |
|
|
633 | (1) |
|
Resetting a shortcut menu |
|
|
634 | (2) |
|
Disabling a shortcut menu |
|
|
636 | (1) |
|
Disabling shortcut menu items |
|
|
636 | (1) |
|
Adding a new item to the Cell shortcut menu |
|
|
637 | (2) |
|
Adding a submenu to a shortcut menu |
|
|
639 | (3) |
|
Limiting a shortcut menu to a single workbook |
|
|
642 | (1) |
|
Shortcut Menus and Events |
|
|
642 | (5) |
|
Adding and deleting menus automatically |
|
|
642 | (1) |
|
Disabling or hiding shortcut menu items |
|
|
643 | (1) |
|
Creating a context-sensitive shortcut menu |
|
|
643 | (4) |
|
Chapter 19 Providing Help for Your Applications |
|
|
647 | (18) |
|
Help for Your Excel Applications |
|
|
647 | (2) |
|
Help Systems That Use Excel Components |
|
|
649 | (8) |
|
Using cell comments for help |
|
|
649 | (1) |
|
Using a text box for help |
|
|
650 | (2) |
|
Using a worksheet to display help text |
|
|
652 | (1) |
|
Displaying help in a UserForm |
|
|
653 | (4) |
|
Displaying Help in a Web Browser |
|
|
657 | (2) |
|
|
|
657 | (1) |
|
|
|
658 | (1) |
|
Using the HTML Help System |
|
|
659 | (6) |
|
Using the Help method to display HTML Help |
|
|
662 | (1) |
|
Associating a help file with your application |
|
|
663 | (1) |
|
Associating a help topic with a VBA function |
|
|
663 | (2) |
|
Chapter 20 Leveraging Class Modules |
|
|
665 | (20) |
|
|
|
665 | (2) |
|
|
|
666 | (1) |
|
|
|
666 | (1) |
|
|
|
667 | (4) |
|
|
|
668 | (1) |
|
Adding VBA code to the class module |
|
|
668 | (2) |
|
|
|
670 | (1) |
|
Coding Properties, Methods, and Events |
|
|
671 | (3) |
|
Programming properties of objects |
|
|
671 | (2) |
|
Programming methods for objects |
|
|
673 | (1) |
|
|
|
673 | (1) |
|
Exposing a QueryTable Event |
|
|
674 | (3) |
|
Creating a Class to Hold Classes |
|
|
677 | (8) |
|
Creating the CSalesRep and CSalesReps classes |
|
|
677 | (2) |
|
Creating the Clnvoice and Clnvoices classes |
|
|
679 | (1) |
|
Filling the parent classes with objects |
|
|
680 | (2) |
|
Calculating the commissions |
|
|
682 | (3) |
|
Chapter 21 Understanding Compatibility Issues |
|
|
685 | |
|
|
|
685 | (1) |
|
Types of Compatibility Problems |
|
|
686 | (1) |
|
|
|
687 | (2) |
|
But Will It Work on a Mac? |
|
|
689 | (1) |
|
Dealing with 64-Bit Excel |
|
|
690 | (1) |
|
Creating an International Application |
|
|
691 | |
|
Multilanguage applications |
|
|
693 | (1) |
|
VBA language considerations |
|
|
694 | (1) |
|
|
|
694 | (1) |
|
Identifying system settings |
|
|
695 | (2) |
|
|
|
697 | |
| Part V: Appendix |
|
| Appendix A: VBA Statements and Function Reference |
|
701 | (8) |
| Index |
|
709 | |
Rohkem infot Wiley Online kohta