Microsoft Excel 2021 Programming Pocket Primer

by Julitta Korol

Overview

No detailed description available for "Microsoft Excel 2021 Programming Pocket Primer".

Product Details

ISBN-13:	9781683928928
Publisher:	Mercury Learning and Information
Publication date:	10/24/2022
Series:	Pocket Primer
Pages:	262
Product dimensions:	6.00(w) x 9.00(h) x (d)

About the Author

Korol Julitta :

Since 1993, Julitta Korol has published two dozen computer books on using and programming Microsoft Excel and Microsoft Access. She has completed professional certifications by Microsoft Corporation and the Computing Technology Industry Association (CompTIA).

Table of Contents

Acknowledgments xi

Introduction xiii

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

Macros and VBA 2

Excel Macro-Enabled File Formats 2

Macro Security Settings 3

Enabling the Developer Tab in Excel 6

Using the Built-in Macro Recorder 8

Planning a Macro 8

Recording a Macro 9

Editing Recorded Macros 15

Macro Comments 20

Cleaning up the Macro Code 21

Running a Macro 23

Testing and Debugging a Macro 23

Saving and Renaming a Macro 25

Printing Macro Code 26

Improving Your Recorded Macros 26

Creating a Master Macro 28

Various Methods of Running Macros 29

Running the Macro Using a Keyboard Shortcut 29

Running the Macro from the Quick Access Toolbar 30

Running the Macro from a Worksheet Button 34

Summary 36

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

Understanding the Project Explorer Window 38

Understanding the Properties Window 39

Understanding the Code Window 40

Setting the VBE Options 41

Syntax and Programming Assistance 42

List Properties/Methods 42

List Constants 43

Parameter Info 44

Quick Info 45

Complete Word 45

Indent/Outdent 45

Comment Bio ck/Un comment Block 46

Using the Object Browser 46

Locating Procedures with the Object Browser 52

Using the VBA Object Library 52

Using the Immediate Window 54

Obtaining Information in the Immediate Window 57

Working with Worksheet Cells and Ranges 58

Using the Range Property 59

Using the Cells Property 59

Using the Offset Property 60

Using the Resize Property 61

Using the End Property 63

Moving, Copying, and Deleting Cells 63

Working with Rows and Columns 64

Obtaining Information about the Worksheet 65

Entering Data and Formatting Cells 65

Returning Information Entered in a Worksheet 66

Finding Out about Cell Formatting 66

Working with Workbooks and Worksheets 67

Working with Windows 69

Working with the Excel Application 70

Summary 71

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

Excel Objects, Properties, and Methods 73

Microsoft Excel Object Model 75

Writing Simple and Complex VBA Statements 76

Breaking Up Long VBA Statements 80

Saving Results of VBA Statements 80

Introducing Data Types 81

Using Variables 83

How to Create Variables 84

How to Declare Variables 85

Specifying the Data Type of a Variable 88

Assigning Values to Variables 90

Forcing Declaration of Variables 94

Understanding the Scope of Variables 96

Procedure-Level (Local) Variables 96

Module-Level Variables 96

Project-Level Variables 98

Lifetime of Variables 99

Finding a Variable Definition 99

Determining a Data Type of a Variable 99

Using Constants 101

Built-in Constants 102

Converting between Data Types 103

Using Static Variables in VBA Procedures 106

Using Object Variables in VBA Procedures 107

Using Specific Object Variables 110

Summary 110

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

Understanding Function Procedures 112

Creating a Function Procedure 112

Various Methods of Running Function Procedures 114

Running a Function Procedure from a Worksheet 115

Running a Function Procedure from Another VBA Procedure 117

Ensuring Availability of Your Custom Functions 117

Passing Arguments to Function Procedures 118

Specifying Argument Types 119

Passing Arguments by Reference and by Value 121

Using Optional Arguments 123

Testing a Function Procedure 124

Locating Built-in Functions 125

Getting to Know the MsgBox Function 125

Returning Values from the MsgBox Function 132

Getting to Know the InputBox Function 134

Determining and Converting Data Types 136

Using the InputBox Method 138

Summary 142

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

Relational and Logical Operators 143

Using If…Then Statement 144

Using If…Then…Else Statement 148

Using If…Then…ElseIf Statement 150

Nested If…Then Statements 152

Using the Select Case Statement 153

Using Is with the Case Clause 155

Specifying a Range of Values in a Case Clause 156

Specifying Multiple Expressions in a Case Clause 157

Writing a VBA Procedure with Multiple Conditions 157

Using Conditional Logic in Function Procedures 160

Summary 161

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

Introducing Looping Statements 163

Understanding Do…While and Do…Until Loops 164

Avoiding Infinite Loops 168

Executing a Procedure Line by Line 168

Understanding While…Wend Loop 169

Understanding For…Next Loop 170

Understanding For…Each…Next Loop 173

Exiting Loops Early 174

Using a Do…While Statement 175

Using Loops and Conditionals 176

Summary 177

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

Understanding Arrays 179

Declaring Arrays 181

Array Upper and Lower Bounds 183

Initializing and Filling an Array 183

Filling an Array Using Individual Assignment Statements 183

Filling an Array Using the Array Function 184

Filling an Array Using For…Next Loop 184

Using a One-Dimensional Array 185

Using a Two-Dimensional Array 187

Using a Dynamic Array 188

Using Array Functions 190

The Array Function 190

The IsArray Function 191

The Erase Function 191

The LBound and UBound Functions 192

Troubleshooting Errors in Arrays 193

Using the ParamArray Keyword 194

Data Entry with an Array 195

Sorting an Array with Excel 196

Summary 198

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

Working with Built-in Collections 199

Creating Your Own Collection 201

Adding Objects to a Custom Collection 201

Determining the Number of Items in Your Collection 203

Accessing Items in a Collection 203

Removing Items from a Collection 203

Updating Items in a Collection 204

Returning a Collection from a Function 207

Using Custom and Built-in Collections Together 209

Collections versus Arrays 214

Watching the Execution of Your VBA Procedures 214

Summary 219

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

Testing VBA Procedures 221

Stopping a Procedure 222

Using Breakpoints 223

When to Use a Breakpoint 227

Using the Immediate Window in Break Mode 228

Using the Stop and Assert Statements 228

Using the Watch Window 230

Removing Watch Expressions 234

Using Quick Watch 234

Using the Locals Windows and the Call Stack Dialog Box 235

Navigating with Bookmarks 237

Trapping Errors 238

Using the Err Object 239

Setting Error Trapping Options in a VBA Project 243

Stepping through VBA Procedures 244

Stepping Over a Procedure and Running to Cursor 245

Setting the Next Statement 246

Showing the Next Statement 247

Stopping and Resetting VBA Procedures 247

Terminating a Procedure Based on a Condition 247

Summary 250

Index 253