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

Index

Cover Title Page Copyright Dedication 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 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. Inserting and Naming a Worksheet (Macro Task 1) Hands-On 1.4. Inserting Column Headings and Applying Formatting (Macro Task 2) Hands-On 1.5. Entering Employee Data (Macro Task 3) Hands-On 1.6. Entering Formulas to Fill in Employee First Name (Macro Task 4) Hands-On 1.7. Entering Formulas to Fill in Employee Last Name (Macro Task 5) Hands-On 1.8. Entering Formulas to Calculate Employee Total Wages (Macro Task 6) Hands-On 1.9. Applying Table Format (Macro Task 7) Editing Recorded Macros Hands-On 1.10. Examining the Macro Code

Macro Comments Hands-On 1.11. Adding Comments to the Macro Code Cleaning Up the Macro Code Hands-On 1.12. Cleaning Up the Macro Code

Running a Macro Hands-On 1.13. Running a Macro Using the Macro Dialog Box Testing and Debugging a Macro Hands-On 1.14. Running a Macro from the VBE Screen Saving and Renaming a Macro Hands-On 1.15. Saving Macros and Running Macros from Another Workbook Printing Macro Code Hands-On 1.16. Printing Macro Code

Improving Your Recorded Macros Hands-On 1.17. Adding Visual Basic Statements to the Recorded Macro Code Creating a Master Macro Hands-On 1.18. Creating a Master Macro Procedure Various Methods of Running Macros

Running the Macro Using a Keyboard Shortcut Hands-On 1.19. Assigning a Macro to a Keyboard Shortcut Running the Macro from the Quick Access Toolbar Hands-On 1.20. Running a Macro from the Quick Access Toolbar Running the Macro from a Worksheet Button Hands-On 1.21. Running a Macro from a Button Placed on a Worksheet

Summary

Chapter 2: Excel Programming Environment – A Quick Overview of Its Tools and Features

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

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

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

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)

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

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

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

Getting to Know the InputBox Function

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

Using the InputBox Method Hands-On 4.14. Using the Excel 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 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

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 Hands-On 6.1. Writing a VBA Procedure with a Do… While Statement Hands-On 6.2. Writing a VBA Procedure with a Do… Until Statement Avoiding Infinite Loops Executing a Procedure Line by Line Hands-On 6.3. Executing a Procedure Line by Line Understanding While...Wend Loop Hands-On 6.4. Writing a VBA Procedure with a While… Wend Statement Understanding For...Next Loop Hands-On 6.5. Writing a VBA Procedure with a For… Next Statement Understanding For...Each...Next Loop Hands-On 6.6. Writing a VBA Procedure with a For Each… Next Statement Exiting Loops Early Hands-On 6.7. Writing a VBA Procedure with an Early Exit from a For Each…Next Statement Using a Do…While Statement Hands-On 6.8. Writing a VBA Procedure with a Do… While Statement Using Loops and Conditionals Hands-On 6.9. Writing a VBA Procedure with 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 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

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 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.3a. Creating a Class Module Variable Declarations Lab 8.3b. Declaring Members of the CAsset Class Defining the Properties for the Class Writing Property Procedures Lab 8.3c. Writing Property Procedures for the CAsset Class Writing Class Methods Lab 8.3d. Writing a Method for the CAsset Class Creating an Instance of a Class Lab 8.3e. Writing Code

Summary

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

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 a 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

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 Hands-On 10.1. Using the CurDir Function Changing the Name of a File or Folder Checking the Existence of a File or Folder Hands-On 10.2. Using the Dir Function Hands-On 10.3. Using the Dir Function in a Procedure Finding Out the Date and Time the File Was Modified Hands-On 10.4. Using the FileDateTime Function Finding Out the Size of a File (the FileLen Function) Hands-On 10.5. Using the FileLen Function Returning and Setting File Attributes (the GetAttr and SetAttr Functions) Hands-On 10.6. Returning File Attributes with the GetAttr Function Hands-On 10.7. Setting File Attributes with the SetAttr Function Changing the Default Folder or Drive (the ChDir and ChDrive Statements) Creating and Deleting Folders (the MkDir and RmDir Statements) Hands-On 10.8. Creating and Deleting Folders with the MkDir and RmDir Statements Copying Files (the FileCopy Statement) Hands-On 10.9. Copying Files with the FileCopy Statement Deleting Files (the Kill Statement) Hands-On 10.10. Deleting Files with the Kill Statement

Summary

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

Hands-On 11.1. Controlling Objects with Windows Script Host (WSH) Finding Information about Files with WSH Hands-On 11.2. Using WSH to Obtain File Information

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 Hands-On 11.3. Running Other Applications Using the WSH Object Obtaining Information about Windows Retrieving Information about the User, Domain, or Computer Creating Shortcuts Hands-On 11.4. Creating Shortcuts Using the WshShell Object 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 Hands-On 12.1. Reading File Contents with the Open and Line Input # Statements Reading Characters from Sequential Files Hands-On 12.2. Reading Characters from Sequential Files Hands-On 12.3. Printing File Contents to a Worksheet Text Box Reading Delimited Text Files Hands-On 12.4. Reading a Comma-Delimited (CSV) File with the Input # Statement Writing Data to Sequential Files

Using Write # and Print # Statements Hands-On 12.5. Using the Write # Statement to Write Data to a File

Working with Random-Access Files Hands-On 12.6. Creating a Random-Access Database with a User-Defined Data Type Working with Binary Files Hands-On 12.7. Mastering the Get and Put Statements Summary

Part III: Controlling other Applications with Vba

Chapter 13: Using Excel VBA to Interact with Other Applications

Launching Applications Hands-On 13.1. Using the Shell Function to Activate the Control Panel Moving between Applications Controlling Another Application Hands-On 13.2. Using the SendKeys Statement in a VBA Procedure Other Methods of Controlling Applications

Understanding Automation Understanding Linking and Embedding Hands-On 13.3. Experiments with Linking and Embedding

Linking and Embedding with VBA Hands-On 13.4. Writing a Procedure to Embed a Word Document in a Worksheet

COM and Automation Understanding Binding

Late Binding Hands-On 13.5. Printing a Word Document with VBA Early Binding

Establishing a Reference to a Type Library Hands-On 13.6. Setting Up a Reference to a Type Library

Creating Automation Objects

Using the CreateObject Function Creating a New Word Document Using Automation Hands-On 13.7. Creating a New Word Document with VBA Using the GetObject Function Opening an Existing Word Document Hands-On 13.8. Opening and Modifying a Word Document with VBA Using the New Keyword Using Automation to Access Microsoft Outlook Hands-On 13.9. Bringing Outlook Contacts to Excel

Summary

Chapter 14: Using Excel with Microsoft Access

Object Libraries

Setting Up References to Object Libraries Hands-On 14.1. Establishing a Reference to the Access Object Library

Connecting to Access Opening an Access Database

Using Automation to Connect to an Access Database Hands-On 14.2. Opening an Access Database Using Automation Using DAO to Connect to an Access Database Hands-On 14.3. Opening an Access Database with DAO Using ADO to Connect to an Access Database Hands-On 14.4. Opening an Access Database with ADO

Performing Access Tasks from Excel

Creating a New Access Database with DAO Hands-On 14.5. Creating a New Access Database Opening an Access Form Hands-On 14.6. Opening an Access Form from a VBA Procedure Opening an Access Report Hands-On 14.7. Opening an Access Report Creating a New Access Database with ADO Hands-On 14.8. Creating a New Access Database with ADO Running a Select Query Hands-On 14.9. Running an Access Select Query Running a Parameter Query Hands-On 14.10. Running an Access Parameter Query Calling an Access Function

Retrieving Access Data into an Excel Worksheet

