Excel 2021 / Microsoft 365 Programming By Example E-Book

Julitta Korol

• Herausgeber: Packt Publishing
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 1345

Veröffentlichungsjahr: 2024

Beschreibung

Updated for Excel 2021, this book is a practical guide for those proficient with the Excel user interface and looking to automate routine tasks. It introduces programming concepts through numerous hands-on exercises, progressing to advanced topics via custom projects. The journey begins with recording and editing macros, writing VBA code, and extends to programming the Ribbon interface and working with XML documents.
The course covers automating operations on files, folders, and other Microsoft Office applications. It also delves into event procedures, testing, and debugging. Advanced features include working with VBA classes and raising custom events in standalone class modules. By mastering these skills, users can significantly enhance their productivity and efficiency in Excel.
The book's structure, with 29 chapters and over 275 examples, ensures comprehensive learning. Companion files with source code, projects, and figures further support the user. This course is essential for anyone aiming to streamline their Excel tasks and harness the full power of Excel programming.

Leseprobe

Excel® 2021/Microsoft® 365

Programming by Example

LICENSE, DISCLAIMER OF LIABILITY, AND LIMITED WARRANTY

By purchasing or using this book (the “Work”), you agree that this license grants permission to use the contents contained herein but does not give you the right of ownership to any of the textual content in the book or ownership to any of the information or products contained in it. This license does not permit uploading of the Work onto the Internet or on a network (of any kind) without the written consent of the Publisher. Duplication or dissemination of any text, code, simulations, images, etc. contained herein is limited to and subject to licensing terms for the respective products, and permission must be obtained from the Publisher or the owner of the content, etc., in order to reproduce or network any portion of the textual material (in any media) that is contained in the Work.

Mercury Learning and Information (“MLI” or “the Publisher”) and anyone involved in the creation, writing, or production of the companion disc, accompanying algorithms, code, or computer programs (“the software”), and any accompanying Web site or software of the Work, cannot and do not warrant the performance or results that might be obtained by using the contents of the Work. The author, developers, and the Publisher have used their best efforts to ensure the accuracy and functionality of the textual material and/or programs contained in this package; we, however, make no warranty of any kind, express or implied, regarding the performance of these contents or programs. The Work is sold “as is” without warranty (except for defective materials used in manufacturing the book or due to faulty workmanship).

The author, developers, and the publisher of any accompanying content, and anyone involved in the composition, production, and manufacturing of this work will not be liable for damages of any kind arising out of the use of (or the inability to use) the algorithms, source code, computer programs, or textual material contained in this publication. This includes, but is not limited to, loss of revenue or profit, or other incidental, physical, or consequential damages arising out of the use of this Work.

The companion files on the disc are also available for download by writing to the publisher at [email protected].

The sole remedy in the event of a claim of any kind is expressly limited to replacement of the book, and only at the discretion of the Publisher. The use of “implied warranty” and certain “exclusions” vary from state to state, and might not apply to the purchaser of this product.

Excel® 2021/Microsoft® 365

Programming by Example

Julitta Korol

Copyright ©2023 by Mercury Learning and Information. All rights reserved.

This publication, portions of it, or any accompanying software may not be reproduced in any way, stored in a retrieval system of any type, or transmitted by any means, media, electronic display or mechanical display, including, but not limited to, photocopy, recording, Internet postings, or scanning, without prior permission in writing from the publisher.

Publisher: David PallaiMercury Learning and Information 22841 Quicksilver Drive Dulles, VA [email protected] [phone]

Julitta Korol. Excel®2021/Microsoft®365 Programming by Example. ISBN: 978-1-68392-886-7

222324321    This book is printed on acid-free paper in the United States of America.

The publisher recognizes and respects all marks used by companies, manufacturers, and developers as a means to distinguish their products. All brand names and product names mentioned in this book are trademarks or service marks of their respective companies. Any omission or misuse (of any kind) of service marks or trademarks, etc. is not an attempt to infringe on the property of others.

Library of Congress Control Number: 2022947022

Our titles are available for adoption, license, or bulk purchase by institutions, corporations, etc. For additional information, please contact the Customer Service Dept. at [phone].

Companion disc files for this title are available by contacting [email protected]. The sole obligation of Mercury Learning and Information to the purchaser is to replace the disc, based on defective materials or faulty workmanship, but not based on the operation or functionality of the product.

To my niece, Jowita

Contents

Acknowledgments

Introduction

PART I EXCEL VBA PRIMER

Chapter 1 Excel Macros: A Quick Start in Excel VBA Programming

Macros and VBA

Excel Macro-Enabled File Formats

Macro Security Settings

Enabling the Developer Tab in Excel

Using the Built-In Macro Recorder

Planning a Macro

Recording a Macro

Editing Recorded Macros

Macro Comments

Cleaning Up the Macro Code

Running a Macro

Testing and Debugging a Macro

Saving and Renaming a Macro

Printing Macro Code

Improving Your Recorded Macros

Creating a Master Macro

Various Methods of Running Macros

Running the Macro Using a Keyboard Shortcut

Running the Macro from the Quick Access Toolbar

Running the Macro from a Worksheet Button

Summary

Chapter 2 Excel Programming Environment: A Quick Overview of its Tools and Features (VBE)

Understanding the Project Explorer Window

Understanding the Properties Window

Understanding the Code Window

Setting the VBE Options

Syntax and Programming Assistance

List Properties/Methods

List Constants

Parameter Info

Quick Info

Complete Word

Indent/Outdent

Comment Block/Uncomment Block

Using the Object Browser

Locating Procedures with the Object Browser

Using the VBA Object Library

Using the Immediate Window

Obtaining Information in the Immediate Window

Working with Worksheet Cells and Ranges

Using the Range Property

Using the Cells Property

Using the Offset Property

Using the Resize Property

Using the End Property

Moving, Copying, and Deleting Cells

Working with Rows and Columns

Obtaining Information about the Worksheet

Entering Data and Formatting Cells

Returning Information Entered in a Worksheet

Finding Out about Cell Formatting

Working with Workbooks and Worksheets

Working with Windows

Working with the Excel Application

Summary

Chapter 3 Excel VBA Fundamentals: A Quick Reference to Writing VBA Code

Excel Objects, Properties, and Methods

Microsoft Excel Object Model

Writing Simple and Complex VBA Statements

Breaking Up Long VBA Statements

Saving Results of VBA Statements

Introducing Data Types

Using Variables

How to Create Variables

How to Declare Variables

Specifying the Data Type of a Variable

Assigning Values to Variables

Forcing Declaration of Variables

Understanding the Scope of Variables

Procedure-Level (Local) Variables

Module-Level Variables

Project-Level Variables

Lifetime of Variables

Finding a Variable Definition

Determining a Data Type of a Variable

Using Constants

Built-In Constants

Converting between Data Types

Using Static Variables in VBA Procedures

Using Object Variables in VBA Procedures

Using Specific Object Variables

Summary

Chapter 4 Excel VBA Procedures: A Quick Guide to Writing Function Procedures

Understanding Function Procedures

Creating a Function Procedure

Various Methods of Running Function Procedures

Running a Function Procedure from a Worksheet

Running a Function Procedure from Another VBA Procedure

Ensuring Availability of Your Custom Functions

Passing Arguments to Function Procedures

Specifying Argument Types

Passing Arguments by Reference and by Value

Using Optional Arguments

Testing a Function Procedure

Locating Built-In Functions

Getting to Know the MsgBox Function

Returning Values from the MsgBox Function

Getting to Know the InputBox Function

Determining and Converting Data Types

Using the InputBox Method

Summary

Chapter 5 Adding Decisions to Excel VBA Programs: A Quick Introduction to Conditional Statements

Relational and Logical Operators

Using If...Then Statement

Using If...Then...Else Statement

Using If...Then...ElseIf Statement

Nested If...Then Statements

Using the Select Case Statement

Using Is with the Case Clause

Specifying a Range of Values in a Case Clause

Specifying Multiple Expressions in a Case Clause

Writing a VBA Procedure with Multiple Conditions

Using Conditional Logic in Function Procedures

Summary

Chapter 6 Adding Repeating Actions to Excel VBA Programs: A Quick Introduction to Looping Statements

Introducing Looping Statements

Understanding Do...While and Do...Until Loops

Avoiding Infinite Loops

Executing a Procedure Line by Line

Understanding While...Wend Loop

Understanding For...Next Loop

Understanding For...Each...Next Loop

Exiting Loops Early

Using a Do...While Statement

Using Loops and Conditionals

Summary

Chapter 7 Storing Multiple Values in Excel VBA Programs: A Quick Introduction to Working with Arrays

Understanding Arrays

Declaring Arrays

Array Upper and Lower Bounds

Initializing and Filling an Array

Filling an Array Using Individual Assignment Statements

Filling an Array Using the Array Function

Filling an Array Using For...Next Loop

Using a One-Dimensional Array

Using a Two-Dimensional Array

Using a Dynamic Array

Using Array Functions

The Array Function

The IsArray Function

The Erase Function

The LBound and UBound Functions

Troubleshooting Errors in Arrays

Using the ParamArray Keyword

Data Entry with an Array

Sorting an Array with Excel

Summary

Chapter 8 Keeping Track of Multiple Values in Excel VBA Programs: A Quick Introduction to Creating and Using Collections

Working with Built-in Collections

Creating Your Own Collection

Adding Objects to a Custom Collection

Determining the Number of Items in your Collection

Accessing Items in a Collection

Removing Items from a Collection

Updating Items in a Collection

Returning a Collection from a Function

Using Custom and Built-in Collections Together

Collections versus Arrays

Watching the Execution of Your VBA Procedures

Summary

Chapter 9 Excel Tools for Testing and Debugging: A Quick Introduction to Testing VBA Programs

Testing VBA Procedures

Stopping a Procedure

Using Breakpoints

When to Use a Breakpoint

Using the Immediate Window in Break Mode

Using the Stop and Assert Statements

Using the Watch Window

Removing Watch Expressions

Using Quick Watch

Using the Locals Windows and the Call Stack Dialog Box

Navigating with Bookmarks

Trapping Errors

Using the Err Object

Setting Error Trapping Options in a VBA Project

Stepping through the VBA Code

Stepping Over a Procedure and Running to Cursor

Setting the Next Statement

Showing the Next Statement

Stopping and Resetting VBA Procedures

Terminating a Procedure Based on a Condition

Summary

PART II MANIPULATING FILES AND FOLDERS WITH VBA

Chapter 10 File and Folder Manipulation with VBA

Manipulating Files and Folders

Finding Out the Name of the Active Folder

Changing the Name of a File or Folder

Checking the Existence of a File or Folder

Finding Out the Date and Time the File Was Modified

Finding Out the Size of a File (the FileLen Function)

Returning and Setting File Attributes (the GetAttr and SetAttr Functions)

Changing the Default Folder or Drive (the ChDir and ChDrive Statements)

Creating and Deleting Folders (the MkDir and RmDir Statements)

Copying Files (the FileCopy Statement)

Deleting Files (the Kill Statement)

Summary

Chapter 11 File and Folder Manipulation with Windows Script Host (WSH)

Referencing the Microsoft Scripting Runtime

Finding Information about Files with WSH

Methods and Properties of FileSystemObject

Properties of the File Object

Properties of the Folder Object

Properties of the Drive Object

Creating a Text File Using WSH

Performing Other Operations with WSH

Running Other Applications

Obtaining Information about Windows

Retrieving Information about the User, Domain, or Computer

Creating Shortcuts

Listing Shortcut Files

Summary

Chapter 12 Using Low-Level File Access

File Access Types

Working with Sequential Files

Reading Data Stored in Sequential Files

Reading a File Line by Line

Reading Characters from Sequential Files

Reading Delimited Text Files

Writing Data to Sequential Files

Using Write # and Print # Statements

Working with Random-Access Files

Working with Binary Files

Summary

PART III CONTROLLING OTHER APPLICATIONS WITH VBA

Chapter 13 Using Excel VBA to Interact with Other Applications

Launching Applications Using the Shell Function

Moving between Applications

Controlling Another Application with the SendKeys Statement

Using VBA to Work with Microsoft PowerShell

Other Methods of Controlling Applications

Understanding Automation

Understanding Linking and Embedding

COM and Automation

Understanding Binding

Late Binding

Early Binding

Establishing a Reference to a Type Library

Creating Automation Objects

Using the CreateObject Function

Using the GetObject Function

Opening an Existing Word Document

Using the New Keyword

Using Automation to Access Microsoft Outlook

Summary

Chapter 14 Using Excel with Microsoft Access

Object Libraries

Setting Up References to Object Libraries

Connecting to Access

Opening an Access Database

Using Automation to Connect to an Access Database

Using DAO to Connect to an Access Database

Using ADO to Connect to an Access Database

Performing Access Tasks from Excel

Creating a New Access Database with DAO

Opening an Access Form

Opening an Access Report

Creating a New Access Database with ADO

Running a Select Query

Running a Parameter Query

Calling an Access Function

Retrieving Access Data into an Excel Worksheet

Retrieving Data with the GetRows Method

Retrieving Data with the CopyFromRecordset Method

Retrieving Data with the TransferSpreadsheet Method

Using the OpenDatabase Method

Creating a Text File from Access Data

Creating a Query Table from Access Data

Creating an Embedded Chart from Access Data

Transferring the Excel Worksheet to an Access Database

Linking an Excel Worksheet to an Access Database

Importing an Excel Worksheet to an Access Database

Placing Excel Data in an Access Table

Summary

PART IV ENHANCING THE USER EXPERIENCE

Chapter 15 Event-Driven Programming

Introduction to Event Procedures

Writing Your First Event Procedure

Enabling and Disabling Events

Event Sequences

Worksheet Events

Worksheet_Activate()

Worksheet_Deactivate()

Worksheet_SelectionChange(ByVal Target As Range)

Worksheet_Change(ByVal Target As Range)

Worksheet_Calculate()

Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Workbook Events

Workbook_Activate()

Workbook_Deactivate()

Workbook_Open()

Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Workbook_BeforePrint(Cancel As Boolean)

