S

Sachs, Jonathan (Lotus 1-2-3 inventor), 4 sales commission rate calculator based on years, 292 one-argument, 289-291

saving all workbooks, 340-341 chart as a file, 499-500 and closing all workbooks, 341 Loan Amortization Wizard default settings, 794-796 schedule, macro running, 895 scoping formula names, 51 Function procedure, 283-284 variables, 199-204, 895-896 VBA procedures, 239-240 screen. See display Script Editor, 82, 136 scrolling controls, 411

label, Help display, 775-776 UserForm sheet, 451-454 seamless integration, 667 searching newsgroups, 926-928 security add-ins, 672

applications, designing, 108 Trusted Sources, 888 selecting embedded chart, 28 multiple ranges, 319 objects, 27-28 ranges activating versus, 900 procedure, 320-321 by row and column, 900-901 user, allowing, 915 rows by user, counting, 905 SelectionChange event, 626 self-expanding chart creating, 595-596 described, 594-595 mechanics, 599 named formulas, 596, 599 offset function, 599-601 series, modifying, 596-598 testing, 598 separator bar, 735

sequence access to text files, 824 event, 607 serial number system date and time, displaying, 345-346 manipulating, 63 storing, 62 SERIES formula names, 561-563 series, modifying self-expanding chart,

596-598 server application, 648 setup button, print preview, 903 shading disabled menu items, 752, 764 row or column, 626 Shape property, 170 shapes color, setting, 174 Excel, 29-30 sheet active, calculating formula in all open

(Shift+F9), 44 ActiveX controls, 111-112 application Help text display, 773-774 database access, 31, 32 drop-down list, storing on different, 884 events

BeforeRightClick, 627 Change, 621-622 described, 606 listed, 620-621 SelectionChange, 626 specific range, monitoring, 622-625 existence, determining, 353 formula, executing Function procedure,

284-285 function array of nonduplicated random integers, returning, 369-370 cell formatting, returning, 356-357 cells between two values, counting,

359-360 custom, ease of adding, 12

file save or print date, displaying, 357-358

last nonempty cell in a column or row, determining, 361-363 maximum value across all, returning,

368-369 multifunctional, 366-367 nth element, extracting from string,

364-366 object parents, 358-359 range, randomizing, 371-372 relative manner, addressing

(SHEETOFFSET), 367 simplifying access, 669 sound, playing from, 377-378 strings matching patterns, 363-364 VBA statement, using, 214-215 visible cells in a range, counting, 360-361

Function procedure, using, 279-280 hiding, 120

ListBox, activating to, 468-470 Loan Amortization Wizard, 793-794 names, putting into array of strings,

267-272 in object hierarchy, 163 outline analysis tool, 35 range cell data type, determining, 332 cell value, prompting for, 321-323 cells with certain format, selecting,

338-340 copying, 316-317 copying to different, 317 copying variably sized, 318 counting selected cells, 326 determining whether contained within another, 331 empty rows, deleting, 330-331 looping through, 328-330 macro, pausing for user-selected range, 324-325

maximum value, selecting, 337-338 moving, 318

offering (RefEdit), Toolbox controls, 411

one-dimensional arrays, transferring, 336

reading and writing, 332-333 selecting or identifying, 320-321,

900-901 tips for handling, 319 type, determining, 326-328 value, entering in next empty cell, 323-324

variant array, transferring to, 336-337 writing to, better way, 333-335 referencing, 47-48 rows, selecting ListBox, 466-468 scrolling, avoiding, 900

size, 18

sort, 348, 349

storing multiple charts, 593-594 substitute, creating, 758-760 synchronizing menu with, 756 Toolbox controls, 412-413 workbooks, 17 XLS and XLA, 679-680 SheetActivate event, 616 SheetExists function, 353 Shift key, detecting, 312-313 shortcut keys assigning, 243 combination, 736 creating, 116

displaying on menus, 748-750 executing with Macro Recorder, 160 Manual Calculation, 44-45 shortcut menu adding items, 762-763 chart, displaying, 30 creating new, 765-767 customizing, ease of, 12 deleting items, 763 described, 20, 761-762 disabling, 764 resetting, 765 working with, 761-767

single counting criterion (COUNTIF), 62 Single data type described, 195 prefix (s), 203, 891 typing, 201 single-block budgets spreadsheets, 102 single-entry input box

Excel InputBox method, 387-389 VBA, 386-387 single-user spreadsheet applications, 100-101 16-bit format, 313, 372-373, 802 sizing

ChartObjects, 559 fonts, 153, 618 named range in charts, 561 toolbar button, 21 UserForm, 450-451, 915 Smart Tags, 23 software activating with Excel, 645-646

already running (AppActivate statement),

645-646 CD, back-of-the-book, 961-962 copy protection, 6 events described, 606, 629-631 determining when workbook opened,

632-633 enabling, 631-632 monitoring, 633-634 sequences, 607 Help cell comments, 772 Excel, 769-771 file, associating, 783 HTML, 781-782, 785-786 Office Assistant, 777-780 text box, 772-773 UserForm display, 774-777 VBA function, associating, 783-784 What's This? UserForm, 780-781 worksheet text display, 773-774 keystrokes, sending to (SendKeys), 664-666

Microsoft Office, activating from Excel, 646 opening information, 447-449

sharing data among (XML) compatibility, 802 data, importing to list, 85-87 described, 82-84

Excel 2003 version, supported, 11 Excel features, 34 exporting data, 87 importing through map, 84-85 range, exporting to file, 838-840 sound, adding to, 376-378 starting from Excel, 641-645 user-oriented described, 787

development checklist, 797-798 Loan Amortization Wizard, 788-796 VBA Shell, starting from Excel, 642-644 Solver add-in, 36 sort array, 347-349, 904

procedure writing VBA procedures, 268-272 sound applications, adding to, 376-378 MIDI files, 377

playing from worksheet function, 377-378, 885

WAV files, 376-377 space character around equal sign, 144 intersecting ranges, 50-51 line continuation sequence, 889 space-delimited files, 73 spaghetti code or application, 101, 229 special-purpose tools (Analysis ToolPak) analysis tools, 35 described, 667 VBA code, 898 SpinButton events, 430, 432-436, 775 Spirograph pattern. See hypocycloid chart splash screen, 447-449 spreadsheet

FAQs newsgroup, 929 history of, 3-11 newsgroups, 925 types data storage and access, 103 database front ends, 103

for-your-eyes-only, 100 functions, add-in, 102 quick-and-dirty, 99 single-block budgets, 102 single-user applications, 100-101 spaghetti applications, 101 turnkey applications, 104 utility applications, 101-102 what-if models, 103 UserForm, displaying, 503-507 spreadsheet application appeal and intuitiveness, 121 bugs, 118 described, 93-94 developers and end users, 95-97 development steps, summarized, 105-106 distributing, 123 documenting development, 122 Excel version, target user's, 124 language issues, 125 needs, determining, 106-107 planning to meet user needs, 107-109 problems, solving, 98 protection, adding, 119-121 spreadsheet types, 99-104 system speed, 125 testing, 117-118 updating, 124 user Help system, 122 user interface, choosing, 110-117 video modes, 125-126 SQL (Structured Query Language), 541 square root, calculating, 214 standalone progress indicator, 475-477 starting applications from Excel, 641-645 Excel, 69-71

procedure, progress indicator, 477 UserForm position, 915 statement, summarized VBA, 931-935 static chart, creating, 583-585 function, declaring, 282 variables, 204 VBA procedure, 238 statistical functions, accuracy of, 35

Status Bar, displaying progress in, 474, 903 storing event handler procedures, 142, 607-608 Function procedure, 310 macro, 160

multiple charts on sheet, 593-594 serial number system, 62 toolbars, 698-699 VBA code, 141-142 string area, describing, 327

