Excel programming: your visual blueprint for creating interactive spreadsheets

Excel Programming Your visual blueprint for creating interactive spreadsheets by Jinjer Simon......Page 1
Front of Book Information......Page 3
maranGraphics......Page 4
GENERAL AND ADMINISTRATIVE......Page 5
ABOUT THE AUTHOR......Page 6
AUTHOR’S ACKNOWLEDGMENTS......Page 7
TABLE OF CONTENTS......Page 8
HOW TO USE THIS BOOK......Page 14
AN INTRODUCTION TO MACROS......Page 17
RECORD A MACRO......Page 19
RUN A MACRO......Page 21
CREATE AND LAUNCH A KEYBOARD SHORTCUT......Page 23
DELETE A MACRO FROM A WORKBOOK......Page 25
DELETE FROM THE PERSONAL MACRO WORKBOOK......Page 27
ASSIGN A MACRO TO A TOOLBAR BUTTON......Page 29
ASSIGN A MACRO TO A MENU......Page 31
SET MACRO SECURITY......Page 33
AN INTRODUCTION TO THE VISUAL BASIC EDITOR......Page 35
ACTIVATE THE VISUAL BASIC EDITOR......Page 37
ARRANGE THE VISUAL BASIC EDITOR WINDOWS......Page 39
SET PROPERTIES FOR A PROJECT......Page 41
SET DISPLAY OPTIONS FOR THE CODE WINDOW......Page 43
ADD A NEW MODULE......Page 45
REMOVE A MODULE......Page 47
RENAME A MACRO......Page 49
CREATE A STARTUP MACRO......Page 51
HIDE A MACRO......Page 53
ASSIGN A DIGITAL SIGNATURE TO A MACRO......Page 55
UPDATE A RECORDED MACRO......Page 57
AN INTRODUCTION TO VBA......Page 59
CREATE A SUBROUTINE......Page 65
CREATE A FUNCTION......Page 67
DECLARE A VARIABLE......Page 69
PERFORM MATHEMATICAL CALCULATIONS......Page 71
CREATE A CONSTANT......Page 73
COMMENT YOUR CODE......Page 75
JOIN TWO STRINGS......Page 77
AN INTRODUCTION TO THE EXCEL OBJECT MODEL......Page 79
USING THE OBJECT BROWSER......Page 81
CREATE AN OBJECT VARIABLE......Page 83
CHANGE THE PROPERTIES OF AN OBJECT......Page 85
COMPARE OBJECT VARIABLES......Page 87
USING AN OBJECT METHOD......Page 89
DISPLAY A BUILT-IN DIALOG BOX......Page 91
ASSIGN VALUES TO VARIABLES......Page 93
USING GLOBAL VARIABLES......Page 95
DECLARE AN ARRAY......Page 97
DECLARE A MULTIDIMENSIONAL ARRAY......Page 99
CONVERT A LIST INTO AN ARRAY......Page 101
REDIMENSION AN ARRAY......Page 103
CREATE A USER-DEFINED DATA TYPE......Page 105
EXECUTE A TASK WHILE A CONDITION IS TRUE......Page 107
PERFORM MULTIPLE TASKS UNTIL A CONDITION IS MET......Page 109
EXECUTE TASKS A SPECIFIC NUMBER OF TIMES......Page 111
USING THE FOR EACH NEXT LOOP......Page 113
CONDITIONALLY EXECUTE A GROUP OF STATEMENTS......Page 115
EXECUTE A STATEMENT BASED UPON THE VALUE......Page 117
JUMP TO A SPECIFIC LOCATION IN A MACRO......Page 119
CONDITIONALLY CALL A SUBROUTINE......Page 121
USING EXCEL WORKSHEET FUNCTIONS......Page 123
USING THE MSGBOX FUNCTION......Page 125
USING THE INPUTBOX FUNCTION......Page 127
RETRIEVE CURRENT DATE AND TIME......Page 129
DETERMINE THE AMOUNT OF TIME BETWEEN DATES......Page 131
FORMAT A DATE EXPRESSION......Page 133
FORMAT A NUMERIC EXPRESSION......Page 135
REMOVE EXTRA SPACING FROM A STRING......Page 137
RETURN A PORTION OF A STRING......Page 139
COMPARE TWO STRINGS......Page 141
DEBUGGING BASICS......Page 143
DEBUG A PROCEDURE WITH INSERTED BREAK POINTS......Page 145
USING WATCH EXPRESSIONS TO DEBUG A PROCEDURE......Page 147
STEP THROUGH A PROCEDURE......Page 149
RESUME EXECUTION IF AN ERROR IS ENCOUNTERED......Page 151
PROCESS A RUNTIME ERROR......Page 153
OPEN A WORKBOOK......Page 155
OPEN A TEXT FILE AS A WORKBOOK......Page 157
OPEN A FILE REQUESTED BY THE USER......Page 159
SAVE A WORKBOOK......Page 161
SAVE WORKBOOK IN FORMAT SPECIFIED BY USER......Page 163
DETERMINE IF A WORKBOOK IS OPEN......Page 165
CLOSE A WORKBOOK......Page 167
CREATE A NEW WORKBOOK......Page 169
DELETE A FILE......Page 171
FIND A FILE......Page 173
ADD A SHEET......Page 175
DELETE A SHEET......Page 177
MOVE A SHEET......Page 179
COPY AND PASTE A SHEET......Page 181
HIDE A SHEET......Page 183
CHANGE THE NAME OF A SHEET......Page 185
SAVE A SHEET TO ANOTHER FILE......Page 187
PROTECT A WORKSHEET......Page 189
PROTECT A CHART......Page 191
PRINT A SHEET......Page 193
SORT WORKSHEETS BY NAME......Page 195
USING THE RANGE PROPERTY......Page 197
USING THE CELLS PROPERTY......Page 199
COMBINE MULTIPLE RANGES......Page 201
USING THE OFFSET PROPERTY......Page 203
DELETE A RANGE OF CELLS......Page 205
HIDE A RANGE OF CELLS......Page 207
SPECIFY THE NAME OF A RANGE......Page 209
RESIZE A RANGE......Page 211
INSERT A RANGE......Page 213
SET THE WIDTH OF COLUMNS IN A RANGE......Page 215
SET THE HEIGHT OF ROWS IN A RANGE......Page 217
CONVERT A COLUMN OF TEXT INTO MULTIPLE COLUMNS......Page 219
FIND THE INTERSECTION OF TWO RANGES......Page 221
CUT AND PASTE RANGES OF CELLS......Page 223
COPY AND PASTE RANGES OF CELLS......Page 225
CUSTOM PASTE VALUES IN CELLS......Page 227
ADD COMMENTS TO A CELL......Page 229
AUTOMATICALLY FILL A RANGE OF CELLS......Page 231
COPY A RANGE TO MULTIPLE SHEETS......Page 233
PLACE BORDERS AROUND A RANGE OF CELLS......Page 235
FIND SPECIFIC CELL VALUES......Page 237
FIND AND REPLACE VALUES IN CELLS......Page 239
USERFORM BASICS......Page 241
WORKING WITH COMMANDBARS......Page 243
CREATE A CUSTOM DIALOG BOX......Page 245
CALL A CUSTOM DIALOG BOX FROM A PROCEDURE......Page 247
CAPTURE INPUT FROM A CUSTOM DIALOG BOX......Page 249
VALIDATE INPUT FROM A DIALOG BOX......Page 253
CREATE CUSTOM USERFORM CONTROLS......Page 255
CREATE A USERFORM TEMPLATE......Page 257
CREATE A CUSTOM TOOLBAR......Page 259
ADD CONTROLS TO A TOOLBAR......Page 261
CREATE A CUSTOM MENU......Page 263
ADD ITEMS TO A MENU......Page 265
CREATE A SHORTCUT MENU......Page 267
DELETE CUSTOM TOOLBARS AND SHORTCUT MENUS......Page 269
CHART BASICS......Page 271
CREATE A CHART SHEET......Page 273
EMBED A CHART WITHIN A WORKSHEET......Page 275
APPLY CHART WIZARD SETTINGS TO A CHART......Page 277
ADD A NEW DATA SERIES TO A CHART......Page 279
FORMAT CHART TEXT......Page 281
CREATE CHARTS WITH MULTIPLE CHART TYPES......Page 283
DETERMINE VARIATIONS IN A SERIES OF DATA......Page 285
ADD A DATA TABLE TO THE CHART......Page 287
CUSTOMIZE THE CHART AXIS......Page 289
UNDERSTANDING EXCEL EVENTS......Page 291
RUN A PROCEDURE AS A WORKBOOK OPENS......Page 295
RUN A PROCEDURE BEFORE CLOSING A WORKBOOK......Page 297
RUN A PROCEDURE BEFORE SAVING A WORKBOOK......Page 299
RUN A PROCEDURE WHEN EXCEL CREATES A WORKBOOK......Page 301
MONITOR A RANGE OF CELLS FOR CHANGES......Page 305
EXECUTE A PROCEDURE AT A SPECIFIC TIME......Page 307
EXECUTE A PROCEDURE WHEN YOU PRESS KEYS......Page 309
RUN A PROCEDURE WHEN RIGHT- CLICKING A CHART......Page 311
VBA AND EXCEL OBJECT MODEL QUICK REFERENCE......Page 313
WHAT’S ON THE CD-ROM......Page 337
USING THE E-VERSION OF THE BOOK......Page 339
HUNGRY MINDS, INC. END-USER LICENSE AGREEMENT......Page 341
A - B......Page 343
C......Page 344
D - E......Page 345
F - H......Page 346
I - M......Page 347
N - O......Page 348
P - R......Page 349
S......Page 350
T - V......Page 351
W - Y......Page 352