Muutke küpsiste eelistusi

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

  • Formaat: 1104 pages
  • Sari: Mr. Spreadsheets Bookshelf
  • Ilmumisaeg: 17-Apr-2007
  • Kirjastus: John Wiley & Sons Ltd
  • ISBN-10: 1118257596
  • ISBN-13: 9781118257593
Teised raamatud teemal:
  • Wiley Online
  • Hind: 52,85 €*
  • * hind, mis tagab piiramatu üheaegsete kasutajate arvuga ligipääsu piiramatuks ajaks
Excel 2007 Power Programming with VBASuurem pilt
  • Formaat: 1104 pages
  • Sari: Mr. Spreadsheets Bookshelf
  • Ilmumisaeg: 17-Apr-2007
  • Kirjastus: John Wiley & Sons Ltd
  • ISBN-10: 1118257596
  • ISBN-13: 9781118257593
Teised raamatud teemal:
Wiley Online e-raamatudRohkem infot Wiley Online kohta
Raamatu kodulehekülg: https://onlinelibrary.wiley.com/doi/book/10.1002/9781118257593
* This book is a single reference that's indispensable for Excel beginners, intermediate users, power users, and would-be power users everywhere* Fully updated for the new release, this latest edition provides comprehensive, soup-to-nuts coverage, delivering over 900 pages of Excel tips, tricks, and techniques readers won't find anywhere else* John Walkenbach, aka "Mr. Spreadsheet," is one of the world's leading authorities on Excel* Thoroughly updated to cover the revamped Excel interface, new file formats, enhanced interactivity with other Office applications, and upgraded collaboration features* Includes a valuable CD-ROM with templates and worksheets from the book Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.
Preface vii
Part I: Some Essential Background
Excel 2007: Where It Came From
3(12)
A Brief History of Spreadsheets
3(10)
It all started with VisiCalc
4(1)
Lotus 1-2-3
5(2)
Quattro Pro
7(1)
Microsoft Excel
7(6)
Why Excel Is Great for Developers
13(1)
Excel's Role in Microsoft's Strategy
14(1)
Excel in a Nutshell
15(32)
Thinking in Terms of Objects
15(1)
Workbooks
16(4)
Worksheets
17(1)
Chart sheets
18(1)
XLM macro sheets
18(2)
Excel 5/95 dialog sheets
20(1)
Excel's User Interface
20(10)
Introducing the Ribbon
20(6)
Shortcut menus
26(1)
Dialog boxes
27(1)
Keyboard shortcuts
28(1)
Smart Tags
28(1)
Task pane
29(1)
Customizing the Display
30(1)
Data Entry
30(1)
Formulas, Functions, and Names
30(2)
Selecting Objects
32(1)
Formatting
33(1)
Numeric formatting
33(1)
Stylistic formatting
34(1)
Protection Options
34(3)
Protecting formulas from being overwritten
35(1)
Protecting a workbook's structure
36(1)
Applying password protection to a workbook
36(1)
Protecting VBA code with a password
36(1)
Charts
37(1)
Shapes and SmartArt
38(1)
Database Access
39(2)
Worksheet databases
39(1)
External databases
40(1)
Internet Features
41(1)
Analysis Tools
41(2)
Outlines
42(1)
Analysis ToolPak
42(1)
Pivot tables
42(1)
Solver
42(1)
XML features
42(1)
Add-Ins
43(1)
Macros and Programming
44(1)
File Format
44(1)
Excel's Help System
44(3)
Formula Tricks and Techniques
47(26)
About Formulas
48(1)
Calculating Formulas
48(1)
Cell and Range References
49(4)
Why use references that aren't relative?
50(1)
About R1C1 notation
50(1)
Referencing other sheets or workbooks
51(2)
Using Names
53(6)
Naming cells and ranges
53(1)
Applying names to existing references
54(1)
Intersecting names
55(1)
Naming columns and rows
55(1)
Scoping names
56(1)
Naming constants
56(1)
Naming formulas
57(2)
Naming objects
59(1)
Formula Errors
59(1)
Array Formulas
60(3)
An array formula example
61(1)
An array formula calendar
62(1)
Array formula pros and cons
63(1)
Counting and Summing Techniques
63(4)
Counting formula examples
64(2)
Summing formula examples
66(1)
Other counting tools
67(1)
Working with Dates and Times
67(2)
Entering dates and times
67(1)
Using pre-1900 dates
68(1)
Creating Megaformulas
69(4)
Understanding Excel's Files
73(24)
Starting Excel
73(3)
File Types
76(4)
Excel file formats
76(2)
Text file formats
78(1)
Database file formats
79(1)
Other file formats
79(1)
Working with Template Files
80(4)
Viewing templates
81(1)
Creating templates
82(2)
Creating workbook templates
84(1)
Inside an Excel File
84(5)
Dissecting a file
85(3)
Why is the file format important?
88(1)
The QAT File
89(1)
The XLB File
90(1)
Add-In Files
90(1)
Excel Settings in the Registry
91(6)
About the Registry
91(1)
Excel's settings
92(5)
Part II: Excel Application Development
What Is a Spreadsheet Application?
97(12)
Spreadsheet Applications
98(1)
The Developer and the End User
99(3)
Who are developers? What do they do?
99(1)
Classifying spreadsheet users
100(1)
The audience for spreadsheet applications
101(1)
Solving Problems with Excel
102(1)
Basic Spreadsheet Types
103(6)
Quick-and-dirty spreadsheets
103(1)
For-your-eyes-only spreadsheets
104(1)
Single-user applications
104(1)
Spaghetti applications
104(1)
Utility applications
105(1)
Add-ins that contain worksheet functions
105(1)
Single-block budgets
106(1)
What-if models
106(1)
Data storage and access spreadsheets
106(1)
Database front ends
107(1)
Turnkey applications
107(2)
Essentials of Spreadsheet Application Development
109(24)
Determining User Needs
110(1)
Planning an Application That Meets User Needs
111(3)
Determining the Most Appropriate User Interface
114(6)
Customizing the Ribbon
114(2)
Customizing shortcut menus
116(1)
Creating shortcut keys
116(1)
Creating custom dialog boxes
117(1)
Using ActiveX controls on a worksheet
118(2)
Executing the development effort
120(1)
Concerning Yourself with the End User
120(8)
Testing the application
121(1)
Making the application bulletproof
122(2)
Making the application aesthetically appealing and intuitive
124(1)
Creating a user Help system
125(1)
Documenting the development effort
126(1)
Distributing the application to the user
127(1)
Updating the application when necessary
127(1)
Other Development Issues
128(5)
The user's installed version of Excel
128(1)
Language issues
128(1)
System speed
129(1)
Video modes
129(4)
Part III: Understanding Visual Basic for Applications
Introducing Visual Basic for Applications
133(56)
Some Basic Background
134(1)
About VBA
134(1)
Object models
134(1)
VBA versus XLM
135(1)
The Basics of VBA
135(4)
Introducing the Visual Basic Editor
139(3)
Displaying Excel's Developer tab
139(1)
Activating the VBE
139(1)
The VBE windows
140(2)
Working with the Project Explorer
142(2)
Adding a new VBA module
143(1)
Removing a VBA module
143(1)
Exporting and importing objects
143(1)
Working with Code Windows
144(8)
Minimizing and maximizing windows
144(1)
Storing VBA code
145(1)
Entering VBA code
145(7)
Customizing the VBE Environment
152(6)
Using the Editor tab
152(3)
Using the Editor Format tab
155(1)
Using the General tab
156(1)
Using the Docking tab
157(1)
The Macro Recorder
158(7)
What the macro recorder actually records
158(1)
Relative or absolute?
159(3)
Recording options
162(1)
Cleaning up recorded macros
163(2)
About Objects and Collections
165(2)
The object hierarchy
165(1)
About collections
166(1)
Referring to objects
167(1)
Properties and Methods
167(4)
Object properties
168(1)
Object methods
169(2)
The Comment Object: A Case Study
171(6)
Viewing Help for the Comment object
171(1)
Properties of a Comment object
171(1)
Methods of a Comment object
172(1)
The Comments collection
173(1)
About the Comment property
174(1)
Objects within a Comment object
175(1)
Determining whether a cell has a comment
176(1)
Adding a new Comment object
177(1)
Some Useful Application Properties
177(2)
Working with Range Objects
179(6)
The Range property
179(3)
The Cells property
182(2)
The Offset property
184(1)
Things to Know about Objects
185(4)
Essential concepts to remember
185(1)
Learning more about objects and properties
186(3)
VBA Programming Fundamentals
189(48)
VBA Language Elements: An Overview
189(3)
Comments
192(1)
Variables, Data Types, and Constants
193(14)
Defining data types
194(3)
Declaring variables
197(2)
Scoping variables
199(4)
Working with constants
203(2)
Working with strings
205(1)
Working with dates
206(1)
Assignment Statements
207(2)
Arrays
209(2)
Declaring arrays
210(1)
Declaring multidimensional arrays
210(1)
Declaring dynamic arrays
211(1)
Object Variables
211(1)
User-Defined Data Types
212(1)
Built-in Functions
213(4)
Manipulating Objects and Collections
217(3)
With-End With constructs
217(1)
For Each-Next constructs
218(2)
Controlling Code Execution
220(17)
GoTo statements
220(1)
If-Then constructs
221(4)
Select Case constructs
225(3)
Looping blocks of instructions
228(9)
Working with VBA Sub Procedures
237(38)
About Procedures
237(3)
Declaring a Sub procedure
238(1)
Scoping a procedure
239(1)
Executing Sub Procedures
240(11)
Executing a procedure with the Run Sub/UserForm command
241(1)
Executing a procedure from the Macro dialog box
241(1)
Executing a procedure with a Ctrl+shortcut key combination
242(1)
Executing a procedure from the Ribbon
243(1)
Executing a procedure from a customized shortcut menu
244(1)
Executing a procedure from another procedure
244(5)
Executing a procedure by clicking an object
249(1)
Executing a procedure when an event occurs
250(1)
Executing a procedure from the Immediate window
250(1)
Passing Arguments to Procedures
251(4)
Error-Handling Techniques
255(5)
Trapping errors
256(1)
Error-handling examples
257(3)
A Realistic Example That Uses Sub Procedures
260(15)
The goal
260(1)
Project requirements
260(1)
What you know
261(1)
The approach
261(1)
What you need to know
262(1)
Some preliminary recording
262(2)
Initial setup
264(1)
Code writing
265(1)
Writing the Sort procedure
266(4)
More testing
270(1)
Fixing the problems
270(4)
Utility availability
274(1)
Evaluating the project
274(1)
Creating Function Procedures
275(40)
Sub Procedures versus Function Procedures
276(1)
Why Create Custom Functions?
276(1)
An Introductory Function Example
277(4)
A custom function
277(1)
Using the function in a worksheet
278(1)
Using the function in a VBA procedure
278(1)
Analyzing the custom function
279(2)
Function Procedures
281(4)
Declaring a function
281(2)
A function's scope
283(1)
Executing function procedures
283(2)
Function Arguments
285(1)
Function Examples
286(15)
Functions with no argument
286(3)
A function with one argument
289(3)
A function with two arguments
292(1)
A function with an array argument
293(1)
A function with optional arguments
294(2)
A function that returns a VBA array
296(3)
A function that returns an error value
299(1)
A function with an indefinite number of arguments
300(1)
Emulating Excel's Sum function
301(4)
Debugging Functions
305(1)
Dealing with the Insert Function Dialog Box
306(4)
Specifying a function category
307(1)
Adding a function description
308(2)
Using Add-ins to Store Custom Functions
310(1)
Using the Windows API
311(4)
Windows API examples
311(1)
Determining the Windows directory
311(2)
Detecting the Shift key
313(1)
Learning more about API functions
314(1)
VBA Programming Examples and Techniques
315(74)
Working with Ranges
316(28)
Copying a range
316(2)
Moving a range
318(1)
Copying a variably sized range
318(1)
Selecting or otherwise identifying various types of ranges
319(2)
Prompting for a cell value
321(2)
Entering a value in the next empty cell
323(1)
Pausing a macro to get a user-selected range
324(2)
Counting selected cells
326(1)
Determining the type of selected range
327(2)
Looping through a selected range efficiently
329(3)
Deleting all empty rows
332(1)
Duplicating rows a variable number of times
333(1)
Determining whether a range is contained in another range
334(1)
Determining a cell's data type
335(1)
Reading and writing ranges
336(1)
A better way to write to a range
337(2)
Transferring one-dimensional arrays
339(1)
Transferring a range to a variant array
339(1)
Selecting cells by value
340(2)
Copying a noncontiguous range
342(2)
Working with Workbooks and Sheets
344(3)
Saving all workbooks
344(1)
Saving and closing all workbooks
344(1)
Hiding all but the selection
345(1)
Synchronizing worksheets
346(1)
VBA Techniques
347(9)
Toggling a Boolean property
347(1)
Determining the number of printed pages
348(1)
Displaying the date and time
349(2)
Getting a list of fonts
351(1)
Sorting an array
352(2)
Processing a series of files
354(2)
Some Useful Functions for Use in Your Code
356(5)
The FileExists function
356(1)
The FileNameOnly function
356(1)
The PathExists function
357(1)
The RangeNameExists function
357(1)
The SheetExists function
358(1)
The WorkbookIsOpen function
358(1)
Retrieving a value from a closed workbook
358(3)
Some Useful Worksheet Functions
361(16)
Returning cell formatting information
361(2)
A talking worksheet
363(1)
Displaying the date when a file was saved or printed
363(1)
Understanding object parents
364(1)
Counting cells between two values
365(1)
Counting visible cells in a range
366(1)
Determining the last non-empty cell in a column or row
366(2)
Does a string match a pattern?
368(1)
Extracting the nth element from a string
369(2)
A multifunctional function
371(1)
The SheetOffset function
372(1)
Returning the maximum value across all worksheets
373(1)
Returning an array of nonduplicated random integers
374(1)
Randomizing a range
375(2)
Windows API Calls
377(12)
Determining file associations
377(1)
Determining disk drive information
378(1)
Determining default printer information
379(1)
Determining video display information
380(2)
Adding sound to your applications
382(2)
Reading from and writing to the Registry
384(5)
Part IV: Working with UserForms
Custom Dialog Box Alternatives
389(24)
Before You Create That UserForm
390(1)
Using an Input Box
390(4)
The VBA InputBox function
390(2)
The Excel InputBox method
392(2)
The VBA MsgBox Function
394(5)
The Excel GetOpenFilename Method
399(4)
The Excel GetSaveAsFilename Method
403(1)
Prompting for a Directory
403(4)
Using a Windows API function to select a directory
404(2)
Using the FileDialog object to select a directory
406(1)
Displaying Excel's Built-In Dialog Boxes
407(3)
About the Dialogs collection
407(1)
Executing Ribbon commands
408(2)
Displaying a Data Form
410(3)
Making the data form accessible
410(1)
Displaying a data form by using VBA
411(2)
Introducing UserForms
413(36)
How Excel Handles Custom Dialog Boxes
414(1)
Inserting a New UserForm
414(1)
Adding Controls to a UserForm
415(1)
Toolbox Controls
416(5)
CheckBox
416(1)
ComboBox
416(1)
CommandButton
417(1)
Frame
417(1)
Image
417(1)
Label
418(1)
ListBox
418(1)
MultiPage
418(1)
OptionButton
418(1)
RefEdit
418(1)
ScrollBar
418(1)
SpinButton
419(1)
TabStrip
419(1)
TextBox
419(1)
ToggleButton
419(2)
Adjusting UserForm Controls
421(1)
Adjusting a Control's Properties
422(5)
Using the Properties window
422(2)
Common properties
424(1)
Learning more about properties
424(1)
Accommodating keyboard users
424(3)
Displaying and Closing UserForms
427(3)
Displaying a UserForm
427(1)
Closing a UserForm
428(1)
About event handler procedures
429(1)
Creating a UserForm: An Example
430(7)
Creating the UserForm
430(3)
Writing code to display the dialog box
433(1)
Testing the dialog box
434(1)
Adding event handler procedures
435(1)
Validating the data
436(1)
The finished dialog box
437(1)
Understanding UserForm Events
437(7)
Learning about events
437(1)
UserForm events
438(1)
SpinButton events
439(2)
Pairing a SpinButton with a TextBox
441(3)
Referencing UserForm Controls
444(1)
Customizing the Toolbox
445(2)
Changing icons or tip text
445(1)
Adding new pages
445(1)
Customizing or combining controls
445(2)
Adding new ActiveX controls
447(1)
Creating UserForm Templates
447(1)
A UserForm Checklist
448(1)
UserForm Examples
449(38)
Creating a UserForm ``Menu''
450(2)
Using CommandButtons in a UserForm
450(1)
Using a ListBox in a UserForm
450(2)
Selecting Ranges from a UserForm
452(2)
Creating a Splash Screen
454(2)
Disabling a UserForm's Close Button
456(1)
Changing a UserForm's Size
457(1)
Zooming and Scrolling a Sheet from a UserForm
458(2)
ListBox Techniques
460(19)
About the ListBox control
461(1)
Adding items to a ListBox control
461(5)
Determining the selected item
466(1)
Determining multiple selections in a ListBox
466(1)
Multiple lists in a single ListBox
467(1)
ListBox item transfer
468(2)
Moving items in a ListBox
470(2)
Working with multicolumn ListBox controls
472(2)
Using a ListBox to select worksheet rows
474(2)
Using a ListBox to activate a sheet
476(3)
Using the MultiPage Control in a UserForm
479(1)
Using an External Control
480(2)
Animating a Label
482(5)
Advanced UserForm Techniques
487(46)
A Modeless Dialog Box
488(3)
Displaying a Progress Indicator
491(9)
Creating a standalone progress indicator
492(4)
Showing a progress indicator by using a MultiPage control
496(3)
Showing a progress indicator without using a MultiPage control
499(1)
Creating Wizards
500(6)
Setting up the MultiPage control for the wizard
500(1)
Adding the buttons to the wizard UserForm
501(1)
Programming the wizard buttons
502(1)
Programming dependencies in a wizard
503(2)
Performing the task with the wizard
505(1)
Emulating the MsgBox Function
506(4)
MsgBox emulation: MyMsgBox code
507(1)
How the MyMsgBox function works
508(2)
Using the MyMsgBox function in the MsgBox emulation
510(1)
A UserForm with Movable Controls
510(1)
A UserForm with No Title Bar
511(2)
Simulating a Toolbar with a UserForm
513(2)
A Resizable UserForm
515(5)
Handling Multiple UserForm Controls with One Event Handler
520(3)
Selecting a Color in a UserForm
523(1)
Displaying a Chart in a UserForm
524(2)
General steps to display a chart in a userform
525(1)
Saving a chart as a GIF file
525(1)
Changing the Image control Picture property
526(1)
An Enhanced Data Form
526(3)
About the Enhanced Data Form
527(1)
Installing the Enhanced Data Form add-in
528(1)
A Puzzle on a UserForm
529(4)
Part V: Advanced Programming Techniques
Developing Excel Utilities with VBA
533(22)
About Excel Utilities
533(1)
Using VBA to Develop Utilities
534(1)
What Makes a Good Utility?
535(1)
Text Tools: The Anatomy of a Utility
535(19)
Background for Text Tools
536(1)
Project goals for Text Tools
537(1)
The Text Tools workbook
537(1)
How the Text Tools utility works
538(1)
The UserForm for the Text Tools utility
538(2)
The Module1 VBA module
540(2)
The UserForm1 code module
542(2)
Making the Text Tools utility efficient
544(1)
Saving the Text Tools utility settings
545(2)
Implementing Undo
547(2)
Displaying the Help file
549(1)
Adding the RibbonX code
550(3)
Post-mortem of the project
553(1)
Understand the Text Tools utility
554(1)
More about Excel Utilities
554(1)
Working with Pivot Tables
555(16)
An Introductory Pivot Table Example
555(5)
Creating a pivot table
556(2)
Examining the recorded code for the pivot table
558(1)
Cleaning up the recorded pivot table code
558(2)
Creating a More Complex Pivot Table
560(5)
Data for a more complex pivot table
560(2)
The code that created the pivot table
562(1)
How the more complex pivot table works
563(2)
Creating Multiple Pivot Tables
565(3)
Creating a Reverse Pivot Table
568(3)
Working with Charts
571(58)
About Charts
571(4)
Chart locations
572(1)
The macro recorder and charts
572(2)
The Chart object model
574(1)
Common VBA Charting Techniques
575(12)
Creating an embedded chart
575(2)
Creating a chart on a chart sheet
577(1)
Using VBA to activate a chart
578(1)
Moving a chart
578(2)
Using VBA to deactivate a chart
580(1)
Determining whether a chart is activated
580(1)
Deleting from the ChartObjects or Charts collection
581(1)
Looping through all charts
582(2)
Sizing and aligning ChartObjects
584(1)
Exporting a chart
585(1)
Exporting all graphics
586(1)
Using VBA to Apply Chart Formatting
587(5)
Formatting a chart
587(2)
More chart formatting examples
589(3)
Changing the Data Used in a Chart
592(6)
Changing chart data based on the active cell
593(2)
Using VBA to determine the ranges used in a chart
595(3)
Using VBA to Display Arbitrary Data Labels on a Chart
598(2)
Displaying a Chart in a UserForm
600(3)
Understanding Chart Events
603(7)
An example of using Chart events
604(3)
Enabling events for an embedded chart
607(1)
Example: Using Chart events with an embedded chart
608(2)
VBA Charting Tricks
610(7)
Printing embedded charts on a full page
610(1)
Displaying a slide show
611(1)
Hiding series by hiding columns
612(1)
Creating unlinked charts
613(2)
Displaying text with the MouseOver event
615(2)
Animating Charts
617(7)
Scrolling a chart
619(2)
Creating a hypocycloid chart
621(1)
Creating a ``clock'' chart
622(2)
Creating an Interactive Chart without VBA
624(5)
Getting the data to create an interactive chart
625(1)
Creating the Option Button controls for an interactive chart
625(1)
Creating the city lists for the interactive chart
625(1)
Creating the interactive chart data range
626(1)
Creating the interactive chart
627(2)
Understanding Excel's Events
629(40)
Event Types That Excel Can Monitor
630(1)
What You Should Know about Events
631(6)
Understanding event sequences
631(1)
Where to put event handler procedures
631(1)
Disabling events
632(2)
Entering event handler code
634(1)
Event handler procedures that use arguments
635(2)
Workbook-Level Events
637(8)
The Open event
639(1)
The Activate event
640(1)
The SheetActivate event
640(1)
The NewSheet event
640(1)
The BeforeSave event
640(1)
The Deactivate event
641(1)
The BeforePrint event
642(1)
The BeforeClose event
643(2)
Worksheet Events
645(9)
The Change event
646(1)
Monitoring a specific range for changes
647(4)
The SelectionChange event
651(1)
The BeforeDoubleClick event
652(1)
The BeforeRightClick event
653(1)
Chart Events
654(2)
Application Events
656(5)
Enabling Application-level events
657(1)
Determining when a workbook is opened
658(1)
Monitoring Application-level events
659(2)
UserForm Events
661(1)
Events Not Associated with an Object
662(7)
The OnTime event
662(2)
The OnKey event
664(5)
Interacting with Other Applications
669(30)
Starting an Application from Excel
670(4)
Using the VBA Shell function
670(2)
Using the Windows ShellExecute API function
672(2)
Activating an Application with Excel
674(1)
Using AppActivate
674(1)
Activating a Microsoft Office application
674(1)
Running Control Panel Dialog Boxes
675(1)
Using Automation in Excel
676(11)
Working with foreign objects using automation
677(1)
Early versus late binding
677(3)
A simple example of late binding
680(1)
Controlling Word from Excel
681(3)
Controlling Excel from another application
684(3)
Sending Personalized E-Mail via Outlook
687(3)
Sending E-Mail Attachments from Excel
690(3)
Using SendKeys
693(3)
Working with ADO
696(3)
Creating and Using Add-Ins
699(28)
What Is an Add-In?
699(3)
Comparing an add-in with a standard workbook
700(1)
Why create add-ins?
701(1)
Understanding Excel's Add-In Manager
702(1)
Creating an Add-in
703(1)
An Add-In Example
704(6)
Setting up the workbook for the example add-in
705(1)
Adding descriptive information for the example add-in
705(1)
Creating an add-in
706(1)
Installing an add-in
707(1)
Testing the add-in
708(1)
Distributing an add-in
708(1)
Modifying an add-in
708(2)
Comparing XLAM and XLSM Files
710(4)
XLSM and XLAM file size and structure
710(1)
XLAM file VBA collection membership
710(1)
Visibility of XLSM and XLAM files
710(1)
Worksheets and chart sheets in XLSM and XLAM files
711(1)
Accessing VBA procedures in an add-in
712(2)
Manipulating Add-Ins with VBA
714(6)
Understanding the Addlns collection
714(2)
AddIn object properties
716(3)
Accessing an add-in as a workbook
719(1)
AddIn object events
719(1)
Optimizing the Performance of Add-ins
720(1)
Special Problems with Add-Ins
721(6)
Ensuring that an add-in is installed
721(2)
Referencing other files from an add-in
723(1)
Detecting the proper Excel version for your add-in
724(3)
Part VI: Developing Applications
Working with the Ribbon
727(34)
Ribbon Basics
727(4)
Ribbon tabs
728(3)
VBA and the Ribbon
731(5)
Accessing a Ribbon control
732(2)
Working with the Ribbon
734(1)
Activating a tab
735(1)
Customizing the Ribbon
736(20)
A simple RibbonX example
736(3)
More about the simple RibbonX example
739(3)
Another RibbonX example
742(3)
Ribbon controls demo
745(7)
A DynamicMenu Control Example
752(3)
More on Ribbon customization
755(1)
Creating an Old-Style Toolbar
756(5)
Limitations of old-style toolbars in Excel 2007
756(1)
Code to create a toolbar
757(4)
Working with Shortcut Menus
761(20)
CommandBar Overview
762(6)
CommandBar types
762(1)
Listing shortcut menus
762(2)
Referring to CommandBars
764(1)
Referring to controls in a CommandBar
764(2)
Properties of CommandBar controls
766(1)
Displaying all shortcut menu items
766(2)
Using VBA to Customize Shortcut Menus
768(7)
Resetting a shortcut menu
768(1)
Disabling a Shortcut Menu
769(1)
Disabling shortcut menu items
770(1)
Adding a new item to the Cell shortcut menu
770(2)
Adding a submenu to a shortcut menu
772(3)
Shortcut Menus and Events
775(6)
Adding and deleting menus automatically
775(1)
Disabling or hiding shortcut menu items
776(1)
Creating a context-sensitive shortcut menu
777(4)
Providing Help for Your Applications
781(20)
Help for Your Excel Applications
782(2)
Help Systems That Use Excel Components
784(8)
Using cell comments for help
784(2)
Using a text box for help
786(1)
Using a worksheet to display help text
787(1)
Displaying help in a UserForm
788(4)
Displaying help in a Web Browser
792(2)
Using HTML files
792(1)
Using an HTML file
793(1)
Using the HTML Help System
794(4)
Using the Help method to display HTML Help
797(1)
Using an API function to display HTML help
797(1)
Associating a Help File with Your Application
798(3)
Associating a help topic with a VBA function
798(3)
Developing User-Oriented Applications
801(16)
What is a User-Oriented Application?
801(1)
the Loan Amortization Wizard
802(11)
Using the Loan Amortization Wizard
803(2)
The Loan Amortization Wizard workbook structure
805(1)
How the Loan Amortization Wizard works
805(7)
Potential enhancements for the Loan Amortization Wizard
812(1)
Application Development Concepts
813(4)
Part VII: Other Topics
Compatibility Issues
817(12)
What Is Compatibility?
817(1)
Types of Compatibility Problems
818(1)
Avoid Using New Features
819(1)
But Will It Work on a Mac?
820(2)
Creating an International Application
822(7)
Multilanguage applications
823(1)
VBA language considerations
824(1)
Using local properties
824(1)
Identifying system settings
825(3)
Date and time settings
828(1)
Manipulating Files with VBA
829(30)
Performing Common File Operations
830(8)
Using VBA file-related commands
830(5)
Using the FileSystemObject object
835(3)
Displaying Extended File Information
838(3)
Working with Text Files
841(4)
Opening a text file
842(1)
Reading a text file
842(1)
Writing a text file
843(1)
Getting a file number
843(1)
Determining or setting the file position
843(1)
Statements for reading and writing
843(2)
Text File Manipulation Examples
845(10)
Importing data in a text file
845(1)
Exporting a range to a text file
845(2)
Importing a text file to a range
847(1)
Logging Excel usage
848(1)
Filtering a text file
848(1)
Exporting a range to HTML format
849(3)
Exporting a range to an XML file
852(3)
Zipping and Unzipping Files
855(4)
Zipping files
855(3)
Unzipping a File
858(1)
Manipulating Visual Basic Components
859(26)
Introducing the IDE
859(2)
The IDE Object Model
861(3)
The VBProjects collection
862(2)
Displaying All Components in a VBA Project
864(2)
Listing All VBA Procedures in a Workbook
866(1)
Replacing a Module with an Updated Version
867(2)
Using VBA to Write VBA Code
869(3)
Adding Controls to a UserForm at Design Time
872(3)
Design-time versus runtime UserForm manipulations
872(2)
Adding 100 CommandButtons at design time
874(1)
Creating UserForms Programmatically
875(10)
A simple runtime UserForm example
876(2)
A useful (but not so simple) dynamic UserForm example
878(7)
Understanding Class Modules
885(16)
What is a Class Module?
885(1)
Example: Creating a NumLock Class
886(5)
Inserting a class module
887(1)
Adding VBA code to the class module
888(2)
Using the NumLockClass class
890(1)
More about Class Modules
891(4)
Naming the object class
892(1)
Programming properties of objects
892(2)
Programming methods for objects
894(1)
Class module events
894(1)
Example: A CSV File Class
895(6)
Class module-level variables for the CSVFileClass
895(1)
Property procedures for the CSVFileClass
896(1)
Method procedures for the CSVFileClass
896(2)
Using the CSVFileClass object
898(3)
Working with Colors
901(28)
Specifying Colors
902(4)
The RGB color system
902(1)
The HSL color system
903(1)
Converting colors
904(2)
Understanding Grayscale
906(2)
Converting colors to gray
907(1)
Viewing charts as grayscale
908(1)
Experimenting with Colors
908(2)
Understanding Document Themes
910(7)
About document themes
910(1)
Understanding document theme colors
911(4)
Displaying all theme colors
915(2)
Working with Shape Objects
917(8)
A shape's background color
917(2)
Using other fill types with a shape
919(6)
Learning more about shapes
925(1)
Modifying Chart Colors
925(4)
Frequently Asked Questions about Excel Programming
929(42)
General Excel Questions
930(7)
How do I record a macro?
930(1)
How do I run a macro?
930(1)
What do I do if I don't have a Developer tab?
930(1)
I recorded a macro and saved my workbook. When I reopened it, the macros were gone! Where did they go?
931(1)
Before saving my workbook as an XLSM file, I converted all my VBA statements to comments so I could debug the code later. When I re-opened the workbook, all my VBA code was gone
931(1)
How do I hide the Ribbon so it doesn't take up so much space?
931(1)
Where are my old custom toolbars?
931(1)
Can I make my old custom toolbars float?
931(1)
Where can I find examples of VBA code?
931(1)
How can I hide the status bar in Excel 2007?
932(1)
Is there a utility that will convert my Excel application into a standalone EXE file?
932(1)
Why doesn't Ctrl+A select all the cells in my worksheet?
932(1)
Why is the Custom Views command is grayed out?
932(1)
How can I add a drop-down list to a cell so the user can choose a value from the list?
932(1)
Can I use this drop-down list method if my list is stored on a different worksheet in the workbook?
932(1)
I use Application.Calculation to set the calculation mode to manual. However, this seems to affect all workbooks and not just the active workbook
933(1)
Why doesn't the F4 function key repeat all my operations?
933(1)
What happened to the ability to ``speak'' the cell contents?
933(1)
How can I increase the number of columns in a worksheet?
933(1)
How can I increase the number of rows in a worksheet?
933(1)
I opened a workbook, and it has only 65,546 rows. What happened?
934(1)
How do I get my old workbook to use the new fonts?
934(1)
How do I get a print preview?
934(1)
When I switch to a new document template, my worksheet no longer fits on a single page
934(1)
How do I get rid of the annoying dotted-line page break display in Normal view mode?
934(1)
Can I add that Show Page Breaks option to my QAT?
934(1)
I changed the text in a cell to use Angle Clockwise orientation (in the Home ⇒ Alignment group). How do I get the orientation back to normal? There's no Horizontal Alignment option
935(1)
I'm trying to apply a table style to a table, but it has no visible effect. What can I do?
935(1)
How do I get Office 2007 to support PDF output?
935(1)
Can I change the color of the sheet tabs?
935(1)
Can I change the font of the sheet tabs?
935(1)
Can I change the default font and color of cell comments?
935(1)
Can I write VBA macros that play sounds?
936(1)
When I open a workbook, Excel asks whether I want to update the links. I've searched all my formulas and cannot find any links in this workbook. Is this a bug?
936(1)
Why does Excel crash every time I start it?
936(1)
The Visual Basic Editor
937(3)
Can I use the VBA macro recorder to record all my macros?
937(1)
I turned on the macro recorder when I edited a chart, but many of the commands weren't recorded
937(1)
I have some macros that are general in nature. I would like to have these available all the time. What's the best way to do this?
937(1)
I can't find my Personal Macro Workbook. Where is it?
937(1)
I locked my VBA project with a password, and I forget what it was. Is there any way to unlock it?
937(1)
How can I write a macro to change the password of my project?
937(1)
When I insert a new module, it always starts with an Option Explicit line. What does this mean?
938(1)
Why does my VBA code appear in different colors? Can I change these colors?
938(1)
Can I delete a VBA module by using VBA code?
938(1)
I wrote a macro in Excel 2000 that adds VBA code to the VB project. When I run it in Excel 2007, I get an error message. What's wrong?
938(1)
How can I write a macro to change the user's macro security setting? I want to avoid the ``this workbook contains macros'' message when my application is opened
938(1)
How does the UserInterfaceOnly option work when protecting a worksheet?
939(1)
How can I tell whether a workbook has a macro virus?
939(1)
I'm having trouble with the concatenation operator (&) in VBA. When I try to concatenate two strings, I get an error message
939(1)
I can't seem to get the VBA line continuation character (underscore) to work
939(1)
I distributed an Excel application to many users. On some machines, my VBA error-handling procedures don't work. Why not?
939(1)
Procedures
940(5)
What's the difference between a VBA procedure and a macro?
940(1)
What's a procedure?
940(1)
What is a variant data type?
940(1)
What's the difference between a variant array and an array of variants?
940(1)
What's a type-definition character?
941(1)
I would like to create a procedure that automatically changes the formatting of a cell based on the data that I enter. For example, if I enter a value greater than 0, the cell's background color should be red. Is this possible?
941(1)
The Conditional Formatting feature is useful, but I'd like to perform other types of operations when data is entered into a cell
941(1)
What other types of events can be monitored?
941(1)
I tried entering an event procedure (Sub Workbook_Open), but the procedure isn't executed when the workbook is opened. What's wrong?
942(1)
I can write an event procedure for a particular workbook, but can I write an event procedure that will work for any workbook that's open?
942(1)
I'm very familiar with creating formulas in Excel. Does VBA use the same mathematical and logical operators?
942(1)
How can I execute a procedure that's in a different workbook?
942(1)
I've used VBA to create several custom functions. I like to use these functions in my worksheet formulas, but I find it inconvenient to precede the function name with the workbook name. Is there any way around this?
943(1)
I would like a particular workbook to be loaded every time I start Excel. I would also like a macro in this workbook to execute automatically. Am I asking too much?
943(1)
I have a workbook that uses a Workbook_Open procedure. Is there a way to prevent this from executing when I open the workbook?
943(1)
Can a VBA procedure access a cell's value in a workbook that is not open?
943(1)
How can I prevent the ``save file'' prompt from being displayed when I close a workbook from VBA?
944(1)
How can I set things up so that my macro runs once every hour?
944(1)
How do I prevent a macro from showing in the macro list?
944(1)
Can I save a chart as a GIF file?
944(1)
Are variables in a. VBA procedure available to other VBA procedures? What if the procedure is in a different module? Or in a different workbook?
945(1)
Functions
945(3)
I created a VBA function for use in worksheet formulas. However, it always returns #NAME?. What went wrong?
945(1)
I wrote a VBA function that works perfectly when I call it from another procedure, but it doesn't work when I use it in a worksheet formula. What's wrong?
945(1)
When I access a custom worksheet function with the Insert Function dialog box, it reads ``No help available.'' How can I get the Insert Function dialog box to display a description of my function?
946(1)
Can I also display help for the arguments for my custom function in the Insert Function dialog box?
946(1)
My custom worksheet function appears in the User Defined category in the Insert Function dialog box. How can I make my function appear in a different function category?
946(1)
How can I create a new function category?
947(1)
I have a custom function that will be used in a worksheet formula. If the user enters arguments that are not appropriate, how can I make the function return a true error value (#VALUE!)?
947(1)
How can I force a recalculation of formulas that use my custom worksheet function?
947(1)
Can I use Excel's built-in worksheet functions in my VBA code?
948(1)
Is there any way to force a line break in the text of a message box?
948(1)
Objects, Properties, Methods, and Events
948(10)
Is there a listing of the Excel objects I can use?
948(1)
I'm overwhelmed with all the properties and methods available. How can I find out which methods and properties are available for a particular object?
948(1)
What's the story with collections? Is a collection an object? What are collections?
949(1)
When I refer to a worksheet in my VBA code, I get a ``subscript out of range'' error. I'm not using any subscripts. What gives?
949(1)
How can I prevent the user from scrolling around the worksheet?
950(1)
What's the difference between using Select and Application.Goto?
950(1)
What's the difference between activating a range and selecting a range?
950(1)
Is there a quick way to delete all values from a worksheet yet keep the formulas intact?
950(1)
I know how to write a VBA instruction to select a range by using a cell address, but how can I write one to select a range if I know only its row and column number?
951(1)
When I try to record the Office ⇒ Exit Excel command, Excel closes down before I can see what code it generates. Is there a VBA command to quit Excel?
951(1)
How can I turn off screen updating while a macro is running?
951(1)
What's the easiest way to create a range name in VBA?
952(1)
How can I determine whether a particular cell or range has a name?
952(1)
Can I disable the Setup and Margins buttons that are displayed in Excel's Print Preview window?
952(1)
I have a lengthy macro, and it would be nice to display its progress in the status bar. Can I display messages in the status bar while a macro is running?
952(1)
I recorded a VBA macro that copies a range and pastes it to another area. The macro uses the Select method. Is there a more efficient way to copy and paste?
953(1)
I have not been able to find a method to sort a VBA array. Does this mean that I have to copy the values to a worksheet and then use the Range.Sort method?
953(1)
My macro works with the selected cells, but it fails if something else (like a chart) is selected. How can I make sure that a range is selected?
953(1)
How can I determine if a chart is activated?
954(1)
My VBA macro needs to count the number of rows selected by the user. Using Selection.Rows. Count doesn't work when nonadjacent rows are selected. Is this a bug?
954(1)
I use Excel to create invoices. Can I generate a unique invoice number?
954(1)
Is there a workbook property that forces an Excel workbook to always remain visible so it won't be hidden by another application's window?
955(1)
Is there a VBA instruction to select the last entry in a column or row? Normally, I can use Ctrl+Shift+↓ or Ctrl+Shift+→ to do this, but how can I do it with a macro?
955(1)
How can I determine the last non-empty cell in a particular column?
955(1)
VBA references can become very lengthy, especially when I need to fully qualify an object by referencing its sheet and workbook. Can I reduce the length of these references?
956(1)
Can I declare an array if I don't know how many elements it will have?
956(1)
Can I let the user undo my macro?
956(1)
Can I pause a macro so the user can enter data into a certain cell?
956(1)
VBA has an InputBox function, but there's also an InputBox method for the Application object. Are these the same?
957(1)
I'm trying to write a VBA instruction that creates a formula. To do so, I need to insert a quote character (``) within quoted text. How can I do that?
957(1)
I created an array, but the first element in that array is being treated as the second element. What's wrong?
958(1)
I would like my VBA code to run as quickly as possible. Any suggestions?
958(1)
UserForms
958(6)
My macro needs to get just a few pieces of information from the user, and a UserForm seems like overkill. Are there any alternatives?
958(1)
I have 12 CommandButtons on a UserForm. How can I assign a single macro to be executed when any of the buttons is clicked?
959(1)
How can I display a chart in a UserForm?
959(1)
How can I remove the ``X'' from the title bar of my UserForm? I don't want the user to click that button to close the form
959(1)
I created a UserForm with controls that are linked to cells on the worksheet with the ControlSource property. Is this the best way to do this?
959(1)
Can I create a control array for a UserForm? It's possible with Visual Basic, but I can't figure out how to do it with Excel VBA
960(1)
Is there any difference between hiding a UserForm and unloading a UserForm?
960(1)
How can I make my UserForm stay open while I do other things?
960(1)
Excel 97 gives me a compile error when I write UserForm1.Show vbModeless. How can I make the form modeless in Excel 2000 and later while allowing it to remain modal in Excel 97?
960(1)
I need to display a progress indicator like those you see when you're installing software while a lengthy process is being executed. How can I do this?
961(1)
How can I use Excel's shapes on my UserForm?
961(1)
How can I generate a list of files and directories into my UserForm so the user can select a file from the list?
961(1)
I need to concatenate strings and display them in a ListBox control. But when I do so, they aren't aligned properly. How can I get them to display equal spacing between strings?
962(1)
Is there an easy way to fill a ListBox or ComboBox control with items?
962(1)
Can I display a built-in Excel dialog box from VBA?
962(1)
I tried the technique described in the preceding question and received an error message. Why is that?
962(1)
Every time I create a UserForm, I go through the steps of adding an OK button and a Cancel button. Is there a way to get these controls to appear automatically?
963(1)
Can I create a UserForm without a title bar?
963(1)
When I click a button on my UserForm, nothing happens. What am I doing wrong?
963(1)
Can I create a UserForm whose size is always the same, regardless of the video display resolution?
963(1)
Can I create a UserForm box that lets the user select a range in a worksheet by pointing?
963(1)
Can I change the startup position of a UserForm?
963(1)
Can I make a UserForm that's resizable by the user?
963(1)
Add-ins
964(2)
Where can I get Excel add-ins?
964(1)
How do I install an add-in?
964(1)
When I install my add-in from Excel's Add-Ins dialog box, it shows up without a name or description. How can I give my add-in a description?
964(1)
I have several add-ins that I no longer use, but I can't figure out how to remove them from the Add-Ins Available list in the Add-Ins dialog box. What's the story?
964(1)
How do I create an add-in?
965(1)
I try to create an add-in, but the Save as Type drop-down box doesn't provide Add-in as an option
965(1)
Should I convert all my essential workbooks to add-ins?
965(1)
Do I need to keep two copies of my workbook: the XLSM version and the XLAM version?
965(1)
How do I modify an add-in after it has been created?
965(1)
What's the difference between an XLSM file and an XLAM file created from an XLSM file? Is the XLAM version compiled? Does it run faster?
965(1)
How do I protect the code in my add-in from being viewed by others?
966(1)
Are my add-ins safe? In other words, if I distribute an XLAM file, can I be assured that no one else will be able to view my code?
966(1)
User Interface
966(5)
How do I use VBA to add a simple button to the Ribbon?
966(1)
What are my options for modifying the user interface to make it easy for a user to run my macros?
966(1)
How do I add a macro to the Quick Access Toolbar?
967(1)
I added my macro to the QAT, but clicking the icon generates an error
967(1)
How do I use VBA to activate a particular tab on the Ribbon?
967(1)
How can I disable all the right-click shortcut menus?
967(4)
Part VIII: Appendixes
Appendix A: Excel Resources Online
971(40)
The Excel Help System
971(1)
Microsoft Technical Support
972(1)
Support options
972(1)
Microsoft Knowledge Base
972(1)
Microsoft Excel home page
972(1)
Microsoft Office home page
972(1)
Internet Newsgroups
973(2)
Accessing newsgroups by using a newsreader
973(1)
Accessing newsgroups by using a Web browser
973(1)
Searching newsgroups
974(1)
Internet Web sites
975(2)
The Spreadsheet Page
975(1)
Daily Dose of Excel
976(1)
Jon Peltier's Excel Page
976(1)
Pearson Software Consulting
976(1)
Stephen Bullen's Excel Page
976(1)
David McRitchie's Excel Pages
976(1)
Mr. Excel
976(1)
Appendix B: VBA Statements and Functions Reference
977(12)
Invoking Excel functions in VBA instructions
981(8)
Appendix C: VBA Error Codes
989(4)
Appendix D: What's on the CD-ROM
993(18)
System Requirements
993(1)
Using the CD
994(1)
Files and Software on the CD
994(1)
eBook version of Excel 2007 Power Programming with VBA
994(1)
Sample files for Excel 2007 Power Programming with VBA
994(15)
Troubleshooting
1009(2)
Index 1011


John Walkenbach, arguably the foremost authority on Excel, has written hundreds of articles and created the award-winning Power Utility Pak. His 40-plus books include Excel 2007 Bible, Excel 2007 Formulas, and John Walkenbach's Favorite Excel Tips & Tricks, all published by Wiley. Visit his popular Spreadsheet Page at www.j-walk.com/ss.
Püsilink: https://www.kriso.ee/db/9781118257593_pe.html
Märksõnad: