A comprehensive guide to gaining a 360-degree overview of the VBA programming language and learning how to build your own programs for automating routine tasks
Key Features
Extend the capabilities of Excel and other Office applications with the help of Microsoft VBA
Take your Excel programming skills to the next level by creating custom applications with dialogue boxes and the range object
Automate repetitive and monotonous office work with VBA Excel programming
Book Description
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.
What you will learn
Understand the VBA programming language’s role in the context of the MS Office suite
Discover various aspects of VBA programming such as its terminology, syntax, procedures, functions, and forms
Investigate the elements, features, and characteristics of the VBA Editor to write and edit custom scripts
Automate Excel sheets with the help of ranges
Explore error handling and debugging techniques to catch bugs in your programs
Create and use custom dialog boxes to collect data from users
Customize and extend Office apps such as Excel, PowerPoint, and Word
Who this book is for
This book is for experienced Excel users, business analysts, finance professionals, and business users looking to boost their productivity by learning VBA programming to automate repetitive, tedious, or complex tasks. No prior programming experience is required to get started with this book.
Table of Contents
Getting Started with VBA
Working with the VBA Editor
The VBA Object Model
Working with Procedures
Next Level Recording
VBA Language Elements
Working with Ranges
Using Functions
Implementing Program Flow
Implementing Automation
Handling Errors
Debugging
Creating and Modifying Dialog Boxes
Creating UserForms
UserForm Controls
Creating Custom Functions
Creating Word Documents with Excel VBA
Working with PowerPoint in Excel VBA
Author(s): Van Niekerk, Mike
Series: VBA Automation for Excel 2019 Cookbook
Publisher: Published by Packt Publishing Ltd.
Year: 2020
Language: English
Commentary: A comprehensive guide of the VBA programming language and learning how to build your own programs for automating routine tasks
Pages: 362
Tags: A comprehensive guide of the VBA programming language and learning how to build your own programs for automating routine tasks
Cover
Title Page
Copyright and Credits
About Packt
Contributors
Table of Contents
Preface
Chapter 1: Getting Started with VBA
Technical requirements
Investigating VBA code
Getting ready
How to do it…
How it works…
There's more…
Recording a macro
Getting ready
How to do it…
How it works…
There's more…
Testing the macro
Getting ready
How to do it…
How it works…
There's more…
Using the VBA Editor
Getting ready
How to do it…
How it works…
There's more…
Editing the code by changing cell references
Getting ready
How to do it…
How it works…
There's more…
Saving the file with an embedded macro
Getting ready
How to do it…
How it works…
There's more…
Using the Trust Center for macro security
Getting ready
How to do it…
How it works…
There's more…
Creating a customized ribbon
Getting ready
How to do it…
Chapter 2: Working with the VBA Editor
Technical requirements
Identifying, activating, and closing components in the VBA Editor
Getting ready
How to do it…
How it works…
Creating a module for storing a Sub procedure
Getting ready
How to do it…
How it works…
Creating a basic procedure in the code window of the Editor
Getting ready
How to do it…
How it works…
Creating VBA code in the code window in three different ways
Getting ready
How to do it…
How it works…
There's more…
Customizing the Editor tab to suit your preferences
Getting ready
How to do it…
How it works…
Chapter 3: The VBA Object Model
Technical requirements
Understanding and identifying objects in VBA
Getting ready
How to do it…
How it works…
Objects as a collection, and changing the attributes of the entire collection
Getting ready
How to do it…
How it works…
Modifying object properties
Getting ready
How to do it…
How it works…
There's more…
Associating methods with objects
Getting ready
How to do it…
How it works…
Using events to activate objects
Getting ready
How to do it…
How it works…
Using the Object Browser in the VBA Editor
Getting ready
How to do it…
How it works…
Chapter 4: Working with Procedures
Technical requirements
Creating Sub and Function procedures
Getting ready
How to do it…
How it works…
There's more…
Executing Sub procedures directly
Getting ready
How to do it…
How it works…
Executing Sub procedures from the Macro dialog box
Getting ready
How to do it…
How it works…
Executing Sub procedures using buttons
Getting ready
How to do it…
How it works…
There's more…
Executing Sub procedures using a shortcut key
Getting ready
How to do it…
How it works…
Executing Function procedures using a worksheet formula
Getting ready
How to do it…
How it works…
Executing Function procedures by calling from a Sub procedure
Getting ready
How to do it…
How it works…
Chapter 5: Next Level Recording
Technical requirements
Setting the macro recorder for absolute or relative reference
Getting ready
How to do it…
How it works…
Recording options
Getting ready
How to do it…
How it works…
Chapter 6: VBA Language Elements
Technical requirements
Adding comments
Getting ready
How to do it…
How it works…
Declaring variables
Getting ready
How to do it…
How it works…
Declaring constants
Getting ready
How to do it…
How it works…
There's more…
Working with data types in variables and constants
Getting ready
How to do it…
How it works…
Scoping variables
Getting ready
How to do it…
How it works…
Declaring arrays
Getting ready
How to do it…
How it works…
Chapter 7: Working with Ranges
Technical requirements
Referring to ranges
Getting ready
How to do it…
How it works…
Using the Range object properties
Getting ready
How to do it…
How it works…
Adding the Range object's methods
Getting ready
How to do it…
How it works…
Making ranges work
Getting ready
How to do it…
How it works…
Chapter 8: Using Functions
Technical requirements
Using built-in VBA functions
Getting ready
How to do it…
How it works…
Using worksheet functions
Getting ready
How to do it…
How it works…
Creating custom functions
Getting ready
How to do it…
How it works…
There's more…
Chapter 9: Implementing Program Flow
Technical requirements
Exploring program flow
Changing the flow using the GoTo statement
Getting ready
How to do it…
How it works…
Using If and If-Then structures to make decisions
Getting ready
How to do it…
How it works…
There's more…
Using the Select Case structure
Getting ready
How to do it…
How it works…
Using loops
Getting ready
How to do it…
How it works…
Chapter 10: Implementing Automation
Technical requirements
Writing event handler procedures
Getting ready
How to do it…
How it works…
There's more…
Discovering events
Creating workbook-related events
Getting ready
How to do it…
How it works…
Creating worksheet-related events
Getting ready
How to do it…
How it works…
Creating non-object events
Getting ready
How to do it…
How it works…
Chapter 11: Handling Errors
Technical requirements
Handling different types of errors
Getting ready
How to do it…
How it works…
Correcting a runtime error
Getting ready
How to do it…
How it works
Testing to find errors
Getting ready
How to do it…
How it works…
Using the On Error statement
Getting ready
How to do it…
How it works…
Resuming after an error
Getting ready
How to do it…
How it works…
Chapter 12: Debugging
Technical requirements
Exploring various bugs
Debugging techniques
Getting ready
How to do it…
How it works…
Using the debugger in Excel
Setting breakpoints
Using the Watch window
Chapter 1:3 Creating and Modifying Dialog Boxes
Technical requirements
Using the MsgBox function
Getting ready
How to do it…
How it works…
Using the InputBox function
Getting ready
How to do it…
How it works…
Using the Application.InputBox method
Getting ready
How to do it…
How it works…
Chapter 14: Creating UserForms
Technical requirements
Inserting the form
Getting ready
How to do it…
How it works…
Changing properties
Getting ready
How to do it…
How it works…
Adding controls
Getting ready
How to do it…
How it works…
There's more…
Using Frames
Getting ready
How to do it…
There's more…
Chapter 15: UserForm Controls
Technical requirements
Assigning controls
Getting ready
How to do it…
How it works
Unloading a form
Getting ready
How to do it…
How it works…
Loading the form
Getting ready
How to do it…
How it works…
Using the Combo Box control
Getting ready
How to do it…
How it works…
Using the SpinButton control
Getting ready
How to do it…
How it works…
There's more…
Setting the tab order
Getting ready
How to do it…
How it works
Chapter 16: Creating Custom Functions
Technical requirements
Writing functions
Getting ready
How to do it…
How it works…
Calling functions
Getting ready
How to do it…
How it works…
Adding function arguments
Getting ready
How to do it…
How it works…
There's more…
Displaying function and argument descriptions
Getting ready
How to do it…
Chapter 17: Creating Word Documents with Excel VBA
Technical requirements
Creating a new instance of Word
Getting ready
How to do it…
How it works…
Writing and formatting text
Getting ready
How to do it…
Copying data into Word
Getting ready
How to do it…
How it works…
See also
Using templates and bookmarks
Getting ready
How to do it…
See also
Chapter 18: Working with PowerPoint in Excel VBA
Technical requirements
Creating a new instance of PowerPoint
Getting ready
How to do it…
Creating presentations and slides
Getting ready
How to do it…
Adding text to textboxes
Getting ready
How to do it…
Copying Excel content into PowerPoint
Getting ready
How to do it…
There's more…
Other Books You May Enjoy
Leave a review - let other readers know what you think
Index
