Microsoft Excel Vba Programming For The Absolute Beginner

by Birnbaum, Duane

• ISBN13: 9781592007295
• ISBN10: 1592007295
• Edition: 2nd
• Format: Paperback
• Copyright: 2005-03-30
• Publisher: Cengage Learning Ptr

Summary

Written specifically with the beginner in mind, "Microsoft Excel VBA for the Absolute Beginner, Second Edition" is the follow up to the most successful and best selling title in the Absolute Beginner series. The book contains completely updated information written for the current version of Excel, Excel 2003. ?Microsoft Excel VBA for the Absolute Beginner, Second Edition,? is geared towards both students taking introductory programming courses, as well as professionals who frequently use spreadsheets and want to expand their knowledge of the capabilities of Excel by writing their own programs. In recent years, programming has experienced a surge unlike any other technical skill, due to both advancements in technology and the declining cost of computer hardware and software. This boom has created a need for more programmers and this book teaches programming using a very popular software package, making it perfect for new programmers. ?Microsoft Excel VBA for the Absolute Beginner, Second Edition,? uses the Excel VBA language to provide readers with an introduction to programming. Concentrating on introductory programming topics and good programming practices, the book uses the VBA Excel language and the creation of simple games to teach these topics and practices.

Author Biography

Duane Birnbaum began programming in graduate school. Since completing his Ph.D. in physical chemistry, he has been working in academia and industry while teaching on a part-time basis. He is a part-time lecturer in the Computer Science Department at Indiana University/Purdue University -- Indianapolis

Table of Contents

