Excel 2016 power programming with VBA

Excel® 2016 Power Programming with VBA
About the Authors
About the Technical Editor
Credits
Contents at a Glance
Table of Contents
Introduction
Part I: Introduction to Excel VBA
	Chapter 1: Essentials of Spreadsheet Application Development
		What Is a Spreadsheet Application?
		Steps for Application Development
		Determining User Needs
		Planning an Application That Meets  User Needs
		Determining the Most Appropriate User Interface
			Customizing the Ribbon
			Customizing shortcut menus
			Creating shortcut keys
			Creating custom dialog boxes
			Using ActiveX controls on a worksheet
			Executing the development effort
		Concerning Yourself with the End User
			Testing the application
			Making the application bulletproof
			Making the application aesthetically appealing and intuitive
			Creating a user Help system
			Documenting the development effort
			Distributing the application to the user
			Updating the application when necessary
		Other Development Issues
			The user’s installed version of Excel
			Language issues
			System speed
			Video modes
	Chapter 2: Introducing Visual Basic for Applications
		Getting a Head Start with the Macro Recorder
			Creating your first macro
			Comparing absolute and relative macro recording
			Other macro recording concepts
		Working with the Visual Basic Editor
			Understanding VBE components
			Working with the Project window
			Working with a Code window
			Customizing the VBA environment
			The Editor Format tab
			The General tab
			The Docking tab
		VBA Fundamentals
			Understanding objects
			Understanding collections
			Understanding properties
		Deep Dive: Working with Range Objects
			Finding the properties of the Range object
			The Range property
			The Cells property
			The Offset property
		Essential Concepts to Remember
		Don’t Panic—You Are Not Alone
			Read the rest of the book
			Let Excel help write your macro
			Use the Help system
			Use the Object Browser
			Pilfer code from the Internet
			Leverage user forums
			Visit expert blogs
			Mine YouTube for video training
			Learn from the Microsoft Office Dev Center
			Dissect the other Excel files in your organization
			Ask your local Excel genius
	Chapter 3: VBA Programming Fundamentals
		VBA Language Elements: An Overview
		Comments
		Variables, Data Types, and Constants
			Defining data types
			Declaring variables
			Scoping variables
			Working with constants
			Working with strings
			Working with dates
		Assignment Statements
		Arrays
			Declaring arrays
			Declaring multidimensional arrays
			Declaring dynamic arrays
		Object Variables
		User-Defined Data Types
		Built-In Functions
		Manipulating Objects and Collections
			With-End With constructs
			For Each-Next constructs
		Controlling Code Execution
			GoTo statements
			If-Then constructs
			Select Case constructs
			Looping blocks of instructions
	Chapter 4: Working with VBA Sub Procedures
		About Procedures
			Declaring a Sub procedure
			Scoping a procedure
		Executing Sub Procedures
			Executing a procedure with the Run Sub/UserForm command
			Executing a procedure from the Macro dialog box
			Executing a procedure with a Ctrl+shortcut key combination
			Executing a procedure from the Ribbon
			Executing a procedure from a customized shortcut menu
			Executing a procedure from another procedure
			Executing a procedure by clicking an object
			Executing a procedure when an event occurs
			Executing a procedure from the Immediate window
		Passing Arguments to Procedures
		Error-Handling Techniques
			Trapping errors
			Error-handling examples
		A Realistic Example That Uses Sub Procedures
			The goal
			Project requirements
			What you know
			The approach
			Some preliminary recording
			Initial setup
			Code writing
			Writing the Sort procedure
			More testing
			Fixing the problems
			Utility availability
			Evaluating the project
	Chapter 5: Creating Function Procedures
		Sub Procedures versus Function Procedures
		Why Create Custom Functions?
		An Introductory Function Example
			Using the function in a worksheet
			Using the function in a VBA procedure
			Analyzing the custom function
		Function Procedures
			A function’s scope
			Executing function procedures
		Function Arguments
		Function Examples
			Functions with no argument
			A function with one argument
			A function with two arguments
			A function with an array argument
			A function with optional arguments
			A function that returns a VBA array
			A function that returns an error value
			A function with an indefinite number of arguments
		Emulating Excel’s SUM Function
		Extended Date Functions
		Debugging Functions
		Dealing with the Insert Function Dialog Box
			Using the MacroOptions method
			Specifying a function category
			Adding a function description manually
		Using Add-Ins to Store Custom Functions
		Using the Windows API
			Windows API examples
			Determining the Windows directory
			Detecting the Shift key
			Learning more about API functions
	Chapter 6: Understanding Excel’s Events
		What You Should Know about Events
			Understanding event sequences
			Where to put event-handler procedures
			Disabling events
			Entering event-handler code
			Event-handler procedures that use arguments
		Getting Acquainted with Workbook-Level Events
			The Open event
			The Activate event
			The SheetActivate event
			The NewSheet event
			The BeforeSave event
			The Deactivate event
			The BeforePrint event
			The BeforeClose event
		Examining Worksheet Events
			The Change event
			Monitoring a specific range for changes
			The SelectionChange event
			The BeforeDoubleClick event
			The BeforeRightClick event
		Monitoring with Application Events
			Enabling Application-level events
			Determining when a workbook is opened
			Monitoring Application-level events
		Accessing Events Not Associated with an Object
			The OnTime event
			The OnKey event
	Chapter 7: VBA Programming Examples and Techniques
		Learning by Example
		Working with Ranges
			Copying a range
			Moving a range
			Copying a variably sized range
			Selecting or otherwise identifying various types of ranges
			Resizing a range
			Prompting for a cell value
			Entering a value in the next empty cell
			Pausing a macro to get a user-selected range
			Counting selected cells
			Determining the type of selected range
			Looping through a selected range efficiently
			Deleting all empty rows
			Duplicating rows a variable number of times
			Determining whether a range is contained in another range
			Determining a cell’s data type
			Reading and writing ranges
			A better way to write to a range
			Transferring one-dimensional arrays
			Transferring a range to a variant array
			Selecting cells by value
			Copying a noncontiguous range
		Working with Workbooks and Sheets
			Saving all workbooks
			Saving and closing all workbooks
			Hiding all but the selection
			Creating a hyperlink table of contents
			Synchronizing worksheets
		VBA Techniques
			Toggling a Boolean property
			Displaying the date and time
			Displaying friendly time
			Getting a list of fonts
			Sorting an array
			Processing a series of files
		Some Useful Functions for Use in Your Code
			The FileExists function
			The FileNameOnly function
			The PathExists function
			The RangeNameExists function
			The SheetExists function
			The WorkbookIsOpen function
			Retrieving a value from a closed workbook
		Some Useful Worksheet Functions
			Returning cell formatting information
			A talking worksheet
			Displaying the date when a file was saved or printed
			Understanding object parents
			Counting cells between two values
			Determining the last nonempty cell in a column or row
			Does a string match a pattern?
			Extracting the nth element from a string
			Spelling out a number
			A multifunctional function
			The SHEETOFFSET function
			Returning the maximum value across all worksheets
			Returning an array of nonduplicated random integers
			Randomizing a range
			Sorting a range
		Windows API Calls
			Understanding API Declarations
			Determining file associations
			Determining default printer information
			Determining video display information
			Reading from and writing to the Registry
