Excel VBA Macro Programming

Make Excel work harder and faster for you. This unique book presents sample code for more than twenty practical, high-powered Excel VBA macro applications. You'll get all the essentials of VBA, and then explore ways to power Excel with VBA. Automate tasks, convert numbers to labels, transpose cells, add formula details, globally changes values, and much, much more.

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.

Acknowledgments

xiii

Introduction

xiii

Part I Programming in Excel VBA

Chapter 1 The Basics

3

(8)

Exploring the Visual Basic Editor in Excel

3

(8)

Chapter 2 Variables, Arrays, Constants, and Data Types

11

(14)

Data Types

16

(2)

VBA Data Types

18

(2)

Arrays

20

(2)

User-Defined Types

22

(1)

Constants

23

(1)

Reserved Words

23

(2)

Chapter 3 Modules, Functions, and Subroutines

25

(8)

Modules

25

(1)

The Difference Between Subroutines and Functions

26

(1)

Writing a Simple Subroutine

27

(1)

Writing a Simple Function

28

(2)

Public and Private Functions and Subroutines

30

(1)

Argument Data Types

31

(1)

Optional Arguments

31

(1)

Passing Arguments by Value

31

(2)

Chapter 4 Programming Basics: Decisions and Looping

33

(10)

Decisions

34

(3)

Looping

37

(6)

Chapter 5 Strings and Functions and Message Boxes

43

(24)

Strings

43

Functions

41

(8)

Conversion Functions

49

(1)

Format Function

50

(4)

Date and lime Functions

54

(6)

SendKeys Command

60

(4)

Message Boxes

64

(3)

Chapter 6 Operators

67

(10)

Arithmetic Operators

68

(2)

Comparison Operators

70

(1)

Concatenation Operator

70

(1)

Logical Operators

71

(3)

Other Operators

74

(3)

Chapter 7 Debugging

77

(10)

Types of Errors

77

(1)

Design Time, Runtime, and Break Mode

78

(2)

Breakpoints

80

(1)

Using Stop Statements

80

(1)

Running Selected Parts of Your Code

80

(2)

The Debug Window

82

(2)

Events That Can Cause Problems When Debugging

84

(1)

Using Message Boxes in Debugging

84

(2)

Avoiding Bugs

86

(1)

Chapter 8 Errors and the Error Function

87

(4)

The Resume Statement

89

(1)

Implications of Error Trapping

90

(1)

Generating Your Own Errors

90

(1)

Chapter 9 Dialogs

91

(16)

Viewing Your Form

95

(2)

Displaying Your Form in Code

97

(1)

Populating Your Form

97

(1)

Default Toolbox Controls

98

(9)

Chapter 10 Common Dialog Control

107

(10)

Using the Common Dialog Control

108

(6)

Default Dialogs

114

(3)

Chapter 11 Command Bars and Buttons

117

(8)

Command Bars

118

(3)

Command Buttons

121

(4)

Part II Object Models

Chapter 12 The Excel Object Model

125

(16)

Properties and Methods Explained

126

(8)

Using the Object Browser

134

(3)

Hierarchy

137

(1)

Recording Macros

138

(3)

Chapter 13 The Excel Object Model-Main Objects

141

(20)

Application Object

141

(6)

Workbook Object

147

(2)

Windows Object

149

(4)

Worksheet Object

153

(2)

Range Object

155

(6)

Chapter 14 Using Excel to Interact with Other Office Programs

161

(10)

Driving Microsoft Outlook

164

(2)

Driving Excel from Other Office Programs

166

(5)

Part III Advanced Techniques in Excel VBA

Chapter 15 Charts and Graphs

171

(6)

Chapter 16 Working with Databases

177

(6)

Chapter 17 API Calls

183

(10)

What Is an API Call?

183

(1)

Using an API Call

184

(9)

Chapter 18 Class Modules

193

(6)

Inserting a Class Module

194

(1)

Creating an Object

194

(1)

Creating a Collection

195

(3)

Using the PNames Collection

198

(1)

Chapter 19 Animation

199

(6)

Part IV VBA in Action

Chapter 20 Converting Labels to Numbers and Numbers to Labels

205

(4)

Chapter 21 Transposing a Range of Cells

209

(4)

Chapter 22 Adding Formula Details into Comments

213

(4)

Chapter 23 Calculating a Range

217

(2)

Chapter 24 Reversing a Label

219

(2)

Chapter 25 Who Created the Workbook?

221

(4)

Chapter 26 Evaluating a Cell

225

(4)

Chapter 27 Sorting Worksheets into Alphabetical Order

229

(2)

Chapter 28 Replacing Characters in a String

231

(4)

Chapter 29 Timed Events

235

(2)

Chapter 30 Auto Totaling a Matrix of Numbers

237

(4)

Chapter 31 Absolute and Relative Formulas

241

(4)

Chapter 32 Coloring Alternate Rows and Columns of the Spreadsheet

245

(6)

Chapter 33 Coloring Cells Containing Formulas

251

(4)

Chapter 34 Summing Cells by Reference to a Master Cell

255

(4)

Chapter 35 Globally Changing a Range of Values

259

(4)

Chapter 36 Displaying Hidden Sheets Without a Password

263

(6)

Chapter 37 Searching Multiple Sheets and Workbooks

269

(8)

Chapter 38 Brighten Up Your Comments

277

(10)

Chapter 39 An Alternative to Message Boxes

287

(4)

Chapter 40 Working with Shapes

291

(4)

Chapter 41 Turning Your VBA Code into an Add-In

295

(8)

Appendix A ASCII Character Codes

303

(6)

Index

309

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Excel VBA Macro Programming

Summary

Author Biography

Table of Contents

Supplemental Materials