Excel VBA Programming For Dummies

Title Page
Copyright Page
Table of Contents
Introduction
	About This Book
		Typographical conventions
		Macro security
	Foolish Assumptions
	Icons Used in This Book
	Beyond the Book
	Where to Go from Here
Part 1 Starting Excel VBA Programming
	Chapter 1 Getting to Know VBA
		Understanding VBA Basics
		Knowing What VBA Can Do
			Inserting a bunch of text
			Automating a task you perform frequently
			Automating repetitive operations
			Creating a custom command
			Creating a custom button
			Developing new worksheet functions
			Creating custom add-ins for Excel
		Getting the Most from VBA
			Knowing what VBA does best
			Recognizing the disadvantages of using VBA
		Understanding VBA Concepts
		Ensuring Excel Compatibility
	Chapter 2 Building Simple Macros
		Displaying the Developer Tab
		Creating a Macro
		Preparing the Environment
		Recording a Macro
		Running the Macro
		Viewing a Macro in the Visual Basic Editor
		Modifying the Macro
		Saving Workbooks That Contain Macros
		Understanding Macro Security
Part 2 Employing VBA with Excel
	Chapter 3 Working in the Visual Basic Editor
		Getting to Know the Visual Basic Editor
			Activating the VBE
			Exploring VBE components
		Working with the Project Explorer
			Adding a new VBA module
		Working with a Code Pane
			Minimizing and maximizing windows
			Looking at the parts of a module
			Getting VBA code into a module
			Entering code directly
			Using the macro recorder
			Copying VBA code
		Customizing the VBE
			Using the Editor tab
			Using the Editor Format tab
			Using the General tab
			Using the Docking tab
	Chapter 4 Introducing the Excel Object Model
		Working with the Excel Object Model
			Understanding the object hierarchy
			Referring to objects
		Diving into Object Properties and Methods
			Setting object properties
			Taking action with object methods
			Triggering actions with object events
		Finding Out More from VBA Resources
			Using VBA’s Help system
			Using the Object Browser
			Automatically listing properties and methods
	Chapter 5 VBA Sub and Function Procedures
		Understanding Subs versus Functions
			Looking at Sub procedures
			Looking at Function procedures
		Naming Subs and Functions
		Executing Sub procedures
			Executing the Sub procedure directly
			Executing the procedure from the Macro dialog box
			Executing a macro by using a shortcut key
			Executing the procedure from a button or shape
			Executing the procedure from another procedure
			Executing the procedure from the Immediate window
		Executing Function Procedures
			Calling the function from a Sub procedure
			Calling a function from the Immediate window
			Calling a function from a worksheet formula
	Chapter 6 Using the Excel Macro Recorder
		Recording Basics
		Preparing to Record
		Choosing Between Relative and Absolute Mode
			Recording in absolute mode
			Recording in relative mode
		Watching the Macro Recorder in Action
		Specifying Recording Options for Your Macro
		Streamlining Code Generated by the Macro Recorder
Part 3 Programming Concepts
	Chapter 7 Essential VBA Language Elements
		Using Comments in Your VBA Code
		Using Variables, Constants, and Data Types
			Understanding variables
			What are VBA’s data types?
			Declaring and scoping variables
			Working with constants
			Using premade constants
			Working with strings
			Working with dates
		Using Assignment Statements
			Assignment statement examples
			About that equal sign
			Smooth operators
		Working with Arrays
			Declaring arrays
			Multidimensional arrays
			Dynamic arrays
		Using Labels
	Chapter 8 Working with Range Objects
		Referring to Range Objects
		Referring to a Range Using Properties
			The Cells property
			The Offset property
			The Resize property
		Working with Range Object Properties
			The Value property
			The Text property
			The Count property
			The Column and Row properties
			The Address property
			The HasFormula property
			The Font property
			The Interior property
			The Formula property
			The NumberFormat property
		Taking Action with Range Object Methods
			The Select method
			The Copy and Paste methods
			The Clear method
			The Delete method
	Chapter 9 Using VBA and Worksheet Functions
		Understanding Functions
		Using Built-In VBA Functions
			Working with dates and times
			Manipulating strings
			Identifying objects and data
			VBA functions that do more than return a value
			Discovering VBA functions
		Using Worksheet Functions in VBA
			Worksheet function examples
			Entering worksheet functions
			More about using worksheet functions
		Using Custom Functions
	Chapter 10 Controlling Program Flow and Making Decisions
		Going with the Flow, Dude
		The GoTo Statement
		Decisions, Decisions
			The If-Then structure
			The Select Case structure
		Knocking Your Code for a Loop
			For-Next loops
			Do-While loop
			Do-Until loop
		Using For Each-Next Loops with Collections
	Chapter 11 Automatic Procedures and Events
		Preparing for the Big Event
			Learning when to use event procedures
			Programming event-handler procedures
		Knowing Where to Put the Event Code
		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
			Activate and deactivate events in a workbook
			Workbook activation events
		Programming Worksheet-Related Events
			The BeforeDoubleClick event
			The BeforeRightClick event
			The Change event
		Understanding Events Not Associated with Objects
			The OnTime event
			Keypress events
	Chapter 12 Error-Handling Techniques
		Types of Errors
		An Erroneous Macro Example
			Not-quite-perfect macros
			Perfecting the macro
			Giving up on perfection
		Alternate Ways of Handling Errors
			Revisiting the EnterSquareRoot procedure
			Trapping errors with the On Error statement
		Handling Errors: The Details
			Resuming after an error
			Error handling in a nutshell
		An Intentional Error
	Chapter 13 Bug Extermination Techniques
		Species of Bugs
		Identifying Bugs
		Debugging Techniques
			Examining your code
			Using the MsgBox function
			Inserting Debug.Print statements
			Using the VBA debugger
		Using the Debugger’s Tools
			Setting breakpoints in your code
			Using the Watches window
			Using the Locals window
		Bug Reduction Tips
	Chapter 14 VBA Programming Examples
		Working with Ranges
			Copying a range
			Copying a variable-size range
			Selecting to the end of a row or column
			Selecting a row or column
			Moving a range
			Looping through a range efficiently
			Looping through a range efficiently (Part II)
			Prompting for a cell value
			Determining the selection type
			Identifying a multiple selection
		Changing Excel Settings
			Changing Boolean settings
			Changing non-Boolean settings
		Working with Charts
			AddChart versus AddChart2
			Modifying the chart type
			Looping through the ChartObjects collection
			Modifying chart properties
			Applying chart formatting
		VBA Speed Tips
			Turning off screen updating
			Turning off automatic calculation
			Eliminating those pesky alert messages
			Simplifying object references
			Declaring variable types
			Using the With-End With structure
