Microsoft Office Excel 2007 Programming: Your visual blueprint for creating interactive spreadsheets

Microsoft Office Excel 2007 Programming~ Your visual blueprint for creating interactive spreadsheets - Denise Etheridge (9780470132302) ......Page 1
Table of Contents ......Page 9
How To Use This Book ......Page 15
Introducing Excel Programming ......Page 17
Introducing Macros ......Page 19
Set Macro Security ......Page 21
Create a Digital Signature ......Page 22
Record a Macro ......Page 23
Assign a Digital Signature to a Macro ......Page 25
Run a Macro ......Page 27
Create and Launch a Keyboard Shortcut ......Page 29
Assign a Macro to the Quick Access Toolbar ......Page 31
Delete a Macro ......Page 33
Add a Form Control to a Worksheet ......Page 35
Assign Values to a Form Control ......Page 37
Add a Macro to a Form Control ......Page 39
Introducing the Visual Basic Editor ......Page 41
Activate the Visual Basic Editor ......Page 43
Open Visual Basic Editor Windows ......Page 45
Set Properties for a Project ......Page 47
Set Display Options for the Code Window ......Page 49
Add a New Module ......Page 51
Remove a Module ......Page 53
Hide a Macro ......Page 55
Update a Macro ......Page 57
Create Sub Procedures ......Page 59
Create Functions ......Page 61
Comment Your Code ......Page 63
Understanding Variables and Data Types ......Page 65
Reference Cells and Ranges ......Page 67
Declare Variables ......Page 69
Work with Numbers ......Page 71
Work with Strings ......Page 73
Create a Constant ......Page 75
Discover the Excel Object Model ......Page 77
Access the Excel Object Model Reference ......Page 79
Create an Object Variable ......Page 81
Change the Properties of an Object ......Page 83
Compare Object Variables ......Page 85
Using an Object Method ......Page 87
Display a Built-in Dialog Box ......Page 89
Declare an Array ......Page 91
Declare a Multidimensional Array ......Page 93
Convert a List to an Array ......Page 95
Redimension an Array ......Page 97
Create a User-Defined Data Type ......Page 99
Create Comparisons ......Page 101
Make Use of Logical Operators ......Page 102
Employ Do While Loops ......Page 103
Create Do Until Loops ......Page 105
Create For Next Loops ......Page 107
Execute For Each In Loops ......Page 109
Create If Then Else Statements ......Page 111
Construct Select Case Statements ......Page 113
GoTo a Named Location ......Page 115
Call a Procedure ......Page 117
Work with Excel Worksheet Functions ......Page 119
Work with a MsgBox Function ......Page 121
Using the InputBox Function ......Page 123
Retrieve the Current Date and Time ......Page 125
Perform Date and Time Calculations ......Page 127
Format a Date Expression ......Page 129
Format a Numeric Expression ......Page 131
Change the Case of a String ......Page 133
Return a Portion of a String ......Page 135
Debug a Procedure with Inserted Break Points ......Page 137
Using the Watches Window to Debug a Procedure ......Page 139
Step through a Procedure ......Page 141
Use the Immediate Window ......Page 143
Resume Execution When an Error Is Encountered ......Page 145
Process a Run-Time Error ......Page 147
Open a Workbook ......Page 149
Open a Text File as a Workbook ......Page 151
Open a File Requested by the User ......Page 153
Save a Workbook ......Page 155
Save a Workbook in a Format Specified by the User ......Page 157
Determine if a Workbook Is Open ......Page 159
Close a Workbook ......Page 161
Create a New Workbook ......Page 163
Delete a File ......Page 165
Add a Sheet ......Page 167
Delete a Sheet ......Page 169
Move a Sheet ......Page 171
Copy and Paste a Sheet ......Page 173
Hide a Sheet ......Page 175
Change the Name of a Sheet ......Page 177
Save a Sheet to Another File ......Page 179
Protect a Worksheet ......Page 181
Protect a Chart ......Page 183
Print a Sheet ......Page 185
Sort Worksheets by Name ......Page 187
Using the Range Property ......Page 189
Using the Cells Property ......Page 191
Combine Multiple Ranges ......Page 193
Using the Offset Property ......Page 195
Delete a Range of Cells ......Page 197
Hide a Range of Cells ......Page 199
Create a Range Name ......Page 201
Resize a Range ......Page 203
Insert a Range ......Page 205
Set the Width of Columns in a Range ......Page 207
Set the Height of Rows in a Range ......Page 209
Convert a Column of Text into Multiple Columns ......Page 211
Find the Intersection of Two Ranges ......Page 213
Cut and Paste Ranges of Cells ......Page 215
Copy and Paste Ranges of Cells ......Page 217
Using Paste Special Options When Pasting ......Page 219
Add Comments to a Cell ......Page 221
Automatically Fill a Range of Cells ......Page 223
Copy a Range to Multiple Sheets ......Page 225
Place a Border Around a Range of Cells ......Page 227
Find Specific Cell Values ......Page 229
Find and Replace Values in Cells ......Page 231
UserForm Basics ......Page 233
Create a Custom Dialog Box ......Page 235
Call a Custom Dialog Box from a Procedure ......Page 237
Capture Input from a Custom Dialog Box ......Page 239
Validate Input from a Dialog Box ......Page 243
Create Custom UserForm Controls ......Page 245
Create a UserForm Template ......Page 247
Create a CustomUI.xml File ......Page 249
Customize the Ribbon ......Page 251
Add Additional Options to the Ribbon ......Page 253
Create a Chart Sheet ......Page 257
Embed a Chart in a Worksheet ......Page 259
Apply Chart Wizard Settings to a Chart ......Page 261
Add a New Data Series to a Chart ......Page 263
Format Chart Text ......Page 265
Create Charts with Multiple Chart Types ......Page 267
Add a Data Table to the Chart ......Page 269
Customize a Chart Axis ......Page 271
Understanding Excel Events ......Page 273
Run a Procedure as a Workbook Opens ......Page 277
Run a Procedure before Closing a Workbook ......Page 279
Run a Procedure before Saving a Workbook ......Page 281
Run a Procedure When Excel Creates a Workbook ......Page 283
Execute a Procedure at a Specific Time ......Page 287
Execute a Procedure When You Press Keys ......Page 289
Create an Add-In ......Page 291
Set Add-in Properties ......Page 293
Install Add-Ins ......Page 295
Using VBA to Load Add-Ins ......Page 297
Introducing XML ......Page 299
Understanding Excel XML Files ......Page 301
Open an XML File in Excel as a Table ......Page 305
Create an XML Map ......Page 307
Import and Export XML Files Using Excel ......Page 309
Load XML Files Using VBA ......Page 311
Import XML Files Using VBA ......Page 313
APPENDIX A: VBA QUICK REFERENCE ......Page 315
APPENDIX B: RIBBON CONTROLS QUICK REFERENCE ......Page 335
INDEX ......Page 345