Microsoft Excel Vba Programming for the Absolute Beginner

by Birnbaum, Duane

• ISBN13: 9781931841047
• ISBN10: 1931841047
• Format: Paperback
• Copyright: 2002-04-02
• Publisher: Cengage Learning Ptr

Summary

Designed to help readers with no programming experience learn not only the basics of VBA, but also the fundamental programming concepts they need to grasp in order to learn their next programming language. CD-ROM included. Softcover.

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	p. x
Visual Basic for Applications with Excel	p. 1
Project: Colorful Header	p. 2
The VBA Integrated Development Environment (IDE)	p. 3
Getting to the IDE from Excel	p. 3
Components of the IDE	p. 4
Programming Components within Excel	p. 8
Macro Selection	p. 8
The Visual Basic Toolbar	p. 9
Getting Help with VBA	p. 13
Excel Help	p. 14
VBA Help	p. 14
Installing VBA Help Files	p. 15
Constructing the Colorful Header Program	p. 15
Chapter Summary	p. 19
Beginning Programs with VBA	p. 21
Project: Time of Your Life	p. 22
Variables, Data Types, and Constants	p. 22
Declaring Variables	p. 23
Data Types	p. 27
Constants	p. 36
Simple Input and Output with VBA	p. 37
Collecting User Input with InputBox()	p. 37
Output with MsgBox()	p. 38
Manipulating Strings with VBA Functions	p. 39
Fun with Strings	p. 40
Constructing the Time of Your Life Program	p. 44
Project Statement	p. 44
Project Tools	p. 44
Project Algorithm	p. 44
Adding the Code	p. 45
Chapter Summary	p. 49
Procedures and Conditions	p. 51
Project: Poker Dice	p. 52
VBA Procedures	p. 52
Event Procedures	p. 52
Sub Procedures	p. 56
Function Procedures	p. 61
Logical Operators in VBA	p. 64
Conditionals and Branching	p. 67
Constructing the Poker Dice Program	p. 72
Project Statement	p. 73
Project Tools	p. 73
Project Algorithm	p. 75
Adding the Code	p. 76
Chapter Summary	p. 84
Loops and Arrays	p. 87
Project: The Math Game	p. 88
Looping with VBA	p. 88
Do Loops	p. 89
For Loops	p. 91
Input Validation	p. 94
Validation with the InputBox() Function	p. 95
Validation with a Spreadsheet Cell	p. 97
Arrays	p. 99
One-Dimensional Arrays	p. 101
Multi-Dimensional Arrays	p. 104
Dynamic Arrays	p. 106
Programming Formulas into Worksheet Cells	p. 109
A1 Style References	p. 109
R1C1 Style References	p. 111
Chapter Project: Math Game	p. 112
Project Statement	p. 112
Project Tools	p. 113
Project Algorithm	p. 119
Adding the Code	p. 122
Chapter Summary	p. 131
Basic Excel Objects	p. 133
Project: Battlecell	p. 134
VBA and OOP	p. 134
Objects Defined	p. 135
VBA Collection Objects	p. 137
The Object Browser	p. 139
Top-Level Excel Objects	p. 144
The Application Object	p. 145
The Workbook and Window Objects	p. 145
The Worksheet Object	p. 153
The Range Object	p. 154
Working with Objects	p. 157
Chapter Project: Battlecell	p. 161
Project Statement	p. 161
Project Tools	p. 162
Project Algorithm	p. 162
Adding the Code	p. 164
Chapter Summary	p. 184
Enhancing VBA Programs: Adding Multimedia and Intelligence	p. 187
Project: Enhanced Battlecell	p. 188
Adding Multimedia to a Program	p. 188
Animation in VBA	p. 188
Sound in VBA	p. 200
Simulating Intelligence in Programs	p. 203
Chapter Project: Enhanced Battlecell	p. 204
Project Statement	p. 204
Project Tools	p. 205
Project Algorithm	p. 205
Adding the Code	p. 208
Chapter Summary	p. 224
UserForms and Additional Controls	p. 227
Project: Blackjack	p. 228
UserForms	p. 228
Adding a UserForm to a Project	p. 229
Components of the UserForm Object	p. 229
Adding ActiveX Controls to a UserForm	p. 231
Showing and Hiding UserForms	p. 233
Modal Forms	p. 233
Custom Dialog Boxes with UserForms	p. 235
Chapter Project: Blackjack	p. 246
Project Statement	p. 247
Project Tools	p. 247
Project Algorithm	p. 247
Adding the Code	p. 250
Chapter Summary	p. 267
Data Access, File I/O, Error Handling, and Debugging	p. 269
Project: Word Find	p. 270
File Input and Output (I/O)	p. 270
Using VBA File I/O Methods	p. 271
Error Handling	p. 283
Using the On Error Statement	p. 283
Debugging	p. 287
Break Mode	p. 287
The Immediate Window	p. 289
The Watch Window	p. 290
The Locals Window	p. 291
Chapter Project: Word Find	p. 292
Project Statement	p. 292
Project Tools	p. 293
Project Algorithm	p. 293
Adding the Code	p. 296
Chapter Summary	p. 310
Excel Charts	p. 313
Project: The Alienated Game	p. 314
The Chart Object	p. 314
Accessing Existing Charts	p. 314
Manipulating Charts	p. 318
Creating Charts	p. 322
Chart Events	p. 326
Chapter Project: The Alienated Game	p. 332
Project Statement	p. 333
Project Tools	p. 333
Project Algorithm	p. 333
Adding the Code	p. 335
Chapter Summary	p. 355
VBA Shapes	p. 357
Project: Excetris	p. 358
The Shapes Collection and Shape Objects	p. 358
Manipulating a Shape Object	p. 360
The ShapeRange Collection Object	p. 362
Activating Shape Objects	p. 365
The OLEObjects Collection	p. 366
Chapter Project: Excetris	p. 368
Project Statement	p. 369
Project Tools	p. 369
Project Algorithm	p. 370
Adding the Code	p. 372
Chapter Summary	p. 395
A Final Word	p. 396
Index	p. 397
Table of Contents provided by Syndetics. All Rights Reserved.