book

Excel 2016 VBA and Macros

Name: Excel 2016 VBA and Macros
ISBN: 9780134386027

by Bill Jelen, Tracy Syrstad

November 2015

Intermediate to advanced

656 pages

18h 57m

English

Que

Read now

Unlock full access

About This E-Book
Title Page
Copyright Page
Contents at a Glance
Contents
About the Authors
Dedications
Acknowledgments
We Want to Hear from You!
Reader Services

Introduction
What Is in This Book?Reducing the Learning CurveExcel VBA PowerTechie Stuff Needed to Produce ApplicationsDoes This Book Teach Excel?The Future of VBA and Windows Versions of ExcelVersions of ExcelDifferences for Mac UsersSpecial Elements and Typographical ConventionsCode FilesNext Steps
1. Unleashing the Power of Excel with VBA
The Power of ExcelBarriers to EntryThe Macro Recorder Doesn’t Work!No One on the Excel Team Is Focused on the Macro RecorderVisual Basic Is Not Like BASICGood News: Climbing the Learning Curve Is EasyGreat News: Excel with VBA Is Worth the EffortKnowing Your Tools: The Developer TabUnderstanding Which File Types Allow MacrosMacro SecurityAdding a Trusted LocationUsing Macro Settings to Enable Macros in Workbooks Outside Trusted LocationsUsing Disable All Macros with NotificationOverview of Recording, Storing, and Running a MacroFilling Out the Record Macro DialogRunning a MacroCreating a Macro Button on the RibbonCreating a Macro Button on the Quick Access ToolbarAssigning a Macro to a Form Control, Text Box, or ShapeUnderstanding the VB EditorVB Editor SettingsThe Project ExplorerThe Properties WindowUnderstanding Shortcomings of the Macro RecorderRecording the MacroExamining Code in the Programming WindowRunning the Macro on Another Day Produces Undesired ResultsPossible Solution: Use Relative References When RecordingNever Use AutoSum or Quick Analysis While Recording a MacroFour Tips for Using the Macro RecorderNext Steps
2. This Sounds Like BASIC, So Why Doesn’t It Look Familiar?
I Can’t Understand This CodeUnderstanding the Parts of VBA “Speech”VBA Is Not Really HardVBA Help Files: Using F1 to Find AnythingUsing Help TopicsExamining Recorded Macro Code: Using the VB Editor and HelpOptional ParametersDefined ConstantsProperties Can Return ObjectsUsing Debugging Tools to Figure Out Recorded CodeStepping Through CodeMore Debugging Options: BreakpointsBacking Up or Moving Forward in CodeNot Stepping Through Each Line of CodeQuerying Anything While Stepping Through CodeUsing a Watch to Set a BreakpointUsing a Watch on an ObjectObject Browser: The Ultimate ReferenceSeven Tips for Cleaning Up Recorded CodeTip 1: Don’t Select AnythingTip 2: Use Cells(2,5) Because It’s More Convenient Than Range("E2")Tip 3: Use More Reliable Ways to Find the Last RowTip 4: Use Variables to Avoid Hard-Coding Rows and FormulasTip 5: Use R1C1 Formulas That Make Your Life EasierTip 6: Copy and Paste in a Single StatementTip 7: Use With...End With to Perform Multiple ActionsNext Steps
3. Referring to Ranges
The Range ObjectSyntax for Specifying a RangeNamed RangesShortcut for Referencing RangesReferencing Ranges in Other SheetsReferencing a Range Relative to Another RangeUsing the Cells Property to Select a RangeUsing the Offset Property to Refer to a RangeUsing the Resize Property to Change the Size of a RangeUsing the Columns and Rows Properties to Specify a RangeUsing the Union Method to Join Multiple RangesUsing the Intersect Method to Create a New Range from Overlapping RangesUsing the IsEmpty Function to Check Whether a Cell Is EmptyUsing the CurrentRegion Property to Select a Data RangeUsing the Areas Collection to Return a Noncontiguous RangeReferencing TablesNext Steps
4. Looping and Flow Control
For...Next LoopsUsing Variables in the For StatementVariations on the For...Next LoopExiting a Loop Early After a Condition Is MetNesting One Loop Inside Another LoopDo LoopsUsing the While or Until Clause in Do LoopsThe VBA Loop: For EachObject VariablesFlow Control: Using If...Then...Else and Select CaseBasic Flow Control: If...Then...ElseUsing Select Case...End Select for Multiple ConditionsNext Steps
5. R1C1-Style Formulas
Referring to Cells: A1 Versus R1C1 ReferencesToggling to R1C1-Style ReferencesWitnessing the Miracle of Excel FormulasEntering a Formula Once and Copying 1,000 TimesThe Secret: It’s Not That AmazingUnderstanding the R1C1 Reference StyleUsing R1C1 with Relative ReferencesUsing R1C1 with Absolute ReferencesUsing R1C1 with Mixed ReferencesReferring to Entire Columns or Rows with R1C1 StyleReplacing Many A1 Formulas with a Single R1C1 FormulaRemembering Column Numbers Associated with Column LettersUsing R1C1 Formulas with Array FormulasNext Steps
6. Creating and Manipulating Names in VBA
Global Versus Local NamesAdding NamesDeleting NamesAdding CommentsTypes of NamesFormulasStringsNumbersTablesUsing Arrays in NamesReserved NamesHiding NamesChecking for the Existence of a NameNext Steps
7. Event Programming
Levels of EventsUsing EventsEvent ParametersEnabling EventsWorkbook EventsWorkbook-Level Sheet and Chart EventsWorksheet EventsChart EventsEmbedded ChartsEmbedded Chart and Chart Sheet EventsApplication-Level EventsNext Steps
8. Arrays
Declaring an ArrayDeclaring a Multidimensional ArrayFilling an ArrayRetrieving Data from an ArrayUsing Arrays to Speed Up CodeUsing Dynamic ArraysPassing an ArrayNext Steps
9. Creating Classes and Collections
Inserting a Class ModuleTrapping Application and Embedded Chart EventsApplication EventsEmbedded Chart EventsCreating a Custom ObjectUsing a Custom ObjectUsing CollectionsCreating a CollectionCreating a Collection in a Standard ModuleCreating a Collection in a Class ModuleUsing DictionariesUsing User-Defined Types to Create Custom PropertiesNext Steps
Input Boxes
Message BoxesCreating a UserformCalling and Hiding a UserformProgramming UserformsUserform EventsProgramming ControlsUsing Basic Form ControlsUsing Labels, Text Boxes, and Command ButtonsDeciding Whether to Use List Boxes or Combo Boxes in FormsAdding Option Buttons to a UserformAdding Graphics to a UserformUsing a Spin Button on a UserformUsing the MultiPage Control to Combine FormsVerifying Field EntryIllegal Window ClosingGetting a FilenameNext Steps
11. Data Mining with Advanced Filter
Replacing a Loop with AutoFilterUsing AutoFilter TechniquesSelecting Visible Cells OnlyAdvanced Filter—Easier in VBA Than in ExcelUsing the Excel Interface to Build an Advanced FilterUsing Advanced Filter to Extract a Unique List of ValuesExtracting a Unique List of Values with the User InterfaceExtracting a Unique List of Values with VBA CodeGetting Unique Combinations of Two or More FieldsUsing Advanced Filter with Criteria RangesJoining Multiple Criteria with a Logical ORJoining Two Criteria with a Logical ANDOther Slightly Complex Criteria RangesThe Most Complex Criteria: Replacing the List of Values with a Condition Created as the Result of a FormulaUsing Filter in Place in Advanced FilterCatching No Records When Using a Filter in PlaceShowing All Records After Running a Filter in PlaceThe Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records OnlyCopying All ColumnsCopying a Subset of Columns and ReorderingExcel in Practice: Turning Off a Few Drop-downs in the AutoFilterNext Steps
12. Using VBA to Create Pivot Tables
Understanding How Pivot Tables Evolved Over Various Excel VersionsWhile Building a Pivot Table in Excel VBADefining the Pivot CacheCreating and Configuring the Pivot TableAdding Fields to the Data AreaLearning Why You Cannot Move or Change Part of a Pivot ReportDetermining the Size of a Finished Pivot Table to Convert the Pivot Table to ValuesUsing Advanced Pivot Table FeaturesUsing Multiple Value FieldsGrouping Daily Dates to Months, Quarters, or YearsChanging the Calculation to Show PercentagesEliminating Blank Cells in the Values AreaControlling the Sort Order with AutoSortReplicating the Report for Every ProductFiltering a Data SetManually Filtering Two or More Items in a Pivot FieldUsing the Conceptual FiltersUsing the Search FilterSetting Up Slicers to Filter a Pivot TableSetting Up a Timeline to Filter an Excel 2016 Pivot TableUsing the Data Model in Excel 2016Adding Both Tables to the Data ModelCreating a Relationship Between the Two TablesDefining the PivotCache and Building the Pivot TableAdding Model Fields to the Pivot TableAdding Numeric Fields to the Values AreaPutting It All TogetherUsing Other Pivot Table FeaturesCalculated Data FieldsCalculated ItemsUsing ShowDetail to Filter a Record SetChanging the Layout from the Design TabSettings for the Report LayoutSuppressing Subtotals for Multiple Row FieldsNext Steps
13. Excel Power
File OperationsListing Files in a DirectoryImporting and Deleting a CSV FileReading a Text File into Memory and ParsingCombining and Separating WorkbooksSeparating Worksheets into WorkbooksCombining WorkbooksFiltering and Copying Data to Separate WorksheetsCopying Data to Separate Worksheets Without Using FilterExporting Data to an XML FileWorking with Cell CommentsResizing CommentsPlacing a Chart in a CommentSelecting CellsUsing Conditional Formatting to Highlight the Selected CellHighlighting the Selected Cell Without Using Conditional FormattingSelecting/Deselecting Noncontiguous CellsCreating a Hidden Log FileTechniques for VBA ProsCreating an Excel State Class ModuleDrilling-Down a Pivot TableFiltering an OLAP Pivot Table by a List of ItemsCreating a Custom Sort OrderCreating a Cell Progress IndicatorUsing a Protected Password BoxChanging CaseSelecting with SpecialCellsResetting a Table’s FormatCool ApplicationsGetting Historical Stock/Fund QuotesUsing VBA Extensibility to Add Code to New WorkbooksNext Steps
14. Sample User-Defined Functions
Creating User-Defined FunctionsSharing UDFsUseful Custom Excel FunctionsSetting the Current Workbook’s Name in a CellSetting the Current Workbook’s Name and File Path in a CellChecking Whether a Workbook Is OpenChecking Whether a Sheet in an Open Workbook ExistsCounting the Number of Workbooks in a DirectoryRetrieving the User IDRetrieving Date and Time of Last SaveRetrieving Permanent Date and TimeValidating an Email AddressSumming Cells Based on Interior ColorCounting Unique ValuesRemoving Duplicates from a RangeFinding the First Nonzero-Length Cell in a RangeSubstituting Multiple CharactersRetrieving Numbers from Mixed TextConverting Week Number into DateExtracting a Single Element from a Delimited StringSorting and ConcatenatingSorting Numeric and Alpha CharactersSearching for a String Within TextReversing the Contents of a CellReturning the Addresses of Duplicate Max ValuesReturning a Hyperlink AddressReturning the Column Letter of a Cell AddressUsing Static RandomUsing Select Case on a WorksheetNext Steps
15. Creating Charts
Contrasting the Good and Bad VBA to Create ChartsPlanning for More Charts to BreakUsing .AddChart2 to Create a ChartUnderstanding Chart StylesFormatting a ChartReferring to a Specific ChartSpecifying a Chart TitleApplying a Chart ColorFiltering a ChartUsing SetElement to Emulate Changes from the Plus IconUsing the Format Method to Micromanage Formatting OptionsChanging an Object’s FillFormatting Line SettingsCreating a Combo ChartExporting a Chart as a GraphicConsidering Backward CompatibilityNext Steps
16. Data Visualizations and Conditional Formatting
VBA Methods and Properties for Data VisualizationsAdding Data Bars to a RangeAdding Color Scales to a RangeAdding Icon Sets to a RangeSpecifying an Icon SetSpecifying Ranges for Each IconUsing Visualization TricksCreating an Icon Set for a Subset of a RangeUsing Two Colors of Data Bars in a RangeUsing Other Conditional Formatting MethodsFormatting Cells That Are Above or Below AverageFormatting Cells in the Top 10 or Bottom 5Formatting Unique or Duplicate CellsFormatting Cells Based on Their ValueFormatting Cells That Contain TextFormatting Cells That Contain DatesFormatting Cells That Contain Blanks or ErrorsUsing a Formula to Determine Which Cells to FormatUsing the New NumberFormat PropertyNext Steps
17. Dashboarding with Sparklines in Excel 2016
Creating SparklinesScaling SparklinesFormatting SparklinesUsing Theme ColorsUsing RGB ColorsFormatting Sparkline ElementsFormatting Win/Loss ChartsCreating a DashboardObservations About SparklinesCreating Hundreds of Individual Sparklines in a DashboardNext Steps
18. Reading from and Writing to the Web
Getting Data from the WebBuilding Multiple Queries with VBAFinding Results from Retrieved DataPutting It All TogetherExamples of Scraping Websites Using Web QueriesUsing Application.OnTime to Periodically Analyze DataUsing Ready Mode for Scheduled ProceduresSpecifying a Window of Time for an UpdateCanceling a Previously Scheduled MacroClosing Excel Cancels All Pending Scheduled MacrosScheduling a Macro to Run x Minutes in the FutureScheduling a Verbal ReminderScheduling a Macro to Run Every Two MinutesPublishing Data to a Web PageUsing VBA to Create Custom Web PagesUsing Excel as a Content Management SystemBonus: FTP from ExcelNext Steps
19. Text File Processing
Importing from Text FilesImporting Text Files with Fewer Than 1,048,576 RowsDealing with Text Files with More Than 1,048,576 RowsWriting Text FilesNext Steps
20. Automating Word
Using Early Binding to Reference a Word ObjectUsing Late Binding to Reference a Word ObjectUsing the New Keyword to Reference a Word ApplicationUsing the CreateObject Function to Create a New Instance of an ObjectUsing the GetObject Function to Reference an Existing Instance of WordUsing Constant ValuesUsing the Watches Window to Retrieve the Real Value of a ConstantUsing the Object Browser to Retrieve the Real Value of a ConstantUnderstanding Word’s ObjectsThe Document ObjectThe Selection ObjectThe Range ObjectBookmarksControlling Form Fields in WordNext Steps
21. Using Access as a Back End to Enhance Multiuser Access to Data
ADO Versus DAOsThe Tools of ADOAdding a Record to a DatabaseRetrieving Records from a DatabaseUpdating an Existing RecordDeleting Records via ADOSummarizing Records via ADOOther Utilities via ADOChecking for the Existence of TablesChecking for the Existence of a FieldAdding a Table On the FlyAdding a Field On the FlySQL Server ExamplesNext Steps
22. Advanced Userform Techniques
Using the UserForm Toolbar in the Design of Controls on UserformsMore Userform ControlsCheckbox ControlsControls and CollectionsModeless UserformsUsing Hyperlinks in UserformsAdding Controls at RuntimeResizing the Userform On the FlyAdding a Control On the FlySizing On the FlyAdding Other ControlsAdding an Image On the FlyPutting It All TogetherAdding Help to a UserformShowing Accelerator KeysAdding Control Tip TextCreating the Tab OrderColoring the Active ControlCreating Transparent FormsNext Steps
23. The Windows Application Programming Interface (API)
Understanding an API DeclarationUsing an API DeclarationMaking 32-Bit- and 64-Bit-Compatible API DeclarationsAPI Function ExamplesRetrieving the Computer NameChecking Whether an Excel File Is Open on a NetworkRetrieving Display-Resolution InformationCustomizing the About DialogDisabling the X for Closing a UserformCreating a Running TimerPlaying SoundsNext Steps
24. Handling Errors
What Happens When an Error Occurs?A Misleading Debug Error in Userform CodeBasic Error Handling with the On Error GoTo SyntaxGeneric Error HandlersHandling Errors by Choosing to Ignore ThemSuppressing Excel WarningsEncountering Errors on PurposeTraining Your ClientsErrors While Developing Versus Errors Months LaterRuntime Error 9: Subscript Out of RangeRuntime Error 1004: Method Range of Object Global FailedThe Ills of Protecting CodeMore Problems with PasswordsErrors Caused by Different VersionsNext Steps
25. Customizing the Ribbon to Run Macros
Where to Add Code: The customui Folder and FileCreating a Tab and a GroupAdding a Control to a RibbonAccessing the File StructureUnderstanding the RELS FileRenaming an Excel File and Opening a WorkbookUsing Images on ButtonsUsing Microsoft Office Icons on a RibbonAdding Custom Icon Images to a RibbonTroubleshooting Error MessagesThe Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/SchemaIllegal Qualified Name CharacterElement “customui Tag Name” Is Unexpected According to Content Model of Parent Element “customui Tag Name”Found a Problem with Some ContentWrong Number of Arguments or Invalid Property AssignmentInvalid File Format or File ExtensionNothing HappensOther Ways to Run a MacroUsing a Keyboard Shortcut to Run a MacroAttaching a Macro to a Command ButtonAttaching a Macro to a ShapeAttaching a Macro to an ActiveX ControlRunning a Macro from a HyperlinkNext Steps
26. Creating Add-ins
Characteristics of Standard Add-insConverting an Excel Workbook to an Add-inUsing Save As to Convert a File to an Add-inUsing the VB Editor to Convert a File to an Add-inHaving a Client Install an Add-inClosing Add-insRemoving Add-insUsing a Hidden Workbook as an Alternative to an Add-inNext Steps
27. An Introduction to Creating Office Add-ins
Creating Your First Office Add-in—Hello WorldAdding Interactivity to an Office Add-inA Basic Introduction to HTMLUsing TagsAdding ButtonsUsing CSS FilesUsing XML to Define an Office Add-inUsing JavaScript to Add Interactivity to an Office Add-inThe Structure of a FunctionVariablesStringsArraysJavaScript for LoopsHow to Do an if Statement in JavaScriptHow to Do a Select..Case Statement in JavaScriptHow to Do a For each..next Statement in JavaScriptMathematical, Logical, and Assignment OperatorsMath Functions in JavaScriptWriting to the Content Pane or Task PaneJavaScript Changes for Working in an Office Add-inNapa Office 365 Development ToolsNext Steps
28. What’s New in Excel 2016 and What’s Changed
If It Has Changed in the Front End, It Has Changed in VBAThe RibbonSingle Document Interface (SDI)Quick Analysis ToolChartsPivot TablesSlicersSmartArtLearning the New Objects and MethodsCompatibility ModeUsing the Version PropertyUsing the Excel8CompatibilityMode PropertyNext Steps
Index
Code Snippets

Content preview from Excel 2016 VBA and Macros

11. Data Mining with Advanced Filter

In This Chapter

Replacing a Loop with AutoFilter

Advanced Filter—Easier in VBA Than in Excel

Using Advanced Filter to Extract a Unique List of Values

Using Advanced Filter with Criteria Ranges

Using Filter in Place in Advanced Filter

The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only

Next Steps

Read this chapter.

Although very few people use Advanced Filter in Excel, it is a workhorse in Excel VBA. I estimate that I end up using one of these filtering techniques as the core of a macro in 80% of the macros I develop for clients. Given that Advanced Filter is used in fewer than 1% of Excel sessions, this is a dramatic statistic.

So even if you hardly ever use Advanced Filter ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780134386027Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Excel 2016 VBA and Macros

by Bill Jelen, Tracy Syrstad

11. Data Mining with Advanced Filter

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.