Excel Programming: Your visual blueprint for creating interactive spreadsheets, 3rd Edition

HOW TO USE THIS BOOK V

1 USING MACROS AND FORM CONTROLS 2

Introducing Excel Programming 2

Introducing Macros 4

Set Macro Security 6

Create a Digital Signature 7

Record a Macro 8

Assign a Digital Signature to a Macro 10

Run a Macro 12

Create and Launch a Keyboard Shortcut 14

Assign a Macro to the Quick Access Toolbar 16

Delete a Macro 18

Add a Form Control to a Worksheet 20

Assign Values to a Form Control 22

Add a Macro to a Form Control 24

2 USING THE VISUAL BASIC EDITOR 26

Introducing the Visual Basic Editor 26

Activate the Visual Basic Editor 28

Open Visual Basic Editor Windows 30

Set Properties for a Project 32

Set Display Options for the Code Window 34

Add a New Module 36

Remove a Module 38

Hide a Macro 40

Update a Macro 42

3 INTRODUCING VISUAL BASIC FOR APPLICATIONS 44

Create Sub Procedures 44

Create Functions 46

Comment Your Code 48

Reference Cells and Ranges 50

Understanding Variables and Data Types 52

Declare Variables 54

Work with Strings 56

Work with Numbers 58

Create a Constant 60

4 INTRODUCING THE EXCEL OBJECT MODEL 62

Discover the Excel Object Model 62

Access the Excel Object Model Reference 64

Create an Object Variable 66

Change the Properties of an Object 68

Compare Object Variables 70

Using an Object Method 72

Display a Built-in Dialog Box 74

5 UNDERSTANDING ARRAYS 76

Declare an Array 76

Declare a Multidimensional Array 78

Convert a List to an Array 80

Redimension an Array 82

Create a User-Defined Data Type 84

6 CONTROLLING PROGRAM FLOW 86

Create Comparisons 86

Make Use of Logical Operators 87

Employ Do While Loops 88

Create Do Until Loops 90

Create For Next Loops 92

Execute For Each In Loops 94

Create If Then Else Statements 96

Construct Select Case Statements 98

GoTo a Named Location 100

Call a Procedure 102

7 USING EXCEL WORKSHEET FUNCTIONS 104

Work with Excel Worksheet Functions 104

Work with a MsgBox Function 106

Using the InputBox Function 108

Retrieve the Current Date and Time 110

Perform Date and Time Calculations 112

Format a Date Expression 114

Format a Numeric Expression 116

Change the Case of a String 118

Return a Portion of a String 120

8 DEBUGGING MACROS 122

Debug a Procedure with Inserted Breakpoints 122

Using the Watches Window to Debug a Procedure 124

Step through a Procedure 126

Using the Immediate Window 128

Resume Execution When an Error Is Encountered 130

Process a Runtime Error 132

9 WORKING WITH WORKBOOKS AND FILES 134

Open a Workbook 134

Open a Text File as a Workbook 136

Open a File Requested by the User 138

Save a Workbook 140

Save a Workbook in a Format Specified by the User 142

Determine if a Workbook Is Open 144

Close a Workbook 146

Create a New Workbook 148

Delete a File 150

10 WORKING WITH WORKSHEETS 152

Add a Sheet 152

Delete a Sheet 154

Move a Sheet 156

Copy a Sheet 158

Hide a Sheet 160

Change the Name of a Sheet 162

Save a Sheet to Another File 164

Protect a Worksheet 166

Protect a Chart 168

Print a Sheet 170

Sort Sheets by Name 172

11 DEFINING RANGES 174

Using the Range Property 174

Using the Cells Property 176

Combine Multiple Ranges 178

Using the Offset Property 180

Delete a Range of Cells 182

Hide a Range of Cells 184

Create a Range Name 186

Resize a Range 188

Insert a Range 190

Set the Width of Columns in a Range 192

Set the Height of Rows in a Range 194

12 WORKING WITH CELLS 196

Cut and Paste Ranges of Cells 196

Copy and Paste Ranges of Cells 198

Using Paste Special Options When Pasting 200

Add Comments to a Cell 202

Automatically Fill a Range of Cells 204

Copy a Range to Multiple Sheets 206

Add a Border 208

Find Specific Cell Values 210

Find and Replace Values in Cells 212

13 WORKING WITH LISTS 214

Convert a Column of Text into Multiple Columns 214

Perform a Sort 216

Perform a Filter 220

Perform an Advanced Filter 222

Create Subtotals 224

Create Groups 226

Define a List as a Table 228

14 CREATING DIALOG BOXES AND CUSTOMIZING THE RIBBON
230

UserForm Basics 230

Create a Custom Dialog Box 232

Call a Custom Dialog Box from a Procedure 234

Capture Input from a Custom Dialog Box 236

Validate Input from a Dialog Box 240

Create Custom UserForm Controls 242

Create a UserForm Template 244

Customize the Ribbon 246

Create a CustomUI.xml File 250

Add a CustomUI.xml File to a Workbook 252

Add Additional Options to the Ribbon 254

15 WORKING WITH CHARTS 258

Create a Chart Sheet 258

Embed a Chart in a Worksheet 260

Apply Chart Wizard Settings to a Chart 262

Add a New Data Series to a Chart 264

Format Chart Text 266

Create Charts with Multiple Chart Types 268

Add a Data Table to a Chart 270

16 WORKING WITH PIVOTTABLES 272

Create a PivotTable 272

Add Fields to a PivotTable 274

Display Subtotals and Grand Totals 276

Filter a PivotTable 278

Create Groups 279

17 AUTOMATING PROCEDURES WITH EXCEL EVENTS 280

Understanding Excel Events 280

Run a Procedure as a Workbook Opens 284

Run a Procedure before Closing a Workbook 286

Run a Procedure before Saving a Workbook 288

Run a Procedure When Excel Creates a Workbook 290

Execute a Procedure at a Specific Time 294

Execute a Procedure When You Press Keys 296

Monitor a Range of Cells for Changes 298

18 BUILDING ADD-INS 300

Create an Add-In 300

Set Add-In Properties 302
Install Add-Ins 304

Using VBA to Load Add-Ins 306

19 UNDERSTANDING XML 308

Introducing XML 308

Understanding Excel XML Files 310

Open an XML File in Excel as a Table 314

Create an XML Map 316

Import and Export XML Files Using Excel 318

Load XML Files Using VBA 320

Import XML Files Using VBA 322

APPENDIX: VBA QUICK REFERENCE 324

INDEX 342