Book Image

VBA Automation for Excel 2019 Cookbook

By : Mike Van Niekerk
Book Image

VBA Automation for Excel 2019 Cookbook

By: Mike Van Niekerk

Overview of this book

Visual Basic for Applications (VBA) is a programming language developed by Microsoft to automate tasks in MS Office applications. This book will help you to focus on the essential aspects of your role by automating mundane tasks in Excel and other Office applications. With comprehensive coverage of VBA delivered in the form of practice problems and bite-sized recipes, this book will help you to hit the ground running. Unlike most books that assume prior programming experience, this book starts with the fundamentals and gradually progresses to solving bigger problems. You’ll start by becoming familiar with VBA so that you can start recording macros right away. With this foundation in place, you’ll advance to using the full capabilities of the language as you apply loops, functions, and custom dialog boxes to design your own automation programs. You'll also get to grips with embedded macros and other advanced tools to enhance productivity and explore topics relating to app performance and security. Throughout this VBA book, you’ll cover multiple practice projects in Excel, Word, and PowerPoint while exploring tips and best practices to hone your skills. By the end of this book, you’ll have developed the skills you need to use VBA to create your own programs that control MS Office applications.

Related Content you might be interested in

Current Title:

Small book image
VBA Automation for Excel 2019 Cookbook
Mike Van Niekerk
Sep, 2020 | 362 pages
Small book image
Exploring Microsoft Excel’s Hidden Treasures
David Ringstrom
Sep, 2022 | 444 pages
Small book image
Learn Microsoft Office 2019
Linda Foulkes
May, 2020 | 794 pages
Small book image
Visual Basic Quickstart Guide
Aspen Olmsted
Oct, 2023 | 238 pages
Table of Contents (20 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Code in Action
Download the color images
Conventions used
Sections
Get in touch
Reviews
1
Chapter 1: Getting Started with VBA
Chapter 1: Getting Started with VBA
Technical requirements
Investigating VBA code
Recording a macro
Testing the macro
Using the VBA Editor
Editing the code by changing cell references
Saving the file with an embedded macro
Using the Trust Center for macro security
Creating a customized ribbon
Free Chapter
2
Chapter 2: Working with the VBA Editor
Chapter 2: Working with the VBA Editor
Technical requirements
Identifying, activating, and closing components in the VBA Editor
Creating a module for storing a Sub procedure
Creating a basic procedure in the code window of the Editor
Creating VBA code in the code window in three different ways
Customizing the Editor tab to suit your preferences
3
Chapter 3: The VBA Object Model
Chapter 3: The VBA Object Model
Technical requirements
Understanding and identifying objects in VBA
Objects as a collection, and changing the attributes of the entire collection
Modifying object properties
Associating methods with objects
Using events to activate objects
Using the Object Browser in the VBA Editor
4
Chapter 4: Working with Procedures
Chapter 4: Working with Procedures
Technical requirements
Creating Sub and Function procedures
Executing Sub procedures directly
Executing Sub procedures from the Macro dialog box
Executing Sub procedures using buttons
Executing Sub procedures using a shortcut key
Executing Function procedures using a worksheet formula
Executing Function procedures by calling from a Sub procedure
5
Chapter 5: Next Level Recording
Chapter 5: Next Level Recording
Technical requirements
Setting the macro recorder for absolute or relative reference
Recording options
6
Chapter 6: VBA Language Elements
Chapter 6: VBA Language Elements
Technical requirements
Adding comments
Declaring variables
Declaring constants
Working with data types in variables and constants
Scoping variables
Declaring arrays
7
Chapter 7: Working with Ranges
Chapter 7: Working with Ranges
Technical requirements
Referring to ranges
Using the Range object properties
Adding the Range object's methods
Making ranges work
8
Chapter 8: Using Functions
Chapter 8: Using Functions
Technical requirements
Using built-in VBA functions
Using worksheet functions
Creating custom functions
9
Chapter 9: Implementing Program Flow
Chapter 9: Implementing Program Flow
Technical requirements
Exploring program flow
Changing the flow using the GoTo statement
Using If and If-Then structures to make decisions
Using the Select Case structure
Using loops
10
Chapter 10: Implementing Automation
Chapter 10: Implementing Automation
Technical requirements
Writing event handler procedures
Discovering events
Creating workbook-related events
Creating worksheet-related events
Creating non-object events
11
Chapter 11: Handling Errors
Chapter 11: Handling Errors
Technical requirements
Handling different types of errors
Correcting a runtime error
Testing to find errors
Using the On Error statement
Resuming after an error
12
Chapter 12: Debugging
Chapter 12: Debugging
Technical requirements
Exploring various bugs
Debugging techniques
Using the debugger in Excel
13
Chapter 13: Creating and Modifying Dialog Boxes
Chapter 13: Creating and Modifying Dialog Boxes
Technical requirements
Using the MsgBox function
Using the InputBox function
Using the Application.InputBox method
14
Chapter 14: Creating UserForms
Chapter 14: Creating UserForms
Technical requirements
Inserting the form
Changing properties
Adding controls
Using Frames
15
Chapter 15: UserForm Controls
Chapter 15: UserForm Controls
Technical requirements
Assigning controls
Unloading a form
Loading the form
Using the Combo Box control
Using the SpinButton control
Setting the tab order
16
Chapter 16: Creating Custom Functions
Chapter 16: Creating Custom Functions
Technical requirements
Writing functions
Calling functions
Adding function arguments
Displaying function and argument descriptions
17
Chapter 17: Creating Word Documents with Excel VBA
Chapter 17: Creating Word Documents with Excel VBA
Technical requirements
Creating a new instance of Word
Writing and formatting text
Copying data into Word
Using templates and bookmarks
18
Chapter 18: Working with PowerPoint in Excel VBA
Chapter 18: Working with PowerPoint in Excel VBA
Technical requirements
Creating a new instance of PowerPoint
Creating presentations and slides
Adding text to textboxes
Copying Excel content into PowerPoint
19
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

Creating workbook-related events

The Open event is one of many workbook-related events. In this recipe, we will be creating more workbook-related events.

Getting ready

Make sure that Events.xlsm is still open. Sheet1 should be active. Press Alt + F11 to switch to the VBA Editor, and then double-click the ThisWorkbook object to activate the code window.

How to do it…

Let's create three workbook-related events:

  1. If we can welcome users when they open a file, we can greet them when they leave. In the top-right corner of the code window, select the BeforeClose event from the drop-down list:

    Figure 10.9 – List of events

    A new procedure will be created in the code window:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub
  2. Insert the next line of code in the procedure:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgBox "Goodbye - have a nice day"
End Sub
  3. After saving the file, close it. A message...
Previous Section
End of Section 5
Next Section