Part II: Advanced VBA Techniques
	Chapter 8: Working with Pivot Tables
		An Introductory Pivot Table Example
			Creating a pivot table
			Examining the recorded code for the pivot table
			Cleaning up the recorded pivot table code
		Creating a More Complex Pivot Table
			The code that created the pivot table
			How the more complex pivot table works
		Creating Multiple Pivot Tables
		Creating a Reverse Pivot Table
	Chapter 9: Working with Charts
		Getting the Inside Scoop on Charts
			Chart locations
			The macro recorder and charts
			The Chart object model
		Creating an Embedded Chart
		Creating a Chart on a Chart Sheet
		Modifying Charts
		Using VBA to Activate a Chart
		Moving a Chart
		Using VBA to Deactivate a Chart
		Determining Whether a Chart Is Activated
		Deleting from the ChartObjects or Charts Collection
		Looping through All Charts
		Sizing and Aligning ChartObjects
		Creating Lots of Charts
		Exporting a Chart
			Exporting all graphics
		Changing the Data Used in a Chart
			Changing chart data based on the active cell
			Using VBA to determine the ranges used in a chart
		Using VBA to Display Arbitrary Data Labels on a Chart
		Displaying a Chart in a UserForm
		Understanding Chart Events
			An example of using Chart events
			Enabling events for an embedded chart
			Example: Using Chart events with an embedded chart
		Discovering VBA Charting Tricks
			Printing embedded charts on a full page
			Creating unlinked charts
			Displaying text with the MouseOver event
			Scrolling a chart
		Working with Sparkline Charts
	Chapter 10: Interacting with Other Applications
		Understanding Microsoft Office Automation
			Understanding the concept of binding
			A simple automation example
		Automating Access from Excel
			Running an Access Query from Excel
			Running an Access Macro from Excel
		Automating Word from Excel
			Sending Excel data to a Word document
			Simulating mail merge with a Word document
		Automating PowerPoint from Excel
			Sending Excel data to a PowerPoint presentation
			Sending all Excel charts to a PowerPoint presentation
			Convert a workbook into a PowerPoint presentation
		Automating Outlook from Excel
			Mailing the Active Workbook as an Attachment
			Mailing a Specific Range as an Attachment
			Mailing a Single Sheet as an Attachment
			Mailing All Email Addresses in Your Contact List
		Starting Other Applications from Excel
			Using the VBA Shell function
			Using the Windows ShellExecute API function
			Using AppActivate
			Running Control Panel dialog boxes
	Chapter 11: Working with External Data and Files
		Working with External Data Connections
			Manually creating a connection
			Manually editing data connections
			Using VBA to create dynamic connections
			Iterating through all connections in a workbook
		Using ADO and VBA to Pull External Data
			The connection string
			Declaring a Recordset
			Referencing the ADO object library
			Putting it all together in code
			Using ADO with the active workbook
		Working with Text Files
			Opening a text file
			Reading a text file
			Writing a text file
			Getting a file number
			Determining or setting the file position
			Statements for reading and writing
		Text File Manipulation Examples
			Importing data in a text file
			Exporting a range to a text file
			Importing a text file to a range
			Logging Excel usage
			Filtering a text file
		Performing Common File Operations
			Using VBA file-related statements
			Using the FileSystemObject object
		Zipping and Unzipping Files
			Zipping files
			Unzipping a file
