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

Using the Combo Box control

We still need to create a list of values for the Combo Box. Users must select a department from a pre-defined list, rather than being unsure about the possible options. To populate the Combo Box, we first need to create that lookup list.

In this recipe, we will be using the Combo Box control.

Getting ready

Make sure that Controls.xlsm is still open. New Record Entry Form should be visible, and both the project window and the properties window should be visible on the left of the VBA Editor.

How to do it…

We need to do the following:

  1. Before configuring the Combo Box, we need to create a lookup list. Switch to Excel and insert a new sheet. In Sheet2, create the following list:

    Figure 15.7 – Lookup list for the Combo Box

  2. Once done, select the list and save it as a range, (click in the Name Box, then type the word Department, and press Enter), calling it Department.
  3. Switch back to the UserForm and select the Department...
Previous Section
End of Section 6
Next Section