Retrieving Data with the GetRows Method Hands-On 14.11. Retrieving Access Data Using the GetRows Method Retrieving Data with the CopyFromRecordset Method Hands-On 14.12. Retrieving Access Data Using the CopyFrom Recordset Method Retrieving Data with the TransferSpreadsheet Method Hands-On 14.13. Retrieving Access Data Using the TransferSpreadsheet Method Using the OpenDatabase Method Hands-On 14.14. Retrieving Access Data into a PivotTable Using the OpenDatabase Method Creating a Text File from Access Data Hands-On 14.15. Creating a Text File from Access Data Creating a Query Table from Access Data Hands-On 14.16. Creating a Query Table from Access Data Creating an Embedded Chart from Access Data Hands-On 14.17. Creating an Embedded Chart from Access Data

Transferring the Excel Worksheet to an Access Database

Linking an Excel Worksheet to an Access Database Hands-On 14.18. Linking an Excel Worksheet to an Access Database Importing an Excel Worksheet to an Access Database Placing Excel Data in an Access Table Hands-On 14.19. Creating an Access Table and Populating It with the Worksheet Data

Summary

Part IV: Enhancing the user Experience

Chapter 15: Event-Driven Programming

Introduction to Event Procedures Writing Your First Event Procedure Hands-On 15.1. Writing an Event Procedure Enabling and Disabling Events Hands-On 15.2. Disabling a Custom Event Procedure Event Sequences Worksheet Events

Worksheet_Activate() Hands-On 15.3. Writing the Worksheet_Activate() Event Procedure Worksheet_Deactivate() Hands-On 15.4. Writing the Worksheet_Deactivate() Event Procedure Worksheet_SelectionChange() Hands-On 15.5. Writing the Worksheet_SelectionChange() Event Procedure Worksheet_Change() Hands-On 15.6. Writing the Worksheet_Change() Event Procedure Worksheet_Calculate() Hands-On 15.7. Writing the Worksheet_Calculate() Event Procedure Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Hands-On 15.8. Writing the Worksheet_BeforeDoubleClick() Event Procedure Worksheet_BeforeRightClick (ByVal Target As Range, Cancel As Boolean) Hands-On 15.9. Writing the Worksheet_BeforeRightClick() Event Procedure

Workbook Events

PivotTable Events Chart Events Hands-On 15.20. Creating Charts for Trying Out 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) Hands-On 15.21. Writing Event Procedures for a Chart Sheet

Writing Event Procedures for Embedded Charts Hands-On 15.22. Writing the Chart_Activate() Event Procedure for an Embedded Chart

Events Recognized by the Application Object Hands-On 15.23. Writing Event Procedures for the Application Object Query Table Events Hands-On 15.24. Writing Event Procedures for a Query Table Other Excel Events

OnTime Method OnKey Method

Summary

Chapter 16: Using Dialog Boxes

Excel Dialog Boxes Hands-On 16.1. Using Excel Dialog Boxes from the Immediate Window File Open and File Save As Dialog Boxes Hands-On 16.2. Using the FileDialog Object from the Immediate Window Filtering Files Hands-On 16.3. Writing a List of Default File Filters to an Excel Worksheet Selecting Files Hands-On 16.4. Loading Files into a Worksheet Listbox Control GetOpenFilename and GetSaveAsFilename Methods

Using the GetOpenFilename Method Hands-On 16.5. Using the GetOpenFilename Method Using the GetSaveAsFilename Method Hands-On 16.6. Using the GetSaveAsFilename Method

Summary

Chapter 17: Creating Custom Forms

Creating Forms

