发帖
雷达卡
Excel 2003 VBA Programmer's Reference。pdf版，有目录，超清晰。
187155.pdf
(18.09 MB, 需要: 5 个论坛币)
Chapter 1: Primer in Excel VBA 1 Using the Macro Recorder 2 Recording Macros 2 Running Macros 5 The Visual Basic Editor 8 Other Ways to Run Macros 11 User Defined Functions 18 Creating a UDF 18 What UDFs Cannot Do 22 The Excel Object Model 22 Objects 23 Getting Help 29 Experimenting in the Immediate Window 30 The VBA Language 32 Basic Input and Output 32 Calling Functions and Subroutines 37 Variable Declaration 38 Scope and Lifetime of Variables 40 Variable Type 42 Object Variables 45 Making Decisions 47 Looping 50 Arrays 55 Runtime Error Handling 58 Summary 62 Chapter 2: Programming in the VBE 63 Writing Code 63 Programming for People 63 Writing Code 64 Where Does My Code Go? 65 Managing a Project 65 Adding Classes 67 Modifying Properties 68 Importing and Exporting Visual Basic Code 69 Editing 70 Managing Editor Options 70 Running and Debugging Code 71 Using Watches 71 Using the Object Browser 72 Summary 73 Chapter 3: The Application Object 75 Globals 75 The Active Properties 76 Display Alerts 77 Screen Updating 77 Evaluate 78 InputBox 80 StatusBar 81 SendKeys 82 OnTime 83 OnKey 84 Worksheet Functions 85 Caller 87 Summary 88 Chapter 4: Object-Oriented Theory and VBA 89 Comparing Classes and Interfaces 89 Defining an Interface 90 Implementing an Interface 91 Defining Methods 92 Parameters 93 Implementing Recursive Methods 94 Eliminating Recursion with Loops 94 Defining Fields 95 Defining Properties 95 Read-Only Properties 96 Write-Only Properties 97 Defining Events 97 Defining Events in Classes 97 Raising Events 98 Handling Events 99 Information Hiding and Access Modifiers 100 Encapsulation, Aggregation, and References 100 Summary 101 Chapter 5: Event Procedures 103 Worksheet Events 103 Enable Events 104 Worksheet Calculate 105 Chart Events 106 Before Double Click 106 Workbook Events 108 Save Changes 110 Headers and Footers 111 Summary 112 Chapter 6: Class Modules 113 Creating Your Own Objects 113 Using Collections 115 Class Module Collection 116 Trapping Application Events 118 Embedded Chart Events 120 A Collection of UserForm Controls 122 Referencing Classes Across Projects 124 Summary 125 Chapter 7: Writing Bulletproof Code 127 Using Debug.Print 127 Using Debug.Assert 128 A Brief Exemplar of PC Debugging 129 Creating Reusable Tools with the Debug Object 133 Tracing Code Execution 133 Trapping Code Execution Paths 135 Asserting Application Invariants 137 Raising Errors 140 Writing Error Handlers 142 On Error Goto Line Number 142 On Error Resume Next 143 xiii Contents On Error GoTo 0 145 Using the Err Object 145 Scaffolding 145 Writing to the EventLog 147 Summary 149 Chapter 8: Debugging and Testing 151 Stepping Through Code 151 Running Your Code 152 Stepping into Your Code 153 Step Over 153 Step Out 153 Run to Cursor 154 Set Next Statement 154 Show Next Statement 155 Using Breakpoints 155 Using Watches 155 Add Watch 156 Edit Watch 158 Quick Watch 158 Locals Windows 158 Testing an Expression in the Immediate Window 159 Resources for Finding Definitions 160 Edit ➪Quick Info 160 Edit ➪Parameter Info 161 Edit ➪Complete Word 161 Edit ➪List Properties/Methods 161 Edit ➪List Constants 162 Edit ➪Bookmarks 162 View ➪Definition 162 View ➪Object Browser 163 Viewing the Call Stack 163 Asserting Application Invariants 164 Summary 165 Chapter 9: UserForms 167 Displaying a UserForm 167 Creating a UserForm 169 Directly Accessing Controls in UserForms 171 Stopping the Close Button 174 xiv Contents Maintaining a Data List 175 Modeless UserForms 181 Summary 181 Chapter 10: Adding Controls 183 The Toolbars 183 ActiveX Controls 184 Scrollbar Control 185 Spin Button Control 186 CheckBox Control 186 Option Button Controls 187 Forms Toolbar Controls 188 Dynamic ActiveX Controls 191 Controls on Charts 194 Summary 195 Chapter 11: Data Access with ADO 197 An Introduction to Structured Query Language (SQL) 197 The SELECT Statement 198 The INSERT Statement 200 The UPDATE Statement 201 The CREATE TABLE Statement 202 The DROP TABLE Statement 203 An Overview of ADO 203 The Connection Object 204 The Recordset Object 212 The Command Class 218 Using ADO in Microsoft Excel Applications 222 Using ADO with Microsoft Access 223 Using ADO with Microsoft SQL Server 230 Using ADO with Non-Standard Data Sources 239 Summary 244 Chapter 12: Creating and Using Add-ins 245 Hiding the Code 245 Converting the Workbook to an Add-in 247 Closing Add-ins 247 Code Changes 248 Saving Changes 249 xv Contents Installing an Add-in 250 Add-in Install Event 251 Removing an Add-in from the Add-ins List 252 Summary 252 Chapter 13: Automation Addins and COM Addins 253 Automation Addins 253 Creating a Simple Addin 254 Registering Automation Addins with Excel 255 Using Automation Addins 257 An Introduction to the IDTExtensibility2 Interface 259 A Complex Addin—Generating a Unique Random Number 262 COM Addins 268 IDTExtensibility2 Interface Continued 268 Summary 279 Chapter 14: Customizing the VBE 281 Identifying VBE Objects in Code 281 The VBE Object 282 The VBProject Object 283 The VBComponent Object 283 The CodeModule Object 284 The CodePane Object 285 The Designer Object 285 Starting Up 285 Adding Menu Items to the VBE 286 Table-Driven Menu Creation 288 Displaying Built-In Dialogs, UserForms, and Messages 296 Working with Code 301 Working with UserForms 305 Working with References 310 Summary 311 Chapter 15: Interacting with Other Office Applications 313 Establishing the Connection 314 Late Binding 314 Early Binding 315 Opening a Document in Word 317 Accessing an Active Word Document 318 Creating a New Word Document 319 xvi Contents Access and DAO 320 Access, Excel and, Outlook 321 When Is a Virus not a Virus? 323 Summary 324 Chapter 16: Programming with the Windows API 327 Anatomy of an API Call 328 Interpreting C-Style Declarations 329 Constants, Structures, Handles, and Classes 332 What if Something Goes Wrong? 335 Wrapping API Calls in Class Modules 336 Some Example Classes 341 A High-Resolution Timer Class 341 Freeze a UserForm 342 A System Info Class 344 Modifying UserForm Styles 346 Resizable Userforms 350 Other Examples 356 Summary 358 Chapter 17: International Issues 359 Changing Windows Regional Settings and the Office XP UI Language 359 Responding to Regional Settings and the Windows Language 360 Identifying the User’s Regional Settings and Windows Language 360 VBA Conversion Functions from an International Perspective 361 Interacting with Excel 366 Sending Data to Excel 367 Reading Data from Excel 369 Rules for Working with Excel 370 Interacting with Users 370 The Rules for Working with Your Users 373 Excel 2003’s International Options 373 Features That Don’t Play by the Rules 375 Responding to Office XP Language Settings 382 Where Does the Text Come From? 382 Identifying the Office UI Language Settings 383 Creating a Multilingual Application 384 Working in a Multilingual Environment 386 The Rules for Developing a Multilingual Application 388 xvii Contents Some Helpful Functions 388 Implementing WinToNum Function 388 Implementing WinToDate Function 389 Implementing FormatDate Function 390 Implementing ReplaceHolders Function 390 Summary 391 Chapter 18: Workbooks and Worksheets 393 Using the Workbooks Collection 393 Creating a New Workbook 393 Saving the ActiveWorkbook 394 Activating a Workbook 394 Getting a FileName from a Path 395 Files in the Same Directory 397 Overwriting an Existing Workbook 398 Saving Changes 399 The Sheets Collection 400 Worksheets 400 Copy and Move 402 Grouping Worksheets 403 The Window Object 405 Synchronizing Worksheets 406 Summary 407 Chapter 19: Using Ranges 409 Activate and Select 409 Range Property 411 Shortcut Range References 412 Ranges on Inactive Worksheets 412 Range Property of a Range Object 413 Cells Property 413 Cells used in Range 414 Ranges of Inactive Worksheets 414 More on the Cells Property of the Range Object 415 Single-Parameter Range Reference 417 Offset Property 418 Resize Property 420 SpecialCells Method 420 CurrentRegion Property 424 xviii Contents End Property 426 Referring to Ranges with End 426 Summing a Range 427 Columns and Rows Properties 428 Areas 429 Union and Intersect Methods 431 Empty Cells 432 Transferring Values between Arrays and Ranges 434 Deleting Rows 436 Summary 438 Chapter 20: Using Names 441 Naming Ranges 442 Using the Name Property of the Range Object 443 Special Names 443 Storing Values in Names 444 Storing Arrays 445 Hiding Names 446 Working with Named Ranges 446 Searching for a Name 447 Searching for the Name of a Range 449 Determining which Names Overlap a Range 450 Summary 452 Chapter 21: Working with Lists 453 Creating a List 453 Shortcut Options for Lists 454 Sorting and Filtering a List 454 Creating a UserForm from a List 455 Resizing Lists 456 Dragging the resize handle in the bottom corner of the list 456 Totaling Rows 456 Converting Lists to a Range 456 Publishing Lists 457 Publishing Your List 459 Updating Changes to Your List 459 View a List on a SharePoint Server 460 Unlinking the List 461 Summary 461 xix Contents Chapter 22: PivotTables 463 Creating a PivotTable Report 464 PivotCaches 467 PivotTables Collection 467 PivotFields 467 CalculatedFields 470 PivotItems 473 Grouping 473 Visible Property 476 CalculatedItems 477 PivotCharts 478 External Data Sources 479 Summary 481 Chapter 23: Filtered Lists 483 Structuring the Data 483 Data Form 483 AutoFilter 485 Custom AutoFilter 485 Adding Combo Boxes 486 Copying the Visible Rows 490 Finding the Visible Rows 491 Advanced Filter 493 Summary 495 Chapter 24: Generating Charts 497 Chart Sheets 497 Embedded Charts 500 Editing Data Series 503 Defining Chart Series with Arrays 506 Converting a Chart to use Arrays 509 Determining the Ranges used in a Chart 509 Chart Labels 511 Summary 512 Chapter 25: Office Files and Folders 515 FileSearch 516 FoundFiles 518 PropertyTests 519 xx Contents FileTypes 520 SearchScopes 521 ScopeFolder 522 SearchFolders 523 FileDialog 525 FileDialogFilters 527 FileDialogSelectedItems 527 Dialog Types 527 Execute Method 528 MultiSelect 528 Summary 529 Chapter 26: Command Bars 531 Toolbars, Menu Bars, and Popups 531 Excel’s Built-In Commandbars 534 Controls at All Levels 537 FaceIds 540 Creating New Menus 542 The OnAction Macros 544 Passing Parameter Values 545 Deleting a Menu 545 Creating a Toolbar 546 Pop-Up Menus 551 Showing Pop-Up Command Bars 554 Disabling Commandbars 556 Disabling Shortcut Access to Customize 558 Table-Driven Command Bar Creation 559 Summary 569 Chapter 27: SmartTags 571 SmartTag Enhancements 572 Microsoft SmartTags 2.0 Type Library 573 The FileName SmartTag 574 Anatomy of a SmartTag 574 The SmartTag Unique Identifier 575 The SmartTag Recognizer Class 576 The SmartTag Actions Class 580 Implementing Office 2003 SmartTag Features 586 Registering SmartTags 589 Using the FileName SmartTag 591 Controlling SmartTags with VBA 592 xxi Contents The Problems with SmartTags 595 Summary 596 Chapter 28: Excel and the Internet 597 So What’s all the Hype About? 598 Using the Internet for Storing Workbooks 598 Using the Internet as a Data Source 599 Opening Web Pages as Workbooks 599 Using Web Queries 600 Parsing Web Pages for Specific Information 603 Using the Internet to Publish Results 605 Setting Up a Web Server 605 Saving Worksheets as Web Pages 605 Adding Interactivity with the Web Components 607 Using the Internet as a Communication Channel 608 Communicating with a Web Server 609 XML 618 The XML-SS Schema 620 Using XSLT to Transform XML 624 Summary 628 Chapter 29: XML and Excel 629 What Is XML? 629 What Is XSD? 630 What Is XMLSS? 631 Importing XML Data 633 BlackJack: Data Versatility 633 Importing an XML File 635 Exporting a Worksheet to an XML File 637 Summary 638 Appendix A: Excel 2003 Object Model 641 Appendix B: VBE Object Model 961 Appendix C: VBE Object Model 991 Index 1081 xxii
提升卡
置顶卡
沉默卡
变色卡
抢沙发
千斤顶
显身卡
京公网安备 11010802022788号
