C

C programming, 310 calculating dates, 206-207 formulas analysis tools, handling through, 34

array, 56-59

automatic subtotals, 35

calculating, 44-45

cell and range references, 45-48

code type returned by Excel InputBox method, 388 counting and summing techniques,

59-62 creating, 908-909 dates and times, 62-64 described, 25-27, 43-44 errors, 55-56

first character, acceptable, 24 HTML worksheet, activating, 81-82 linking, 48

megaformulas, 64-67 monitoring range to make bold, 623 names, 48-55

overwrite protection, 37-38 ranges and cells, 54 results, containing in cell, 17 self-expanding chart, 596, 599 worksheet, executing Function procedure, 284-285 writing, 892

calculating continued function add-in, spreadsheet types, 102 available arguments, displaying, 151 cell formatting, returning, 356-357 cells between two values, counting,

359-360

custom, ease of adding, 12 described, 25-27, 896-899 early spreadsheets, 5 expressions invoking, 208 file save or print date, displaying, 357-358

last nonempty cell in a column or row, finding, 361-363 object parents, 358-359 path existence, determining, 817 procedures, similarity to arguments,

254-255 simplifying access, 669 summary, 32

troubleshooting, 896-899 VBA, 213-218, 816-817, 935-941 visible cells in a range, counting,

360-361

Windows API, learning about, 313 rows or columns in range, 326 sum of first 100 integers, 189-190 calendar array formulas, 58

data, displaying for 365 days, 592-593 international formatting, 808 Call keyword, 246 calling function from another procedure, 393 VBA procedure described, 237

from different workbook, 248-250 from old macro, 883 Cancel button adding automatically, 914 wizard, 482, 483 caption, command bar, 726-728 cascading menus, 19

case-sensitivity changing with utility, 515 VBA, 194

worksheet names, converting to same, 273 category, Insert Function dialog box, 306-307 CD, back-of-the-book applications, 961-962 author-created materials, listed by chapter,

948-961 eBook version of book, 962-963 system requirements, 947 troubleshooting, 963 Windows, using with, 947-948

cell chart data, changing, 563-564 comments application Help, 772 cell comments, 885 determining whether contain, 175 counting selected, 326

between two values, 359-360 criteria, selecting those that meet (SpecialCells method), 260 data entry, 24-25 data type, determining, 332 drag-and-drop actions, 22 drop-down list, adding, 883-884 formatting returning, 356-357 selecting with certain, 338-340 formula names, setting, 48-49 formula references links to recover data in corrupt files, 48 non-relative, benefits of using, 45-46 R1C1 notation, 46-47 referencing other sheets or workbooks,

47-48 styles, 45 last nonempty, finding, 906 locking specific, 119 name, 902

overwriting, protecting from, 37-38

references absolute versus relative, 156-159 code type returned by Excel InputBox method, 388 to other workbooks or worksheets, 47, 48

value, prompting for, 321-323 Cells range object property, 180-182 change, planning for, 109 Change worksheet event, 621-622 characters altering within text (Text Tools utility) described, 515-516 efficiency, improving, 523-525 mechanics, 517

Modulel VBA module, 520-522 project goals, 516-517 reviewing project, 528-529 saving settings, 525-526 ThisWorkbook code module, 520 understanding, 529 Undo, implementing, 526-528 UserForm, 518-519 UserForm1 code module, 522-523 workbook, 517-518 argument, reversing (Reverse function),

279-282 font, 152

left side of string, returning, 293-295 special, inserting (Windows Character Map application), 642-644

chart animated, 587-588

arbitrary data labels, displaying, 570-571

ChartObjects, sizing and aligning, 559

"clock," 589-591

creating "dead," 583-585

data series control with Autofiltering,

592-593 data, specifying, 563-566 deleting from ChartObjects or Charts collection, 556-557 determining whether activated, 555-556, 904

displaying in UserForm creating on-the-fly, 572-574 described, 498-499 OWC ChartSpace control, 500-503 saving as a file, 498-500, 910 embedded printing on full page, 583 selecting, 28 events described, 574-582, 606 finding with Object Browser, 628-629 listed, 627-628 Excel, 30-31 formatting, 557-558 GIF, saving as, 500, 895 hypocycloid, 588-589 interactive, 601-604 locations, 547-548 looping through all, 558-559 macros, recording, 550-553 names, using in a SERIES formula, 561-563 object model, 548-550 sizing and aligning, 559 spot in hierarchy, 163 OWC ChartSpace control, 500-502 pivot, 30, 531, 560 properties, 177