Part III: Working with UserForms
	Chapter 12: Leveraging Custom Dialog Boxes
		Before You Create That UserForm
		Using an Input Box
			The VBA InputBox function
			The Application.InputBox method
		The VBA MsgBox Function
		The Excel GetOpenFilename Method
		The Excel GetSaveAsFilename Method
		Prompting for a Directory
		Displaying Excel’s Built-In Dialog Boxes
		Displaying a Data Form
			Making the data form accessible
			Displaying a data form by using VBA
	Chapter 13: Introducing UserForms
		How Excel Handles Custom Dialog Boxes
		Inserting a New UserForm
		Adding Controls to a UserForm
		Toolbox Controls
			CheckBox
			ComboBox
			CommandButton
			Frame
			Image
			Label
			ListBox
			MultiPage
			OptionButton
			RefEdit
			ScrollBar
			SpinButton
			TabStrip
			TextBox
			ToggleButton
		Adjusting UserForm Controls
		Adjusting a Control’s Properties
			Using the Properties window
			Common properties
			Accommodating keyboard users
		Displaying a UserForm
			Adjusting the display position
			Displaying a modeless UserForm
			Displaying a UserForm based on a variable
			Loading a UserForm
			About event-handler procedures
		Closing a UserForm
		Creating a UserForm: An Example
			Creating the UserForm
			Writing code to display the dialog box
			Testing the dialog box
			Adding event-handler procedures
			The finished dialog box
		Understanding UserForm Events
			Learning about events
			UserForm events
			SpinButton events
			Pairing a SpinButton with a TextBox
		Referencing UserForm Controls
		Customizing the Toolbox
			Adding new pages to the Toolbox
			Customizing or combining controls
			Adding new ActiveX controls
		Creating UserForm Templates
		A UserForm Checklist
	Chapter 14: UserForm Examples
		Creating a UserForm “Menu”
			Using CommandButtons in a UserForm
			Using a ListBox in a UserForm
		Selecting Ranges from a UserForm
		Creating a Splash Screen
		Disabling a UserForm's Close Button
		Changing a UserForm's Size
		Zooming and Scrolling a Sheet from a UserForm
		ListBox Techniques
			Adding items to a ListBox control
			Determining the selected item in a ListBox
			Determining multiple selections in a ListBox
			Multiple lists in a single ListBox
			ListBox item transfer
			Moving items in a ListBox
			Working with multicolumn ListBox controls
			Using a ListBox to select worksheet rows
			Using a ListBox to activate a sheet
			Filtering a ListBox from a TextBox
		Using the MultiPage Control in a UserForm
		Using an External Control
		Animating a Label
	Chapter 15: Advanced UserForm Techniques
		A Modeless Dialog Box
		Displaying a Progress Indicator
			Creating a stand-alone progress indicator
			Showing a progress indicator that’s integrated into a UserForm
			Creating a non-graphical progress indicator
		Creating Wizards
			Setting up the MultiPage control for the wizard
			Adding the buttons to the wizard’s UserForm
			Programming the wizard’s buttons
			Programming dependencies in a wizard
			Performing the task with the wizard
		Emulating the MsgBox Function
			MsgBox emulation: MyMsgBox code
			How the MyMsgBox function works
			Using the MyMsgBox function
		A UserForm with Movable Controls
		A UserForm with No Title Bar
		Simulating a Toolbar with a UserForm
		Emulating a Task Pane with a UserForm
		A Resizable UserForm
		Handling Multiple UserForm Controls with One Event Handler
		Selecting a Color in a UserForm
		Displaying a Chart in a UserForm
			Saving a chart as a GIF file
			Changing the Image control’s Picture property
		Making a UserForm Semitransparent
		A Puzzle on a UserForm
		Video Poker on a UserForm
