<br /><br /><p>第一次上传，希望大家喜欢</p>
<p>Excel.2003.VBA.Programmers</p><p><br></p><br>

Contents&nbsp;<div>About the Authors v
Acknowledgments ix
Introduction xxiii
Chapter 1: Primer in Excel VBA 1
Using the Macro Recorder 2
RContents</div><div>About the Authors v</div><div>Acknowledgments ix</div><div>Introduction xxiii</div><div>Chapter 1: Primer in Excel VBA 1</div><div>Using the Macro Recorder 2</div><div>Recording Macros 2</div><div>Running Macros 5</div><div>The Visual Basic Editor 8</div><div>Other Ways to Run Macros 11</div><div>User Defined Functions 18</div><div>Creating a UDF 18</div><div>What UDFs Cannot Do 22</div><div>The Excel Object Model 22</div><div>Objects 23</div><div>Getting Help 29</div><div>Experimenting in the Immediate Window 30</div><div>The VBA Language 32</div><div>Basic Input and Output 32</div><div>Calling Functions and Subroutines 37</div><div>Variable Declaration 38</div><div>Scope and Lifetime of Variables 40</div><div>Variable Type 42</div><div>Object Variables 45</div><div>Making Decisions 47</div><div>Looping 50</div><div>Arrays 55</div><div>Runtime Error Handling 58</div><div>Summary 62</div><div>Chapter 2: Programming in the VBE 63</div><div>Writing Code 63</div><div>Programming for People 63</div><div>Writing Code 64</div><div>Where Does My Code Go? 65P1: GIG</div><div>WY010-FM WY010-Kingsley WY010-Kimmel-v1.cls May 28, 2004 20:24</div><div>Contents</div><div>Managing a Project 65</div><div>Adding Classes 67</div><div>Modifying Properties 68</div><div>Importing and Exporting Visual Basic Code 69</div><div>Editing 70</div><div>Managing Editor Options 70</div><div>Running and Debugging Code 71</div><div>Using Watches 71</div><div>Using the Object Browser 72</div><div>Summary 73</div><div>Chapter 3: The Application Object 75</div><div>Globals 75</div><div>The Active Properties 76</div><div>Display Alerts 77</div><div>Screen Updating 77</div><div>Evaluate 78</div><div>InputBox 80</div><div>StatusBar 81</div><div>SendKeys 82</div><div>OnTime 83</div><div>OnKey 84</div><div>Worksheet Functions 85</div><div>Caller 87</div><div>Summary 88</div><div>Chapter 4: Object-Oriented Theory and VBA 89</div><div>Comparing Classes and Interfaces 89</div><div>Defining an Interface 90</div><div>Implementing an Interface 91</div><div>Defining Methods 92</div><div>Parameters 93</div><div>Implementing Recursive Methods 94</div><div>Eliminating Recursion with Loops 94</div><div>Defining Fields 95</div><div>Defining Properties 95</div><div>Read-Only Properties 96</div><div>Write-Only Properties 97</div><div>Defining Events 97</div><div>Defining Events in Classes 97</div><div>xiiP1: GIG</div><div>WY010-FM WY010-Kingsley WY010-Kimmel-v1.cls May 28, 2004 20:24</div><div>Contents</div><div>Raising Events 98</div><div>Handling Events 99</div><div>Information Hiding and Access Modifiers 100</div><div>Encapsulation, Aggregation, and References 100</div><div>Summary 101</div><div>Chapter 5: Event Procedures 103</div><div>Worksheet Events 103</div><div>Enable Events 104</div><div>Worksheet Calculate 105</div><div>Chart Events 106</div><div>Before Double Click 106</div><div>Workbook Events 108</div><div>Save Changes 110</div><div>Headers and Footers 111</div><div>Summary 112</div><div>Chapter 6: Class Modules 113</div><div>Creating Your Own Objects 113</div><div>Using Collections 115</div><div>Class Module Collection 116</div><div>Trapping Application Events 118</div><div>Embedded Chart Events 120</div><div>A Collection of UserForm Controls 122</div><div>Referencing Classes Across Projects 124</div><div>Summary 125</div><div>Chapter 7: Writing Bulletproof Code 127</div><div>Using Debug.Print 127</div><div>Using Debug.Assert 128</div><div>A Brief Exemplar of PC Debugging 129</div><div>Creating Reusable Tools with the Debug Object 133</div><div>Tracing Code Execution 133</div><div>Trapping Code Execution Paths 135</div><div>Asserting Application Invariants 137</div><div>Raising Errors 140</div><div>Writing Error Handlers 142</div><div>On Error Goto Line Number 142</div><div>On Error Resume Next 143</div><div>xiiiP1: GIG</div><div>WY010-FM WY010-Kingsley WY010-Kimmel-v1.cls May 28, 2004 20:24</div><div>Contents</div><div>On Error GoTo 0 145</div><div>Using the Err Object 145</div><div>Scaffolding 145</div><div>Writing to the EventLog 147</div><div>Summary 149</div><div>Chapter 8: Debugging and Testing 151</div><div>Stepping Through Code 151</div><div>Running Your Code 152</div><div>Stepping into Your Code 153</div><div>Step Over 153</div><div>Step Out 153</div><div>Run to Cursor 154</div><div>Set Next Statement 154</div><div>Show Next Statement 155</div><div>Using Breakpoints 155</div><div>Using Watches 155</div><div>Add Watch 156</div><div>Edit Watch 158</div><div>Quick Watch 158</div><div>Locals Windows 158</div><div>Testing an Expression in the Immediate Window 159</div><div>Resources for Finding Definitions 160</div><div>Edit ➪Quick Info 160</div><div>Edit ➪Parameter Info 161</div><div>Edit ➪Complete Word 161</div><div>Edit ➪List Properties/Methods 161</div><div>Edit ➪List Constants 162</div><div>Edit ➪Bookmarks 162</div><div>View ➪Definition 162</div><div>View ➪Object Browser 163</div><div>Viewing the Call Stack 163</div><div>Asserting Application Invariants 164</div><div>Summary 165</div><div>Chapter 9: UserForms 167</div><div>Displaying a UserForm 167</div><div>Creating a UserForm 169</div><div>Directly Accessing Controls in UserForms 171</div><div>Stopping the Close Button 174</div><div>xivP1: GIG</div><div>WY010-FM WY010-Kingsley WY010-Kimmel-v1.cls May 28, 2004 20:24</div><div>Contents</div><div>Maintaining a Data List 175</div><div>Modeless UserForms 181</div><div>Summary 181</div><div>Chapter 10: Adding Controls 183</div><div>The Toolbars 183</div><div>ActiveX Controls 184</div><div>Scrollbar Control 185</div><div>Spin Button Control 186</div><div>CheckBox Control 186</div><div>Option Button Controls 187</div><div>Forms Toolbar Controls 188</div><div>Dynamic ActiveX Controls 191</div><div>Controls on Charts 194</div><div>Summary 195</div><div>Chapter 11: Data Access with ADO 197</div><div>An Introduction to Structured Query Language (SQL) 197</div><div>The SELECT Statement 198</div><div>The INSERT Statement 200</div><div>The UPDATE Statement 201</div><div>The CREATE TABLE Statement 202</div><div>The DROP TABLE Statement 203</div><div>An Overview of ADO 203</div><div>The Connection Object 204</div><div>The Recordset Object 212</div><div>The Command Class 218</div><div>Using ADO in Microsoft Excel Applications 222</div><div>Using ADO with Microsoft Access 223</div><div>Using ADO with Microsoft SQL Server 230</div><div>Using ADO with Non-Standard Data Sources 239</div><div>Summary 244</div><div>Chapter 12: Creating and Using Add-ins 245</div><div>Hiding the Code 245</div><div>Converting the Workbook to an Add-in 247</div><div>Closing Add-ins 247</div><div>Code Changes 248</div><div>Saving Changes 249</div><div>xvP1: GIG</div><div>WY010-FM WY010-Kingsley WY010-Kimmel-v1.cls May 28, 2004 20:24</div><div>Contents</div><div>Installing an Add-in 250</div><div>Add-in Install Event 251</div><div>Removing an Add-in from the Add-ins List 252</div><div>Summary 252</div><div>Chapter 13: Automation Addins and COM Addins 253</div><div>Automation Addins 253</div><div>Creating a Simple Addin 254</div><div>Registering Automation Addins with Excel 255</div><div>Using Automation Addins 257</div><div>An Introduction to the IDTExtensibility2 Interface 259</div><div>A Complex Addin—Generating a Unique Random Number 262</div><div>COM Addins 268</div><div>IDTExtensibility2 Interface Continued 268</div><div>Summary 279</div><div>Chapter 14: Customizing the VBE 281</div><div>Identifying VBE Objects in Code 281</div><div>The VBE Object 282</div><div>The VBProject Object 283</div><div>The VBComponent Object 283</div><div>The CodeModule Object 284</div><div>The CodePane Object 285</div><div>The Designer Object 285</div><div>Starting Up 285</div><div>Adding Menu Items to the VBE 286</div><div>Table-Driven Menu Creation 288</div><div>Displaying Built-In Dialogs, UserForms, and Messages 296</div><div>Working with Code 301</div><div>Working with UserForms 305</div><div>Working with References 310</div><div>Summary 311</div><div>Chapter 15: Interacting with Other Office Applications 313</div><div>Establishing the Connection 314</div><div>Late Binding 314</div><div>Early Binding 315</div><div>Opening a Document in Word 317</div><div>Accessing an Active Word Document 318</div><div>Creating a New Word Document 319</div><div>xviP1: GIG</div><div>WY010-FM WY010-Kingsley WY010-Kimmel-v1.cls May 28, 2004 20:24</div><div>Contents</div><div>Access and DAO 320</div><div>Access, Excel and, Outlook 321</div><div>When Is a Virus not a Virus? 323</div><div>Summary 324</div><div>Chapter 16: Programming with the Windows API 327</div><div>Anatomy of an API Call 328</div><div>Interpreting C-Style Declarations 329</div><div>Constants, Structures, Handles, and Classes 332</div><div>What if Something Goes Wrong? 335</div><div>Wrapping API Calls in Class Modules 336</div><div>Some Example Classes 341</div><div>A High-Resolution Timer Class 341</div><div>Freeze a UserForm 342</div><div>A System Info Class 344</div><div>Modifying UserForm Styles 346</div><div>Resizable Userforms 350</div><div>Other Examples 356</div><div>Summary 358</div><div>Chapter 17: International Issues 359</div><div>Changing Windows Regional Settings and the Office XP UI</div><div>Language 359</div><div>Responding to Regional Settings and the Windows Language 360</div><div>Identifying the User’s Regional Settings and</div><div>Windows Language 360</div><div>VBA Conversion Functions from an International Perspective 361</div><div>Interacting with Excel 366</div><div>Sending Data to Excel 367</div><div>Reading Data from Excel 369</div><div>Rules for Working with Excel 370</div><div>Interacting with Users 370</div><div>The Rules for Working with Your Users 373</div><div>Excel 2003’s International Options 373</div><div>Features That Don’t Play by the Rules 375</div><div>Responding to Office XP Language Settings 382</div><div>Where Does the Text Come From? 382</div><div>Identifying the Office UI Language Settings 383</div><div>Creating a Multilingual Application 384</div><div>Working in a Multilingual Environment 386</div><div>The Rules for Developing a Multilingual Application 388</div><div>xviiP1: GIG</div><div>WY010-FM WY010-Kingsley WY010-Kimmel-v1.cls May 28, 2004 20:24</div><div>Contents</div><div>Some Helpful Functions 388</div><div>Implementing WinToNum Function 388</div><div>Implementing WinToDate Function 389</div><div>Implementing FormatDate Function 390</div><div>Implementing ReplaceHolders Function 390</div><div>Summary 391</div><div>Chapter 18: Workbooks and Worksheets 393</div><div>Using the Workbooks Collection 393</div><div>Creating a New Workbook 393</div><div>Saving the ActiveWorkbook 394</div><div>Activating a Workbook 394</div><div>Getting a FileName from a Path 395</div><div>Files in the Same Directory 397</div><div>Overwriting an Existing Workbook 398</div><div>Saving Changes 399</div><div>The Sheets Collection 400</div><div>Worksheets 400</div><div>Copy and Move 402</div><div>Grouping Worksheets 403</div><div>The Window Object 405</div><div>Synchronizing Worksheets 406</div><div>Summary 407</div><div>Chapter 19: Using Ranges 409</div><div>Activate and Select 409</div><div>Range Property 411</div><div>Shortcut Range References 412</div><div>Ranges on Inactive Worksheets 412</div><div>Range Property of a Range Object 413</div><div>Cells Property 413</div><div>Cells used in Range 414</div><div>Ranges of Inactive Worksheets 414</div><div>More on the Cells Property of the Range Object 415</div><div>Single-Parameter Range Reference 417</div><div>Offset Property 418</div><div>Resize Property 420</div><div>SpecialCells Method 420</div><div>CurrentRegion Property 424</div><div>xviiiP1: GIG</div><div>WY010-FM WY010-Kingsley WY010-Kimmel-v1.cls May 28, 2004 20:24</div><div>Contents</div><div>End Property 426</div><div>Referring to Ranges with End 426</div><div>Summing a Range 427</div><div>Columns and Rows Properties 428</div><div>Areas 429</div><div>Union and Intersect Methods 431</div><div>Empty Cells 432</div><div>Transferring Values between Arrays and Ranges 434</div><div>Deleting Rows 436</div><div>Summary 438</div><div>Chapter 20: Using Names 441</div><div>Naming Ranges 442</div><div>Using the Name Property of the Range Object 443</div><div>Special Names 443</div><div>Storing Values in Names 444</div><div>Storing Arrays 445</div><div>Hiding Names 446</div><div>Working with Named Ranges 446</div><div>Searching for a Name 447</div><div>Searching for the Name of a Range 449</div><div>Determining which Names Overlap a Range 450</div><div>Summary 452</div><div>Chapter 21: Working with Lists 453</div><div>Creating a List 453</div><div>Shortcut Options for Lists 454</div><div>Sorting and Filtering a List 454</div><div>Creating a UserForm from a List 455</div><div>Resizing Lists 456</div><div>Dragging the resize handle in the bottom corner of the list 456</div><div>Totaling Rows 456</div><div>Converting Lists to a Range 456</div><div>Publishing Lists 457</div><div>Publishing Your List 459</div><div>Updating Changes to Your List 459</div><div>View a List on a SharePoint Server 460</div><div>Unlinking the List 461</div><div>Summary 461</div><div>xi</div><div><br></div><div>........................</div>