Workbook_BeforeClose(Cancel As Boolean)

Workbook_NewSheet(ByVal Sh As Object)

Workbook_WindowActivate(ByVal Wn As Window)

Workbook_WindowDeactivate(ByVal Wn As Window)

Workbook_WindowResize(ByVal Wn As Window)

PivotTable Events

Chart Events

Writing Event Procedures for a Chart Located on a Chart Sheet

Chart_Activate()

Chart_Deactivate()

Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)

Chart_Calculate()

Chart_BeforeRightClick()

Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

Writing Event Procedures for Embedded Charts

Events Recognized by the Application Object

Query Table Events

Other Excel Events

OnTime Method

OnKey Method

Summary

Chapter 16 Using Dialog Boxes

Excel Dialog Boxes

File Open and File Save As Dialog Boxes

Filtering Files

Selecting Files

GetOpenFilename and GetSaveAsFilename Methods

Using the GetOpenFilename Method

Using the GetSaveAsFilename Method

Summary

Chapter 17 Creating Custom Forms

Creating Forms

Tools for Creating User Forms

Default Toolbox Controls

Placing Controls on a Form

Setting Grid Options

Sample Application: Info Survey

Setting Up the Custom Form

Inserting a New Form and Setting Up the Initial Properties

Changing the Size of the Form

Adding Buttons, Checkboxes, and Other Controls to a Form

Changing Control Names and Properties

Setting the Tab Order

Preparing a Worksheet to Store Custom Form Data

Displaying a Custom Form

Understanding Form and Control Events

Writing VBA Procedures to Respond to Form and Control Events

Writing a Procedure to Initialize the Form

Writing a Procedure to Populate the Listbox Control

Writing a Procedure to Control Option Buttons

Writing Procedures to Synchronize the Text Box with the Spin Button

Writing a Procedure that Closes the User Form

Transferring Form Data to the Worksheet

Using the Info Survey Application

UserForm: Modal versus Modeless

Summary

Chapter 18 Formatting Worksheets with VBA

Performing Basic Formatting Tasks with VBA

Formatting Numbers

Formatting Text

Formatting Dates

Formatting Columns and Rows

Formatting Headers and Footers

Formatting Cell Appearance

Removing Formatting from Cells and Ranges

Performing Advanced Formatting Tasks with VBA

Conditional Formatting Using VBA

Conditional Formatting Rule Precedence

Deleting Rules with VBA

Using Data Bars

Using Color Scales

Using Icon Sets

Formatting with Themes

Formatting with Shapes

Formatting with Sparklines

Understanding Sparkline Groups

Programming Sparklines with VBA

Formatting with Styles

Summary

Chapter 19 Context Menu Programming and Ribbon Customizations

Working with Context Menus

Modifying a Built-In Context Menu

Removing a Custom Item from a Context Menu

Disabling and Hiding Items on a Context Menu

Adding a Context Menu to a Command Button

Finding a FaceID Value of an Image

A Quick Overview of the Ribbon Interface

Ribbon Programming with VBA and XML

Creating the Ribbon Customization XML Markup

Loading Ribbon Customizations

Errors on Loading Ribbon Customizations

Using Images in Ribbon Customizations

About Tabs, Groups, and Controls

Using Various Controls in Ribbon Customizations

Creating Toggle Buttons

Creating Split Buttons, Menus, and Submenus

Creating Checkboxes

Creating Edit Boxes

Creating Combo Boxes and Drop-Downs

Creating a Gallery Control

Creating a Dialog Box Launcher

Disabling a Control

Repurposing a Built-In Control

Refreshing the Ribbon

The CommandBar Object and the Ribbon

Tab Activation and Group Auto-Scaling

Customizing the Backstage View

Customizing the Quick Access Toolbar (QAT)

Modifying Context Menus Using Ribbon Customizations

Summary

Chapter 20 Printing and Sending Email from Excel

Controlling the Page Setup

Controlling the Settings on the Page Layout Tab

Controlling the Settings on the Margins Tab

Controlling the Settings on the Header/Footer Tab

Controlling the Settings on the Sheet Tab

Retrieving Current Values from the Page Setup Dialog Box

Previewing a Worksheet

Changing the Active Printer

Printing a Worksheet with VBA

Disabling Printing and Print Previewing

Using Printing Events

Sending Email from Excel

Sending Email Using the SendMail Method

Sending Email Using the MsoEnvelope Object

Sending Bulk Email from Excel via Outlook

Summary

PART V EXCEL TOOLS FOR DATA ANALYSIS

Chapter 21 Using and Programming Excel Tables

Understanding Excel Tables

Creating a Table Using Built-in Commands

Creating a Table Using VBA

Understanding Column Headings in the Table

Multiple Tables in a Worksheet

Working with the Excel ListObject

Filtering Data in Excel Tables Using AutoFilter

Filtering Data in Excel Tables Using Slicers

Deleting Worksheet Tables

Summary

Chapter 22 Programming PivotTables and PivotCharts

Creating a PivotTable Report

Removing PivotTable Detail Worksheets with VBA

Creating a PivotTable Report with VBA

Creating a PivotTable Report from an Access Database

Using the CreatePivotTable Method of the PivotCache Object

Formatting, Grouping, and Sorting a PivotTable Report

Hiding Items in a PivotTable

Adding Calculated Fields and Items to a PivotTable

Creating a PivotChart Report Using VBA

Understanding and Using Slicers

Creating Slicers Manually

Working with Slicers Using VBA

Data Model Functionality and PivotTables

Programmatic Access to the Data Model

Summary

Chapter 23 Getting and Transforming Data in Excel

Using the Get Data Button

Understanding Power Queries

Step 1: Get Data from an Excel Workbook

Step 2: Adding, Renaming, and Moving a New Column

Step 3: Loading Data from a Text File

Step 4: Combining Data using Append Query

Step 5: Data Cleanup

Step 6: Shaping Data into Final Output

Using the Advanced Editor

Power Query vs Excel Formula Language and Excel VBA

Learning about various M Language Functions

Creating a Query from a Table

The Get Data and VBA Support

Additional Learning Resources for Using the Get Data Feature

Summary

PART VI TAKING CHARGE OF PROGRAMMING ENVIRONMENT

Chapter 24 Programming the Visual Basic Editor (VBE)

The Visual Basic Editor Object Model

Understanding the VBE Objects

Accessing the VBA Project

Finding Information about a VBA Project

VBA Project Protection

Working with Modules

Listing All Modules in a Workbook

Adding a Module to a Workbook

Removing a Module

Deleting All Code from a Module

Deleting Empty Modules

Copying (Exporting/Importing) a Module

Copying (Exporting/Importing) All Modules

Working with Procedures

Listing All Procedures in All Modules

Adding a Procedure

Deleting a Procedure

Creating an Event Procedure

Working with UserForms

Creating and Manipulating UserForms

Copying UserForms Programmatically

Working with References

Understanding Early Binding and Late Binding

Creating a List of References

Adding a Reference

Removing a Reference

Checking for Broken References

Working with Windows

Working with VBE Menus and Toolbars

Generating a Listing of VBE CommandBars and Controls