Part IV: Developing Excel Applications
	Chapter 16: Creating and Using Add-Ins
		What Is an Add-In?
			Comparing an add-in with a standard workbook
			Why create add-ins?
		Understanding Excel’s Add-In Manager
		Creating an Add-in
		An Add-In Example
			Adding descriptive information for the example add-in
			Creating an add-in
			Installing an add-in
			Testing the add-in
			Distributing an add-in
			Modifying an add-in
		Comparing XLAM and XLSM Files
			XLAM file VBA collection membership
			Visibility of XLSM and XLAM files
			Worksheets and chart sheets in XLSM and XLAM files
			Accessing VBA procedures in an add-in
		Manipulating Add-Ins with VBA
			Adding an item to the AddIns collection
			Removing an item from the AddIns collection
			AddIn object properties
			Accessing an add-in as a workbook
			AddIn object events
		Optimizing the Performance of Add-Ins
		Special Problems with Add-Ins
			Ensuring that an add-in is installed
			Referencing other files from an add-in
			Detecting the proper Excel version for your add-in
	Chapter 17: Working with the Ribbon
		Ribbon Basics
		Customizing the Ribbon
			Adding a button to the Ribbon
			Adding a button to the Quick Access Toolbar
			Understanding the limitations of Ribbon customization
		Creating a Custom Ribbon
			Adding a button to an existing tab
			Adding a check box to an existing tab
			Ribbon controls demo
			A dynamicMenu control example
			More on Ribbon customization
		Using VBA with the Ribbon
			Accessing a Ribbon control
			Working with the Ribbon
			Activating a tab
		Creating an Old-Style Toolbar
			Limitations of old-style toolbars in Excel 2007 and later
			Code to create a toolbar
	Chapter 18: Working with Shortcut Menus
		CommandBar Overview
			CommandBar types
			Listing shortcut menus
			Referring to CommandBars
			Referring to controls in a CommandBar
			Properties of CommandBar controls
			Displaying all shortcut menu items
		Using VBA to Customize Shortcut Menus
			Shortcut menu and the single-document interface
			Resetting a shortcut menu
			Disabling a shortcut menu
			Disabling shortcut menu items
			Adding a new item to the Cell shortcut menu
			Adding a submenu to a shortcut menu
			Limiting a shortcut menu to a single workbook
		Shortcut Menus and Events
			Adding and deleting menus automatically
			Disabling or hiding shortcut menu items
			Creating a context-sensitive shortcut menu
	Chapter 19: Providing Help for Your Applications
		Help for Your Excel Applications
		Help Systems That Use Excel Components
			Using cell comments for help
			Using a text box for help
			Using a worksheet to display help text
			Displaying help in a UserForm
		Displaying Help in a Web Browser
			Using HTML files
			Using an MHTML file
		Using the HTML Help System
			Using the Help method to display HTML Help
			Associating a help file with your application
			Associating a help topic with a VBA function
	Chapter 20: Leveraging Class Modules
		What Is a Class Module?
			Built-in class modules
			Custom class modules
		Creating a NumLock Class
			Inserting a class module
			Adding VBA code to the class module
			Using the CNumLock class
		Coding Properties, Methods, and Events
			Programming properties of objects
			Programming methods for objects
			Class module events
		Exposing a QueryTable Event
		Creating a Class to Hold Classes
			Creating the CSalesRep and CSalesReps classes
			Creating the CInvoice and CInvoices classes
			Filling the parent classes with objects
			Calculating the commissons
	Chapter 21: Understanding Compatibility Issues
		What Is Compatibility?
		Types of Compatibility Problems
		Avoid Using New Features
		But Will It Work on a Mac?
		Dealing with 64-Bit Excel
		Creating an International Application
			Multilanguage applications
			VBA language considerations
			Using local properties
			Identifying system settings
			Date and time settings
Part V: Appendix
	Appendix A: VBA Statements and Function Reference
Index
Advert
EULA