Tools for Creating User Forms Hands-On 17.1. Adding an ActiveX Date and Time Picker Control to the Toolbox. 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 Hands-On 17.1a. Inserting a New Form (Step 1) Changing the Size of the Form Hands-On 17.1b. Adjusting the Size of the Form (Step 2) Adding Buttons, Checkboxes, and Other Controls to a Form Hands-On 17.1c. Adding Buttons, Checkboxes, and Other Controls to a Form (Step 3) Changing Control Names and Properties Hands-On 17.1d. Naming Form Controls (Step 4) Setting the Tab Order Hands-On 17.1e. Setting the Tab Order in a Form (Step 5) Preparing a Worksheet to Store Custom Form Data Hands-On 17.1f. Preparing a Worksheet to Store Custom Form Data (Step 6) Displaying a Custom Form Hands-On 17.1g. Displaying a Custom Form (Step 7) Understanding Form and Control Events Writing VBA Procedures to Respond to Form and Control Events

Writing a Procedure to Initialize the Form Hands-On 17.1h. Writing a Procedure to Initialize the Form (Step 8) Writing a Procedure to Populate the Listbox Control Hands-On 17.1i. Populating the Listbox Control (Step 9) Writing a Procedure to Control Option Buttons Hands-On 17.1j. Controlling Option Buttons (Step 10) Writing Procedures to Synchronize the Text Box with the Spin Button Hands-On 17.1k. Synchronizing the Text Box with the Spin Button (Step 11) Writing a Procedure that Closes the User Form Hands-On 17.1l. Writing a Procedure that Closes the Form (Step 12) Transferring Form Data to the Worksheet Hands-On 17.1m. Transferring Form Data to the Worksheet (Step 13)

Using the Info Survey Application Hands-On 17.1n. Using the Info Survey Application (Step 14)

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 Hands-On 18.1. Using Icon Sets Programmatically Formatting with Themes Formatting with Shapes Formatting with Sparklines

Understanding Sparkline Groups Programming Sparklines with VBA Hands-On 18.2. Retrieving Information about Sparklines Hands-On 18.3. Using VBA to Create Sparklines

Formatting with Styles

Summary

Chapter 19: Context Menu Programming and Ribbon Customizations

Working with Context Menus

Modifying a Built-In Context Menu Hands-On 19.1. Enumerating Context Menus Hands-On 19.2. Adding a New Item to a 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 Hands-On 19.3. Using Context Menus on User Forms Finding a FaceID Value of an Image

A Quick Overview of the Ribbon Interface Ribbon Programming with XML and VBA

Creating the Ribbon Customization XML Markup Hands-On 19.4. Creating an XML Document with Ribbon Customizations Loading Ribbon Customizations Hands-On 19.5. Adding VBA Code for Use by the Ribbon Customizations

Errors on Loading Ribbon Customizations

Using Images in Ribbon Customizations 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 Microsoft Office Button Menu in Excel 2016 Customizing the Quick Access Toolbar (QAT) Modifying Context Menus Using Ribbon Customizations Hands-On 19.6. Customizing a Context Menu 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 Hands-On 20.1. Printing Page Setup Settings to the Immediate Window

Previewing a Worksheet Changing the Active Printer Hands-On 20.2. Setting a Default Printer When Opening a Specific Workbook Printing a Worksheet with VBA Disabling Printing and Print Previewing Using Printing Events Hands-On 20.3. Automatically Adding a Footer to Each Workbook Sending Email from Excel

Sending Email Using the SendMail Method Hands-On 20.4. Using the SendMail Method to Send Email Sending Email Using the MsoEnvelope Object Hands-On 20.5. Sending Email Using the MsoEnvelope Object Sending Bulk Email from Excel via Outlook Hands-On 20.6. Sending Bulk Email from Excel

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 Hands-On 21.1. Obtaining Table Data from a Microsoft Access Database Creating a Table Using VBA Hands-On 21.2. Creating a Table Using VBA Understanding Column Headings in the Table Hands-On 21.3. Adding Headings to a Table Multiple Tables in a Worksheet Working with the Excel ListObject Hands-On 21.4. Defining Table Names 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 Slicers Manually Hands-On 22.10. Creating a Slicer Using the Ribbon Working with Slicers Using VBA Hands-On 22.11. Creating a Slicer Using VBA Hands-On 22.12. Retrieving Information about Slicers