ranges, determining, 566-569 saving as a file, 499-500 self-expanding, creating, 594-600 storing multiple on sheet, 593-594 text, displaying with "MouseOver"

event, 585-587 VBA, activating with, 553-555 Chart menu property ID, 743 chart sheet described, 547 workbooks, 17-18 XLS and XLA, 679-680 chart tips, 585-587 check boxes

ListBox display, 455 Update Button, 493

checked menu items creating, 919-920 described, 735 working with, 753-756 city climate data, comparing, 601-604 city lists, creating, 602-603 class, group of objects in same add-in adding, 683-684 removing, 684-685 comment object case study, 171-172 defined, 132, 900 ListBox items, adding, 458 manipulating, 216-220 membership, testing for, 354 operation, performing on all objects in (For

Each-Next construct), 218-220 VBA programming, 164 class module, VBA check boxes, 493 code, adding, 867-870 CSVFileClass, 875 described, 865-866 embedded chart, 579 events, 874-875 inserting, 867

method procedures, 876-878 naming, 872

NumLock class, creating, 866-871 object class, defined, 871 programming properties, 872-874 property procedures, 876 class, object, 132 cleaning up

Pivot Table recorded code, 534-535 recorded macros, 161-163, 552 client application, 648 clock chart, 589-591 clones, spreadsheet, 5

Close button, disabling UserForm, 449-450 closed workbook, retrieving value from, 353-356, 894

closing all workbooks, 341 individual workbooks, 141, 894

installed add-ins, 688 UserForms, 420-422 code, VBA (Visual Basic for Applications) class modules, 867-870 colors, 152 defined, 131

examples, Macro Recorder, 162 procedures, writing, 267-268 programming, entering, 190-191 speed, maximizing, 689-690 writing to display dialog box, 426 Code window described, 137-138 entering code, 142-149 minimizing and maximizing, 140-141 storing code, 141-142 collection add-in adding, 683-684 removing, 684-685 comment object case study, 171-172 defined, 132, 900 ListBox items, adding, 458 manipulating, 216-220 membership, testing for, 354 operation, performing on all objects in (For

Each-Next construct), 218-220 VBA programming, 164 color cell formatting based on data type, 892

comments, 885

improper, 908

objects, setting, 174

shading rows or columns, 626

sheet tabs, 16, 884

use of, 121

UserForm, selecting, 497-498 VBA code, 888 column absolute reference, 45 counting number in range, 326 file types, 827 formula names, 51 hiding, 120

last nonempty cell, determining, 362-363

maximum value, calculating, 231 number in worksheet, 17, 884 range, selecting, 901 shading, 626 COM (Component Object Model), 669 ComboBox chart data, changing via, 565-566 items, adding, 913 operation, changing, 523 comma (,)

comments, 168

multiple values for single case, 226 procedures, executing, 245-246 command

Registry information, 89, 90 VBA files, manipulating, 816 command bar caption, changing dynamically, 726-728 collection creating, 708-709 deleting using VBA, 710 menus, 739

objects, listing, 707-708 properties, 710-716 referring to in VBA, 709-710 types, 706-707 controls adding, 719-720 deleting, 720 listing, 717-718 referring to, 716-717 described, 735, 918-920 images, 725 properties button, executing, 728 button image, adjusting, 722-724 caption, changing dynamically, 726-728 custom macro, assigning to built-in button, 728 custom toolbar images, 725 listed, 721, 728-731 setting, 721-722 toolbars, 697 troubleshooting, 918-920 command line switches, 70-71

CommandButton adding to UserForm at design time, 854-856 macro, assigning, 910 Toolbox controls, 410 using in UserForm, 444 Comma-Separated Value files. See CSV files comment cell application Help, 772 default font and color, 885 determining whether contains, 175 Macro Recorder, 160-161 property of add-in object, 686 VBA programming, 192-193 comment object case study adding new object, 175-176 cell, determining whether contain comments, 175 collection, 171-172 colors, setting, 174 Comment property, 172 creating, 168 Help, 169-170 methods, 170-171 objects within, 173-175 properties, 170, 176-178 compatibility bit formats, 313, 372-373, 802 described, 37, 801-802 error-handling, 890 Excel 2003, 11, 888

