Opis: Excel 2016 VBA and Macros: Includes Content Update Program - Tracy Syrstad, Bill Jelen

MASTER CORE EXCEL 2016 TOOLS FOR BUILDING POWERFUL, RELIABLE SPREADSHEETS! Use this guide to automate virtually any routine task: save yourself hours, days, maybe even weeks! Make Excel do things you thought were impossible, discover macro techniques you won't find anywhere else, and create automated reports that are amazingly powerful. Bill Jelen and Tracy Syrstad help you instantly visualize information, so you can act on it...capture data from anywhere, and use it anywhere...automate Excel 2016's best new features. You'll find simple, step-by-step instructions, real-world case studies, and 50 workbooks packed with bonus examples, macros, and solutions-straight from MrExcel! * Get started fast with Excel macro development * Work efficiently with ranges, cells, and formulas * Build super-fast applications with arrays * Automate Excel's new pivot table enhancements * Collect user data with custom dialogs * Make your macros more reliable and resilient * Pull data from the Internet with web queries * Use advanced classes, collections, and custom functions * Build sophisticated business analysis solutions * Read and write to Access or SQL Server databases * Control other Office programs, and Windows itself * Write code that also works on older Excel versions * Start writing Office Store-style Excel Apps About MrExcel Library Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will * Dramatically increase your productivity-saving you 50 hours a year or more * Present proven, creative strategies for solving real-world problems * Show you how to get great results, no matter how much data you have * Help you avoid critical mistakes that even experienced users make This book is part of Que's Content Update Program. As Microsoft updates features of Excel, sections of this book will be updated or new sections will be added to match the updates to the software. See inside for details.Introduction ...1 What Is in This Book? ...1 Reducing the Learning Curve ...1 Excel VBA Power ...2 Techie Stuff Needed to Produce Applications ...2 Does This Book Teach Excel? ...2 The Future of VBA and Windows Versions of Excel ...4 Versions of Excel ...4 Differences for Mac Users ...4 Special Elements and Typographical Conventions ...5 Code Files ...5 Next Steps ...5 1 Unleashing the Power of Excel with VBA ...7 The Power of Excel ...7 Barriers to Entry ...7 The Macro Recorder Doesn't Work! ...7 No One on the Excel Team Is Focused on the Macro Recorder ...8 Visual Basic Is Not Like BASIC...8 Good News: Climbing the Learning Curve Is Easy ...9 Great News: Excel with VBA Is Worth the Effort ...9 Knowing Your Tools: The Developer Tab ...9 Understanding Which File Types Allow Macros ...10 Macro Security ...12 Adding a Trusted Location ...12 Using Macro Settings to Enable Macros in Workbooks Outside Trusted Locations ...13 Using Disable All Macros with Notification...14 Overview of Recording, Storing, and Running a Macro ...14 Filling Out the Record Macro Dialog ...15 Running a Macro ...16 Creating a Macro Button on the Ribbon ...16 Creating a Macro Button on the Quick Access Toolbar ...17 Assigning a Macro to a Form Control, Text Box, or Shape ...18 Understanding the VB Editor...19 VB Editor Settings ...20 The Project Explorer ...20 The Properties Window ...21 Understanding Shortcomings of the Macro Recorder ...21 Recording the Macro ...23 Examining Code in the Programming Window ...23 Running the Macro on Another Day Produces Undesired Results ...25 Possible Solution: Use Relative References When Recording ...26 Never Use AutoSum or Quick Analysis While Recording a Macro ...30 Four Tips for Using the Macro Recorder...31 Next Steps ...32 2 This Sounds Like BASIC, So Why Doesn't It Look Familiar?...33 I Can't Understand This Code ...33 Understanding the Parts of VBA "Speech" ...34 VBA Is Not Really Hard ...37 VBA Help Files: Using F1 to Find Anything ...38 Using Help Topics ...38 Examining Recorded Macro Code: Using the VB Editor and Help ...39 Optional Parameters ...39 Defined Constants ...40 Properties Can Return Objects ...43 Using Debugging Tools to Figure Out Recorded Code ...43 Stepping Through Code ...43 More Debugging Options: Breakpoints ...45 Backing Up or Moving Forward in Code ...45 Not Stepping Through Each Line of Code ...46 Querying Anything While Stepping Through Code ...46 Using a Watch to Set a Breakpoint ...49 Using a Watch on an Object ...49 Object Browser: The Ultimate Reference ...50 Seven Tips for Cleaning Up Recorded Code...51 Tip 1: Don't Select Anything ...51 Tip 2: Use Cells(2,5) Because It's More Convenient Than Range("E2") ...52 Tip 3: Use More Reliable Ways to Find the Last Row ...52 Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas...53 Tip 5: Use R1C1 Formulas That Make Your Life Easier ...54 Tip 6: Copy and Paste in a Single Statement ...54 Tip 7: Use With...End With to Perform Multiple Actions ...54 Next Steps ...57 3 Referring to Ranges...59 The Range Object ...59 Syntax for Specifying a Range ...60 Named Ranges ...60 Shortcut for Referencing Ranges ...60 Referencing Ranges in Other Sheets ...61 Referencing a Range Relative to Another Range ...61 Using the Cells Property to Select a Range ...62 Using the Offset Property to Refer to a Range ...63 Using the Resize Property to Change the Size of a Range ...65 Using the Columns and Rows Properties to Specify a Range ...66 Using the Union Method to Join Multiple Ranges ...66 Using the Intersect Method to Create a New Range from Overlapping Ranges ...67 Using the IsEmpty Function to Check Whether a Cell Is Empty ...67 Using the CurrentRegion Property to Select a Data Range ...68 Using the Areas Collection to Return a Noncontiguous Range ...70 Referencing Tables ...71 Next Steps ...72 4 Looping and Flow Control ...73 For...Next Loops ...73 Using Variables in the For Statement ...75 Variations on the For...Next Loop ...76 Exiting a Loop Early After a Condition Is Met ...77 Nesting One Loop Inside Another Loop ...78 Do Loops ...78 Using the While or Until Clause in Do Loops ...81 The VBA Loop: For Each ...82 Object Variables...83 Flow Control: Using If...Then...Else and Select Case ...86 Basic Flow Control: If...Then...Else ...86 Using Select Case...End Select for Multiple Conditions ...88 Next Steps ...91 5 R1C1-Style Formulas ...93 Referring to Cells: A1 Versus R1C1 References ...93 Toggling to R1C1-Style References ...94 Witnessing the Miracle of Excel Formulas ...95 Entering a Formula Once and Copying 1,000 Times ...95 The Secret: It's Not That Amazing ...96 Understanding the R1C1 Reference Style...97 Using R1C1 with Relative References...97 Using R1C1 with Absolute References ...98 Using R1C1 with Mixed References ...98 Referring to Entire Columns or Rows with R1C1 Style...99 Replacing Many A1 Formulas with a Single R1C1 Formula ...99 Remembering Column Numbers Associated with Column Letters ...101 Using R1C1 Formulas with Array Formulas ...101 Next Steps ...102 6 Creating and Manipulating Names in VBA ...103 Global Versus Local Names ...103 Adding Names ...104 Deleting Names ...105 Adding Comments ...106 Types of Names ...106 Formulas ...106 Strings ...107 Numbers ...108 Tables...109 Using Arrays in Names ...109 Reserved Names ...110 Hiding Names ...111 Checking for the Existence of a Name ...111 Next Steps ...114 7 Event Programming ...115 Levels of Events ...115 Using Events ...116 Event Parameters ...116 Enabling Events ...117 Workbook Events ...117 Workbook-Level Sheet and Chart Events ...119 Worksheet Events ...120 Chart Events ...123 Embedded Charts ...123 Embedded Chart and Chart Sheet Events ...124 Application-Level Events...125 Next Steps ...130 8 Arrays ...131 Declaring an Array ...131 Declaring a Multidimensional Array ...132 Filling an Array ...133 Retrieving Data from an Array...134 Using Arrays to Speed Up Code ...135 Using Dynamic Arrays ...136 Passing an Array ...137 Next Steps ...138 9 Creating Classes and Collections...139 Inserting a Class Module ...139 Trapping Application and Embedded Chart Events ...140 Application Events ...140 Embedded Chart Events ...141 Creating a Custom Object ...143 Using a Custom Object ...145 Using Collections ...145 Creating a Collection ...146 Creating a Collection in a Standard Module...146 Creating a Collection in a Class Module ...148 Using Dictionaries ...150 Using User-Defined Types to Create Custom Properties ...153 Next Steps ...156 10 Userforms: An Introduction ...157 Input Boxes ...157 Message Boxes ...158 Creating a Userform ...158 Calling and Hiding a Userform ...159 Programming Userforms ...160 Userform Events ...160 Programming Controls ...162 Using Basic Form Controls ...163 Using Labels, Text Boxes, and Command Buttons ...163 Deciding Whether to Use List Boxes or Combo Boxes in Forms ...165 Adding Option Buttons to a Userform ...167 Adding Graphics to a Userform ...169 Using a Spin Button on a Userform ...170 Using the MultiPage Control to Combine Forms ...171 Verifying Field Entry ...174 Illegal Window Closing ...174 Getting a Filename ...175 Next Steps ...176 11 Data Mining with Advanced Filter...177 Replacing a Loop with AutoFilter ...177 Using AutoFilter Techniques ...180 Selecting Visible Cells Only ...183 Advanced Filter-Easier in VBA Than in Excel ...184 Using the Excel Interface to Build an Advanced Filter ...185 Using Advanced Filter to Extract a Unique List of Values ...186 Extracting a Unique List of Values with the User Interface ...186 Extracting a Unique List of Values with VBA Code ...187 Getting Unique Combinations of Two or More Fields ...191 Using Advanced Filter with Criteria Ranges ...192 Joining Multiple Criteria with a Logical OR ...193 Joining Two Criteria with a Logical AND ...194 Other Slightly Complex Criteria Ranges ...194 The Most Complex Criteria: Replacing the List of Values with a Condition Created as the Result of a Formula ...194 Using Filter in Place in Advanced Filter ...201 Catching No Records When Using a Filter in Place ...202 Showing All Records After Running a Filter in Place ...202 The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only ...203 Copying All Columns ...203 Copying a Subset of Columns and Reordering ...204 Excel in Practice: Turning Off a Few Drop-downs in the AutoFilter ...209 Next Steps ...210 12 Using VBA to Create Pivot Tables ...211 Understanding How Pivot Tables Evolved Over Various Excel Versions ...211 While Building a Pivot Table in Excel VBA ...212 Defining the Pivot Cache ...212 Creating and Configuring the Pivot Table ...213 Adding Fields to the Data Area ...214 Learning Why You Cannot Move or Change Part of a Pivot Report...216 Determining the Size of a Finished Pivot Table to Convert the Pivot Table to Values ...217 Using Advanced Pivot Table Features ...219 Using Multiple Value Fields ...220 Grouping Daily Dates to Months, Quarters, or Years ...221 Changing the Calculation to Show Percentages ...222 Eliminating Blank Cells in the Values Area ...225 Controlling the Sort Order with AutoSort ...225 Replicating the Report for Every Product ...225 Filtering a Data Set ...228 Manually Filtering Two or More Items in a Pivot Field ...228 Using the Conceptual Filters ...229 Using the Search Filter ...233 Setting Up Slicers to Filter a Pivot Table ...235 Setting Up a Timeline to Filter an Excel 2016 Pivot Table ...239 Using the Data Model in Excel 2016 ...242 Adding Both Tables to the Data Model ...242 Creating a Relationship Between the Two Tables ...243 Defining the PivotCache and Building the Pivot Table...243 Adding Model Fields to the Pivot Table ...244 Adding Numeric Fields to the Values Area ...244 Putting It All Together...245 Using Other Pivot Table Features ...247 Calculated Data Fields ...247 Calculated Items ...247 Using ShowDetail to Filter a Record Set ...248 Changing the Layout from the Design Tab ...248 Settings for the Report Layout ...248 Suppressing Subtotals for Multiple Row Fields...249 Next Steps ...250 13 Excel Power ...251 File Operations ...251 Listing Files in a Directory ...251 Importing and Deleting a CSV File ...254 Reading a Text File into Memory and Parsing ...254 Combining and Separating Workbooks ...255 Separating Worksheets into Workbooks...255 Combining Workbooks...256 Filtering and Copying Data to Separate Worksheets ...257 Copying Data to Separate Worksheets Without Using Filter ...258 Exporting Data to an XML File ...259 Working with Cell Comments ...260 Resizing Comments...260 Placing a Chart in a Comment ...261 Selecting Cells ...263 Using Conditional Formatting to Highlight the Selected Cell ...263 Highlighting the Selected Cell Without Using Conditional Formatting ...264 Selecting/Deselecting Noncontiguous Cells ...265 Creating a Hidden Log File...267 Techniques for VBA Pros ...268 Creating an Excel State Class Module ...268 Drilling-Down a Pivot Table ...270 Filtering an OLAP Pivot Table by a List of Items ...271 Creating a Custom Sort Order ...273 Creating a Cell Progress Indicator ...274 Using a Protected Password Box ...275 Changing Case ...277 Selecting with SpecialCells ...279 Resetting a Table's Format ...279 Cool Applications ...280 Getting Historical Stock/Fund Quotes ...280 Using VBA Extensibility to Add Code to New Workbooks ...281 Next Steps ...282 14 Sample User-Defined Functions ...283 Creating User-Defined Functions ...283 Sharing UDFs ...286 Useful Custom Excel Functions ...286 Setting the Current Workbook's Name in a Cell ...286 Setting the Current Workbook's Name and File Path in a Cell ...287 Checking Whether a Workbook Is Open ...287 Checking Whether a Sheet in an Open Workbook Exists ...287 Counting the Number of Workbooks in a Directory ...288 Retrieving the User ID...289 Retrieving Date and Time of Last Save ...291 Retrieving Permanent Date and Time ...291 Validating an Email Address ...292 Summing Cells Based on Interior Color ...293 Counting Unique Values ...294 Removing Duplicates from a Range ...295 Finding the First Nonzero-Length Cell in a Range ...296 Substituting Multiple Characters ...297 Retrieving Numbers from Mixed Text ...298 Converting Week Number into Date ...299 Extracting a Single Element from a Delimited String ...300 Sorting and Concatenating ...300 Sorting Numeric and Alpha Characters ...302 Searching for a String Within Text...303 Reversing the Contents of a Cell ...304 Returning the Addresses of Duplicate Max Values ...304 Returning a Hyperlink Address ...305 Returning the Column Letter of a Cell Address ...306 Using Static Random ...306 Using Select Case on a Worksheet ...307 Next Steps ...308 15 Creating Charts ...309 Contrasting the Good and Bad VBA to Create Charts...309 Planning for More Charts to Break ...310 Using .AddChart2 to Create a Chart ...311 Understanding Chart Styles ...312 Formatting a Chart ...315 Referring to a Specific Chart ...315 Specifying a Chart Title ...316 Applying a Chart Color ...317 Filtering a Chart ...318 Using SetElement to Emulate Changes from the Plus Icon ...319 Using the Format Method to Micromanage Formatting Options ...324 Changing an Object's Fill ...325 Formatting Line Settings ...327 Creating a Combo Chart ...327 Exporting a Chart as a Graphic ...330 Considering Backward Compatibility ...331 Next Steps ...331 16 Data Visualizations and Conditional Formatting ...333 VBA Methods and Properties for Data Visualizations ...334 Adding Data Bars to a Range ...335 Adding Color Scales to a Range ...339 Adding Icon Sets to a Range ...341 Specifying an Icon Set ...341 Specifying Ranges for Each Icon ...343 Using Visualization Tricks ...343 Creating an Icon Set for a Subset of a Range ...344 Using Two Colors of Data Bars in a Range ...345 Using Other Conditional Formatting Methods ...347 Formatting Cells That Are Above or Below Average ...348 Formatting Cells in the Top 10 or Bottom 5 ...348 Formatting Unique or Duplicate Cells ...349 Formatting Cells Based on Their Value ...350 Formatting Cells That Contain Text ...351 Formatting Cells That Contain Dates ...351 Formatting Cells That Contain Blanks or Errors ...351 Using a Formula to Determine Which Cells to Format ...352 Using the New NumberFormat Property ...353 Next Steps ...354 17 Dashboarding with Sparklines in Excel 2016 ...355 Creating Sparklines ...356 Scaling Sparklines ...357 Formatting Sparklines ...361 Using Theme Colors ...361 Using RGB Colors ...364 Formatting Sparkline Elements ...365 Formatting Win/Loss Charts ...368 Creating a Dashboard ...369 Observations About Sparklines ...369 Creating Hundreds of Individual Sparklines in a Dashboard...370 Next Steps ...374 18 Reading from and Writing to the Web ...375 Getting Data from the Web ...375 Building Multiple Queries with VBA ...377 Finding Results from Retrieved Data ...378 Putting It All Together...379 Examples of Scraping Websites Using Web Queries ...380 Using Application.OnTime to Periodically Analyze Data ...381 Using Ready Mode for Scheduled Procedures ...381 Specifying a Window of Time for an Update ...382 Canceling a Previously Scheduled Macro ...382 Closing Excel Cancels All Pending Scheduled Macros ...383 Scheduling a Macro to Run x Minutes in the Future ...383 Scheduling a Verbal Reminder ...383 Scheduling a Macro to Run Every Two Minutes...384 Publishing Data to a Web Page ...385 Using VBA to Create Custom Web Pages ...386 Using Excel as a Content Management System ...387 Bonus: FTP from Excel ...389 Next Steps ...390 19 Text File Processing ...391 Importing from Text Files ...391 Importing Text Files with Fewer Than 1,048,576 Rows ...391 Dealing with Text Files with More Than 1,048,576 Rows ...398 Writing Text Files ...402 Next Steps ...403 20 Automating Word ...405 Using Early Binding to Reference a Word Object ...406 Using Late Binding to Reference a Word Object ...408 Using the New Keyword to Reference a Word Application ...409 Using the CreateObject Function to Create a New Instance of an Object ...409 Using the GetObject Function to Reference an Existing Instance of Word ...410 Using Constant Values ...411 Using the Watches Window to Retrieve the Real Value of a Constant...411 Using the Object Browser to Retrieve the Real Value of a Constant ...412 Understanding Word's Objects ...413 The Document Object ...413 The Selection Object ...415 The Range Object ...416 Bookmarks ...419 Controlling Form Fields in Word ...420 Next Steps ...422 21 Using Access as a Back End to Enhance Multiuser Access to Data ...423 ADO Versus DAOs ...424 The Tools of ADO ...426 Adding a Record to a Database ...427 Retrieving Records from a Database ...429 Updating an Existing Record ...431 Deleting Records via ADO ...433 Summarizing Records via ADO ...433 Other Utilities via ADO...434 Checking for the Existence of Tables ...434 Checking for the Existence of a Field ...435 Adding a Table On the Fly ...436 Adding a Field On the Fly ...436 SQL Server Examples ...437 Next Steps ...438 22 Advanced Userform Techniques ...439 Using the UserForm Toolbar in the Design of Controls on Userforms ...439 More Userform Controls ...440 Checkbox Controls ...440 Controls and Collections ...447 Modeless Userforms ...449 Using Hyperlinks in Userforms ...449 Adding Controls at Runtime...450 Resizing the Userform On the Fly ...452 Adding a Control On the Fly ...452 Sizing On the Fly ...452 Adding Other Controls ...453 Adding an Image On the Fly ...453 Putting It All Together...454 Adding Help to a Userform ...456 Showing Accelerator Keys ...456 Adding Control Tip Text ...457 Creating the Tab Order ...457 Coloring the Active Control ...457 Creating Transparent Forms ...460 Next Steps ...461 23 The Windows Application Programming Interface (API) ...463 Understanding an API Declaration ...464 Using an API Declaration ...465 Making 32-Bit- and 64-Bit-Compatible API Declarations ...465 API Function Examples ...467 Retrieving the Computer Name ...467 Checking Whether an Excel File Is Open on a Network ...467 Retrieving Display-Resolution Information ...468 Customizing the About Dialog ...469 Disabling the X for Closing a Userform ...470 Creating a Running Timer ...471 Playing Sounds ...472 Next Steps ...472 24 Handling Errors ...473 What Happens When an Error Occurs? ...473 A Misleading Debug Error in Userform Code ...475 Basic Error Handling with the On Error GoTo Syntax ...477 Generic Error Handlers ...478 Handling Errors by Choosing to Ignore Them ...479 Suppressing Excel Warnings ...481 Encountering Errors on Purpose ...481 Training Your Clients ...481 Errors While Developing Versus Errors Months Later ...482 Runtime Error 9: Subscript Out of Range ...482 Runtime Error 1004: Method Range of Object Global Failed ...483 The Ills of Protecting Code ...484 More Problems with Passwords ...485 Errors Caused by Different Versions ...486 Next Steps ...486 25 Customizing the Ribbon to Run Macros ...487 Where to Add Code: The customui Folder and File ...488 Creating a Tab and a Group ...489 Adding a Control to a Ribbon ...490 Accessing the File Structure ...496 Understanding the RELS File ...496 Renaming an Excel File and Opening a Workbook ...497 Using Images on Buttons ...497 Using Microsoft Office Icons on a Ribbon ...498 Adding Custom Icon Images to a Ribbon ...499 Troubleshooting Error Messages ...500 The Attribute "Attribute Name" on the Element "customui Ribbon" Is Not Defined in the DTD/Schema ...500 Illegal Qualified Name Character ...501 Element "customui Tag Name" Is Unexpected According to Content Model of Parent Element "customui Tag Name"...501 Found a Problem with Some Content ...502 Wrong Number of Arguments or Invalid Property Assignment ...503 Invalid File Format or File Extension ...503 Nothing Happens ...503 Other Ways to Run a Macro ...504 Using a Keyboard Shortcut to Run a Macro ...504 Attaching a Macro to a Command Button ...504 Attaching a Macro to a Shape ...505 Attaching a Macro to an ActiveX Control ...506 Running a Macro from a Hyperlink ...507 Next Steps ...508 26 Creating Add-ins ...509 Characteristics of Standard Add-ins ...509 Converting an Excel Workbook to an Add-in ...510 Using Save As to Convert a File to an Add-in ...511 Using the VB Editor to Convert a File to an Add-in ...512 Having a Client Install an Add-in ...512 Closing Add-ins ...514 Removing Add-ins ...514 Using a Hidden Workbook as an Alternative to an Add-in ...515 Next Steps ...516 27 An Introduction to Creating Office Add-ins ...517 Creating Your First Office Add-in-Hello World ...517 Adding Interactivity to an Office Add-in ...521 A Basic Introduction to HTML ...524 Using Tags...524 Adding Buttons ...524 Using CSS Files ...525 Using XML to Define an Office Add-in ...525 Using JavaScript to Add Interactivity to an Office Add-in ...526 The Structure of a Function ...526 Variables ...527 Strings ...528 Arrays ...528 JavaScript for Loops ...529 How to Do an if Statement in JavaScript ...530 How to Do a Select..Case Statement in JavaScript ...530 How to Do a For each..next Statement in JavaScript ...532 Mathematical, Logical, and Assignment Operators ...532 Math Functions in JavaScript ...534 Writing to the Content Pane or Task Pane ...535 JavaScript Changes for Working in an Office Add-in ...535 Napa Office 365 Development Tools ...536 Next Steps ...537 28 What's New in Excel 2016 and What's Changed ...539 If It Has Changed in the Front End, It Has Changed in VBA ...539 The Ribbon ...539 Single Document Interface (SDI)...540 Quick Analysis Tool ...541 Charts ...541 Pivot Tables ...541 Slicers ...541 SmartArt ...542 Learning the New Objects and Methods ...542 Compatibility Mode ...542 Using the Version Property ...543 Using the Excel8CompatibilityMode Property ...543 Next Steps ...544 TOC, 9780789755858, 10/19/2015

Szczegóły: Excel 2016 VBA and Macros: Includes Content Update Program - Tracy Syrstad, Bill Jelen

Tytuł: Excel 2016 VBA and Macros: Includes Content Update Program
Autor: Tracy Syrstad, Bill Jelen
Producent: Que Corporation
ISBN: 9780789755858
Rok produkcji: 2015
Ilość stron: 640
Oprawa: Miękka
Waga: 0.95 kg

Recenzje: Excel 2016 VBA and Macros: Includes Content Update Program - Tracy Syrstad, Bill Jelen