Data Model Functionality and PivotTables Hands-On 22.13. Creating a Data Model and Exposing its Data through a Pivot Table Programmatic Access to the Data Model Hands-On 22.14. Creating a Data Model and Exposing its Data through a Pivot Table Summary

Chapter 23: Getting and Transforming Data in Excel 2016

Using the New Query button from the Get & Transform Ribbon Group Understanding Get & Transform Queries Custom Project 23.1. Creating a Query from Multiple Sources Advanced Editor Get & Transform Query vs Excel Formula Language and Excel VBA Learning about Various M Language Functions Hands-On 23.1. Using a Blank Query for trying out M functions Creating a Query from a Table Get & Transform and VBA Support Hands-On 23.2. Automating the Creation of Queries Additional Learning Resources for Using Get & Transform Features Summary

Part VI: Taking Charge of Programming Environment

Chapter 24: Programming The Visual Basic Editor (VBE)

The Visual Basic Editor Object Model Hands-On 24.1. Trusting Access to the VBA Project Object Model Understanding the VBE Objects Accessing the VBA Project Hands-On 24.2. Checking Access to the VBA Project Using VBA Finding Information about a VBA Project VBA Project Protection Hands-On 24.3. Using VBA to Determine whether the VBA Project Is Protected Working with Modules

Listing All Modules in a Workbook Hands-On 24.4. Listing All Workbook Modules Adding a Module to a Workbook Hands-On 24.5. Adding a Module to a Workbook Removing a Module Hands-On 24.6. Removing a Module from the Workbook Deleting All Code from a Module Hands-On 24.7. Deleting a Moduleis Code Deleting Empty Modules Hands-On 24.8. Deleting an Empty Module Copying (Exporting/Importing) a Module Hands-On 24.9. Exporting/Importing a Module Copying (Exporting/Importing) All Modules Hands-On 24.10. Exporting/Importing All Modules

Working with Procedures

Listing All Procedures in All Modules Hands-On 24.11. Listing Procedures in Modules Adding a Procedure Hands-On 24.12. Adding a Procedure to a Module Using VBA Deleting a Procedure Hands-On 24.13. Deleting a Procedure from a Module Using VBA Creating an Event Procedure Hands-On 24.14. Creating an Event Procedure with VBA

Working with UserForms

Creating and Manipulating UserForms Hands-On 24.15. Creating a Custom UserForm with VBA Copying UserForms Programmatically Hands-On 24.16. Copying a UserForm with VBA

Working with References

Creating a List of References Hands-On 24.17. Listing VBA Project References and Components Using VBA Adding a Reference Hands-On 24.18. Adding a Project Reference with VBA Hands-On 24.19. Obtaining Information about Installed VBA Libraries from the Registry Removing a Reference Hands-On 24.20. Removing a Reference Using VBA Checking for Broken References Hands-On 24.21. Checking for Broken References in a VBA Project

Working with Windows Hands-On 24.22. Closing the Immediate Window and Listing All Open Windows in the VBE Screen Working with VBE Menus and Toolbars

Generating a Listing of VBE CommandBars and Controls Hands-On 24.23. Listing VBE CommandBars and Controls Adding a CommandBar Button to the VBE Hands-On 24.24. Modifying the VBE Tools Menu Removing a CommandBar Button from the VBE Hands-On 24.25. Removing a Custom Option from the VBE Menu

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 API Functions Understanding the API Data Types and Constants Using Constants with Windows API Functions

64-Bit Office and Windows API Accessing Windows API Documentation Using Windows API Functions in Excel Hands-On 25.1. Retrieving Information about the Computer/User Hands-On 25.2. Enhancing a VBA UserForm by Calling Windows API Functions Summary

Part VII: Excel and Web Technologies

Chapter 26: HTML Programming and Web Queries

