Excel Programming by Korol, Julitta -- Read -- Imperial Library of Trantor

Index

Cover Title Copyright Contents Dedication Acknowledgments Introduction 1. Getting Started with Excel VBA

Introduction Macros and VBA

Excel Macro-Enabled File Formats Macro Security Settings

Enabling the Developer Tab in Excel Hands-On 1.1. Setting Up Excel for Macro Development Using the Built-In Macro Recorder

Planning a Macro Hands-On 1.2. Getting Things Ready for Macro Recording Recording a Macro Hands-On 1.3. Recording a Macro that Applies Formatting to a Worksheet

Using Relative or Absolute References in Macros Hands-On 1.4. Recording a Macro that Removes Formatting from a Worksheet

Editing a Macro Hands-On 1.5. Examining the Macro Code

Macro Comments Hands-On 1.6. Adding Comments to the Macro Code Cleaning Up the Macro Code Hands-On 1.7. Cleaning Up the Macro Code

Running a Macro Hands-On 1.8. Running a Macro Using the Macro Dialog Box Testing and Debugging a Macro Hands-On 1.9. Running a Macro from the VBE Screen Saving and Renaming a Macro Hands-On 1.10. Saving Macros and Running Macros from Another Workbook Printing Macro Code Hands-On 1.11. Printing Macro Code

Improving Your Recorded Macros Hands-On 1.12. Recording Additional Features for the Existing Macro in a New Macro Hands-On 1.13. Adding Additional Code to the Existing Macro Hands-On 1.14. Adding Visual Basic Statements to the Recorded Macro Code Various Methods of Running Macros

Running the Macro Using a Keyboard Shortcut Hands-On 1.15. Assigning a Macro to a Keyboard Shortcut Running the Macro from the Quick Access Toolbar Hands-On 1.16. Running a Macro from the Quick Access Toolbar Running the Macro from a Worksheet Button Hands-On 1.17. Running a Macro from a Button Placed on a Worksheet

Summary

2. Getting to Know Visual Basic Editor (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 Hands-On 2.1. Indenting/Outdenting Visual Basic Code Comment Block/Uncomment Block

Using the Object Browser Hands-On 2.2. Writing a VBA Procedure to Move a Text Box on the Worksheet Hands-On 2.3. Writing a VBA Procedure to Move a Circle on the Worksheet

Locating Procedures with the Object Browser Hands-On 2.4. Using Object Browser to Locate VBA Procedures

Using the VBA Object Library Hands-On 2.5. Writing a VBA Procedure to Create a Folder in Windows Using the Immediate Window Hands-On 2.6. Entering and Executing VBA Statements in the Immediate Window

Obtaining Information in the Immediate Window Hands-On 2.7. Obtaining Information in the Immediate Window

Working with Worksheet Cells and Ranges

Using the Range Property Hands-On 2.8. Practice Using the Range Property to Select Worksheet Cells Using the Cells Property Hands-On 2.9. Practice Using the Cells Property to Select Worksheet Cells (Part I) Hands-On 2.10. Practice Using the Cells Property to Select Worksheet Cells (Part II) Using the Offset Property Hands-On 2.11. Selecting Cells Using the Offset Property Using the Resize Property Hands-On 2.12. Writing a VBA Statement to Resize a Selection of Cells Using the End Property Hands-On 2.13. Selecting Cells Using the End Property Moving, Copying, and Deleting Cells Hands-On 2.14. Moving, Copying, and Deleting Cells

Working with Rows and Columns Hands-On 2.15. Selecting Entire Rows and Columns

Obtaining Information about the Worksheet Hands-On 2.16. Counting Rows and Columns

Entering Data and Formatting Cells Hands-On 2.17. Using VBA Statements to Enter Data in a Worksheet

Returning Information Entered in a Worksheet Finding Out about Cell Formatting

Working with Workbooks and Worksheets Hands-On 2.18. Working with Workbooks Hands-On 2.19. Working with Worksheets Working with Windows Hands-On 2.20. Working with Windows Working with the Excel Application Hands-On 2.21. Working with the Excel Application Summary

3. Excel VBA Fundamentals

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 Hands-On 3.1. Writing a VBA Procedure with Variables Forcing Declaration of Variables Hands-On 3.2. Writing a VBA Procedure with Explicitly Declared Variables Understanding the Scope of Variables

Procedure-Level (Local) Variables Module-Level Variables Hands-On 3.3. Writing a VBA Procedure with a Module-Level Variable Project-Level Variables

Lifetime of Variables Finding a Variable Definition Determining a Data Type of a Variable Hands-On 3.4. Using the Built-In VarType Function

Using Constants

Built-In Constants Hands-On 3.5. Viewing Excel Constants in the Object Browser

Converting between Data Types Hands-On 3.6. Using Data Type Conversion Functions in VBA Using Static Variables in VBA Procedures Hands-On 3.7. Writing a VBA Procedure with a Static Variable Using Object Variables in VBA Procedures Hands-On 3.8. Writing a VBA Procedure with Object Variables

Using Specific Object Variables

Summary

4. Excel VBA Functions

Understanding Function Procedures

Creating a Function Procedure Hands-On 4.1. Writing a Simple Function Procedure

Various Methods of Running Function Procedures

Running a Function Procedure from a Worksheet Hands-On 4.2. Executing a Function Procedure from within an Excel Worksheet Running a Function Procedure from Another VBA Procedure Hands-On 4.3. Executing a Function from a VBA Procedure

Ensuring Availability of Your Custom Functions Passing Arguments to Function Procedures Hands-On 4.4. Passing Arguments to Functions (Example 1) Hands-On 4.5. Passing Arguments to Functions (Example 2)

Specifying Argument Types Hands-On 4.6. Passing Arguments to Functions (Example 3) Passing Arguments by Reference and Value Hands-On 4.7. Passing Arguments to Functions (Example 4) Using Optional Arguments Hands-On 4.8. Writing Functions with Optional Arguments

Testing a Function Procedure Locating Built-In Functions Getting to Know the MsgBox Function Hands-On 4.9. Formatting Text for Display in the MsgBox Function Hands-On 4.10. Using the MsgBox Function with Arguments (Example 1) Hands-On 4.11. Using the MsgBox Function with Arguments (Example 2)

Returning Values from the MsgBox Function Hands-On 4.12. Using the MsgBox Function with Arguments (Example 3)

Getting to Know the InputBox Function Hands-On 4.13. Using the InputBox Function (Example 1)

Determining and Converting Data Types Hands-On 4.14. Using the InputBox Function (Example 2)

Using the InputBox Method Hands-On 4.15. Using the Excel InputBox Method Summary

5. Adding Decisions to Your Excel VBA Programs

Relational and Logical Operators Using If...Then Statement Hands-On 5.1. Evaluating Conditions in the Immediate Window Hands-On 5.2. Writing a VBA Procedure with a Simple If…Then Statement Using If...Then...Else Statement Hands-On 5.3. Writing a VBA Procedure with an If…Then…Else Statement Using If...Then...ElseIf Statement Hands-On 5.4. Writing a VBA Procedure with an If…Then…ElseIf Statement Nested If…Then Statements Using the Select Case Statement Hands-On 5.5. Writing a VBA Procedure with a 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 Hands-On 5.6. Writing a VBA Procedure with Multiple Conditions Using Conditional Logic in Function Procedures Hands-On 5.7. Writing a Function Procedure with a Select Case Statement Summary

6. Adding Repeating Actions to Your VBA Programs

7. Keeping Track of Multiple Values Using 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 Hands-On 7.1. Using a One-Dimensional Array Using a Two-Dimensional Array Hands-On 7.2. Storing Data in a Two-Dimensional Array Using a Dynamic Array Hands-On 7.3. Loading Worksheet Data into an Array Using Array Functions

The Array Function Hands-On 7.4. Using the Array Function The IsArray Function Hands-On 7.5. Using the IsArray Function The Erase Function Hands-On 7.6. Using the Erase Function The LBound and UBound Functions Hands-On 7.7. Using the LBound and UBound Functions

Troubleshooting Errors in Arrays Using the ParamArray Keyword Hands-On 7.8. Passing an Array to Procedures Using the ParamArray Keyword Data Entry with an Array Hands-On 7.9. Using the Array Function to Enter Headings in a Worksheet Sorting an Array with Excel Hands-On 7.10. Using Excel to Sort a VBA Array Summary

8. Keeping Track of Multiple Values Using Object Collections

Working with Collections of Objects

Declaring and Using a Custom Collection Adding Objects to a Custom Collection Hands-On 8.1. Using a Custom Collection Object Removing Objects from a Custom Collection Hands-On 8.2. Removing Items from a Custom Collection

Creating and Using Custom Objects

Lab 8.1a. Creating a Class Module Variable Declarations Lab 8.1b. Declaring Members of the CAsset Class Defining the Properties for the Class Writing Property Procedures Lab 8.1c. Writing Property Procedures for the CAsset Class Writing Class Methods Lab 8.1d. Writing a Method for the CAsset Class Creating an Instance of a Class Lab 8.1e. Writing Code

Summary

9. Getting to Know Built-In Tools for Testing and Debugging

Testing VBA Procedures Stopping a Procedure Using Breakpoints Hands-On 9.1. Setting Breakpoints in a VBA Procedure

When to Use a Breakpoint

Using the Immediate Window in Break Mode Using the Stop and Assert Statements Using the Watch Window Hands-On 9.2. Watching the Values of VBA Expressions

Removing Watch Expressions

Using Quick Watch Hands-On 9.3. Using the Quick Watch Dialog Box Using the Locals Windows and the Call Stack Dialog Box Hands-On 9.4. Using the Locals and Call Stack Windows Navigating with Bookmarks Trapping Errors

Using the Err Object Hands-On 9.5. Writing a VBA Procedure with Error-Handling Code Setting Error Trapping Options in Your VBA Project

Stepping through VBA Procedures Hands-On 9.6. Stepping through a VBA Procedure

Stepping Over a Procedure and Running to Cursor Hands-On 9.7. Stepping over a Procedure Setting the Next Statement Showing the Next Statement Stopping and Resetting VBA Procedures

Summary

Index

← Prev
Back
Next →

← Prev
Back
Next →