Excel 2019 Macros and VBA · an Introduction to Excel Programming (Excel 2019 Mastery Book 4) by George, Nathan -- Read -- Imperial Library of Trantor

Index

Table of Contents Title page Copyright 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

4. Macro Security

Trusted Documents Macro-enabled Workbooks

The Personal Macro Workbook

Trust Center Macro Settings

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

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 Leave a Review Other Books by Author Assign a Macro to a Graphic Object Assign a Macro to a Form Control Button Trusted Locations Adding a Watch Expression The Call Stack

← Prev
Back
Next →

← Prev
Back
Next →