Adding a CommandBar Button to the VBE

Removing a CommandBar Button from the VBE

Summary

Chapter 25 Calling Windows API Functions from VBA

Understanding the Windows API Library Files

How to Declare a Windows API Function

Passing Arguments to the API Functions

Understanding the API Data Types and Constants

Integer

Long

String

Structure

Any

Using Constants with Windows API Functions

Excel 64-Bit and Windows API

Accessing Windows API Documentation

Using Windows API Functions in Excel

Summary

PART VII ADVANCED CONCEPTS IN EXCEL VBA

Chapter 26 Creating Classes in VBA

Important Terminology

Creating and Using Custom Objects

Member Variables in a Class Module

Defining the Properties for the Class

Writing Property Procedures

Writing Class Methods

Creating an Instance of a Class

Creating a Custom Application

Event Procedures in the Class Module

Creating a Form for Data Collection

Creating a Worksheet for Data Output

Writing Code behind the UserForm

Working with the Custom CEmployee Class

Watching the Execution of Your Custom Application

Summary

PART VIII WORKING TOGETHER: VBA, HTML, XML, AND THE REST API

Chapter 27 HTML Programming and Web Queries

Creating Hyperlinks Using VBA

Creating and Publishing HTML Files Using VBA

Web Queries

Creating and Running Web Queries with VBA

Dynamic Web Queries

Refreshing Data

Summary

Chapter 28 Using XML in Excel 2021

What Is XML?

Well-Formed XML Documents

Validating XML Documents

Editing and Viewing an XML Document

Opening an XML Document in Excel

Working with XML Maps

Working with XML Tables

Exporting an XML Table

XML Export Precautions

Validating XML Data

Programming XML Maps

Adding an XML Map to a Workbook

Deleting Existing XML Maps

Exporting and Importing Data via an XML Map

Binding an XML Map to an XML Data Source

Refreshing XML Tables from an XML Data Source

Viewing the XML Schema

Creating XML Schema Files

Using XML Events

The XML Document Object Model

Working with XML Document Nodes

Retrieving Information from Element Nodes

XML via ADO

Saving an ADO Recordset to Disk as XML

Loading an ADO Recordset

Saving an ADO Recordset into the DOMDocument60 Object

Understanding Namespaces

Understanding Open XML Files

Manipulating Open XML Files with VBA

Summary

Chapter 29 Excel and Rest API

Introduction to a VBA Dictionary Object

Accessing the VBA Dictionary

Adding a Reference to the Microsoft Scripting Runtime Library

Working with the Dictionary Object’s Properties and Methods

Dictionary versus Collection

Introduction to Regular Expressions

Character Matching in RegExp Patterns

Quantifiers in RegExp Patterns

Using the RegExp Object in VBA

The RegExp Object Declaration

The RegExp Object’s Properties

The RegExp Object’s Methods

Writing VBA procedures using the RegExp Object

Introduction to the REST API

Accessing REST APIs with VBA

Methods and Properties of the XMLHTTPRequest Object

Making a Basic GET Request

The Overview of JSON

Loading JSON Data into Excel

Parsing JSON with Third-Party Libraries

Summary

Index

Acknowledgments

First, I’d like to express my gratitude to everyone at Mercury Learning and Information. A sincere thank-you to my publisher, David Pallai, for offering me the opportunity to update this book to the new 2021 version and tirelessly keeping things on track during this long project.

A whole bunch of thanks go to the editorial team for working so hard to bring this book to print. In particular, I would like to thank the copyeditor for the thorough review of my writing, Jennifer Blaney, for her production expertise and keeping track of all the edits and file processing issues, and the compositor, for all the composition efforts that gave this book its pleasant look and feel.

Finally, I’d like to acknowledge readers like you who cared enough to post reviews of the previous edition of this book online. Your invaluable feedback has helped me improve the quality of this work by including the material that matters to you most. Please continue to inspire me with your ideas and suggestions.

Introduction

If you ever wanted to open a new worksheet without using built-in commands or create a custom, fully automated form to gather data and store the results in a worksheet, you’ve picked up the right book. This book shows you what’s doable with Microsoft® Excel® 2021 beyond the standard user interface. This book’s purpose is to teach you how to delegate many time-consuming and repetitive tasks to Excel by using its built-in language, VBA (Visual Basic for Applications). By executing special commands and statements and using several Excel’s built-in programming tools, you can work smarter than you ever thought possible. I will show you how.

When I first started programming in Excel (circa 1990), I was working in a sales department, and it was my job to calculate sales commissions and send the monthly and quarterly statements to our sales representatives spread all over the United States. As this was a very time-consuming and repetitive task, I became immensely interested in automating the whole process. In those days, it wasn’t easy to get started in programming on your own. There weren’t as many books written on the subject; all I had was the built-in documentation that was hard to read. Nevertheless, I succeeded; my first macro worked like magic. It automatically calculated our salespeople’s commissions and printed out nicely formatted statements. While the computer was busy performing the same tasks repeatedly, I was free to deal with other more interesting projects.

Many years have passed since that day, and Excel is still working like magic for me and a great number of other people who took time to familiarize themselves with its programming interface. If you’d like to join these people and have Excel do magical things for you as well, this book provides an easy step-by-step introduction to VBA and other technologies that work nicely with Microsoft Excel. Besides this book, there is no extra cost to you; all the tools you need are built into Excel. If you have not yet discovered them, Excel 2021 / Microsoft 365 Programming by Example will lead you through the process of creating your first macros, VBA procedures, web queries and power queries, and XML documents, from start to finish. Along the way, there are detailed, practical “how-to” examples and plenty of illustrations. The book’s approach is to learn by doing. There’s no better way than step by step. Simply turn on the computer, open this book, launch Microsoft Excel, and do all the guided Hands-On exercises. But before you get started, allow me to give you a short overview of the things you’ll be learning as you progress through this book.

Excel 2021 / Microsoft 365 Programming by Example is divided into 8 parts (29 chapters) that progressively introduce you to programming Microsoft Excel 2021 as well as controlling other applications with Excel.

Part I introduces you to Visual Basic for Applications (VBA), the programming language for Microsoft Excel. In this part of the book, you acquire the fundamentals of VBA that you will use over and over again in building real-life spreadsheet applications. Part I chapters are also the subject of a standalone book “Microsoft Excel 2021 Programming Pocket Primer,” available from Mercury Learning and Information (ISBN: 978-1-68392-892-8). If you already worked through the pocket primer book, you could skip chapters 1-9 and begin from Chapter 10.

PART I CONSISTS OF THE FOLLOWING NINE CHAPTERS:

Chapter 1—Excel Macros: A Quick Start in Excel VBA Programming In this chapter, you learn how you can introduce automation into your Excel worksheets by simply using the built-in macro recorder. You learn about different phases of macro design and execution. You also learn about macro security.

Chapter 2—Excel Programming Environment: A Quick Overview of Its Tools and Features In this chapter, you learn almost everything you need to know about working with the Visual Basic Editor window, commonly referred to as VBE. Some of the programming tools that are not covered here are discussed and used in Chapter 9.

