EXCEL POWER PROGRAMMING WITH VBA & MACROS

CONTENTS
PART ONE
STARTING EXCEL VBA PROGRAMMING
CHAPTER ONE
GETTING TO KNOW VBA
	What is VBA?
	Knowing What VBA Can Do
		Inserting A Bunch of Text
		Automating a task, you perform frequently
		Automating repetitive operations
		Making your own command
		Creating a custom button
		Developing new worksheet functions
		Creating custom add-ins for Excel
		Creating complete, macro-driven applications
	Advantages of VBA
		Understanding what VBA excels at
		Understanding the disadvantages of using VBA
CHAPTER TWO
BUILDING SIMPLE MACROS
	Displaying the Developers Tab
	Creating a Macro
		Preparing the Environment
		Recording the Macro
		Running the Macro
		Viewing a Macro in the Visual Basic Editor
		Modifying the Macro
		Saving Workbooks that Contain Macros
		Understanding Macro Security
		Revealing More about the NameAndTime Macro
PART TWO
EMPLOYING VBA WITH EXCEL
CHAPTER THREE
WORKING IN THE VISUAL BASIC EDITOR
	Getting to Know the Visual Basic Editor
	Activating the VBE
	Exploring VBE components
	The menu bars
	Toolbar
	Project window
	Code window
	Immediate window
	IN THE VISUAL BASIC EDITOR, WHAT\'S NEW?
	Working with the project explorer
	Adding a new VBA Module
	Removing a VBA module
	Exporting and importing objects
	Working with a Code Pane
	Window minimization and maximization
	Looking at the parts of a module
	Getting VBA code into a module
	Directly entering a code
	Using the Macro recorder
	Copying VBA Code
	Customizing the VBE
	Using the Editor tab
	Auto Syntax Check option
	Require Variable Declaration option
	Explicit Option
	Auto List Members option
	Auto Quick Info option
	Auto Data Tips option
	Setting the Auto Indent
	Text Editing using Drag-and-Drop
	Default to Full Module View option
	Procedure Separator option
	Using the Editor Format tab
	Code Colors option
	Font option
	Size setting
	Margin Indicator Bar option
	Using the General Tab
	Using the Docking tab
CHAPTER FOUR
INTRODUCING THE EXCEL OBJECT MODEL
	Is Excel an Object?
		Understanding the Hierarchy of Objects
		Wrapping Your Mind around Collections
		Referring to Objects
		Getting around the hierarchy
		Setting object references
		Diving into Object Properties and Methods
		Setting objects properties
		Taking action with object methods
		Triggering actions with objects events
		Getting additional information from VBA resources
		Using the Help System in VBA
		Using the Object Browser
		Automatically listing properties and methods
CHAPTER FIVE
VBA SUB AND FUNCTIONS PROCEDURES
	Understanding Subs versus Functions
		Examining Sub-Procedures
		Looking at Function procedures
		Naming Subs and Functions
		Executing Sub procedures
		Directly executing the Sub procedure
		Executing the procedure from the Macro dialog box
		Executing a macro using a shortcut key
		Executing the procedure from a button or shape
		Executing the procedure from another procedure
		Executing Function procedures
		Calling the function from a Sub procedure
		Calling a function from a worksheet formula
CHAPTER SIX
USING THE EXCEL MACRO RECORDER
	Recording Basics
		Getting Ready to Record
		Choosing Between Absolute and Relative Modes
		Absolute recording mode
		Recording in relative mode
		What Gets Recorded?
		Specifying recording options for your Macro
		Macro name
		Shortcut key
		Store Macro In option
		Description
		Streamlining Macro Recorder-Generated Code
PART THREE
PROGRAMMING CONCEPT
CHAPTER SEVEN
ESSENTIAL VBA LANGUAGE ELEMENTS
	Making Use of Comments in VBA Code
	Using Variables, Constants, and Data Types
	What are VBA’s data types?
	Declaring and scoping variables
	Procedure-only variables
	Module-only variables
	Public variables
	Static variables
	Life of variables
	Working with constants
	Using Premade Constants
	Working with strings
	Working with dates
	Using Assignment Statements
	Examples of assignment statements
	About that equal sign
	Smooth operators
	Working with Arrays
		Array declarations
		Multidimensional arrays
		Dynamic arrays
		Making Use of Labels