Excel version number, determining, 805 features, avoiding new, 805 file formats supported, 803-804 international issues country code, displaying, 806-808 date and time settings, 813-814 languages, 808-809 local properties, 810 system settings, 810-813 Macintosh, 805-806 macros, 882

problems, listed, 802-803 complex comparisons (DCOUNT), 62 Component Object Model. See COM

concatenation operator. See ampersand (&) conditional formatting, 29 configuration information, Excel, 89-90 connecting declared object with chart, 579 connection string, pivot table, 541 constant arguments, passing to procedures, 254-258 declaring, 204-205 Excel file formats, 804 formula names, 51-53 predefined, 205 VBA programming, 204-205 constructs, standard programming, 134 container object classes, 132 Toolbox entries, 439 context ID, help topic, 216 contracting projects, 138-139 control properties, command bar caption, changing dynamically, 726-728 custom macro, assigning to built-in button, 728 executing button, 728 images, 722-725 listed, 721, 728-731 setting, 721-722 controls custom dialog boxes, 413-414 ease of adding, 12 placing, 30 converters, file, 89 copy protection, 6 copying add-in to hard drive, 684 cell data described, 24 without incrementing, 25 Drag-and-Drop Text Editing, 151-152 ranges described, 316-317 with macro, 903-904 static chart, 583 toolbar controls, 703 variably sized ranges, 318 VBA code, 142, 148, 887 Windows API functions, 313

corrupt files, 48 counting with array formulas, 60-62 cells between two values in range, 359-360 complex comparisons (DCOUNT), 62 COUNTIF or SUMIF function, 59-60, 62 custom toolbars, 712 filtered rows (SUBTOTAL), 62 pivot table, using, 62 printed pages, 344 selected cells, 326 with single criterion (COUNTIF), 62 counting sort, 348, 349 country code, 806-808 crashing Excel, 885-886 CreateObject,651-652 Creator property, 170 CSV (Comma-Separated Value) files,

73, 827, 875 Ctrl+shortcut key combination, 242-244 curly brackets ({}), 56 Currency data type described, 195 prefix (c), 203 typing, 201 currency, formatting, 25, 28 cursor question mark (?), dialog box title bar,

780-781 variable, displaying under, 151 custom data type described, 196 prefix (u), 203 custom dialog box application Help display, 774-777 buttons

Close, disabling, 449-450 multiple, handling with one event handler, 494-496 100, adding at design time, 854-856 chart, displaying, 498-503 ChartSpace control, adding, 501 checklist, 441 closing, 420-422 code, writing to display, 426 color, selecting, 497-498

controls managing, 413-414 referencing, 437-438 creating interface, choosing, 110-111 sample, 422-426 steps, 407-408 data, validating, 429-430 described, 18, 910-915 displaying, 419-420 dynamic example, 859-864 Enhanced Data Form, 507-509 event handler procedures, 422, 428-429 events, 430-436, 606, 634-636, 792 described, 430-431, 606 loading and unloading, 431-432 processed while displayed, 792 recognizable, listed, 634-636 SpinButton, 432-436 finished dialog box, 430 generating, 98 Help display label controls, 774-775 scrolling label, 775-776 topics, dropdown control, 776-777 What's This?, 780-781 inserting new, 408 ListBox activate to a sheet, 468-470 control, described, 454-455 item transfer, 462-463 items, adding, 455-459 moving items, 463-464 multicolumn controls, 464-466 multiple selections, determining,

460-461 user-selected items, determining, 459-460

worksheet rows, selecting, 466-468 Loan Amortization Wizard (FormMain), 791-792

manipulating at design time versus runtime,

853-854 menu, creating, 443-445 modeless dialog box, 490-494 MsgBox function, emulating, 487-490

MultiPage, 471-472 progress indicator, 473-480 properties, adjusting, 414-419 ranges, selecting, 445-447 runtime example, 856-858 size, changing, 450-451 spreadsheet, displaying, 503-507 templates, creating, 440-441 testing, 426-428 Text Tools, 518-519 Toolbox controls, 409-413 troubleshooting, 910-915 wizards, creating, 480-487 zooming and scrolling sheet from, 451-454 custom macro, assigning to built-in button, 728

0 0

Post a comment