Excel Programming Weekend Crash Course®

by Aitken, Peter G.

• ISBN13: 9780764540622
• ISBN10: 0764540629
• Edition: 1st
• Format: Paperback
• Copyright: 2003-10-10
• Publisher: Wiley

Summary

Thirty clearly defined lessons take the reader from understanding the parts of an Excel application into building applications to work with data, formulas, charts, and the enhanced XML capabilities of the new Excel "X" Book is designed to teach the core concepts of Excel over a weekend or in just fifteen hours, with each session being thirty minutes Applicable to Excel 2000, Excel 2002, and the latest release, Excel 2003 Helps Excel power users in fields such as accounting, finance, operations management, and market research to begin automating data manipulation in Excel quickly, so they can handle real-world projects A how-to guide to using Excel's programmability to create custom data-processing and analysis solutions Covers security, debugging, and error handling Companion Web site includes sample files, projects, and test enginewith self-assessment exam

Author Biography

<b>Peter G. Aitken</b> has been writing about computers and programming for over 10 years, with some 30 books as well as hundreds of magazine and trade publication articles to his credit. His recent book titles include <i>Visual Basic .NET Programming with Peter Aitken</i>, <i>Office XP Development with VBA, XML the Microsoft Way</i>, <i>Windows Script Host</i>, and <i>Teach Yourself Visual Basic .NET Internet Programming in 21 Days</i>. For several years he was a Contributing Editor at <i>Visual Developer Magazine</i> where he wrote a popular Visual Basic column. He is a regular contributor to <i>Microsoft OfficePro</i> magazine and the DevX Web site. Peter is the proprietor of PGA Consulting, providing custom application and Internet development to business, academia, and government since 1994.

Table of Contents

