book

Microsoft Excel 2019 VBA and Macros, First Edition

Name: Microsoft Excel 2019 VBA and Macros, First Edition
ISBN: 9781509307203

by Bill Jelen, Tracy Syrstad

December 2018

Intermediate to advanced

625 pages

18h 58m

English

Microsoft Press

Read now

Unlock full access

Cover Page
Title Page
Copyright Page
Dedication Page
Contents at a Glance
Contents
Acknowledgments
About the Authors
Introduction
Chapter 1 Unleashing the power of Excel with VBA
Barriers to entryKnowing your tools: The Developer tabUnderstanding which file types allow macrosMacro securityOverview of recording, storing, and running a macroRunning a macroUnderstanding the VB EditorUnderstanding shortcomings of the macro recorderNext steps

Chapter 2 This sounds like BASIC, so why doesn’t it look familiar?
Understanding the parts of VBA “speech”VBA is not really hardExamining recorded macro code: Using the VB Editor and HelpUsing debugging tools to figure out recorded codeObject Browser: The ultimate referenceSeven tips for cleaning up recorded codeNext steps
Chapter 3 Referring to ranges
The Range objectReferencing 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
Chapter 4 Looping and flow control
For...Next loopsDo loopsThe VBA loop: For EachFlow control: Using If...Then...Else and Select CaseNext steps
Chapter 5 R1C1-style formulas
Toggling to R1C1-style referencesWitnessing the miracle of Excel formulasUnderstanding the R1C1 reference styleUsing R1C1 formulas with array formulasNext steps
Chapter 6 Creating and manipulating names in VBA
Global versus local namesAdding namesDeleting namesAdding commentsTypes of namesHiding namesChecking for the existence of a nameNext steps
Chapter 7 Event programming
Levels of eventsUsing eventsWorkbook eventsWorksheet eventsChart eventsApplication-level eventsNext steps
Chapter 8 Arrays
Declaring an arrayDeclaring a multidimensional arrayFilling an arrayRetrieving data from an arrayUsing arrays to speed up codeUsing dynamic arraysPassing an arrayNext steps
Chapter 9 Creating classes and collections
Inserting a class moduleTrapping application and embedded chart eventsCreating a custom objectUsing a custom objectUsing collectionsUsing dictionariesUsing user-defined types to create custom propertiesNext steps
Chapter 10 Userforms: An introduction
Input boxesMessage boxesCreating a userformCalling and hiding a userformProgramming userformsProgramming controlsUsing basic form controlsVerifying field entryIllegal window closingGetting a file nameNext steps
Chapter 11 Data mining with Advanced Filter
Replacing a loop with AutoFilterAdvanced Filter—easier in VBA than in ExcelUsing Advanced Filter to extract a unique list of valuesUsing Advanced Filter with criteria rangesUsing filter in place in Advanced FilterThe real workhorse: xlFilterCopy with all records rather than unique records onlyNext steps
Chapter 12 Using VBA to create pivot tables
Understanding how pivot tables evolved over various Excel versionsWhile building a pivot table in Excel VBAUsing advanced pivot table featuresFiltering a data setUsing the Data Model in Excel 2019Using other pivot table featuresNext steps
Chapter 13 Excel power
File operationsCombining and separating workbooksWorking with cell commentsTracking user changesTechniques for VBA prosNext steps
Chapter 14 Sample user-defined functions
Creating user-defined functionsSharing UDFsUseful custom Excel functionsNext steps
Chapter 15 Creating charts
Using .AddChart2 to create a chartUnderstanding chart stylesFormatting a chartCreating a combo chartCreating map chartsCreating waterfall chartsExporting a chart as a graphicConsidering backward compatibilityNext steps
Chapter 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 rangeUsing visualization tricksUsing other conditional formatting methodsNext steps
Chapter 17 Dashboarding with sparklines in Excel 2019
Creating sparklinesScaling sparklinesFormatting sparklinesCreating a dashboardNext steps
Chapter 18 Reading from and writing to the web
Getting data from the webUsing Application.OnTime to periodically analyze dataPublishing data to a web pageNext steps
Chapter 19 Text file processing
Importing from text filesWriting Text FilesNext steps
Chapter 20 Automating Word
Using early binding to reference a Word objectUsing late binding to reference a Word objectUsing the New keyword to reference the Word applicationUsing the CreateObject function to create a new instance of an objectUsing the GetObject function to reference an existing instance of WordUsing constant valuesUnderstanding Word’s objectsControlling form fields in WordNext steps
Chapter 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 ADOSQL Server examplesNext steps
Chapter 22 Advanced userform techniques
Using the UserForm toolbar in the design of controls on userformsMore userform controlsControls and collectionsModeless userformsUsing hyperlinks in userformsAdding controls at runtimeAdding help to a userformCreating transparent formsNext steps
Chapter 23 The Windows Application Programming Interface (API)
Understanding an API declarationUsing an API declarationMaking 32-bit- and 64-bit-compatible API declarationsAPI function examplesNext steps
Chapter 24 Handling errors
What happens when an error occurs?Basic error handling with the On Error GoTo syntaxGeneric error handlersTraining your clientsErrors that won’t show up in debug modeErrors while developing versus errors months laterThe ills of protecting codeMore problems with passwordsErrors caused by different versionsNext steps
Chapter 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 buttonsTroubleshooting error messagesOther ways to run a macroNext steps
Chapter 26 Creating add-ins
Characteristics of standard add-insConverting an Excel workbook to an add-inHaving a client install an add-inStandard add-ins are not secureClosing add-insRemoving add-insUsing a hidden workbook as an alternative to an add-inNext steps
Chapter 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 XML to define an Office add-inUsing JavaScript to add interactivity to an Office add-inNext steps
Chapter 28 What’s new in Excel 2019 and what’s changed
Office 365 subscription versus Excel 2019 perpetualIf it has changed in the front end, it has changed in VBALearning the new objects and methodsCompatibility modeNext steps
Index
Code Snippets

Content preview from Microsoft Excel 2019 VBA and Macros, First Edition

CHAPTER 4Looping and flow control

In this chapter, you will:

Work with For...Next loops
Get to know Do loops
Be introduced to the VBA loop: For Each
Use If...Then...Else and Select Case for flow control

Loops make your life easier. You might have 20 lines of macro code that do something cool one time. Add a line of code above and below, and suddenly your macro fixes a million rows instead of one row. Loops are a fundamental component of any programming language. If you’ve taken any programming classes—even BASIC—you’ve likely encountered a For...Next loop. Fortunately, VBA supports all the usual loops, plus a special loop that is excellent to use with VBA.

This chapter covers the basic loop constructs:

For...Next
Do...While
Do...Until

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

Microsoft Excel 2019 Formulas and Functions, First Edition

Publisher Resources

ISBN: 9781509307203

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft Excel 2019 VBA and Macros, First Edition

by Bill Jelen, Tracy Syrstad

CHAPTER 4Looping and flow control

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.