Excel VBA Macro Programming / Edition 1

by Richard Shepherd

Overview

Publisher's Note: Products purchased from Third Party sellers are not guaranteed by the publisher for quality, authenticity, or access to any online entitlements included with the product.

Develop powerful, custom Excel applications using Visual Basic for Applications

Maximize the features and functionality of Excel with help from this unique resource designed specifically for power users. Inside, you’ll find complete details on Excel VBA programming and application development—from the fundamentals to advanced techniques. Then, you’ll get 21 real-world projects, complete with working code, so you can create Excel VBA macros right away. These projects include everything from setting up timed events to auto totaling a matrix of numbers to altering the appearance of spreadsheet components—and much more.

• Write and debug VBA code
• Create custom dialog boxes and toolbars
• Take advantage of the Excel object model
• Write code to interact with a database
• Use application programming interface (API) calls
• Create and use class modules
• Develop unique menus within the Excel menu structure
• Expand calculation and search functions
• Write macros for customized worksheets
• Create full-fledged Excel add-ins

Product Details

ISBN-13:	9780072231441
Publisher:	McGraw Hill LLC
Publication date:	01/14/2004
Series:	In-Depth Programming and Web Development Series
Pages:	320
Product dimensions:	7.30(w) x 9.10(h) x 0.70(d)

About the Author

Richard Shepherd (Reading, Berkshire, England) is a qualified accountant with a special interest in computer languages, particularly Visual Basic, and he’s spent the last 20 years developing computer software in blue chip organizations.

Table of Contents

	Acknowledgments	xiii
	Introduction	xiii
Part I	Programming in Excel VBA
Chapter 1	The Basics	3
	Exploring the Visual Basic Editor in Excel	3
Chapter 2	Variables, Arrays, Constants, and Data Types	11
	Variables	11
	Data Types	16
	VBA Data Types	18
	Arrays	20
	User-Defined Types	22
	Constants	23
	Reserved Words	23
Chapter 3	Modules, Functions, and Subroutines	25
	Modules	25
	The Difference Between Subroutines and Functions	26
	Writing a Simple Subroutine	27
	Writing a Simple Function	28
	Public and Private Functions and Subroutines	30
	Argument Data Types	31
	Optional Arguments	31
	Passing Arguments by Value	31
Chapter 4	Programming Basics: Decisions and Looping	33
	Decisions	34
	Looping	37
Chapter 5	Strings and Functions and Message Boxes	43
	Strings	43
	Functions	47
	Conversion Functions	49
	Format Function	50
	Date and Time Functions	54
	SendKeys Command	60
	Message Boxes	64
Chapter 6	Operators	67
	Arithmetic Operators	68
	Comparison Operators	70
	Concatenation Operator	70
	Logical Operators	71
	Other Operators	74
Chapter 7	Debugging	77
	Types of Errors	77
	Design Time, Runtime, and Break Mode	78
	Breakpoints	80
	Using Stop Statements	80
	Running Selected Parts of Your Code	80
	The Debug Window	82
	Events That Can Cause Problems When Debugging	84
	Using Message Boxes in Debugging	84
	Avoiding Bugs	86
Chapter 8	Errors and the Error Function	87
	The Resume Statement	89
	Implications of Error Trapping	90
	Generating Your Own Errors	90
Chapter 9	Dialogs	91
	Viewing Your Form	95
	Displaying Your Form in Code	97
	Populating Your Form	97
	Default Toolbox Controls	98
Chapter 10	Common Dialog Control	107
	Using the Common Dialog Control	108
	Default Dialogs	114
Chapter 11	Command Bars and Buttons	117
	Command Bars	118
	Command Buttons	121
Part II	Object Models
Chapter 12	The Excel Object Model	125
	Properties and Methods Explained	126
	Using the Object Browser	134
	Hierarchy	137
	Recording Macros	138
Chapter 13	The Excel Object Model--Main Objects	141
	Application Object	141
	Workbook Object	147
	Windows Object	149
	Worksheet Object	153
	Range Object	155
Chapter 14	Using Excel to Interact with Other Office Programs	161
	Driving Microsoft Outlook	164
	Driving Excel from Other Office Programs	166
Part III	Advanced Techniques in Excel VBA
Chapter 15	Charts and Graphs	171
Chapter 16	Working with Databases	177
	ODBC Links	177
	Using ADO	179
Chapter 17	API Calls	183
	What Is an API Call?	183
	Using an API Call	184
Chapter 18	Class Modules	193
	Inserting a Class Module	194
	Creating an Object	194
	Creating a Collection	195
	Using the PNames Collection	198
Chapter 19	Animation	199
Part IV	VBA in Action
Chapter 20	Converting Labels to Numbers and Numbers to Labels	205
Chapter 21	Transposing a Range of Cells	209
Chapter 22	Adding Formula Details into Comments	213
Chapter 23	Calculating a Range	217
Chapter 24	Reversing a Label	219
Chapter 25	Who Created the Workbook?	221
Chapter 26	Evaluating a Cell	225
Chapter 27	Sorting Worksheets into Alphabetical Order	229
Chapter 28	Replacing Characters in a String	231
Chapter 29	Timed Events	235
Chapter 30	Auto Totaling a Matrix of Numbers	237
Chapter 31	Absolute and Relative Formulas	241
Chapter 32	Coloring Alternate Rows and Columns of the Spreadsheet	245
Chapter 33	Coloring Cells Containing Formulas	251
Chapter 34	Summing Cells by Reference to a Master Cell	255
Chapter 35	Globally Changing a Range of Values	259
Chapter 36	Displaying Hidden Sheets Without a Password	263
Chapter 37	Searching Multiple Sheets and Workbooks	269
Chapter 38	Brighten Up Your Comments	277
Chapter 39	An Alternative to Message Boxes	287
Chapter 40	Working with Shapes	291
Chapter 41	Turning Your VBA Code into an Add-In	295
Appendix A	ASCII Character Codes	303
	Index	309