Chapter 3—Excel VBA Fundamentals: A Quick Reference to Writing VBA Code In this chapter, you are introduced to the basic VBA concepts, such as Microsoft Excel object model and its objects, properties, and methods. You also learn concepts that allow you to store various pieces of information for later use.

Chapter 4—Excel VBA Procedures: A Quick Guide to Writing Function Procedures In this chapter, you learn how to write and execute function procedures. You also learn how to provide additional information to your procedures before they are run. You are introduced to working with some useful built-in functions and methods that allow you to interact with your VBA procedure users.

Chapter 5—Adding Decisions to Excel VBA Programs: A Quick Introduction to Conditional Statements In this chapter, you learn how to control your program flow with several different decision-making statements.

Chapter 6—Adding Repeating Actions to Excel VBA Programs: A Quick Introduction to Looping Statements In this chapter, you learn how you can repeat certain groups of statements using procedure loops.

Chapter 7—Storing Multiple Values in Excel VBA Programs: A Quick Introduction to Working with Arrays In this chapter, you learn the concept of static and dynamic arrays, which you can use for holding various values. You also learn about built-in array functions.

Chapter 8—Keeping Track of Multiple Values in Excel VBA Programs: A Quick Introduction to Creating and Using Collections In this chapter, you learn how to use your custom collection object to track and manipulate data in your VBA procedures.

Chapter 9—Excel Tools for Testing and Debugging: A Quick Introduction to Testing VBA Programs In this chapter, you begin using built-in debugging tools to test your programming code and trap errors.

The above nine chapters will give you the fundamental techniques and concepts you will need to continue your Excel VBA learning path. The skills obtained in Excel VBA Primer are very portable. They can be utilized in programming other Microsoft 365 applications that also use VBA as their native programming language such as Access, Word, PowerPoint, Outlook, and others.

While VBA offers numerous built-in functions and statements for working with the file system, you can also perform file and folder manipulation tasks via objects and methods included in the Windows Script Host installed by default on computers running the Windows operating system. Additionally, you can open and manipulate files directly via the low-level file I/O (input/output) functions. In Part II of this book, you discover various methods of working with files and folders, and learn how to programmatically open, read, and write three types of files.

PART II CONSISTS OF THE FOLLOWING THREE CHAPTERS:

Chapter 10—File and Folder Manipulation with VBA In this chapter, you learn about numerous VBA statements used in working with Windows files and folders.

Chapter 11—File and Folder Manipulation with Windows Script Host (WSH) In this chapter, you learn how the Windows Script Host works together with VBA and allows you to get information about files and folders.

Chapter 12—Using Low-Level File Access In this chapter, you learn how to get in direct contact with your data by using the process known as low-level file I/O. You also learn about various types of file access.

The VBA programming language goes beyond Excel. It can be used to program any application that supports this language. In Part III of the book, you learn how other applications expose their objects to VBA.

PART III CONSISTS OF THE FOLLOWING TWO CHAPTERS:

Chapter 13—Using Excel VBA to Interact with Other Applications In this chapter, you learn how you can launch and control other applications from within VBA procedures written in Excel. You also learn how to establish a reference to a Type library and use and create Automation objects.

Chapter 14—Using Excel with Microsoft Access In this chapter, you learn about accessing Microsoft Access data and running Access queries and functions from VBA procedures. If you are interested in learning more about Access programming with VBA using the same step-by-step approach as presented in this book, I recommend another book of mine titled Access 2021/Microsoft 365 Programming by Example (Mercury Information and Learning, 2022).

In Part IV of this book, you learn how to create desired interface elements for your users via Ribbon customizations and the creation of dialog boxes and custom forms. You will also learn how to format worksheets with VBA, handle printing and email tasks, and control Excel with event-driven programming.

PART IV CONSISTS OF THE FOLLOWING SIX CHAPTERS:

Chapter 15—Event-Driven Programming In this chapter, you learn about the types of events that can occur when you are running VBA procedures in Excel. You gain a working knowledge of writing event procedures and handling various types of events.

Chapter 16—Using Dialog Boxes In this chapter, you learn about working with Excel built-in dialog boxes programmatically.

Chapter 17—Creating Custom Forms In this chapter, you learn how to use various controls for designing user-friendly forms. This chapter has a hands-on application you build from scratch.

Chapter 18—Formatting Worksheets with VBA In this chapter, you learn how to perform worksheet formatting tasks with VBA by applying visual features such as data bars, color scales, and icon sets. You also learn how to produce consistent-looking worksheets by using document themes and styles.

Chapter 19—Context Menu Programming and Ribbon Customizations In this chapter, you learn how to add custom options to Excel built-in context (shortcut) menus and how to work programmatically with the Ribbon interface and Backstage View.

Chapter 20—Printing and Sending Email from Excel In this chapter, you learn how to control printing and emailing your workbooks via VBA code.

Some Excel 2021 features are used more frequently than others; some are only used by Excel power users and developers. In Part V of the book, you work with Excel tools for data analysis. You gain experience in programming advanced Excel features such as Excel tables, PivotTables, PivotCharts, and get introduced to the Power Query feature that allows you to create powerful queries that simplify data import and transformation.

PART V CONSISTS OF THE FOLLOWING THREE CHAPTERS:

Chapter 21—Using and Programming Excel Tables In this chapter, you learn how to work with Excel tables. You will learn how to retrieve information from an Access database, convert it into a table, and enjoy database-like functionality in the spreadsheet. You will also learn how tables are exposed through Excel’s object model and manipulated via VBA.

Chapter 22—Programming PivotTables and PivotCharts In this chapter, you learn how to work with two powerful Microsoft Excel objects that are used for data analysis: PivotTable and PivotChart. You will learn how to use VBA to manipulate these two objects to quickly produce reports that allow you or your users to easily examine large amounts of data pulled from an Excel worksheet range or from an external data source such as an Access database.

Chapter 23—Getting and Transforming Data in Excel 2021 In this chapter, you are introduced to data import, transformation and shaping features available in the Get & Transform section of the Excel’s 2021 Data tab. You work with Query Editor and Advanced Editor and learn formulas and functions written in the M expression language while bringing together data from various sources.

While VBA provides a very comprehensive Object Model for automating worksheet tasks, some of the processes and operations that you may need to program are an integral part of the Windows operating system and cannot be controlled via VBA. In Part VI of the book, you start by learning how to programmatically work with VBA projects, modules, and procedures. Next, you are introduced to the Windows API library of functions that will come to your rescue when you need to overcome the limitations of the native VBA library.

PART VI CONSISTS OF THE FOLLOWING TWO CHAPTERS:

Chapter 24—Programming the Visual Basic Editor (VBE) In this chapter, you learn how to use numerous objects, properties, and methods from the Microsoft Visual Basic for Applications Extensibility Object Library to control the Visual Basic Editor to gain full control over Excel.

Chapter 25—Calling Windows API functions from Excel VBA In this chapter, you are introduced to the Windows API library. After learning basic Windows API concepts, you are shown how to declare and utilize API functions from VBA.

As your Excel VBA procedures become more complex, you may find that your code is scattered all over the place and is difficult to maintain. Copying code from place to place and modifying procedures to include more arguments and enhancements because of the changing requirements sooner or later will result in creating a coding mess that is difficult to handle. Before you go this route, you should know that Excel has a special feature known as a class module that allows you to create code that is self-contained and reusable.

PART VII CONSISTS OF THE FOLLOWING CHAPTER:

Chapter 26—Creating Classes in VBA In this chapter, you will learn how coding in a standalone class module can help you organize your code into more manageable objects that can easily be reused and adjusted when necessary. Here you learn about creating and using custom objects, declaring class members, defining class properties, and writing property procedures and class methods. You also learn how to use event procedures in a class module. These advanced topics are covered in custom application that you will build from scratch.

Thanks to the Internet and intranets, your worksheet data can be easily accessed and shared with others 24/7. Excel is capable of both capturing data from the Web and publishing it to the Web.

In Part VIII of the book, you are introduced to using Excel VBA with HTML (the Hypertext Markup Language), XML (the Extensible Markup Language), and calling a new type of a Web service, known as the REST API.

PART VIII CONSISTS OF THE FOLLOWING THREE CHAPTERS:

Chapter 27—HTML Programming and Web Queries In this chapter, you learn how to create hyperlinks and publish HTML files using VBA. You also learn how to activate and work with one of Excel legacy features known as a Web query.

Chapter 28—Using XML in Excel 2021 In this chapter, you learn how to use Extensible Markup Language with Excel. You learn about enhanced XML support in Excel 2021 and many objects and technologies that are used to process XML documents.

Chapter 29—Excel and REST API In this chapter, you explore several external libraries that will help you build more advanced VBA applications. You learn about a Dictionary object and how it compares to the native VBA Collection object. You learn about regular expressions and how to use them to extract data. Finally, you are introduced to the JSON format and learn the basics of making HTTP GET requests to pull data from a RESTful web service.

INTENDED AUDIENCE

This book is designed for Excel users who want to expand their knowledge and learn what can be accomplished with Excel beyond the provided user interface.

Consider this book as a sort of private course that you can attend in the comfort of your office or home. Some courses have prerequisites, and this is no exception. Excel 2021 / Microsoft 365 Programming by Example does not explain how to select options from the Ribbon or use shortcut keys. The book assumes that you can easily locate in Excel the options that are required to perform any of the tasks already preprogrammed by the Microsoft team. With the basics already mastered, this book will take you to the next learning level where your custom requirements and logic are rendered into the language that Excel can understand. Let your worksheets perform magical things for you and let the fun begin.

THE COMPANION FILES

The example files for all the hands-on activities in this book are available in the companion files. These companion files may also be downloaded by contacting the publisher at [email protected]. Digital versions of this title are available at academiccourseware.com and numerous other digital vendors.

PartIExcel VBA Primer

The Excel VBA Primer is divided into nine chapters that progressively introduce you to programming Microsoft Excel using the 2021 version of the product. These chapters present the fundamental techniques and concepts that you need to master before you can take further steps in Excel programming.

Chapter 1

Excel Macros —A Quick Start in Excel VBA Programming

Chapter 2

Excel Programming Environment —A Quick Overview of its Tools and Features

Chapter 3

Excel VBA Fundamentals —A Quick Reference to Writing VBA Code

Chapter 4

Excel VBA Procedures —A Quick Guide to Writing Function Procedures

Chapter 5

Adding Decisions to Excel VBA Programs —A Quick Introduction to Conditional Statements

Chapter 6

Adding Repeating Actions to Excel VBA Programs —A Quick Introduction to Looping Statements

Chapter 7

Storing Multiple Values in Excel VBA Statements —A Quick Introduction to Working with Arrays

Chapter 8

Keeping Track of Multiple Values in Excel VBA Programs —A Quick Introduction to Creating and Using Collections

Chapter 9

Excel Tools for Testing in Debugging —A Quick Introduction to Testing VBA Programs

Chapter1 Excel Macros

A Quick Start in Excel VBA Programming

Visual Basic for Applications (VBA) is the programming language built into all Microsoft® Office® applications, including Microsoft Excel®. By learning some basic VBA commands, you can start automating many of the mundane routine tasks that you perform in Excel. In this chapter, you acquire the fundamentals of VBA by recording macros and using the Visual Basic Editor to examine and edit the VBA code behind the recorded macro.

MACROS AND VBA

Macros are programs that store a series of commands. When you create a macro, you simply combine a sequence of keystrokes into a single command that you can later “play back.” Because macros can reduce the number of steps required to complete tasks, using macros can significantly decrease the time you spend creating, formatting, modifying, and printing your Excel worksheets. You can create macros by using Microsoft Excel’s built-in recording tool (Macro Recorder), or you can write them from scratch using Visual Basic Editor, a special development environment built into Excel. You can combine recorded macros with your own programming code to create unique VBA applications that meet your everyday needs. Whether you write or record your programming code in Excel, you’ll be utilizing the powerful programming language—Visual Basic for Applications—commonly known as VBA.

Microsoft Excel comes with dozens of built-in, time-saving features that allow you to work faster and smarter. Before you decide to automate a worksheet task with a recorded macro or programming code written from scratch, make sure there is not already a built-in feature that you can use to perform that task. Consider writing your own VBA code or recording a macro when you find yourself performing the same series of actions multiple times or when Excel does not provide a built-in tool to do the job.

Just by learning how to handle Excel’s macro recorder and use basic VBA statements and constructs to enhance your macros, you’ll be able to automate any part of your worksheet. For example, you can automate data entry by recording a macro that enters headings in a worksheet or replaces column titles with new labels. Adding a little bit of conditional logic to your VBA code will allow you to automatically check for duplicate entries in a specified range of your worksheet. With a macro, you can quickly apply formatting to several worksheets, as well as combine different formats, such as fonts, colors, borders, and shading. Macros will save you keystrokes when it comes to setting print areas, margins, headers, and footers, and selecting special options for printouts.

Excel Macro-Enabled File Formats

When a workbook contains programming code, it should be saved in one of the following macro-enabled file formats:

Excel Macro-Enabled Workbook (.xlsm)

Excel Binary Workbook (.xlsb)

Excel Macro-Enabled Template (.xltm)

If you attempt to save the workbook in a file format that is incompatible with the type of content it includes, Excel will warn you with a message as shown in Figure 1.1.

FIGURE 1.1 When a workbook contains programming code, you must save it in a macro-enabled file type instead of a regular .XLSX workbook file.

Macro Security Settings

Because macros can contain malicious code designed to put a virus on a user’s computer, it is important to understand different security settings that are available in Excel. It is also critical that you run up-to-date antivirus software on your computer. Antivirus software installed on your computer will scan the workbook file you are attempting to open if the file contains macros. The default macro security setting is to disable all macros with notification, as shown in Figure 1.2.

FIGURE 1.2 The Macro Settings options in the Trust Center allow you to control how Excel should deal with macros when they are present in an open workbook. To open Trust Center’s Macro Settings, choose File | Options | Trust Center | Trust Center Settings and click the Macro Settings link.

NOTE