Introduction	x
Visual Basic for Applications with Excel	1	(24)
Project: Colorful Stats	1	(2)
Installing and Enabling VBA	3	(2)
The VBA Integrated Development Environment (IDE)	5	(5)
Getting to the IDE from Excel	5	(1)
Components of the IDE	5	(5)
Programming Components within Excel	10	(5)
Macro Selection	10	(1)
The Visual Basic Toolbar	11	(4)
Getting Help with VBA	15	(2)
VBA Help	16	(1)
Constructing the Colorful Stats Program	17	(5)
Requirements of the Colorful Stats Program	18	(4)
Chapter Summary	22	(3)
Beginning Programs with VBA	25	(30)
Project: Biorhythms and the Time of Your Life	25	(1)
Variables, Data Types, and Constants	26	(14)
Declaring Variables	27	(4)
Data Types	31	(9)
Constants	40	(1)
Simple Input and Output with VBA	40	(2)
Collecting User Input with InputBox()	40	(1)
Output with MsgBox()	41	(1)
Manipulating Strings with VBA Functions	42	(5)
Fun with Strings	43	(4)
Constructing the Biorhythms and the Time of Your Life Program	47	(7)
Requirements for Biorhythms and the Time of Your Life	48	(1)
Designing Biorhythms and the Time of Your Life	48	(1)
Coding Biorhythms and the Time of Your Life	49	(5)
Chapter Summary	54	(1)
Procedures and Conditions	55	(44)
Project: Poker Dice	55	(1)
VBA Procedures	56	(21)
Event Procedures	56	(4)
Private, Public, and Procedure Scope	60	(1)
Sub Procedures	60	(4)
Function Procedures	64	(5)
Logical Operators with VBA	69	(2)
Conditionals and Branching	71	(6)
Constructing the Poker Dice Program	77	(20)
Requirements for Poker Dice	77	(1)
Designing Poker Dice	78	(3)
Coding Poker Dice	81	(16)
Chapter Summary	97	(2)
Loops and Arrays	99	(50)
Project: Math Game	100	(1)
Looping with VBA	100	(7)
Do Loops	101	(2)
For Loops	103	(4)
Input Validation	107	(2)
Validation with the InputBox() Function	107	(2)
Validation with a Spreadsheet Cell	109	(1)
Arrays	109	(11)
One-Dimensional Arrays	111	(3)
Multi-Dimensional Arrays	114	(2)
Dynamic Arrays	116	(4)
Programming Formulas into Worksheet Cells	120	(3)
A1 Style References	120	(2)
R1C1-Style References	122	(1)
Constructing the Math Game	123	(24)
Requirements for the Math Game	123	(1)
Designing the Math Game	124	(10)
Coding the Math Game Program	134	(13)
Chapter Summary	147	(2)
Basic Excel Objects	149	(58)
Project: Battlecell	149	(1)
VBA and Object-Oriented Programming	150	(1)
Objects Defined	151	(2)
VBA Collection Objects	153	(2)
The Object Browser	155	(5)
Top-Level Excel Objects	160	(17)
The Application Object	160	(1)
The Workbook and Window Objects	161	(8)
The Worksheet Object	169	(1)
The Range Object	170	(3)
Working with Objects	173	(4)
Constructing Battlecell	177	(28)
Requirements for Battlecell	177	(3)
Designing Battlecell	180	(2)
Coding Battlecell	182	(23)
Chapter Summary	205	(2)
VBA UserForms and Additional Controls	207	(62)
Project: Blackjack	208	(1)
Designing Forms with VBA	208	(25)
Adding a Form to a Project	209	(1)
Components of the UserForm Object	210	(2)
Adding ActiveX Controls to a Form	212	(1)
Showing and Hiding Forms	213	(1)
Modal Forms	214	(1)
Designing Custom Dialog Boxes Using Forms	215	(18)
Derived Data Types in VBA	233	(4)
Defining Custom Data Types in VBA	234	(1)
Defining Enumerated Types in VBA	235	(2)
Chapter Project: Blackjack	237	(29)
Requirements for Blackjack	237	(2)
Designing Blackjack	239	(7)
Writing the Code for Blackjack	246	(20)
Chapter Summary	266	(3)
Error Handling, Debugging, and Basic File I/O	269	(60)
Project: Word Find	269	(1)
Error Handling	270	(4)
Using the On Error Statement	271	(3)
Debugging	274	(5)
Break Mode	274	(1)
The Immediate Window	275	(2)
The Watch Window	277	(2)
The Locals Window	279	(1)
File Input and Output (I/O)	279	(17)
File I/O Using Workbook and Worksheet Objects	281	(1)
Using VBA File I/O Methods	282	(14)
Chapter Project: Word Find	296	(30)
Requirements for Word Find	296	(2)
Designing Word Find	298	(3)
Writing the Code for Word Find	301	(25)
Chapter Summary	326	(3)
Using XML with Excel-VBA Projects	329	(48)
Project: Revisiting the Math Game	329	(1)
Introduction to XML	330	(19)
What Is XML?	331	(3)
XML Syntax	334	(3)
XML Schemas	337	(1)
XML Validation	338	(1)
XML and Excel	338	(5)
XML and VBA	343	(2)
The XmlMap Object	345	(4)
Chapter Project: The Math Game	349	(24)
Requirements for the Math Game Program	350	(1)
Designing the Math Game	351	(6)
Coding the Math Game Program	357	(16)
Chapter Summary	373	(4)
Excel Charts	377	(50)
Project: The Alienated Game	377	(1)
The Chart Object	378	(18)
Accessing Existing Charts	379	(4)
Manipulating Charts	383	(5)
Creating Charts	388	(4)
Chart Events	392	(4)
Chapter Project: The Alienated Game	396	(31)
Requirements for the Alienated Game	397	(1)
Designing the Alienated Game	397	(5)
Coding the Alienated Game	402	(23)
Chapter Summary	425	(2)
VBA Shapes	427	(46)
Project: Excetris	427	(1)
The Shapes Collection and Shape Objects	428	(4)
Manipulating a Shape Object	430	(2)
The ShapeRange Collection Object	432	(3)
Activating Shape Objects	435	(1)
The OLEObjects Collection	436	(3)
Chapter Project: Excetris	439	(32)
Requirements for Excetris	439	(2)
Designing Excetris	441	(4)
Coding Excetris	445	(26)
Chapter Summary	471	(1)
A Final Word	471	(2)
Index	473