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.

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the color images

Conventions used

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

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

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

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

Chapter 5: Next Level Recording

Chapter 5: Next Level Recording

Technical requirements

Setting the macro recorder for absolute or relative reference

Recording options

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

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

Chapter 8: Using Functions

Chapter 8: Using Functions

Technical requirements

Using built-in VBA functions

Using worksheet functions

Creating custom functions

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

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

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

Chapter 12: Debugging

Chapter 12: Debugging

Technical requirements

Exploring various bugs

Debugging techniques

Using the debugger in Excel

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

Chapter 14: Creating UserForms

Chapter 14: Creating UserForms

Technical requirements

Inserting the form

Changing properties

Adding controls

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

Chapter 16: Creating Custom Functions

Chapter 16: Creating Custom Functions

Technical requirements

Writing functions

Calling functions

Adding function arguments

Displaying function and argument descriptions

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

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

Other Books You May Enjoy

Other Books You May Enjoy

Leave a review - let other readers know what you think

Customer Reviews

5 star

0

4 star

0

3 star

0

2 star

0

1 star

0

To get the most out of this book

You will need the latest version of Excel installed on your PC to make the most of these recipes. The coding examples were tested on Windows 10.

All the recipes in a chapter are meant to be executed in the same order.

Table 1

No extra installations are necessary.

If you are using the digital version of this book, we advise you to type the code yourself or access the code via the GitHub repository (link available in the next section). Doing so will help you avoid any potential errors related to copy/pasting of code.

VBA is not difficult. However, unless you apply your newly acquired knowledge diligently, you might not gain all the advantages that are there for the taking. The secret lies in spaced repetition.

Download the example code files

You can download the example code files for this book from your account at www.packt.com. If you purchased this book elsewhere, you can visit www.packtpub.com/support and register to have the files emailed directly to you.

You can download the code files by following these steps:

Log in or register at www.packt.com.
Select the Support tab.
Click on Code Downloads.
Enter the name of the book in the Search box and follow the onscreen instructions.

Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of the following:

WinRAR/7-Zip for Windows
Zipeg/iZip/UnRarX for Mac
7-Zip/PeaZip for Linux

The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/VBA-Automation-for-Excel-2019-Cookbook. In case there's an update to the code, it will be updated on the existing GitHub repository.

We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!