Excel Programming: Your visual... | Buy

Each step in this visual reference is displayed with callouts so you can see exactly where the action takes place on the screen. It covers over 100 tasks, including complete coverage of macros -- with info about VBA basic techniques, variables and arrays, control statements, interfacing with other macros, debugging, add-ins, and automation.

About the Author Jinjer Simon has been actively involved in the computer industry for the past 17 years. Her involvement in the industry has included programming, providing software technical support, training end-users, developing written and online user documentation, creating software tutorials, and developing Web sites. She is the author of several computer books, including Windows CE For Dummies and Windows CE 2 For Dummies. Jinjer and her husband live in Coppell, Texas with their two children. She currently works as a consultant for MillenniSoft, Inc., providing Web site development and online documentation development.

How to use This Book

xiv

Getting Started With Excel Macros

An Introduction to Macros

2

(2)

Record a Macro

4

(2)

Run a Macro

6

(2)

Create and Launch a Keyboard Shortcut

8

(2)

Delete a Macro from a Workbook

10

(2)

Delete from the Personal Macro Workbook

12

(2)

Assign a Macro to a Toolbar Button

14

(2)

Assign a Macro to a Menu

16

(2)

Set Macro Security

18

(2)

Using the Visual Basic Editor

An Introduction to the Visual Basic Editor

20

(2)

Activate the Visual Basic Editor

22

(2)

Arrange the Visual Basic Editor Windows

24

(2)

Set Properties for a Project

26

(2)

Set Display Options for the Code Window

28

(2)

Add a New Module

30

(2)

Remove a Module

32

(2)

Rename a Macro

34

(2)

Create a Startup Macro

36

(2)

Hide a Macro

38

(2)

Assign a Digital Signature to a Macro

40

(2)

Update a Recorded Macro

42

(2)

VBA Programming Basics

An Introduction to VBA

44

(6)

Create a Subroutine

50

(2)

Create a Function

52

(2)

Declare a Variable

54

(2)

Perform Mathematical Calculations

56

(2)

Create a Constant

58

(2)

Comment Your Code

60

(2)

Join Two Strings

62

(2)

Working With the Excel Object Model

An Introduction to the Excel Object Model

64

(2)

Using the Object Browser

66

(2)

Create an Object Variable

68

(2)

Change the Properties of an Object

70

(2)

Compare Object Variables

72

(2)

Using an Object Method

74

(2)

Display a Built-in Dialog Box

76

(2)

Using Variables and Arrays

Assign Values to Variables

78

(2)

Using Global Variables

80

(2)

Declare an Array

82

(2)

Declare a Multidimensional Array

84

(2)

Convert a List into an Array

86

(2)

Redimension an Array

88

(2)

Create a User-Defined Data Type

90

(2)

Adding Control Statements

Execute a Task While a Condition is True

92

(2)

Perform Multiple Tasks Until a Condition is Met

94

(2)

Execute Tasks a Specific Number of Times

96

(2)

Using the For Each Next Loop

98

(2)

Conditionally Execute a Group of Statements

100

(2)

Execute a Statement Based Upon the Value

102

(2)

Jump to a Specific Location in a Macro

104

(2)

Conditionally Call a Subroutine

106

(2)

Using Built-in Functions and Statements

Using Excel Worksheet Functions

108

(2)

Using the MsgBox Function

110

(2)

Using the InputBox Function

112

(2)

Retrieve Current Date and Time

114

(2)

Determine the Amount of Time Between Dates

116

(2)

Format a Date Expression

118

(2)

Format a Numeric Expression

120

(2)

Remove Extra Spacing from a String

122

(2)

Return a Portion of a String

124

(2)

Compare Two Strings

126

(2)

Debugging Macros

Debugging Basics

128

(2)

Debug a Procedure with Inserted Break Points

130

(2)

Using Watch Expressions to Debug a Procedure

132

(2)

Step through a Procedure

134

(2)

Resume Execution if an Error is Encountered

136

(2)

Process a Runtime Error

138

(2)

Working With Other Workbooks and Files

Open a Workbook

140

(2)

Open a Text File as a Workbook

142

(2)

Open a File Requested by the User

144

(2)

Save a Workbook

146

(2)

Save Workbook in Format Specified by User

148

(2)

Determine if a Workbook is Open

150

(2)

Close a Workbook

152

(2)

Create a New Workbook

154

(2)

Delete a File

156

(2)

Find a File

158

(2)

Working With Worksheets

Add a Sheet

160

(2)

Delete a Sheet

162

(2)

Move a Sheet

164

(2)

Copy and Paste a Sheet

166

(2)

Hide a Sheet

168

(2)

Change the Name of a Sheet

170

(2)

Save a Sheet to Another File

172

(2)

Protect a Worksheet

174

(2)

Protect a Chart

176

(2)

Print a Sheet

178

(2)

Sort Worksheets by Name

180

(2)

Defining Ranges

Using the Range Property

182

(2)

Using the Cells Property

184

(2)

Combine Multiple Ranges

186

(2)

Using the Offset Property

188

(2)

Delete a Range of Cells

190

(2)

Hide a Range of Cells

192

(2)

Specify the Name of a Range

194

(2)

Resize a Range

196

(2)

Insert a Range

198

(2)

Set the Width of Columns in a Range

200

(2)

Set the Height of Rows in a Range

202

(2)

Convert a Column of Text into Multiple Columns

204

(2)

Find the Intersection of Two Ranges

206

(2)

Working With Cells

Cut and Paste Ranges of Cells

208

(4)

Custom Paste Values in Cells

212

(2)

Add Comments to a Cell

214

(2)

Automatically Fill a Range of Cells

216

(2)

Copy a Range to Multiple Sheets

218

(2)

Place Borders Around a Range of Cells

220

(2)

Find Specific Cell Values

222

(2)

Find and Replace Values in Cells

224

(2)

Customizing Dialog Boxes, Menus, and Toolbars

UserForm Basics

226

(2)

Working with CommandBars

228

(2)

Create a Custom Dialog Box

230

(2)

Call a Custom Dialog Box from a Procedure

232

(2)

Capture Input from a Custom Dialog Box

234

(4)

Validate Input from a Dialog Box

238

(2)

Create Custom UserForm Controls

240

(2)

Create a UserForm Template

242

(2)

Create a Custom Toolbar

244

(2)

Add Controls to a Toolbar

246

(2)

Create a Custom Menu

248

(2)

Add Items to a Menu

250

(2)

Create a Shortcut Menu

252

(2)

Delete Custom Toolbars and Shortcut Menus

254

(2)

Working With Charts

Chart Basics

256

(2)

Create a Chart Sheet

258

(2)

Embed a Chart within a Worksheet

260

(2)

Apply Chart Wizard Settings to a Chart

262

(2)

Add a New Data Series to a Chart

264

(2)

Format Chart Text

266

(2)

Create Charts with Multiple Chart Types

268

(2)

Determine Variations in a Series of Data

270

(2)

Add a Data Table to the Chart

272

(2)

Customize the Chart Axis

274

(2)

Automating Procedures With Excel Events

Understanding Excel Events

276

(4)

Run a Procedure as a Workbook Opens

280

(2)

Run a Procedure Before Closing a Workbook

282

(2)

Run a Procedure Before Saving a Workbook

284

(2)

Run a Procedure When Excel Creates a Workbook

286

(4)

Monitor a Range of Cells for Changes

290

(2)

Execute a Procedure at a Specific Time

292

(2)

Execute a Procedure When You Press Keys

294

(2)

Run a Procedure When Right Clicking a Chart

296

(2)

Appendix A: VBA Quick Reference

VBA and Excel Object Model Quick Reference

298

(24)

Appendix B: About the Cd-Rom

What's on the CD-ROM

322

(2)

Using the E-Version of the Book

324

(2)

Hungry Minds, Inc. End-User License Agreement

326

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.