D

DAO (Data Access Objects), 12, 33 data analysis options, 12 chart, changing based on active cell, 563-564 using ComboBox, 565-566 entry, spreadsheet, 24-25, 98 file formats, surviving round trip between, 71 filtering, 32

importing VBA text files, 828 interactive charts, getting, 602 Pivot Table, 535-536

range, creating interactive charts, 603-604 series control with Autofiltering, 592-593 storage and access spreadsheet, 103 structure, deciding on, 108 summarizing in table cleaning up recorded code, 534-535

code creating, 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

data continued multiple, creating, 541-544 in object hierarchy, 164 recorded code, examining, 534 type arguments, defining, 256-257 benchmarking, 196-197 built-in, 195-196 cell, determining, 332 function procedure, 283 returned by Excel InputBox method, 388 user-defined, 212-213 variables, determining, 198-199 variant, 196, 197-198, 890 VBA programming, 195-197 validating UserForm, 429-430 XML, importing to list, 85-87 Data Access Objects. See DAO Data Interchange Format file. See DIF file Data menu property ID, 743 database

ADO format, 661-663 external, 31, 32-33, 539-541 files formats, supported, 73 pivot table, 36, 539-541 Quattro Pro link, 7 queries, performing, 541 records, displaying total number, 770 worksheet, 31, 32 database front ends, 103 database, Microsoft Windows Registry browsing, 88 described, 87 editing, caution about, 88 Excel configuration information, 89-90 keys and values, 88-89 opening Excel, 69 date bug, 207

calculating, 206-207 current, entering, 25 data type, 196 dialog box, 647 displaying, 345-346 entering, 63

international settings, 813-814 ListBox, filling, 465-466 pre-1900, 64

serial number system, storing, 62 weekend, determining whether day is (Weekday function), 226 Date/Time data type (dt prefix), 203 dBASE files, 73 Deactivate event, 617 "dead" charts, creating, 583-585 debugging add-in, 674

Function procedure, 304-305 Decimal data type, 195 decimal number, converting Roman numeral to, 214-215 decimal separator, international issue, 810 decision-making applications, 221-224 declarations arrays, 210 constants, 204-205 defined, 141

Function procedure, 282-283 Module1 VBA module, 521 variables, 197-199 declared object, connecting with chart, 579 decreasing value (SpinButton control),

Toolbox controls, 411 default settings, saving and retrieving, 794-796 Default to Full Module View option, 152 Define Name dialog box, 53-54 deleting add-ins, 916

all values from worksheet, 901 charts from ChartObjects or Charts collection, 556-557 CommandBars collection via VBA, 710 comments, 171

control from command bar, 720 empty rows, 330-331 items from ListBox, 462-463 menus, 743, 751-752 project references, 845-846 restoring items, 920 sheets, preventing, 38

shortcut menu items, 763 toolbar, 703 dependencies, wizard programming, 484-485 description adding for example add-in, 673-674, 916 custom function, adding, 896 Insert Function dialog box, adding, 307-309 developers described, 95-96 Excel's benefits, 11-13 development spreadsheet applications, documenting, 122 steps, summarized for spreadsheet applications, 105-106 user interface, executing, 117 diagram object type, 30 dialog box. See also UserForm built-in controls, direct, 111-112 directory, prompting for, 398-401 displaying, 402-406, 913-914 Excel, emulating, 440-441 file, getting user to choose among

(GetSaveAsFilename), 398 filename, asking user for

(GetOpenFilename), 394-397 listed, 385

message, displaying to user or getting single response (MsgBox), 390-394 running with Windows Control Panel, 647-648

series soliciting user input (wizard) buttons, programming, 482-484 chart, 30, 31

dependencies, programming, 484-485 described, 480-481 MultiPage control, setting up, 481 task, performing, 485-487 single-entry input box, 386-389 tabbed described, 21-22

Toolbox controls (MultiPage), 411 testing, 426-428 dialog sheets, Excel 5/95, 18-19, 915

DIF (Data Interchange Format) file, 74 Dim statement array, declaring, 210-211 described, 200-202 variable, declaring, 213 directory files, displaying list (FileSearch object), 820 prompting for

FileDialog object, 401 Windows API function, 398-401 VBA files, listing, 817-818 Windows API, determining, 311-312 disabling events, 609-610 menus, 752-753 shortcuts, 764, 920 display customizing, 27

equal spacing between strings, 913 GridLines menu, toggling, 755-756 splash screen, 447-449 text in worksheet, 773-774 updating problems, 273, 902 UserForm application Help, 774-777 chart in, 572-574, 910 same despite video size, 915 video modes, 125-126 displaying arbitrary data labels, 570-571 built-in dialog boxes arguments, using with, 404-405 Dialogs collection, 402-404 learning more about, 404 menu item, executing directly, 405-406 chart text with "MouseOver" event, 585-587 in UserForm, 498-503, 572-574 date and time, 345-346 menu shortcut key, 748-750 toolbar, 702

UserForms, 419-420, 498-503 VBA components, 846-848

distributing add-in, 676

custom toolbars, 704-706 spreadsheet applications, 123 toolbar with add-in, 705-706 #DIV/0! error, 55

DLL (Dynamic Link Library) files, 310, 669.

See also Windows API Do Until loop, 234-235 Do While loop, 232-234 docking toolbars, 20 document, hiding, 120 documenting development benefits of, 122 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

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 dollar, formatting, 25, 28 dollar sign ($), 201 dot processing (.), 212 Double data type described, 195 prefix (d), 203 typing, 201 drag-and-drop feature, 22, 151 drawing

AutoShapes, 29

layer, ActiveX controls, 111-112 on UserForm, 912-913

drop-down box, Toolbox chart data, changing via, 565-566 items, adding, 913 drop-down list cell, adding to, 883-884 Help menu topics, 776-777 duplicates, finding, 458-459 duplicating 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 dynamic array, 211, 907 Dynamic Link Library files. See DLL files dynamic UserForm example (GetOption), 859-864

0 0

Post a comment