Creating Hyperlinks Using VBA Hands-On 26.1 Using VBA to Place a Hyperlink in a Worksheet Cell Hands-On 26.2. Using a Search Engine to Find Text Entered in a Worksheet Cell Creating and Publishing HTML Files Using VBA Hands-On 26.3. Creating and Publishing an Excel Worksheet with an Embedded Chart Web Queries Creating and Running Web Queries with VBA Hands-On 26.4. Creating a Web Query in an Active Workbook

Web Queries with Parameters Hands-On 26.5. Checking for POST and GET Methods in Web Pages Static and Dynamic Parameters Hands-On 26.6. Using the GET Method to Send Parameters to the Web Server Dynamic Web Queries Hands-On 26.7. Using the GET Method for Sending Dynamic Parameters

Refreshing Data Summary

Chapter 27: Excel and Active Server Pages

Introduction to Active Server Pages The ASP Object Model HTML and VBScript Creating an ASP Page Hands-On 27.1. Creating an ASP Page that Retrieves Data from a Database Table into an Excel Worksheet Installing Internet Information Services (IIS) Hands-On 27.2. Enabling Classic ASP in Windows Creating a Virtual Directory Hands-On 27.3. Creating a Virtual Directory Setting ASP Configuration Properties Hands-On 27.4. Configuring ASP Properties Turning Off Friendly HTTP Error Messages Hands-On 27.5. Turning Off Friendly HTTP Error Messages Running Your First ASP Script Hands-On 27.6. Running Your First ASP Script Sending Data from an HTML Form to an Excel Workbook Hands-On 27.7. Sending Data from an HTML Form to an Excel Workbook Hands-On 27.8. Creating a Web Based Form for Data Collection Sending Excel Data to the Internet Browser Hands-On 27.9. Using the GetString Method to Retrieve Data from an Excel File Summary

Chapter 28: Using XML in Excel 2016

What Is XML? Well-Formed XML Documents Validating XML Documents Editing and Viewing an XML Document Hands-On 28.1. Adding a Comment to an XML Document Hands-On 28.2. Viewing an XML Document in the Internet Browser Opening an XML Document in Excel Hands-On 28.3. Opening an XML Document in Excel Working with XML Maps Hands-On 28.4. Mapping Schema Elements to Worksheet Cells Working with XML Tables

Exporting an XML Table Hands-On 28.5. Exporting XML Data in Mapped Worksheet Cells 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 Hands-On 28.6. Using VBA to Program XML Maps Creating XML Schema Files Using XML Events The XML Document Object Model Hands-On 28.7. Setting up a Reference to DOM Hands-On 28.8. Reading an XML Document with DOM Working with XML Document Nodes Hands-On 28.9. Working with XML Document Nodes Retrieving Information from Element Nodes Hands-On 28.10. Obtaining Data from Element Nodes Hands-On 28.11. Obtaining Data from an Element Node Based on a Condition Hands-On 28.12. Finding a Specific Node Hands-On 28.13. Using a Conditional Expression with an Element Node XML via ADO

Saving an ADO Recordset to Disk as XML Hands-On 28.14. Saving an ADO Recordset as an XML Document Loading an ADO Recordset Hands-On 28.15. Opening a Persisted Recordset with XML Data Saving an ADO Recordset into the DOMDocument60 Object Hands-On 28.16. Modifying a Recordset Saved into the XML DOMDocument Object

Understanding Namespaces Understanding Open XML Files Manipulating Open XML Files with VBA Hands-On 28.17. Unzipping an Excel 2016 File with VBA Hands-On 28.18. Zipping Files to Create an Excel 2016 Package Container Hands-On 28.19. Retrieving Unique Text Values from the sharedStrings XML File Hands-On 28.20. Modifying the sharedString XML File Hands-On 28.21. Retrieving All Text Values from the XML Part to a Worksheet Hands-On 28.22. Changing and Removing Elements in an XML Part Summary

Index

← Prev
Back
Next →

← Prev
Back
Next →