Microsoft Excel 2021 Programming Pocket Primer

✍ Scribed by Julitta Korol

• Publisher: Mercury Learning and Information
• Year: 2022
• Tongue: English
• Leaves: 279
• Category: Library

✦ Synopsis

As part of the successful Pocket Primer series, this book was designed for someone like you who needs to master Excel programming fundamentals without spending too much time. All you need is a short book to get you started. It 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 also apply to, say, Access programming. The book is divided into nine chapters that progressively introduce you to programming Microsoft Excel 2021.

Features:

• Includes a companion disc with all of the “Hands-On” files needed to complete the chapter projects and all the images from the text (also available from the publisher by writing to [email protected]
• Introduces you to using the VBA programming language in Microsoft Excel 2021.

✦ Table of Contents

Cover
Title
Copyright Page
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 Variabl
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 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 Condition
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 Collections of Objects
Declaring and Using a Custom 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 Executive 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