Microsoft Excel 2019 VBA and Macros

【实例简介】
Microsoft Excel 2019 VBA and Macros (Business Skills) By 作者: Bill Jelen – Tracy Syrstad ISBN-10 书号: 1509306110 ISBN-13 书号: 9781509306114 Edition 版本: 1 出版日期: 2019-01-07 pages 页数: (1580) Use this guide to automate virtually any routine Excel task: save yourself hours, days, maybe even weeks. Make Exc
Downloadfromwww.finelybook.comgirro@qq.com Microsoft excel 2019 vba and macros Published with the authorization of Microsoft Corporation by Pearson education. inc Copyright c 2019 by Pearson education, Inc All rights reserved. Printed in the United States of America. This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permissions, request forms, and the appropriate contacts within the pearson education Globalrights&PermissionsDepartmentpleasevisitwww.pearsoned.com/permissionss No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein ISBN-13:978-1-5093-0611-4 ISBN-10:1-5093-0611-0 Library of Congress Control Number: 2018963483 118 Trademarks Microsoftandthetrademarkslistedathttp://www.microsoft.comontheTrademarks webpage are trademarks of the microsoft group of companies. All other marks are property of their respective owners Warning and disclaimer Every effort has been made to make this book as complete and as accurate as possible but no warranty or fitness is implied. The information provided is on an "as is"basis. The author, the publisher, and Microsoft Corporation shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book Special sales For information about buying this title in bulk quantities, or for special sales opportunities (which may include electronic versions; custom cover designs; and content particular to your business, training goals, marketing focus, or branding interests), please contact our corporate sales department at corpsales@pearsoned com or(800)382-3419 Downloadfromwww.finelybook.comgirro@qq.com Forgovernmentsalesinquiriespleasecontactgovernmentsales(@pearsoned.com Forquestionsaboutsalesoutsidetheu.S.,pleasecontactintlcs(@pearson.com Editor-in-Chief: Brett bartow Executive editor: loretta yates Project Editor: Charlotte Kughen Managing editor: Sandra Schroeder Senior Project Editor: Tracey croom Copy Editor: Charlotte Kughen Indexer: Cheryl lenser Proofreaders: sarah Kearns and Karen davis Technical editor: bob umlas Editorial Assistant: Cindy Teeters Cover designer: Twist Creative, Seattle Compositor: Bronkella Publishing LLC Downloadfromwww.finelybook.comgirro@qq.com For Chip pearson. Chip's website on vBa helped tens of thousands of people around the globe. We were sorry to hear that he died this year in an auto accident and dedicate this edition of the book to Chip Bill Jelen tracy Syrstad Downloadfromwww.finelybook.comgirro@qq.com Contents at a glance Introduction CHAPTER 1 Unleashing the power of Excel with VBA chapter 2 This sounds like baSIC, so why doesn't it look familiar? ChaPTER 3 Referring to ranges ChAPTER 4 Looping and flow control CHAPTER5 R1C1-style formulas CHAPTER 6 Creating and manipulating names in VBa CHAPTER 7 Event programming CHAPTER 8 Arrays ChaPTER 9 Creating classes and collections CHAPTER 10 Userforms: An introduction CHAPTER 11 Data mining with Advanced filter CHAPTER 12 Using vBa to create pivot tables CHAPTER 13 Excel power CHAPTER 14 Sample user-defined functions CHAPTER 15 Creating charts CHAPTER 16 Data visualizations and conditional formatting CHAPTER 17 Dashboarding with sparklines in Excel 2019 CHAPTER 18 Reading from and writing to the web CHAPTER 19 Text file processing CHAPTER 20 Automating word CHAPTER 21 Using Access as a back end to enhance multiuser access to Downloadfromwww.finelybook.comgirro@qq.com data CHAPTER 22 Advanced userform techniques CHAPTER 23 The Windows Application Programming Interface(APD CHAPTER 24 Handling errors CHAPTER 25 Customizing the ribbon to run macros CHAPTER 26 Creating add-ins CHAPTER 27 An introduction to creating office add-ins CHAPTER 28 What's new in Excel 2019 and what's changed Index Downloadfromwww.finelybook.comgirro@qq.com Contents Introduction Chapter 1 Unleashing the power of Excel with VBA Barriers to entry The macro recorder doesn't work! No one person on the Excel team is focused on the macro recorder Visual basic is not like basic Good news: Climbing the learning curve is easy Great news, Excel with vba is worth the effort Knowing your tools: The developer tab Understanding which file types allow macros Macro security Adding a trusted location Using macro settings to enable macros in workbooks outside trusted locations Using disable all macros With Notification Overview of recording, storing, and running a macro Filling out the record macro dialog box Running a macro Creating a macro button on the ribbon Creating a macro button on the quick access toolbar assigning a macro to a form control, text box or shape Understanding the vb editor VB Editor settings The project explorer The Properties window Downloadfromwww.finelybook.comgirro@qq.com Understanding shortcomings of the macro recorde Recording the macro Examining code in the Programming window Running the macro on another day produces undesired results Possible solution: Use relative references when recording Never use auto Sum or Quick analysis while recording a macro Four tips for using the macro recorder Next steps Chapter 2 This sounds like BASIC, so why doesn't it look familiar? Understanding the parts of VBA"speech' vba is not really hard VBA Help files: Using F1 to find anything Using Help topics Examining recorded macro code: Using the vB Editor and Help Optional parameters Defined constants Properties can return objects Using debugging tools to figure out recorded code Stepping through code More debugging options: Breakpoints Backing up or moving forward in code Not stepping through each line of code Querying anything while stepping through code Using a watch to set a breakpoint Using a watch on an object Object Browser: The ultimate reference Seven tips for cleaning up recorded code Tip 1: Don't select anything Tip 2: Use Cells(2, 5)because it's more convenient than Downloadfromwww.finelybook.comgirro@qq.com Range( E2 Tip 3: Use more reliable ways to find the last row Tip 4: Use variables to avoid hard-coding rows and formulas Tip 5: Use R1C1 formulas that make your life easier Tip 6: Copy and paste in a single statement Tip 7: Use With.End With to perform multiple actions Next steps Chapter 3 Referring to ranges The range object Syntax for specifying a range Referencing named ranges Shortcut for referencing ranges Referencing ranges in other sheets Referencing a range relative to another range Using the Cells property to select a range Using the offset property to refer to a range Using the resize property to change the size of a range Using the Columns and Rows properties to specify a range Using the Union method to join multiple ranges Using the Intersect method to create a new range from overlapping ranges Using the isEmpty function to check whether a cell is empty Using the CurrentRegion property to select a data range Using the areas collection to return a noncontiguous range Referencing tables Next steps Chapter 4 Looping and flow control For.Next loops 【实例截图】
【核心代码】