Excel 2013 Power Programming with VBA.


John. Walkenbach
Bok Engelsk 2013 · Electronic books.
Utgitt
Hoboken, N.J. : Wiley , c2013
Omfang
1 online resource (1106 pages)
Utgave
1st ed.
Opplysninger
Cover -- Title Page -- Copyright -- About the Author -- Contents -- Introduction -- Topics Covered -- What You Need to Know -- What You Need to Have -- Conventions in This Book -- What the Icons Mean -- How This Book Is Organized -- About This Book's Website -- About the Power Utility Pak Offer -- How to Use This Book -- Part 1: Some Essential Background -- Chapter 1: Excel in a Nutshell -- About Excel -- Thinking in Terms of Objects -- Workbooks -- Excel's User Interface -- Data Entry -- Formulas, Functions, and Names -- Selecting Objects -- Formatting -- Protection Options -- Charts -- Shapes and SmartArt -- Database Access -- Internet Features -- Analysis Tools -- Add-Ins -- Macros and Programming -- File Format -- Excel's Help System -- Chapter 2: Formula Tricks and Techniques -- About Formulas -- Calculating Formulas -- Cell and Range References -- Using Names -- Formula Errors -- Excel Auditing Tools -- Array Formulas -- Counting and Summing Techniques -- Lookup Formulas -- Working with Dates and Times -- Creating Megaformulas -- Chapter 3: Understanding Excel Files -- Starting Excel -- File Types -- Excel File Compatibility -- Protected View -- Using AutoRecover -- Working with Template Files -- Inside an Excel File -- The OfficeUI File -- The XLB File -- Add-In Files -- Excel Settings in the Registry -- Chapter 4: Essentials of Spreadsheet Application Development -- What Is a Spreadsheet Application? -- Steps for Application Development -- Determining User Needs -- Planning an Application That Meets User Needs -- Determining the Most Appropriate User Interface -- Concerning Yourself with the End User -- Other Development Issues -- Part 2: Understanding Visual Basic for Applications -- Chapter 5: Introducing Visual Basic for Applications -- Getting Some BASIC Background -- Delving into VBA -- Covering the Basics of VBA.. - Chapter 11: Introducing UserForms -- How Excel Handles Custom Dialog Boxes -- Inserting a New UserForm -- Adding Controls to a UserForm -- Toolbox Controls -- Adjusting UserForm Controls -- Adjusting a Control's Properties -- Displaying a UserForm -- Closing a UserForm -- Creating a UserForm: An Example -- Understanding UserForm Events -- Referencing UserForm Controls -- Customizing the Toolbox -- Creating UserForm Templates -- A UserForm Checklist -- Chapter 12: UserForm Examples -- Creating a UserForm " Menu" -- Selecting Ranges from a UserForm -- Creating a Splash Screen -- Disabling a UserForm's Close Button -- Changing a UserForm's Size -- Zooming and Scrolling a Sheet from a UserForm -- ListBox Techniques -- Using the MultiPage Control in a UserForm -- Using an External Control -- Animating a Label -- Chapter 13: Advanced UserForm Techniques -- A Modeless Dialog Box -- Displaying a Progress Indicator -- Creating Wizards -- Emulating the MsgBox Function -- A UserForm with Movable Controls -- A UserForm with No Title Bar -- Simulating a Toolbar with a UserForm -- Emulating a Task Pane with a UserForm -- A Resizable UserForm -- Handling Multiple UserForm Controls with One Event Handler -- Selecting a Color in a UserForm -- Displaying a Chart in a UserForm -- Making a UserForm Semitransparent -- An Enhanced Data Form -- A Puzzle on a UserForm -- Video Poker on a UserForm -- Part 4: Advanced Programming Techniques -- Chapter 14: Developing Excel Utilities with VBA -- About Excel Utilities -- Using VBA to Develop Utilities -- What Makes a Good Utility? -- Text Tools: The Anatomy of a Utility -- More about Excel Utilities -- Chapter 15: Working with Pivot Tables -- An Introductory Pivot Table Example -- Creating a More Complex Pivot Table -- Creating Multiple Pivot Tables -- Creating a Reverse Pivot Table -- Chapter 16: Working with Charts.. - Getting the Inside Scoop on Charts -- Creating an Embedded Chart -- Creating a Chart on a Chart Sheet -- Modifying Charts -- Using VBA to Activate a Chart -- Moving a Chart -- Using VBA to Deactivate a Chart -- Determining Whether a Chart Is Activated -- Deleting from the ChartObjects or Charts Collection -- Looping through All Charts -- Sizing and Aligning ChartObjects -- Creating Lots of Charts -- Exporting a Chart -- Changing the Data Used in a Chart -- Using VBA to Display Arbitrary Data Labels on a Chart -- Displaying a Chart in a UserForm -- Understanding Chart Events -- Discovering VBA Charting Tricks -- Animating Charts -- Creating an Interactive Chart without VBA -- Working with Sparkline Charts -- Chapter 17: Understanding Excel's Events -- What You Should Know about Events -- Getting Acquainted with Workbook-Level Events -- Examining Worksheet Events -- Checking Out Chart Events -- Monitoring with Application Events -- Using UserForm Events -- Accessing Events Not Associated with an Object -- Chapter 18: Interacting with Other Applications -- Starting an Application from Excel -- Activating an Application with Excel -- Running Control Panel Dialog Boxes -- Using Automation in Excel -- Sending Personalized E-Mail via Outlook -- Sending E-Mail Attachments from Excel -- Chapter19: Creating and Using Add-Ins -- What Is an Add-In? -- Understanding Excel's Add-In Manager -- Creating an Add-in -- An Add-In Example -- Comparing XLAM and XLSM Files -- Manipulating Add-Ins with VBA -- Optimizing the Performance of Add-ins -- Special Problems with Add-Ins -- Part 5: Developing Applications -- Chapter 20: Working with the Ribbon -- Ribbon Basics -- Using VBA with the Ribbon -- Customizing the Ribbon -- Creating an Old-Style Toolbar -- Chapter 21: Working with Shortcut Menus -- CommandBar Overview -- Using VBA to Customize Shortcut Menus.. - Introducing Visual Basic Editor -- Working with Project Explorer -- Working with Code Windows -- Customizing the VBE Environment -- The Macro Recorder -- About Objects and Collections -- Properties and Methods -- The Comment Object: A Case Study -- Some Useful Application Properties -- Working with Range Objects -- Things to Know about Objects -- Chapter 6: VBA Programming Fundamentals -- VBA Language Elements: An Overview -- Comments -- Variables, Data Types, and Constants -- Assignment Statements -- Arrays -- Object Variables -- User-Defined Data Types -- Built-in Functions -- Manipulating Objects and Collections -- Controlling Code Execution -- Chapter 7: Working with VBA Sub Procedures -- About Procedures -- Executing Sub Procedures -- Passing Arguments to Procedures -- Error-Handling Techniques -- A Realistic Example That Uses Sub Procedures -- Chapter 8: Creating Function Procedures -- Sub Procedures versus Function Procedures -- Why Create Custom Functions? -- An Introductory Function Example -- Function Procedures -- Function Arguments -- Function Examples -- Emulating Excel's SUM function -- Extended Date Functions -- Debugging Functions -- Dealing with the Insert Function Dialog Box -- Using Add-ins to Store Custom Functions -- Using the Windows API -- Chapter 9: VBA Programming Examples and Techniques -- Learning by Example -- Working with Ranges -- Working with Workbooks and Sheets -- VBA Techniques -- Some Useful Functions for Use in Your Code -- Some Useful Worksheet Functions -- Windows API Calls -- Part 3: Working with User Forms -- Chapter 10: Custom Dialog Box Alternatives -- Before You Create That UserForm . . . -- Using an Input Box -- The VBA MsgBox Function -- The Excel GetOpenFilename Method -- The Excel GetSaveAsFilename Method -- Prompting for a Directory -- Displaying Excel's Built-In Dialog Boxes -- Displaying a Data Form.. - Part 7: Appendixes.. - Shortcut Menus and Events -- Chapter 22: Providing Help for Your Applications -- Help for Your Excel Applications -- Help Systems That Use Excel Components -- Displaying Help in a Web Browser -- Using the HTML Help System -- Chapter 23: Developing User-Oriented Applications -- What Is a User-Oriented Application? -- The Loan Amortization Wizard -- Application Development Concepts -- Part 6: Other Topics -- Chapter 24: Compatibility Issues -- What Is Compatibility? -- Types of Compatibility Problems -- Avoid Using New Features -- But Will It Work on a Mac? -- Dealing with 64-bit Excel -- Creating an International Application -- Chapter 25: Manipulating Files with VBA -- Performing Common File Operations -- Displaying Extended File Information -- Working with Text Files -- Text File Manipulation Examples -- Zipping and Unzipping Files -- Working with ADO -- Chapter 26: Manipulating Visual Basic Components -- Introducing IDE -- The IDE Object Model -- Displaying All Components in a VBA Project -- Listing All VBA Procedures in a Workbook -- Replacing a Module with an Updated Version -- Using VBA to Write VBA Code -- Adding Controls to a UserForm at Design Time -- Creating UserForms Programmatically -- Chapter 27: Understanding Class Modules -- What Is a Class Module? -- Example: Creating a NumLock Class -- More about Class Modules -- Example: A CSV File Class -- Chapter 28: Working with Colors -- Specifying Colors -- Understanding Grayscale -- Experimenting with Colors -- Understanding Document Themes -- Working with Shape Objects -- Modifying Chart Colors -- Chapter 29: Frequently Asked Questions about Excel Programming -- Getting the Scoop on FAQs -- General Excel Questions -- Visual Basic Editor -- Procedures -- Functions -- Objects, Properties, Methods, and Events -- Security-Related Issues -- UserForms -- Add-Ins -- User Interface.. - Maximize your Excel 2013 experience using VBA application development The new Excel 2013 boasts updated features, enhanced power, and new capabilities. Naturally, that means John Walkenbach returns with a new edition of his bestselling VBA Programming book and covers all the methods and tools you need to know in order to program with Excel. With this comprehensive guide, "Mr. Spreadsheet" shows you how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Featuring a complete introduction to Visual Basic for Applications and fully updated for the latest features of Excel 2013, this essential reference includes an analysis of Excel application development and is packed with procedures, tips, and ideas for expanding Excel's capabilities with VBA. Offers an analysis of Excel application development and a complete introduction to VBA Features invaluable advice from "Mr. Spreadsheet" himself, bestselling author John Walkenbach, who demonstrates all the techniques you need to create Excel applications, both large and small Covers navigating the Excel interface, formatting worksheets, interacting with other Office applications, working with collaboration tools, and using sample workbooks and John Walkenbach's award-winning Power Utility Pak to help enhance your Excel skills Provides tips, tricks, and techniques for expanding Excel's capabilities with VBA that you wont find anywhere else Excel 2013 Power Programming with VBA is packed with procedures, tips, and ideas for achieving Excel excellence with VBA.
Emner
Sjanger
Dewey
ISBN
9781118490396

Bibliotek som har denne