Part 4 Communicating with Your Users
	Chapter 15 Simple Dialog Boxes
		Interacting with the User in VBA
		Displaying Messages with the MsgBox Function
			Displaying a simple message box
			Getting a response from a message box
			Customizing message boxes
		Getting Data with an Input Box
			Understanding the InputBox syntax
			Using the InputBox function
			Using the InputBox method
		Allowing the User to Select a File or Folder
			Constructing a GetOpenFilename statement
			Selecting a file with GetOpenFilename
			Picking a file with GetSaveAsFilename
			Getting a folder name
		Displaying Excel’s Built-In Dialog Boxes
	Chapter 16 UserForm Basics
		Knowing When to Use a UserForm
		Creating UserForms: An Overview
		Working with UserForms
			Inserting a new UserForm
			Adding controls to a UserForm
			Changing properties for a UserForm control
			Viewing the UserForm Code pane
			Displaying a UserForm
			Using information from a UserForm
		A UserForm Example
			Creating the UserForm
			Adding the CommandButtons
			Adding the OptionButtons
			Adding event-handler procedures
			Creating a macro to display the dialog box
			Making the macro available
			Testing the macro
	Chapter 17 Using UserForm Controls
		Getting Started with Dialog Box Controls
			Adding controls
			Introducing control properties
		Learning Dialog Box Controls Details
			CheckBox control
			ComboBox control
			CommandButton control
			Frame control
			Image control
			Label control
			ListBox control
			MultiPage control
			OptionButton control
			RefEdit 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 hot keys
			Testing a UserForm
		Dialog Box Aesthetics
	Chapter 18 UserForm Techniques and Tricks
		Using Dialog Boxes
		A UserForm Example
			Creating the dialog box
			Writing code to display the dialog box
			Making the macro available
			Trying out your dialog box
			Adding event-handler procedures
			Validating the data
			Now the dialog box works
		A ListBox Control Example
			Filling a ListBox Control
			Determining the selected item
			Determining multiple selections
		Selecting a Range
		Using Multiple Sets of Option Buttons
		Using a Spin Button and a Text Box
		Using a UserForm as a Progress Indicator
			Creating the progress-indicator dialog box
			The procedures
			How this example works
		Creating a Modeless Tabbed Dialog Box
		Displaying a Chart in a UserForm
		A Dialog Box Checklist
	Chapter 19 Accessing Your Macros through the User Interface
		Customizing the Ribbon
			Customizing the Ribbon manually
			Adding a macro to the Ribbon
			Customizing the Ribbon with XML
		Customizing the Excel UI with VBA
			Adding commands to the Add-ins Ribbon tab
			Adding a new item to the Cell shortcut menu
			Adding customizations automatically
			Understanding shortcut menus and the single document interface
Part 5 Putting It All Together
	Chapter 20 Creating Worksheet Functions
		Create Custom Functions to Simplify Your Work
		Understanding VBA Function Basics
		Writing Functions
		Working with Function Arguments
			A function with no argument
			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 NumberFormat function
			The ExtractElement function
			The SayIt function
			The IsLike function
		Working with Functions That Return an Array
			Returning an array of month names
			Returning a sorted list
		Using the Insert Function Dialog Box
			Displaying the function’s description
			Adding argument descriptions
	Chapter 21 Creating Excel Add-Ins
		Add-Ins Defined
		Reasons to Create Add-Ins
		Working with Add-Ins
		Understanding Add-In Basics
		Looking at an Add-In Example
			Setting up the workbook
			Testing the workbook
			Adding descriptive information
			Protecting the VBA code
			Creating the add-in
			Opening the add-in
			Distributing the add-in
			Modifying the add-in
Part 6 The Part of Tens
	Chapter 22 Ten Handy Visual Basic Editor 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 23 Resources for VBA Help
		Letting Excel Write Code for You
		Referencing the Help System
		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 24 Ten VBA Do’s and Don’ts
		Do Declare 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
EULA