Excel 2019 Macros and VBA: An Introduction to Excel Programming

by Nathan George

Overview

Enhance Your Productivity with Excel Automation

Maybe you regularly format a set of Excel data you'll like to automate, or you regularly perform repetitive data-crunching tasks that you're trying to automate. Perhaps you got a new job (congratulations). If you are new to Excel programming and you want a crash course to get you up and running with creating your own Excel applications, you've come to the right place. This book will hold your hands through the process of learning how to program Excel to maximize its power, and in the process, enhance your productivity. Excel 2019 Macros and VBA starts off with a walkthrough on how to use the macro recorder to record your actions in Excel. Then we delve right into Visual Basic for Applications (VBA) and all the essentials you need to start creating applications from scratch that automate Excel.

Create Your Own Customized Functions for Any Kind of Calculation

Have you ever had a calculation you would like to perform but it requires a convoluted Excel formula with several nested functions? Excel formulas are great, but when your solution requires multiple decisions, formulas can begin to get too convoluted. Visual Basic for Applications (VBA) gives you more flexibility in what you can do. VBA provides a cleaner and more efficient way to perform complex calculations and return the result with just one custom function.

Unlike some other programming books, this book does not assume you have any programming experience. The information has been delivered in a user-friendly manner without unnecessary technical jargon.

All source code samples from the book are included in a downloadable file.

After reading this book, you'll know how to:

• Record and run macros.
• Edit and customize your recorded macros.
• Assign macros to custom buttons on the Excel toolbar or in your worksheet.
• Use the Visual Basic Editor.
• Create and use variables, procedures, and modules in VBA.
• Control program flow with programming constructs for decision making and looping.
• Use the Excel object model to create applications to automate Excel.
• Use a wide array of debugging tools to obliterate even difficult to find bugs and errors in your code.
• Create efficient custom functions for complex calculations instead of using cumbersome Excel formulas.
• Create Excel add-ins to easily package and distribute your work.
• Use several predesigned macros in this book in your own worksheets.

Get Excel 2019 Macros and VBA today and start your journey towards being an Excel programming expert.

Product Details

ISBN-13:	9781916211346
Publisher:	Gtech Publishing
Publication date:	12/22/2019
Series:	Excel 2019 Mastery , #4
Pages:	168
Product dimensions:	6.00(w) x 9.00(h) x 0.36(d)

About the Author

Nathan George is a computer science graduate with several years' experience in the IT services industry in different roles, including Excel programming and providing end-user support to Excel power users. One of his main interests is using computers to automate tasks and increase productivity. As an author, he has written several technical and non-technical books.

Table of Contents

Introduction

Who is this book for?

How to Use This Book

Assumptions

Source Code

What’s the Difference Between Macros and VBA?

1. Record and Run Macros

Displaying the Developer Tab

Where to Store Your Macro

How to Start the Macro Recorder

Recording a Macro

Running a Macro

Saving Your Macro

2. Absolute Reference vs Relative Reference Macros

Absolute Reference Macro

Testing the Macro

Relative Reference Macro

Examining the Source Code

3. Assign Macros to Buttons

Add a Macro Button to the Ribbon

Create a New Custom Tab and Group

Assign a Macro command to the new group

Assign a Macro to a Button on the Quick Access Toolbar

Assign a Macro to a Graphic Object

Assign a Macro to a Form Control Button

4. Macro Security

5. Visual Basic for Applications Essentials

VBA and Macros

The Visual Basic Editor Overview

Using Variables

Variable Data Types

Variable Naming Conventions

Declaring Variables

Assigning Values to Variables

Using Option Explicit

Operators in VBA

Arithmetic Operators

Comparison Operators

Concatenation

Logical Operators

Operator Precedence

Parentheses and Operator Precedence

Modules

How to Insert a New Module

Procedures

Sub Procedures

Function Procedures

Procedure Naming Conventions

How to Insert Procedures

How to Execute a Sub Procedure

Run a Procedure in The Visual Basic Editor

Call a Sub Procedure from Another Procedure

Executing Function Procedures

Program Flow

If… Then… Else

Select Case

Do…Loop

For…Next

For Each…Next

Using the MsgBox Function

Displaying a Simple Message Box

Getting a Response from The User

6. Debugging

Overview of Debugging

Debugging Tools

Break Mode

Setting a Breakpoint

Stepping Through Code

Using the Immediate Window

Using the Debug.Print Statement

Using the Locals Window

Using the Watch Window

Adding a Watch Expression

The Call Stack

7. The Excel Object Model

Objects and Collections

The Object Hierarchy

Declaring an Object

Properties and Methods

Setting Properties and Calling Methods

Setting Properties

Calling Methods

Using the With…End With Statement

8. Editing Recorded Macros

Viewing Your Macro’s Source Code from Excel

Viewing Your Macro’s Source Code from Visual Basic

Editing Your Macro

Saving Your Changes

Using the InputBox Function to make your Macro Interactive

Find and Replace Code in Your Macro

Finding Text

Replacing Text

Handling Error Messages from Your Macro

9. Creating User-Defined Functions

Creating a User Defined Function

Adding a Description to a User-Defined Function

Using a User-Defined Function in Your Worksheet

Saving a User-Defined Function as an Add-In

10. Examples of Useful Macros

Move rows up or down

Unhide all rows and columns

Unmerge all merge cells

Convert all formulas to values

Create branded rows in your data list

Change text to uppercase

Highlight blank cells

Sort data using multiple columns

11. Getting More Help for Excel VBA

Use the Macro Recorder

Use Internet Search

User Forums

VBA online reference

Afterword