VBA macros are the macros you create using the Excel builtin language – VBA. You will be working with these macros throughout this book. Excel 4.0 macros are legacy Excel macros. Introduced in 1992, they are commonly referred to as XLM 4.0 macros. They are still in Excel for backward compatibility reasons. Using these macros is discouraged as they can hide malicious code in Excel formulas.

If VBA macros are present in a workbook you are trying to open, you will receive a security warning message just under the Ribbon, as shown in Figure 1.3.

FIGURE 1.3 Upon opening a workbook with VBA macros, Excel brings up a security warning message.

To use the disabled components, you should click the Enable Content button on the message bar. This will add the workbook to the Trusted Documents list in your registry. The next time you open this workbook you will not be alerted to macros. If you need more information before enabling content, you can click the message text displayed in the security message bar to activate the Backstage View, where you will find an explanation of the active content that has been disabled, as shown in Figure 1.4. Clicking the Enable Content button in the Backstage View presents two options:

Enable All Content This option provides the same functionality as the Enable Content button in the security message bar. This will enable all the content and make it a trusted document.

Advanced Options This option brings up the Microsoft Office Security Options dialog shown in

Figure 1.5

. This dialog provides options for enabling content for the current session only.

FIGURE 1.4 The Backstage View in Excel.

FIGURE 1.5 Disabled macros can be enabled for the current session in the Microsoft Office Security Options dialog.

ENABLING THE DEVELOPER TAB IN EXCEL

To make it easy to work with macro-enabled workbooks while working with this book’s exercises, you will permanently trust your workbooks with recorded macros or VBA code by placing them in a folder on your local drive that you mark as trusted. Notice the Open the Trust Center hyperlink shown in Figure 1.5. This hyperlink will open the Trust Center dialog where you can set up a trusted folder. You can also activate the Trust Center by selecting File | Options.

Let’s take a few minutes now to set up your Excel application so you can run VBA macros on your computer without security prompts.

NOTE

Please note files for the “Hands-On” project may be found in the companion files.

Hands-On 1.1    Setting Up Excel for Macro Development

Create a folder on your hard drive named

C:\VBAPrimerExcel2021_ByExample

.

Launch Excel and open a blank workbook.

Choose

File | Options

.

In the Excel Options dialog, click

Customize Ribbon

. In the Main Tabs listing on the right-hand side, select

Developer

as illustrated in

Figure 1.6

and click

OK

. The Developer tab should now be visible in the Ribbon.

In the Code group of the Developer tab on the Ribbon, click the

Macro Security

button, as shown in

Figure 1.7

. The Trust Center dialog appears as depicted in

Figure 1.2

.

In the left pane of the Trust Center dialog, click

Trusted Locations

. The Trusted Locations dialog already shows several predefined trusted locations that were created when you installed Excel. For this book, we will add a custom location to this list.

Click the

Add new location

button.

In the Path text box, type the name of the folder you created in Step 1 of this Hands-On as shown in

Figure 1.8

.

Click

OK

to close the Microsoft Office Trusted Location dialog.

Notice that the Trusted Locations list in the Trust Center now includes the C:\

VBAPrimerExcel2021_ByExample

folder as a trusted location. Files placed in a trusted location can be opened without being checked by the Trust Center security feature.

Click

OK

to close the Trust Center dialog box.

Your Excel application is now set up for easy macro development as well as opening files containing macros. You should save all the files created in the book’s Hands-On exercises into your trusted C:\VBAPrimerExcel2021_ByExample folder.

FIGURE 1.6 To enable the Developer tab on the Ribbon, use the Excel Options dialog and select Customize Ribbon.

FIGURE 1.7 Use the Macro Security button in the Code group on the Developer tab to customize the macro security settings.

FIGURE 1.8 Designating a Trusted Location folder for this book’s programming examples.

USING THE BUILT-IN MACRO RECORDER

In this section, we will go through the process of recording several short macros that perform data entry and formatting tasks in an Excel worksheet. You will learn how to plan your macros, record your keystrokes, edit, and improve your recorded macro code, run your macros, and learn basic troubleshooting techniques that will get you back on track in case you encounter errors while running your macros. You will also learn how to save your macros, rename them, combine them, and print them.

Planning a Macro

Before you create a macro, take a few minutes to consider what you want to do. The easiest way to plan your macro is to manually perform all the actions that the macro needs to do. As you enter the keystrokes, write them down on a piece of paper exactly as they occur. Don’t leave anything out. Like a voice recorder, Excel’s macro recorder records every action you perform. If you do not plan your macro prior to recording, you may end up with unnecessary actions that will not only slow it down but also require more editing later to make it work as intended. Although it’s easier to edit a macro than it is to erase unwanted passages from a voice recording, performing only the actions you want recorded will save you editing time and trouble later.

Suppose you are asked to programmatically create the worksheet depicted in Figure 1.9. No worries. Getting started is very easy with the macro recorder. Let’s begin by identifying the tasks required to complete this worksheet.

Task 1

Insert a new sheet into a workbook and name it Employee Wages.

Task 2

Enter column headings into first row of the worksheet and apply required formatting (column size, font styles).

Task 3

Enter employee data (Full Name, Hourly Rate, Hours Worked).

Tasks 4 and 5

Enter formulas to fill in the employee First and Last Name columns.

Task 6

Enter formulas to calculate employee total wages.

Task 7

Apply formatting to the completed worksheet.

Instead of recording one macro to complete your assignment, you will create a separate macro for each task. This approach will give you a chance to learn how to combine code from several simpler macros and how to create a master macro. Let’s get started.

FIGURE 1.9 A sample worksheet to be created and formatted with the help of the Excel built-in macro recorder.

Hands-On 1.2    Getting Things Ready for Macro Recording

Open a new workbook and save it as

Chap01_ExcelPrimer.xlsm

in your trusted

VBAPrimerExcel2021_ByExample

folder. You must save the file in the macro-enabled file format (.xlsm) to allow for storing macros. Keep this file open as you will use it to record all the macros in this chapter.

Recording a Macro

Before you record a macro, you need to decide whether you want to record the positioning of the active cell. If you want the macro to always start in a specific location on the worksheet, turn on the macro recorder first and then select the cell you want to start in. If the location of the active cell does not matter, select a single cell first and then turn on the macro recorder.

Hands-On 1.3    Inserting and Naming a Worksheet (Macro Task 1)

Choose

Developer | Record Macro

.

In the Record Macro dialog box, enter the name

Insert_NewSheet

for the macro, as shown in

Figure 1.10

. Do not dismiss this dialog box until you are instructed to do so.

Macro Names

If you forget to enter a name for the macro, Excel assigns a default name, such as Macro1, Macro2, and so on. Macro names can contain letters, numbers, and the underscore character, but the first character must be a letter. For example, Report1 is a correct macro name, while 1Report is not. Spaces are not allowed. If you want a space between the words, use the underscore.

Select

This Workbook

in the

Store macro in

list box.

Storing Macros

Excel allows you to store macros in three locations:

Personal Macro Workbook—Macros stored in this location will be available each time you work with Excel. You can find the Personal Macro Workbook in the XLStart folder. If this workbook doesn’t already exist, Excel creates it the first time you select this option.

New Workbook—Excel will place the macro in a new workbook.

This Workbook—The macro will be stored in the workbook you are currently using.

FIGURE 1.10 When you record a new macro, you must name it. In the Record Macro dialog box, you can also supply a shortcut key, a storage location, and a description for your macro.

In the Description box, enter the following text:

Insert and rename a worksheet

.

Choose

OK

to close the Record Macro dialog box. The Stop Recording button shown in

Figure 1.11

appears in the status bar. Do not click this button until you are instructed to do so. When this button appears in the status bar, the workbook is in the recording mode.

FIGURE 1.11 The Stop Recording button in the status bar indicates that the macro recording mode is active.

The Stop Recording button remains in the status bar while you record your macro. Only the actions finalized by pressing Enter or clicking OK are recorded. If you press the Esc key or click Cancel before completing the entry, the macro recorder does not record that action.

Add a new sheet to the current workbook. You can do this by either right clicking the Sheet1 tab and choosing

Insert | Worksheet | OK

, or simply clicking the plus button to the right of the Sheet1 tab.

Rename the new sheet

Employee Wages

.

Click the

Stop Recording

button in the status bar as shown in

Figure 1.11

or choose

View | Macros | Stop Recording

. When you stop the macro recorder, the status bar displays a button that allows you to record another macro (see

Figure 1.12

).

FIGURE 1.12 Excel status bar with the macro recording button turned off.

You have now recorded your first macro. Excel has written all the necessary statements to execute the actions you performed. Let’s continue recording all the remaining actions to complete the tasks that we defined earlier. After that you will have a chance to review the recorded macro code and try out your macros.

Hands-On 1.4    Inserting Column Headings and Applying Formatting (Macro Task 2)

Choose

View | Macros | Record Macro

(or you may click the

Begin recording

button located in the status bar).

Enter

Insert_Headings

as the name for your macro.

Ensure that

This Workbook

is selected in the

Store macro

in list box.

Click

OK

. Excel turns on the macro recorder. All your Excel actions from now on are being recorded.

Select

cell A1

and enter the first heading:

Employee Name

.

Move to cell

B1

and enter:

First Name

.

Enter the remaining headings in cells

C1: F1 (Last Name, Hourly Rate, Hours Worked, Total Wages)

.

Select

A1:F1

and apply the bold formatting to the selection by pressing the

B

button in the Font group of the Ribbon’s Home tab.

With the range

A1:F1

still selected, choose

Home | Cells | Format | Autofit Column Width

.

Click the

Stop Recording

button in the status bar as shown in

Figure 1.11

or choose

View | Macros | Stop Recording

.

You have just recorded your second macro. The Employee Wages worksheet should now have the required headings in Row 1.

Using Relative or Absolute References in Macros

The Excel macro recorder can record your actions using absolute or relative cell references (see Figure 1.13).

To have your macro execute the recorded action in a specific cell, no matter what cell is selected during the execution of the macro, use absolute cell addressing. Absolute cell references have the following form: $A$1, $C$5, etc. By default, the Excel macro recorder uses absolute references. Before you begin to record a new macro, make sure the Use Relative References option is not selected when you click the Macros button as shown in

Figure 1.13

.

To have your macro perform the action in any cell, be sure to select the Use Relative References option before you choose the Record Macro option. Relative cell references have the following form: A1, C5, etc. The Excel macro recorder will continue to use relative cell references until you exit Microsoft Excel or click the Use Relative References option again.

During the process of recording your macro, you may use both methods of cell addressing. For example, you may select a specific cell (e.g., $A$4), perform an action, and then choose another cell relative to the selected cell (e.g., C9, which is located five rows down and two columns to the right of the currently active cell $A$4). Relative references automatically adjust when you copy them, and absolute references don’t.

FIGURE 1.13 Excel macro recorder can record your actions using absolute or relative cell references. To make your selection, use the Macros drop-down on the Ribbon’s View tab.

Hands-On 1.5    Entering Employee Data (Macro Task 3)

Choose

View | Macros | Record Macro

(or you may click the Begin recording button located in the status bar).

Enter

Insert_EmployeeData

as the name for your macro.

Ensure that

This Workbook

is selected in the

Store macro

in list box.

Click

OK

. Excel turns on the macro recorder. All your Excel actions from now on are being recorded.

Enter employee data in columns

A, D,

and

E

as shown in

Figure 1.9

. Leave the First Name, Last Name, and Total Wages columns blank as they will be filled in later.

Click the

Stop Recording

button in the status bar as shown in

Figure 1.11

or choose

View | Macros | Stop Recording

. You have just recorded the third macro. The static data entry has been completed. We will now proceed to record macros that use formulas to fill the remaining columns of the worksheet.

Hands-On 1.6    Entering Formulas to Fill in Employee First Name (Macro Task 4)

Choose

View | Macros | Record Macro

(or you may click the

Begin recording

button, located in the status bar).

Enter

Get_FirstName

as the name for your macro.

Ensure that

This Workbook

is selected in the

Store macro in

list box.

Click

OK

. Excel turns on the macro recorder. All your Excel actions from now on are being recorded.

Enter the following formula in cell B2:

=LEFT(A2,FIND(" ", A2)-1)

Copy the formula down to cells B3:B7 by dragging the selection handle in the bottom right corner of cell B2. Excel fills in the first names of all employees.

Click the

Stop Recording

button in the status bar as shown in

Figure 1.11

or choose

View | Macros | Stop Recording

. You have just recorded a macro that makes use of a formula to retrieve employee first names from their full name. The next macro will populate the last name column using another formula.

Hands-On 1.7    Entering Formulas to Fill in Employee Last Name (Macro Task 5)

Choose

View | Macros | Record Macro

(or you may click the

Begin recording

button located in the status bar).

Enter

Get_LastName

as the name for your macro.

Ensure that

This Workbook

is selected in the

Store macro

in list box.

Click

OK

. Excel turns on the macro recorder. All your Excel actions from now on are being recorded.

Enter the following formula in cell

C2

:

=RIGHT(A2,LEN(A2)-FIND(" ", A2))

Copy the formula down to cells

C3:C7

by dragging the selection handle in the bottom right corner of cell C2. Excel fills in the last names of all employees.

Click the

Stop Recording

button in the status bar as shown in

Figure 1.11

or choose

View | Macros | Stop Recording

. You have just recorded a macro that makes use of a formula to retrieve employee last names from their full name. We have one more column to fill in before we can apply the final formatting to this worksheet.

Hands-On 1.8    Entering Formulas to Calculate Employee Total Wages (Macro Task 6)

Choose

View | Macros | Record Macro

(or you may click the

Begin recording

button located in the status bar).

Enter

CalculateWages

as the name for your macro.

Ensure that

This Workbook

is selected in the

Store macro

in list box.

Click

OK

. Excel turns on the macro recorder. All your Excel actions from now on are being recorded.

Select cells

F2:F7

and type the formula shown here. Press

Ctrl+Enter

to ensure that formula is entered into the selected range F2:F7.

=D2*E2

Apply Currency format to cells

F2:F7

.