CHAPTER EIGHT
WORKING WITH RANGE OBJECTS
	Referring to Range Objects
		Referring to a Range Using Properties
		The Cells property
		The Offset property
		Working with Range Properties
		The Value property
		The Text property
		The Font property
		The Interior property
		The Formula property
		The Number Format property
	Using Range Object Methods to Take Action
		The Select method
		The Copy and Paste methods
		The Clear method
		The Delete method
CHAPTER NINE
USING VBA AND WORKSHEET FUNCTIONS
	Understanding Functions
	Using VBA\'s Built-In Functions
	Examples of VBA functions
	VBA functions that do more than return a value
	Using Worksheet Functions in VBA
	Worksheet Functions Examples
	Determining the highest value in a range
	Entering worksheet functions
	More about using worksheet functions
	Using Custom Functions
CHAPTER TEN
CONTROLLING PROGRAM FLOW AND MAKING DECISIONS
	Going with the Flow, Dude
	The GoTo Statement
	WHAT IS STRUCTURED PROGRAMMING? DOES IT MATTER?
	Decisions, Decisions
	An If-Then-Else example
	Using ElseIf
	Another If-Then example
	The Select Case structure
		An example of a Select Case
		A nested Select Case example
		Knocking Your Code for a Loop
CHAPTER ELEVEN
AUTOMATIC PROCEDURES AND EVENTS
	Preparing for the Big Event
		Are events useful?
		Programming event-handler procedures
		Where Does the VBA Code Go?
		Writing an Event-Handler Procedure
		Triggering Workbook Events
		The Open event for a workbook
		The BeforeClose event for a workbook
		The BeforeSave event for a workbook
		Using Activation Events
		Activate and Deactivate events in a sheet
		Activating and deactivating events in a workbook
		Workbook activation events
		Other Worksheet-Related Events
		Why not using data validation?
		Preventing data validation from being destroyed
		Events Not Associated with Objects
		The OnTime event
		Keypress events
CHAPTER TWELVE
ERROR HANDLING TECHNIQUES
	Types of Errors
		An Erroneous Macro Example
		Not quite perfect macros
		Perfecting the Macro
		Giving up on perfection
		Handling Errors Another Way
		Error handling in a nutshell
		Knowing when to ignore errors
		Identifying specific errors
		An Intentional Error
CHAPTER THIRTEEN
BUG EXTERMINATION TECHNIQUES
	Species of Bugs
	Detecting Bugs
	Debugging Techniques
	Reviewing your code
	Using the MsgBox function.
	Using the VBA debugger
	Using the debugger’s tools
	Setting breakpoints in your code
	Stop
	Stepping through your code
	Using the Watch window
	Using the Locals window
	Tips for Bug Reduction
CHAPTER FOURTEEN
VBA PROGRAMMING EXAMPLES
	Working with Ranges
	Copying a range
	Copying a variable-size range
	Selecting the end of a row or column
	Selecting a row or column
	Moving a range
	Looping through a range efficiently
	On Error Resume Next
	Looping through a range efficiently (Part II)
	Prompting for a cell value
	Identifying multiple selections
	Changing Excel Preferences
	Changing Boolean settings
	Changing non-Boolean settings
	Working with Charts
	AddChart versus AddChart2
	Modifying the chart type
	Looping through the Chart Objects collection
	Modifying chart properties
	Applying chart formatting
	VBA Quick Tips
	Turning off screen updating
	Disabling automatic calculation
	Eliminating those pesky alert messages
	Simplifying object references
	Declaring variable types
PART FOUR
COMMUNICATING WITH YOUR USERS
CHAPTER FIFTEEN
SIMPLE DIALOG BOXES
	Interacting with User In VBA
	The MsgBox Function
	Displaying Messages with the MsgBox Function
	Customizing message boxes
	Getting Data with an Input Box syntax
	Using the Input Box function
	Constructing a GetOpenFilename Statement
	The syntax for the GetOpenFilename method
	An example of GetOpenFilename
	Displaying Excel’s Built-in Dialog Boxes
CHAPTER SIXTEEN
USERFORM BASICS
	Knowing When to Use a UserForm
	Working with UserForms
	Inserting a new UserForm
	Adding controls to a UserForm
	Changing properties for a UserForm control
	Viewing the UserForm Code window
	Displaying a UserForm
	Using information from a UserForm
	A UserForm Example
	Adding the CommandButtons
	Adding the Option Buttons
	Adding event-handler procedures
	Creating a macro to display the dialog box
	Making the macro available
	Testing the macro
