book

VBA Automation for Excel 2019 Cookbook

Name: VBA Automation for Excel 2019 Cookbook
Author: Mike Van Niekerk
ISBN: 9781789610031

by Mike Van Niekerk

September 2020

Intermediate to advanced

362 pages

6h 14m

English

Packt Publishing

Read now

Unlock full access

VBA Automation for Excel 2019 Cookbook
Why subscribe?ContributorsAbout the authorAbout the reviewerPackt is searching for authors like you
Preface
Who this book is forWhat this book coversTo get the most out of this bookDownload the example code filesCode in ActionDownload the color imagesConventions usedSectionsGetting readyHow to do it…How it works…There's more…See alsoGet in touchReviews
Chapter 1: Getting Started with VBA
Technical requirementsInvestigating VBA codeGetting readyHow to do it…How it works…There's more…Recording a macroGetting readyHow to do it…How it works…There's more…Testing the macroGetting readyHow to do it…How it works…There's more…Using the VBA EditorGetting readyHow to do it…How it works…There's more…Editing the code by changing cell referencesGetting readyHow to do it…How it works…There's more…Saving the file with an embedded macroGetting readyHow to do it…How it works…There's more…Using the Trust Center for macro securityGetting readyHow to do it…How it works…There's more…Creating a customized ribbonGetting readyHow to do it…
Chapter 2: Working with the VBA Editor
Technical requirementsIdentifying, activating, and closing components in the VBA EditorGetting readyHow to do it…How it works…Creating a module for storing a Sub procedureGetting readyHow to do it…How it works…Creating a basic procedure in the code window of the EditorGetting readyHow to do it…How it works…Creating VBA code in the code window in three different waysGetting readyHow to do it…How it works…There's more…Customizing the Editor tab to suit your preferencesGetting readyHow to do it…How it works…
Chapter 3: The VBA Object Model
Technical requirementsUnderstanding and identifying objects in VBAGetting readyHow to do it…How it works…Objects as a collection, and changing the attributes of the entire collectionGetting readyHow to do it…How it works…Modifying object propertiesGetting readyHow to do it…How it works…There's more…Associating methods with objectsGetting readyHow to do it…How it works…Using events to activate objectsGetting readyHow to do it…How it works…Using the Object Browser in the VBA EditorGetting readyHow to do it…How it works…
Chapter 4: Working with Procedures
Technical requirementsCreating Sub and Function proceduresGetting readyHow to do it…How it works…There's more…Executing Sub procedures directlyGetting readyHow to do it…How it works…Executing Sub procedures from the Macro dialog boxGetting readyHow to do it…How it works…Executing Sub procedures using buttonsGetting readyHow to do it…How it works…There's more…Executing Sub procedures using a shortcut keyGetting readyHow to do it…How it works…Executing Function procedures using a worksheet formulaGetting readyHow to do it…How it works…Executing Function procedures by calling from a Sub procedureGetting readyHow to do it…How it works…
Chapter 5: Next Level Recording
Technical requirementsSetting the macro recorder for absolute or relative referenceGetting readyHow to do it…How it works…Recording optionsGetting readyHow to do it…How it works…
Chapter 6: VBA Language Elements
Technical requirementsAdding commentsGetting readyHow to do it…How it works…Declaring variablesGetting readyHow to do it…How it works…Declaring constantsGetting readyHow to do it…How it works…There's more…Working with data types in variables and constantsGetting readyHow to do it…How it works…Scoping variablesGetting readyHow to do it…How it works…Declaring arraysGetting readyHow to do it…How it works…
Chapter 7: Working with Ranges
Technical requirementsReferring to rangesGetting readyHow to do it…How it works…Using the Range object propertiesGetting readyHow to do it…How it works…Adding the Range object's methodsGetting readyHow to do it…How it works…Making ranges workGetting readyHow to do it…How it works…
Chapter 8: Using Functions
Technical requirementsUsing built-in VBA functionsGetting readyHow to do it…How it works…Using worksheet functionsGetting readyHow to do it…How it works…Creating custom functionsGetting readyHow to do it…How it works…There's more…