code type returned by Excel InputBox method, 388 concatenation operator, 201, 717, 889, 913 converting to uppercase, 286 left side characters, returning, 293-295 matching patterns, worksheet functions,

363-364

nth element, extracting from worksheet,

364-366

pivot tables, creating, 535, 541 VBA programming, 205-206 String data type described, 196 prefix (str), 203 typing, 201 strokes, key detecting (GetKeyState function), 312-313

sending to an application (SendKeys),

664-666 shortcut assigning, 243 combination, 736 creating, 116

displaying on menus, 748-750 executing with Macro Recorder, 160 Manual Calculation, 44-45 structured programming, 229 Structured Query Language. See SQL style, formatting cell and range references, formulas, 45 cell based on data entry, 892 column width and row height, 17 data entry, 25

style, formatting continued date and time, 62-64 progress indicator, 475 saving and retrieving in other formats, 71 selecting cells with certain, 338-340 UserForm controls, 852-856 using, 121

worksheets, 884-885 Sub procedure from another procedure, 245-250 arguments, declaring, 257, 258 calculating sum of first 100 integers, 189-190

with Ctrl+shortcut key combination,

242-244 from custom menu, 244-245 defined, 132, 141, 238 Function procedure versus, 277-278 from Macro dialog box, 242 pausing macro to allow user to select cell,

324-325 reasons to, 250

RunSub/UserForm command, 240, 241 sample, 145 testing procedures, 304 from toolbar button, 250-251 VBA procedure declaring, 238-239

RunSub/UserForm command, 240, 241 when event occurs, 253 submenu, 735 subscript, 900

substitute worksheet, creating, 758-760 subtotal automatic, 35 filtered rows, counting, 62 subtracting dates and times, 63 SUM function complex array formulas, 61-62 Function procedure, emulating (MySum), 301-304 SUMIF function, 59

summarizing data in tables (pivot tables), 36

summary function, 32 summing techniques with array formulas, 60-62 first 100 integers, 189-190 SuperCalc spreadsheet software, 4 switches, command line, 70-71 SYLK (SYmbolic LinK) file, 74 synchronizing menu with worksheet, 756 workbooks, 342-343 syntax errors, 258 system international settings, 810-813 requirements for back-of-the-book CD, 947 spreadsheet application speed, 125

tab color-coding, 16, 884 formatting, 884-885 ListBox, 463-464 message box, inserting, 393, 394 order, changing, 417-418 tabbed dialog boxes described, 21-22 Toolbox controls (MultiPage), 411, 471-472 tab-delimited files, 73, 827 table, pivot cleaning up recorded code, 534-535 code that created, 536-538 complex, how it works, 538-539 counting and summing techniques, 62 creating, 532-533 data for more complex, 535-536 database, creating from external, 539-541 described, 36, 531-532 modifying, 544-546 multiple, creating, 541-544 in object hierarchy, 164 recorded code, examining, 534 TabStrip controls, 412 Tag property, 436

tags HTML

applications Help, 781-782, 785-786 complexity, adding, 79-80 constants and values, 804 as Excel native file format, 78-79 file bloat, resolving, 690-691, 889 files, creating, 34 Help method, 785 input box, displaying from, 786 interactive files, creating, 80-82 message box, displaying from, 785-786 range, exporting to, 835-838 Script Editor, 82, 136 XML

compatibility, 802 data, importing to list, 85-87 described, 82-84 Excel 2003 version, supported, 11 Excel features, 34 exporting data, 87 importing through map, 84-85 range, exporting to file, 838-840 task pane, 23-24

task, wizards performing, 485-487 template files (.XLT), 75-76 last used, listing, 90 UserForms, creating, 440-441 testing for collection membership, 354 compatibility issues, 803 dialog box, 426-428 macros, 145 procedure, 193, 304 self-expanding chart, 598 spreadsheet applications, 117-118 UserForm, 419 VBA procedures, 272-273 workbook for example add-in, 673 text cell, displaying, 17

charts, displaying with "MouseOver" event, 585-587

code color options, 152

line break, forcing, 393, 394, 899

listing VBA files containing specific,

823-824 number of cells, counting, 60 processing, 98 returning or setting, 171 Toolbox controls, displaying (Label), 410 user input (TextBox), Toolbox controls, 412 VBA color-coding, 888 worksheet display, 773-774 text box, 29, 772-773 text files formats, supported, 73-74 VBA

accessing, 824

data, importing, 828

Excel usage, logging, 831-832

exporting range to, 828-829

exporting range to XML file, 838-840

file number, getting, 826

file position, determining or setting, 826

filtering, 832

HTML format, exporting range to,

835-838 import and export features, 827,

832-835 importing to range, 830-831 opening, 825 reading, 826 writing, 826, 827 text string, 388 Text Tools described, 515-516 efficiency, improving, 523-525 mechanics, 517

Module1 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

Text Tools continued

UserForm, 518-519, 522-523 workbook, 517-518 TextBox, SpinButton with, 434-436 thermometer-type display. See progress indicator, UserForm thermometer-type progress display described, 473-474, 912 showing with MultiPage control, 477-479 standalone, creating, 475-477 Status Bar, displaying progress in, 474 troubleshooting, 912 without MultiPage control, 479-480 32-bit format, 372-373, 802 ThisWorkbook code module, 520 thousands separator, international issue, 811 3-D workbooks, 367 time current, entering, 25 dialog box, displaying, 647 displaying, 345-346 entering, 63

file last printed or previewed, 358 international settings, 813-814 pre-1900, 64

serial number system, storing, 62 tip text, Toolbox, 438 title message box, 216 property of add-in object, 685-686 "x," removing, 910 Toggle Folder, 138 toggling

GridLines menu display, 755-756 Num Lock key, 867 on and off Toolbox switch, 412 toolbar command bar, 697, 700-704 controls, listing all, 718-719 crash-causing, 885-886 customizing, 12, 115-116 described, 20-21 displaying size, 21 distributing, 704-706

files (.XLB), 76-77 list, disabling, 920 manipulations, 698 restoring to original state, 919 storing, 698-699 Sub procedures, 250-251 troubleshooting, 699-700, 918-919 VBE, 137 Toolbox controls

ActiveX, adding, 439-440 binary choice (CheckBox), 410 to choose one item from list

(OptionButton), 411 CommandButton, 410 customizing or combining, 438-439 drop-down box (ComboBox), 410 grouping (Frame), 410 Image, 410

list of items (ListBox), 411 on and off switch (ToggleButton), 412 ScrollBar controls, 411 tabbed dialog boxes (MultiPage), 411 TabStrip, 412 text, displaying (Label), 410 UserForm, adding, 409 users to input text (TextBox), 412 value, increasing or decreasing (SpinButton control), 411 worksheet range, offering (RefEdit), 411 worksheet, using controls on, 412-413 Tools menu

Loan Amortization Wizard, 791 property ID, 743 transfers, ListBox items, 462-463 trapping errors, 258-260 UserForm events, 634-635 troubleshooting add-in, 915-917 CD, back-of-the-book, 963 command bar, 918-920 procedure, 890-896 progress indicator, UserForm, 912 toolbar, 699-700, 918-919

UserForm, 910-915 VBA class modules, 865-866 VBA procedure, 890-896 worksheet function, 896-899 True/False cell formatting, determining, 356-357

CheckBox control, 410

described, 195

path, finding, 817

returned by Excel InputBox method, 388 VBA, toggling, 343-344 Trusted Sources tab, 888 turnkey spreadsheet applications, 104 TXT (tab-delimited) files, 73, 827 type-declaration characters, 201, 891 typeface cell comments, 885 header or footer size, changing, 618 listing, 346-347 options, 152 VBA, setting, 153 worksheet tabs, 884-885 typing data types, 201

monitoring (OnKey), 638-639

0 0

Post a comment