Preface	v
Acknowledgments	xiii
FRIDAY
PART I-Friday Evening	4	(62)
SESSION 1-Microsoft Excel Programming-Why and How	5	(12)
Advantages of Programming	5	(1)
Saving Time	6	(1)
Reducing Errors	6	(1)
Enforcing Standards	6	(1)
Integrating with Other Applications	6	(1)
Programming Fundamentals	6	(1)
Creating Instructions	7	(1)
Handling Data	7	(1)
The VBA Language	7	(1)
The Excel Object Model	8	(2)
Objects	8	(1)
Components and Automation	9	(1)
Macros and Programming	10	(1)
Designing Your Custom Application	10	(1)
Your First Excel Program	11	(3)
Creating and Naming the Program	11	(1)
Writing the Code	12	(2)
Running the Program	14	(3)
SESSION 2-The VBA Code Editor	17	(14)
Code and Project Organization	17	(3)
The Project Explorer	19	(1)
Importing and Exporting Modules	19	(1)
Editing Tools	20	(2)
The Property Window	22	(1)
The VBA Editor Menus	23	(1)
Using Macros in Programming	24	(7)
Recording a Macro	25	(1)
Cell References in Macros	26	(1)
Viewing and Running Macros	27	(1)
Online Help	27	(4)
SESSION 3-The Excel Object Model	31	(16)
Understanding Properties and Methods	31	(1)
The Importance of Object References	32	(1)
Working with Collections	33	(2)
The Object Hierarchy	35	(1)
The Workbook Object	36	(5)
Creating and Opening Workbooks	36	(1)
Saving and Closing Workbooks	37	(1)
Referencing Workbooks	38	(1)
Printing Workbooks	39	(1)
E-Mailing a Workbook	39	(1)
Other Workbook Methods and Properties	40	(1)
The Worksheet Object	41	(6)
Adding and Deleting Worksheets	41	(2)
Referencing Worksheets	43	(1)
Copying and Moving Worksheets	44	(3)
SESSION 4-Syntax and Data in VBA	47	(21)
VBA Syntax Fundamentals	47	(2)
Comments	48	(1)
Source Code Formatting	49	(1)
Employing Constants	49	(1)
Declaring and Using Variables	50	(5)
Numeric Variables	51	(1)
String Variables	52	(1)
Date Variables	53	(1)
Object Variables	53	(1)
Boolean Variables	54	(1)
The Variant Type	54	(1)
Using Arrays	55	(6)
Static Arrays	55	(1)
Dynamic Arrays	56	(3)
User-Defined Types	59	(1)
Enumerations	60	(1)
Understanding Variable Scope	61	(5)
SATURDAY	66	(204)
PART II-Saturday Morning	68	(69)
SESSION 5-Operators	69	(8)
The Assignment Operator	69	(1)
Numerical Operators	70	(1)
String Operators	71	(1)
Logical Operators	71	(1)
Comparison Operators	72	(2)
Operator Precedence	74	(3)
SESSION 6-Control Constructs	77	(10)
The If...Then Statement	78	(2)
The Select Case Statement	80	(1)
The Do...Loop Statement	81	(2)
The For...Next Statement	83	(1)
The For Each...Next Statement	84	(1)
The Goto Statement	85	(2)
SESSION 7-Procedures and Modules	87	(10)
Sub Procedures	88	(5)
Procedure Arguments	89	(1)
Optional Arguments	89	(1)
ParamArray Arguments	90	(1)
ByVal and ByRef Arguments	90	(1)
Calling Procedures	91	(1)
Argument Type Checking	92	(1)
Function Procedures	93	(1)
Variables in Procedures	94	(1)
Procedure Scope	95	(1)
Storing Procedures	95	(2)
SESSION 8-Working with Dates and Times	97	(10)
The Date Data Type	97	(1)
Date/Time Values	98	(1)
Date Calculations	99	(1)
Date and Time Details	100	(3)
Formatting Date/Time Values	103	(4)
SESSION 9-Working with Text	107	(14)
Text Input and Output	107	(4)
The MsgBox Function	107	(3)
InputBox Function	110	(1)
Searching for Text	111	(1)
Comparing Strings	112	(1)
String Conversions	112	(2)
The StrConv Function	112	(1)
The LCase and UCase Functions	113	(1)
The Val Function	113	(1)
The Str Function	114	(1)
Working with ASCII Values	114	(3)
The Asc, AscB, and AscW Functions	115	(2)
The Chr Function	117	(1)
Extracting or Modifying Parts of Strings	117	(4)
The Left and Right Functions	117	(1)
The Mid Function	118	(1)
The Mid Statement	118	(1)
Other String Functions	119	(2)
SESSION 10-Using Ranges and Selections	121	(19)
The Range Object	121	(9)
Manipulating Ranges	122	(1)
Relative Ranges	123	(4)
Other Range References	127	(1)
Working with Comments	128	(2)
Range Columns, Rows, and Size	130	(1)
Reading Data from Ranges	130	(2)
Naming Ranges	132	(2)
The Selection Property	134	(3)
PART II-Saturday Morning Part Review	137	(1)
Saturday Afternoon	140	(68)
SESSION 11-Working with Columns, Rows, and Cells	141	(10)
Referencing Cells with the Cells Property	141	(4)
Referencing All Cells	142	(1)
Referencing by Row and Column	142	(3)
Referencing by Cell Position	145	(1)
The Special Cells Method	145	(2)
Manipulating Columns and Rows	147	(2)
Adding and Deleting Rows and Columns	149	(2)
SESSION 12-Programming with Custom Formulas	151	(10)
Cell References in Formulas	151	(5)
Relative Cell References	152	(1)
Absolute Cell References	152	(2)
Named Range References	154	(1)
Referencing Cells in Other Worksheets and Workbooks	155	(1)
Operators	156	(1)
Mathematical Operators	156	(1)
Comparison Operators	156	(1)
Logical Operators	156	(1)
Avoiding Circular References	157	(1)
Controlling Formula Calculation	158	(3)
SESSION 13-Programming with Excel's Built-In Functions	161	(10)
Excel's Built-in Functions	161	(1)
Using Excel Functions in Formulas	162	(1)
Excel Function Overview	163	(6)
Financial Functions	163	(3)
Date and Time Functions	166	(1)
Math and Trig Functions	167	(1)
Text Functions	168	(1)
The Worksheet Function Object	169	(2)
SESSION 14-Formatting a Worksheet	171	(16)
Cell Formatting	171	(13)
Number Formatting	173	(2)
Font Formatting	175	(1)
Alignment and Orientation of Data	176	(2)
Cell Borders	178	(3)
Cell Backgrounds	181	(3)
Changing Row and Column Size	184	(3)
SESSION 15-Find and Replace Operations	187	(8)
Finding Data	187	(5)
The Find Method	188	(1)
The FindNext and FindPrevious Methods	189	(3)
Replacing Data	192	(3)
SESSION 16-Creating Custom Toolbars	195	(13)
Customizing Toolbars in Excel	195	(7)
Displaying and Hiding Toolbars	195	(2)
Creating a New Toolbar	197	(1)
Adding and Removing Toolbar Buttons	198	(1)
Running Programs from Toolbar Buttons	199	(3)
Distributing Toolbars	202	(1)
Hiding and Displaying Toolbars in VBA Code	203	(5)
PART IV-Saturday Evening	208	(62)
SESSION 17-Introduction to Charts	209	(16)
Embedded Charts and Chart Sheets	209	(3)
Embedded Charts	211	(1)
Chart Sheets	211	(1)
The Chart Object	212	(4)
Identifying Data to Be Plotted	212	(2)
Specifying Chart Type	214	(2)
Controlling Chart Appearance	216	(3)
Displaying Chart Titles	216	(1)
Chart Axis Titles	217	(2)
Working with Fonts in a Chart	219	(3)
The ChartWizard Method	222	(3)
SESSION 18-Advanced Charting Techniques	225	(14)
Naming and Referencing Charts	225	(1)
Locking Charts	226	(1)
The Chart Object Object	227	(4)
Using Scatter Charts	231	(4)
Scatter Chart Types	233	(1)
Changing Axis Range	234	(1)
Printing Charts	235	(4)
SESSION 19-Creating Custom Dialog Boxes with User Forms	239	(12)
Overview of User Forms	239	(1)
Form Designer Basics	240	(3)
Designing the Interface	241	(1)
Setting Properties	242	(1)
Form Properties	243	(2)
Appearance Properties	243	(1)
Behavior and Position Properties	244	(1)
Form Methods	245	(1)
Displaying, Using, and Hiding Forms	246	(1)
A Simple User Form Example	247	(4)
SESSION 20-Controls for User Forms	251	(19)
A Summary of Controls	251	(1)
Common Control Properties	252	(1)
User Form Control Details	253	(18)
The CheckBox Control	253	(1)
The ComboBox Control	253	(3)
The CommandButton Control	256	(2)
The Frame Control	258	(1)
The Label Control	258	(1)
The OptionButton Control	258	(2)
The RefEdit Control	260	(2)
The TextBox Control	262	(1)
The ToggleButton Control	263	(7)
SUNDAY
PART V-Sunday Morning	270	(76)
SESSION 21-Advanced User Form Techniques	271	(12)
Using Control Events	271	(2)
Advanced Form Design	273	(10)
The Form Grid	273	(1)
Control Placement and Alignment Tools	274	(4)
Overlapping Controls and the Z-Order	278	(1)
Focus and the Tab Order	278	(5)
SESSION 22-A User Form Example	283	(12)
Planning the Project	283	(2)
Part 1: Creating the Workbook	285	(1)
Part 2: Designing the Form	285	(1)
Part 3: Writing the Initialization Code	286	(1)
Part 4: Restricting Zip Code Entry to Digits	287	(1)
Part 5: Writing the Data Validation Code	288	(1)
Part 6: Completing the Project	289	(3)
Part 7: Testing the Project	292	(3)
SESSION 23-Working with Events	295	(16)
Event Categories	295	(1)
Event Handler Code	296	(1)
Event Sequences	297	(1)
Enabling and Disabling Events	297	(1)
Workbook Events	298	(2)
The Open Event	299	(1)
The NewSheet Event	300	(1)
Worksheet Events	300	(3)
The Change Event	301	(1)
Using the Change Event for Data Validation	301	(2)
Application Events	303	(3)
Writing Application Event Procedures	304	(1)
The WorkbookBeforeClose Event	305	(1)
Other Events	306	(5)
The OnTime Event	307	(1)
The OnKey Event	308	(3)
SESSION 24-Security Considerations	311	(10)
Workbook Protection	311	(1)
Worksheet Protection	312	(3)
VBA Code and Worksheet Protection	314	(1)
Protecting the Loan Calculator Worksheet	315	(1)
Protecting Your VBA Code	315	(1)
Macro Security	316	(1)
Adding a Trusted Source	317	(1)
Removing a Trusted Source	317	(1)
Using Digital Certificates to Sign Macros	317	(4)
SESSION 25-Debugging and Distributing an Application	321	(8)
Debugging an Application	321	(8)
What Are Bugs?	321	(1)
Avoiding Bugs	322	(1)
Debugging Tools	323	(1)
Breakpoints	323	(1)
Step Commands	324	(1)
Using Watches	325	(1)
Using Quick Watches	327	(1)
Distributing an Application	327	(2)
SESSION 26-Defining and Using Custom Classes	329	(17)
Class Fundamentals	329	(1)
The Advantages of Classes	330	(1)
Instantiating Classes	330	(1)
Class Properties	331	(5)
Creating Property Procedures	331	(1)
The Property Variable	332	(1)
Connecting the Property to the Property Procedures	333	(1)
Array Properties	334	(1)
Read-Only Properties	335	(1)
Accessing Properties in Code	336	(1)
Class Methods	336	(1)
Property Validation	336	(1)
Class Events	337	(1)
Reusing a Class	337	(1)
A Class Demonstration	337	(9)
PART VI-Sunday Afternoon	346	(45)
SESSION 27-Handling Runtime Errors	347	(10)
What's a Runtime Error?	347	(3)
The Causes of Errors	348	(1)
Preventing Errors	349	(1)
Trapping Errors	350	(1)
The Err Object	351	(1)
Error-Handling Code	352	(1)
Deferring Error Handling	353	(1)
Some Error-Handling Examples	353	(4)
Ignoring an Error	354	(1)
Notifying the User of an Error	354	(1)
Using an Error as a Programming Tool	355	(2)
SESSION 28-Database Tasks	357	(10)
Databases and Excel	357	(1)
Database Fundamentals	358	(1)
Sorting Data	359	(1)
Filtering Data	360	(3)
Data Entry Forms	363	(1)
Database Functions	364	(3)
SESSION 29-Creating Add-Ins	367	(12)
Add-Ins versus Workbooks	367	(2)
Creating an Add-In	369	(1)
Distributing an Add-In	370	(1)
Using Excel's Add-In Manager	370	(1)
Events in Add-Ins	371	(1)
Functions in Add-Ins	371	(1)
Using VBA to Manipulate Add-Ins	372	(1)
An Add-In Demonstration	373	(6)
Code the Function	374	(1)
Code the Program	375	(1)
Create the Toolbar	375	(2)
Securing the Code and Saving the Add-In	377	(1)
Testing the Add-In	377	(2)
SESSION 30-Adding Online Help to Your Application	379	(12)
Online Help for Excel	379	(1)
Using Excel Components for Help	380	(4)
Putting Help in the Worksheet	380	(1)
Putting Help in a Separate Worksheet	381	(3)
Putting Help in a User Form	384	(1)
Using External Components for Help	384	(7)
HTML Help	384	(2)
Web-Based Help	386	(5)
Appendix A-Answers to Part Reviews	391	(6)
Friday Evening Review Answers	391	(1)
Saturday Morning Review Answers	392	(1)
Saturday Afternoon Review Answers	393	(1)
Saturday Evening Review Answers	394	(1)
Sunday Morning Review Answers	395	(1)
Sunday Afternoon Review Answers	396	(1)
Appendix B-What's on the Web Site	397	(2)
The Self-Assessment Test	397	(1)
Downloadable Examples from the Exercises in the Book	397	(1)
Troubleshooting	397	(2)
Index	399