CHAPTER SEVENTEEN
USING USERFORM CONTROLS
	Getting Started with Dialog Box Controls
		Adding controls
		Introducing control properties
		Dialog Box Controls: The Details
		CheckBox control
		ComboBox control
		CommandButton control
		Frame control
		Image control
		ListBox control
		MultiPage control
		OptionButton control
		ScrollBar control
		SpinButton control
		TabStrip control
		TextBox control
		ToggleButton control
		Working with Dialog Box Controls
		Moving and resizing controls
		Aligning and spacing controls
		Accommodating keyboard users
		Changing the tab order
		Setting hotkeys
		Aesthetics of Dialog Boxes
CHAPTER EIGHTEEN
USERFORM TECHNIQUES AND TRICKS
	Using Dialog Boxes
	A UserForm Example
	Creating the dialog box
	Making the macro available
	Trying out your dialog box
	Adding event-handler procedures
	Validating the data
	Now the dialog box works
	More UserForm Examples
	A ListBox example
	Filling a ListBox
	Determining the selected item
	Determining multiple selections
	Choosing a range
	Using a TextBox and a SpinButton
	How this example works
	Creating a modeless tabbed dialog box
	Displaying a chart in a UserForm
	A Dialog Box Checklist
CHAPTER NINETEEN
ACCESSING YOUR MACROS THROUGH THE USER INTERFACE
Customizing the Ribbon
	Manually customizing the Ribbon
		Tabs
		Groups
		Adding a macro to the Ribbon
		Using XML to customize the Ribbon
		Adding a new item to the Cell shortcut menu
PART FIVE
PUTTING IT ALL TOGETHER
CHAPTER TWENTY
CREATING WORKSHEET FUNCTIONS
	Make Custom Functions to Make Your Work Easier
	What Custom Worksheet Functions Can\'t Do?
	Working with Function Examples
		A function with one argument
		A function with two arguments
		A function with a range argument
		A function with an optional argument
		Introducing Wrapper Functions
		The ExtractElement function
		The SayIt function
		The IsLike function
		Working with Functions That Return an Array
		Returning a sorted list
		Using the Insert Function Dialog Box
		Displaying the function’s description
		Adding argument descriptions
CHAPTER TWENTY-ONE
CREATING EXCEL ADD-INS
	Add-Ins Defined
	Reasons to Create Add-Ins
	Working with Add-Ins
	Understanding the Basics of Add-Ins
	Looking at an Add-In Example
	Setting up the workbook
	Testing the workbook
	Protecting the VBA code
	Creating the add-in
	Opening the add-in
	Distributing the add-in
	Modifying the add-in
PART SIX
OTHER EXCEL PROGRAMMING ETHICS WORTH KNOWING
CHAPTER TWENTY-TWO
TEN HANDY VISUAL BASIC EDITORS TIPS
	Applying Block Comments
	Copying Multiple Lines of Code at Once
	Jumping between Modules and Procedures
	Teleporting to Your Functions
	Staying in the Right Procedure
	Stepping Through Your Code
	Stepping to a Specific Line in Your Code
	Stopping Your Code at a Predefined Point
	Seeing the Beginning and End of Variable Values
	Turning Off Auto Syntax Check
CHAPTER TWENTY-THREE
RESOURCES FOR VBA HELP
	Allowing Excel to Generate Code for You
	Pilfering Code from the Internet
	Leveraging User Forums
	Visiting Expert Blogs
	Mining YouTube for Video Training
	Attending Live and Online Training Classes
	Learning from the Microsoft Office Dev Center
	Dissecting the Other Excel Files in Your Organization
	Asking Your Local Excel Guru
CHAPTER TWENTY-FOUR
TEN VBA DO’S AND DON’TS
	Declaring All Variables
	Don’t Confuse Passwords with Security
	Do Clean Up Your Code
	Don’t Put Everything in One Procedure
	Do Consider Other Software
	Don’t Assume That Everyone Enables Macros
	Do Get in the Habit of Experimenting
	Don’t Assume That Your Code Will Work with Other Excel Versions
	Do Keep Your Users in Mind
	Don’t Forget About Backups
INDEX