Chapter 9: Implementing Program Flow
Technical requirementsExploring program flowChanging the flow using the GoTo statementGetting readyHow to do it…How it works…Using If and If-Then structures to make decisionsGetting readyHow to do it…How it works…There's more…Using the Select Case structureGetting readyHow to do it…How it works…Using loopsGetting readyHow to do it…How it works…
Chapter 10: Implementing Automation
Technical requirementsWriting event handler proceduresGetting readyHow to do it…How it works…There's more…Discovering eventsCreating workbook-related eventsGetting readyHow to do it…How it works…Creating worksheet-related eventsGetting readyHow to do it…How it works…Creating non-object eventsGetting readyHow to do it…How it works…
Chapter 11: Handling Errors
Technical requirementsHandling different types of errorsGetting readyHow to do it…How it works…Correcting a runtime errorGetting readyHow to do it…How it worksTesting to find errorsGetting readyHow to do it…How it works…Using the On Error statementGetting readyHow to do it…How it works…Resuming after an errorGetting readyHow to do it…How it works…
Chapter 12: Debugging
Technical requirementsExploring various bugsDebugging techniquesGetting readyHow to do it…How it works…Using the debugger in ExcelSetting breakpointsUsing the Watch window
Chapter 13: Creating and Modifying Dialog Boxes
Technical requirementsUsing the MsgBox functionGetting readyHow to do it…How it works…Using the InputBox functionGetting readyHow to do it…How it works…Using the Application.InputBox methodGetting readyHow to do it…How it works…
Chapter 14: Creating UserForms
Technical requirementsInserting the formGetting readyHow to do it…How it works…Changing propertiesGetting readyHow to do it…How it works…Adding controlsGetting readyHow to do it…How it works…There's more…Using FramesGetting readyHow to do it…There's more…
Chapter 15: UserForm Controls
Technical requirementsAssigning controlsGetting readyHow to do it…How it worksUnloading a formGetting readyHow to do it…How it works…Loading the formGetting readyHow to do it…How it works…Using the Combo Box controlGetting readyHow to do it…How it works…Using the SpinButton controlGetting readyHow to do it…How it works…There's more…Setting the tab orderGetting readyHow to do it…How it works
Chapter 16: Creating Custom Functions
Technical requirementsWriting functionsGetting readyHow to do it…How it works…Calling functionsGetting readyHow to do it…How it works…Adding function argumentsGetting readyHow to do it…How it works…There's more…Displaying function and argument descriptionsGetting readyHow to do it…
Chapter 17: Creating Word Documents with Excel VBA
Technical requirementsCreating a new instance of WordGetting readyHow to do it…How it works…Writing and formatting textGetting readyHow to do it…Copying data into WordGetting readyHow to do it…How it works…See alsoUsing templates and bookmarksGetting readyHow to do it…See also
Chapter 18: Working with PowerPoint in Excel VBA
Technical requirementsCreating a new instance of PowerPointGetting readyHow to do it…Creating presentations and slidesGetting readyHow to do it…Adding text to textboxesGetting readyHow to do it…Copying Excel content into PowerPointGetting readyHow to do it…There's more…
Other Books You May Enjoy
Leave a review - let other readers know what you think

Overview

In the "VBA Automation for Excel 2019 Cookbook," you'll discover how to harness VBA (Visual Basic for Applications) to tackle repetitive tasks and enhance your productivity across Excel and other MS Office applications. The book is designed with practical recipes that go from foundational steps to advanced techniques, ensuring you can confidently automate your workflow and boost efficiency.

What this Book will help me do

Master the basics of VBA to take control over Excel and Office automation steps.
Design and deploy custom dialog boxes to gather user inputs effectively.
Automate sheet-level ranges and data handling for streamlined processes.
Implement debugging strategies and error-handling methods for robust scripts.
Create cross-application automations that integrate Excel with Word and PowerPoint.

Author(s)

Mike Van Niekerk, the author of this book, is a seasoned expert in VBA and MS Office automation. With years of experience and a keen interest in helping professionals automate workflows, Mike presents concepts in a structured, accessible way tailored for learners of all levels. His practical approach and hands-on methodology make this book an invaluable resource.

Who is it for?

Are you a professional looking to improve your proficiency with Excel, automate tasks, and reduce repetitive workflows? This book is perfect for experienced Excel users, business professionals, and financial analysts eager to master VBA and Office Automation, even if you've never programmed before. Your quest to save time starts here.

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: 9781789610031

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills