Microsoft Excel 2021 Programming Pocket Primer E-Book

Mercury Learning and Information

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

Seitenzahl: 400

Veröffentlichungsjahr: 2024

Beschreibung

This book, part of the successful Pocket Primer series, is designed for those who need to quickly master Excel programming fundamentals. It offers a concise, efficient introduction to VBA, ensuring you only learn what you need to feel comfortable with Excel VBA. The concepts learned in this book also apply to Access programming. The nine chapters progressively introduce you to programming in Microsoft Excel 2021.
Starting with a quick guide to Excel macros and the VBA programming environment, the book moves on to fundamental concepts and writing function procedures. You will learn to add decision-making capabilities and looping statements to your programs, store and manage multiple values using arrays and collections, and utilize built-in tools for testing and debugging your code.
By the end of the course, you will have a solid foundation in Excel VBA, enabling you to automate tasks and enhance your productivity. This structured approach makes the book an essential resource for mastering Excel VBA efficiently and effectively.

Leseprobe

Microsoft® Excel® 2021 Programming

Pocket Primer

LICENSE, DISCLAIMER OF LIABILITY, AND LIMITED WARRANTY

By purchasing or using this book and disc (the “Work”), you agree that this license grants permission to use the contents contained herein, including the disc, but does not give you the right of ownership to any of the textual content in the book / disc 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 sole remedy in the event of a claim of any kind is expressly limited to replacement of the book and/or disc, 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.

Companion files are also available for downloading by writing to [email protected].

Microsoft® Excel® 2021 Programming

Pocket Primer

Julitta Korol

Copyright ©2022 by Mercury Learning and Information LLC. 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] 800-232-0223

J. Korol. Microsoft® Excel 2021 Programming Pocket Primer. ISBN: 978-1-68392-892-8

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: 2022941631

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

Our titles are available for adoption, license, or bulk purchase by institutions, corporations, etc. For additional information, please contact the Customer Service Dept. at 800-232-0223(toll free).

All of our titles are available in digital format at academiccourseware.com and other digital vendors. Companion files (figures and code listings) 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.

Contents

Acknowledgments

Introduction

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 Fnction 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 VBA Procedures

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

Index

Acknowledgments

As years pass and we gain more and more knowledge on a particular subject there is a tendency to publish books for people who want to know it all. But the truth is that we really don’t have time to read all the printed pages when we are just getting started in a new subject. I thank my publisher, David Pallai, for continuing to publish this smaller book that serves as a starting point for anyone attempting to get into VBA programming in Excel. I hope that you as a reader of this primer book will appreciate this short book and find that the knowledge gained from its pages will not only allow you to continue your programming journey, but also take you places you never thought possible.

I’m also thankful to Jennifer Blaney for her expert management of this book project. I am grateful to the compositor, Swaradha Typesetting, for all of the composition efforts that gave this book the easy-to-follow look and feel.

Julitta Korol Long Island, New York August 2022

Introduction

I’ve been working with Excel since the very beginning. Database and application concepts were completely new to me, but the Excel interface made it a pleasure to work with almost daily. Step by step I acquired the skills of database management and then programming. I learned the latter by trial and error. When the first consulting opportunity came up to use my Excel skills, I found that I barely knew enough to get started. But challenges do not scare me. I was eager to learn on the job. My first Excel programming project was designing a custom quotation system for an automotive manufacturer. Despite my limited prior exposure to the programming concepts, I was able to deliver a system that automated a big chunk of work for that company. Another project automated several dozens of budget worksheets for a school district. How was I able to do this? I find reading and doing is the first step towards mastering a skill like programming. This book presents enough programming concepts to get you started tackling your own Excel challenges. This is not a book about using Excel. I assume you are already familiar with most tasks that you can achieve using Excel built-in commands. But if you are ready to look beyond the standard user interface, you have come to the right place and have made a decision that will bring a whole set of new possibilities to your Excel experience. So, let’s forget the menus for now. Do your own thing. Automating Excel is something everyone can do. With the right training, that is. This book’s purpose is to introduce you to the Excel built-in language, known as Visual Basic for Applications (VBA). With VBA you can begin delegating repetitive tasks to Excel while freeing your time for projects that are more fun to do. Besides, knowing how to program these days is a sought-after and lucrative skill.

This book was designed for someone like you who needs to master Excel programming fundamentals without spending too much time. Most of the time all you need is a short book to get you started. It’s less overwhelming to deal with a new subject in smaller chunks. The Programming Pocket Primer series will show you only the things you need to know to feel at home with VBA. What you learn in this book on Excel programming will apply to, say, Access programming. Just take a quick look my other book, the Microsoft Access 2021 Programming Pocket Primer, to see what I mean. How’s that for knowledge transfer? Learn in Excel and use it in Excel or other Microsoft 365 applications. I call this sweet learning.

If you are looking for in-depth knowledge of Excel programming (and have time to work through a 1,000-page book), then go ahead and try some of my more complete, programming titles available from Mercury Learning and Information.

Excel is about doing and so is this book. So do not try to read it while not at the computer. You can sit, stand, or lie down; it does not matter. But you do need to work with this book. Do the examples, read the explanations. Do this until it becomes easy to do without the step-by-step instructions. Do not skip anything as the concepts in later chapters build on material introduced earlier.

CHAPTER OVERVIEW

Before you get started, allow me to give you a short overview of the things you’ll be learning as you progress through this primer book. Microsoft Excel 2021 Programming Pocket Primer is divided into nine chapters that progressively introduce you to programming Microsoft Excel.

Chapter 1: Getting Started with Excel VBA–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 you 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 the basic skills of using collections for tracking and maintaining 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 in order to continue your Excel VBA learning path. The skills obtained in this 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 so on. And when you are ready to get more Excel VBA skills under your belt, you can jump right into Chapter 10 in my more complete book - Excel 2021 / Microsoft 365 Programming by Example also available from Mercury Learning and Information. (ISBN: 978-1-68392-886-7).

THE COMPANION FILES

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