Introduction Chapter 1 Unleash the Power of Excel with VBA The Power of Excel Barriers to Entry The Macro Recorder Doesn't Work Visual Basic Is Not Like BASIC Good News: Climbing the Learning Curve Is Easy Great News: Excel with VBA Is Worth the Effort Knowing Your Tools: The Developer Tab Macro Security Adding a Trusted Location Using Macro Settings to Enable Macros in Workbooks Outside of Trusted Locations Using Disable All Macros with Notification Overview of Recording, Storing, and Running a Macro Filling Out the Record Macro Dialog Running a Macro Creating a Macro Button on the Ribbon Creating a Macro Button on the Quick Access Toolbar Assigning a Macro to a Form Control, Text Box, or Shape Using New File Types in Excel 2010 Understanding the VB Editor VB Editor Settings The Project Explorer The Properties Window Understanding Shortcomings of the Macro Recorder Examining Code in the Programming Window Running the Macro on Another Day Produces Undesired Results Possible Solution: Use Relative References When Recording Never Use the AutoSum Button While Recording a Macro Three Tips When Using the Macro Recorder Next Steps Chapter 2 This Sounds Like BASIC, So Why Doesn't It Look Familiar? I Can't Understand This Code Understanding the Parts of VBA Speech VBA Is Not Really Hard VBA Help Files: Using F1 to Find Anything Using Help Topics Examining Recorded Macro Code: Using the VB Editor and Help Optional Parameters Defined Constants Properties Can Return Objects Using Debugging Tools to Figure Out Recorded Code Stepping Through Code More Debugging Options: Breakpoints Backing Up or Moving Forward in Code Not Stepping Through Each Line of Code Querying Anything While Stepping Through Code Using a Watch to Set a Breakpoint Using a Watch on an Object Object Browser: The Ultimate Reference Seven Tips for Cleaning Up Recorded Code Tip 1: Don't Select Anything Tip 2: Cells(2,5) Is More Convenient Than Range(E2) Tip 3: Ride the Range from the Bottom to Find Last Row Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas Tip 5: R1C1 Formulas That Make Your Life Easier Tip 6: Learn to Copy and Paste in a Single Statement Tip 7: Use With...End With to Perform Multiple Actions Next Steps Chapter 3 Referring to Ranges The Range Object Syntax to Specify a Range Named Ranges Shortcut for Referencing Ranges Referencing Ranges in Other Sheets Referencing a Range Relative to Another Range Use the Cells Property to Select a Range Using the Cells Property in the Range Property Use the Offset Property to Refer to a Range Use the Resize Property to Change the Size of a Range Use the Union Method to Join Multiple Ranges Use the ISEMPTY Function to Check Whether a Cell Is Empty Use the Intersect Method to Create a New Range from Overlapping Ranges Use the ISEMPTY Function to Check Whether a Cell Is Empty Use the CurrentRegion Property to Select a Data Range Use the Areas Collection to Return a Noncontiguous Range Referencing Tables Next Steps Chapter 4 User-Defined Functions Creating User-Defined Functions Sharing UDFs Useful Custom Excel Functions Set the Current Workbook's Name in a Cell Set the Current Workbook's Name and File Path in a Cell Check Whether a Workbook Is Open Check Whether a Sheet in an Open Workbook Exists Count the Number of Workbooks in a Directory Retrieve USERID Retrieve Date and Time of Last Save Retrieve Permanent Date and Time Validate an E-mail Address Sum Cells Based on Interior Color Count Unique Values Remove Duplicates from a Range Find the First Nonzero-Length Cell in a Range Substitute Multiple Characters Retrieve Numbers from Mixed Text Convert Week Number into Date Separate Delimited String Sort and Concatenate Sort Numeric and Alpha Characters Search for a String Within Text Reverse the Contents of a Cell Multiple Max Return Hyperlink Address Return the Column Letter of a Cell Address Static Random Using Select Case on a Worksheet Next Steps Chapter 5 Looping and Flow Control Using Variables in the For Statement Variations on the For...Next Loop Exiting a Loop Early After a Condition Is Met Nesting One Loop Inside Another Loop Do Loops Using the While or Until Clause in Do Loops While...Wend Loops VBA Loop: For Each Object Variables Flow Control: Using If...Then...Else and Select Case Basic Flow Control: If...Then...Else Conditions If...Then...End If Either/Or Decisions: If...Then...Else...End If Using If...Else If...End If for Multiple Conditions Using Select Case...End Select for Multiple Conditions Complex Expressions in Case Statements Nesting If Statements Next Steps Chapter 6 R1C1-Style Formulas Referring to Cells: A1 Versus R1C1 References Switching Excel to Display R1C1-Style References The Miracle of Excel Formulas Enter a Formula Once and Copy 1,000 Times The Secret: It's Not That Amazing Explanation of R1C1 Reference Style Using R1C1 with Relative References Using R1C1 with Absolute References Using R1C1 with Mixed References Referring to Entire Columns or Rows with R1C1 Style Replacing Many A1 Formulas with a Single R1C1 Formula Remembering Column Numbers Associated with Column Letters Array Formulas Require R1C1 Formulas Next Steps Chapter 7 What Is New in Excel 2010 and What Has Changed. If It Has Changed in the Front End, It Has Changed in VBA The Ribbon Charts Pivot Tables Slicers Conditional Formatting Tables Sorting SmartArt Learning the New Objects and Methods Compatibility Mode Version Excel8CompatibilityMode Next Steps Chapter 8 Create and Manipulate Names in VBA Excel Names Global Versus Local Names Adding Names Deleting Names Adding Comments Types of Names Formulas Strings Numbers Tables Using Arrays in Names Reserved Names Hiding Names Checking for the Existence of a Name Next Steps Chapter 9 Event Programming Levels of Events Using Events Event Parameters Enabling Events Workbook Events Workbook_Activate() Workbook_Deactivate() Workbook_Open() Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Workbook_BeforePrint(Cancel As Boolean) Workbook_BeforeClose(Cancel As Boolean) Workbook_NewSheet(ByVal Sh As Object). Workbook_WindowResize(ByVal Wn As Window). Workbook_WindowActivate(ByVal Wn As Window) Workbook_WindowDeactivate(ByVal Wn As Window). Workbook_AddInInstall(). Workbook_AddInUninstall. Workbook_Sync(ByVal SyncEventType As Office.MsoSyncEventType). Workbook_PivotTableCloseConnection(ByVal Target As PivotTable) Workbook_PivotTableOpenConnection(ByVal Target As PivotTable). Workbook_RowsetComplete(ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean). Workbook_BeforeXmlExport(ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean) Workbook_AfterXmlExport(ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult). Workbook_BeforeXmlImport(ByVal Map As XmlMap, ByVal Url As String, ByVal IsRefresh As Boolean, Cancel As Boolean). Workbook_AfterXmlImport(ByVal Map As XmlMap, ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult). Workbook Level Sheet and Chart Events. Worksheet Events Worksheet_Activate() Worksheet_Deactivate() Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean). Worksheet_Calculate() Worksheet_Change(ByVal Target As Range) Worksheet_SelectionChange(ByVal Target As Range). Worksheet_FollowHyperlink(ByVal Target As Hyperlink). Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Chart Sheet Events. Embedded Charts Chart_Activate() Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean) Chart_BeforeRightClick(Cancel As Boolean). Chart_Calculate() Chart_Deactivate(). Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long). Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long). Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long). Chart_Resize(). Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long). Chart_SeriesChange(ByVal SeriesIndex As Long, ByVal PointIndex As Long). Chart_DragOver() Chart_DragPlot() Application-Level Events. AppEvent_AfterCalculate() AppEvent_NewWorkbook(ByVal Wb As Workbook) AppEvent_ProtectedViewWindowActivate(ByVal Pvw As ProtectedViewWindow). AppEvent_ProtectedViewWindowBeforeClose(ByVal Pvw As ProtectedViewWindow, ByVal Reason As XlProtectedViewCloseReason, Cancel As Boolean). AppEvent_ProtectedViewWindowDeactivate(ByVal Pvw As ProtectedViewWindow) AppEvent_ProtectedViewWindowOpen(ByVal Pvw As ProtectedViewWindow). AppEvent_ProtectedViewWindowResize(ByVal Pvw As ProtectedViewWindow) AppEvent_SheetActivate (ByVal Sh As Object). AppEvent_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean). AppEvent_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean). AppEvent_SheetCalculate(ByVal Sh As Object) AppEvent_SheetChange(ByVal Sh As Object, ByVal Target As Range). AppEvent_SheetDeactivate(ByVal Sh As Object). AppEvent_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) AppEvent_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range). AppEvent_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable) AppEvent_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window) AppEvent_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window). AppEvent_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window). AppEvent_WorkbookActivate(ByVal Wb As Workbook) AppEvent_WorkbookAddinInstall(ByVal Wb As Workbook) AppEvent_WorkbookAddinUninstall(ByVal Wb As Workbook). AppEvent_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean) AppEvent_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean) AppEvent_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean) AppEvent_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object). AppEvent_WorkbookOpen(ByVal Wb As Workbook) AppEvent_WorkbookPivotTableCloseConnection(ByVal Wb As Workbook, ByVal Target As PivotTable) AppEvent_WorkbookPivotTableOpenConnection(ByVal Wb As Workbook, ByVal Target As PivotTable) AppEvent_WorkbookRowsetComplete(ByVal Wb As Workbook, ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean). AppEvent_WorkbookSync(ByVal Wb As Workbook, ByVal SyncEventType As Office.MsoSyncEventType). AppEvent_WorkbookBeforeXmlExport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean) AppEvent_WorkbookAfterXmlExport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult) AppEvent_WorkbookBeforeXmlImport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, ByVal IsRefresh As Boolean, Cancel As Boolean). AppEvent_WorkbookAfterXmlImport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult) Next Steps Chapter 10 Userforms: An Introduction User Interaction Methods Input Boxes. Message Boxes Creating a Userform Calling and Hiding a Userform Programming the Userform Userform Events Programming Controls Using Basic Form Controls Using Labels, Text Boxes, and Command Buttons Using a Spin Button on a Userform Using the MultiPage Control to Combine Forms Verifying Field Entry Illegal Window Closing Getting a Filename Next Steps Chapter 11 Creating Charts Charting in Excel 2010 Referencing Charts and Chart Objects in VBA Code. Creating a Chart. Specifying the Size and Location of a Chart Later Referring to a Specific Chart Recording Commands from the Layout or Design Tabs Specifying a Built-in Chart Type Specifying a Template Chart Type Changing a Chart's Layout or Style Using SetElement to Emulate Changes on the Layout Tab Using SetElement to Emulate Changes on the Layout Tab Using SetElement to Emulate Changes on the Layout Tab Using SetElement to Emulate Changes on the Layout Tab Changing a Chart Title Using VBA Emulating Changes on the Format Tab Using the Format Method to Access Formatting Options Creating Advanced Charts Creating True Open-High-Low-Close Stock Charts Creating Bins for a Frequency Chart Creating a Stacked Area Chart Exporting a Chart as a Graphic Creating a Dynamic Chart in a Userform Creating Pivot Charts. Next Steps. Chapter 12 Data Mining with Advanced Filter Replacing a Loop with AutoFilter. Using New AutoFilter Techniques Selecting Visible Cells Only Advanced Filter Is Easier in VBA Than in Excel Using the Excel Interface to Build an Advanced Filter. Using Advanced Filter to Extract a Unique List of Values Extracting a Unique List of Values with the User Interface. Extracting a Unique List of Values with VBA Code Getting Unique Combinations of Two or More Fields. Using Advanced Filter with Criteria Ranges. Joining Multiple Criteria with a Logical OR. Joining Two Criteria with a Logical AND. Other Slightly Complex Criteria Ranges. The Most Complex Criteria: Replacing the List of Values with a Condition Created as the Result of a Formula Using Filter in Place in Advanced Filter Catching No Records When Using Filter in Place Showing All Records After Filter in Place The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only Copying All Columns Copying a Subset of Columns and Reordering The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only Using Filter in Place with Unique Records Only Excel in Practice: Turning Off a Few Drop-Downs in the AutoFilter Next Steps Chapter 13 Using VBA to Create Pivot Tables Introducing Pivot Tables Understanding Versions New in Excel 2010 New Beginning with Excel 2007 Creating a Vanilla Pivot Table in the Excel Interface Understanding Compact Layout Building a Pivot Table in Excel VBA Defining the Pivot Cache Creating and Configuring the Pivot Table Adding Fields to the Data Area Learning Why You Cannot Move or Change Part of a Pivot Report Determining Size of a Finished Pivot Table to Convert the Pivot Table to Values Using Advanced Pivot Table Features Using Multiple Value Fields Counting the Number of Records Grouping Daily Dates to Months, Quarters, or Years Changing the Calculation to Show Percentages Eliminating Blank Cells in the Values Area Controlling the Sort Order with AutoSort Replicating the Report for Every Product Filtering a Data Set Manually Filtering Two or More Items in a Pivot Field Using the Conceptual Filters Using the Search Filter Setting Up Slicers to Filter a Pivot Table Filtering an OLAP Pivot Table Using Named Sets Using Other Pivot Table Features Calculated Data Fields Calculated Items Using ShowDetail to Filter a Recordset Changing the Layout from the Design Tab Suppressing Subtotals for Multiple Row Fields Next Steps Chapter 14 Excel Power File Operations List Files in a Directory Import CSV Read Entire TXT to Memory and Parse Combining and Separating Workbooks Separate Worksheets into Workbooks Combine Workbooks Filter and Copy Data to Separate Worksheets Export Data to Word Working with Cell Comments List Comments Resize Comments Resize Comments with Centering Place a Chart in a Comment Utilities to Wow Your Clients Using Conditional Formatting to Highlight Selected Cell Highlight Selected Cell Without Using Conditional Formatting Custom Transpose Data Select/Deselect Noncontiguous Cells Techniques for VBA Pros Pivot Table Drill-Down Speedy Page Setup Calculating Time to Execute Code Custom Sort Order Cell Progress Indicator Protected Password Box Change Case Selecting with SpecialCells ActiveX Right-Click Menu Cool Applications Historical Stock/Fund Quotes Using VBA Extensibility to Add Code to New Workbooks Next Steps. Chapter 15 Data Visualizations and Conditional Formatting Introduction to Data Visualizations VBA Methods and Properties for Data Visualizations Adding Data Bars to a Range Adding Color Scales to a Range Adding Icon Sets to a Range Specifying an Icon Set Specifying Ranges for Each Icon Using Visualization Tricks Creating an Icon Set for a Subset of a Range Using Two Colors of Data Bars in a Range Using Other Conditional Formatting Methods Formatting Cells That Are Above or Below Average Formatting Cells in the Top 10 or Bottom 5 Formatting Unique or Duplicate Cells Formatting Cells Based on Their Value Formatting Cells That Contain Text Formatting Cells That Contain Dates Formatting Cells That Contain Blanks or Errors Using a Formula to Determine Which Cells to Format Using the New NumberFormat Property Next Steps Chapter 16 Reading from and Writing to the Web Getting Data from the Web Manually Creating a Web Query and Refreshing with VBA Using VBA to Update an Existing Web Query Building Many Web Queries with VBA Using Application.OnTime to Periodically Analyze Data Scheduled Procedures Require Ready Mode Specifying a Window of Time for an Update Canceling a Previously Scheduled Macro Closing Excel Cancels All Pending Scheduled Macros Scheduling a Macro to Run x Minutes in the Future Scheduling a Verbal Reminder Scheduling a Macro to Run Every 2 Minutes Publishing Data to a Web Page Using VBA to CreateCustom Web Pages Using Excel as a Content Management System Bonus: FTP from Excel Next Steps Chapter 17 Dashboarding with Sparklines in Excel 2010 Creating Sparklines Scaling the Sparklines Formatting Sparklines Using Theme Colors Using RGB Colors Formatting Sparkline Elements Formatting Win/Loss Charts Creating a Dashboard Observations About Sparklines Creating 100's of Individual Sparklines in a Dashboard Next Steps Chapter 18 Automating Word Early Binding Compile Error: Can't Find Object or Library Late Binding Creating and Referencing Objects The New Keyword CreateObject Function GetObject Function Using Constant Values Using the Watch Window to Retrieve the Real Value of a Constant Using the Object Browser to Retrieve the Real Value of a Constant Understanding Word's Objects Document Object Selection Object Range Object Bookmarks Controlling Form Fields in Word Next Steps Chapter 19 Arrays Declare an Array Multidimensional Arrays Fill an Array Empty an Array Arrays Make It Easier to Manipulate Data, but Is That All? Dynamic Arrays Passing an Array Next Steps Chapter 20 Text File Processing Importing from Text Files Importing Text Files with Fewer Than 1,048,576 Rows Reading Text Files with More Than 1,048,576 Rows Writing Text Files Next Steps Chapter 21 Using Access as a Back End to Enhance Multiuser Access to Data ADO Versus DAO The Tools of ADO Adding a Record to the Database Retrieving Records from the Database Updating an Existing Record Deleting Records via ADO Summarizing Records via ADO Other Utilities via ADO Checking for the Existence of Tables Checking for the Existence of a Field Adding a Table On the Fly Adding a Field On the Fly SQL Server Examples Next Steps Chapter 22 Creating Classes, Records, and Collections Inserting a Class Module Trapping Application and Embedded Chart Events Application Events Embedded Chart Events Creating a Custom Object Using a Custom Object Using Property Let and Property Get to Control How Users Utilize Custom Objects Collections Creating a Collection in a Standard Module Creating a Collection in a Class Module User-Defined Types Next Steps Chapter 23 Advanced Userform Techniques Using the UserForm Toolbar in the Design of Controls on Userforms More Userform Controls Check Boxes Tab Strips RefEdit Toggle Buttons Using a Scrollbar As a Slider to Select Values Controls and Collections Modeless Userforms Using Hyperlinks in Userforms Adding Controls at Runtime Resizing the Userform On-the-fly Adding a Control On-the-fly Sizing On-the-fly Adding Other Controls Adding an Image On-the-fly Putting It All Together Adding Help to the Userform Showing Accelerator Keys Adding Control Tip Text Creating the Tab Order Coloring the Active Control Transparent Forms Next Steps Chapter 24 Windows API What Is the Windows API? Understanding an API Declaration Using an API Declaration API Examples Retrieve the Computer Name Check Whether an Excel File Is Open on a Network Retrieve Display-Resolution Information Custom About Dialog Disable the X for Closing a Userform Running Timer Playing Sounds Retrieving a File Path Finding More API Declarations Next Steps Chapter 25 Handling Errors What Happens When an Error Occurs? Debug Error Inside Userform Code Is Misleading Basic Error Handling with the On Error GoTo Syntax Generic Error Handlers Handling Errors by Choosing to Ignore Them Suppressing Excel Warnings Encountering Errors on Purpose Train Your Clients Errors While Developing Versus Errors Months Later Runtime Error 9: Subscript Out of Range RunTime Error 1004: Method Range of Object Global Failed The Ills of Protecting Code More Problems with Passwords Errors Caused by Different Versions Next Steps Chapter 26 Customizing the Ribbon to Run Macros Out with the Old, In with the New Where to Add Your Code: customui Folder and File Creating the Tab and Group Adding a Control to Your Ribbon Accessing the File Structure Understanding the RELS File Renaming the Excel File and Opening the Workbook Custom UI Editor Tool Using Images on Buttons Microsoft Office Icons Custom Icon Images Troubleshooting Error Messages The Attribute Attribute Name on the Element customui Ribbon Is Not Defined in the DTD/Schema Illegal Qualified Name Character Element customui Tag Name Is Unexpected According to Content Model of Parent Element customui Tag Name Excel Found Unreadable Content Wrong Number of Arguments or Invalid Property Assignment Nothing Happens Other Ways to Run a Macro Keyboard Shortcut Attach a Macro to a Command Button Attach a Macro to a Shape Attach a Macro to an ActiveX Control Running a Macro from a Hyperlink Next Steps Chapter 27 Creating Add-Ins Characteristics of Standard Add-Ins Converting an Excel Workbook to an Add-In Using Save As to Convert a File to an Add-In Using the VB Editor to Convert a File to an Add-In Having Your Client Install the Add-In Standard Add-Ins Are Not Secure Closing Add-Ins Removing Add-Ins Using a Hidden Workbook as an Alternative to an Add-In Next Steps 9780789743145 TOC 6/1/2010
