Excel 2019 Macros and VBA

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