Cover image for Business Solutions VBA and Macros for Microsoft

Business Solutions VBA and Macros for Microsoft

Author Tracy Syrstad , Bill Jelen
Release Date: 2007/08/01
ISBN: 9780789736826
Topic:
0%
32
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

“In this day and age of ‘too much information and not enough time,’ the ability to get to the bottom line quickly and in a concise method is what excels companies to the top of their industry. The techniques in this book will allow you to do things you only dreamt of.”

—Jerry Kohl, president of Brighton Collectibles

Develop your Excel macro programming skills using VBA instantly with proven techniques

Automate Reports

Handle Errors

Master Pivot Tables

Produce Charts

Build User-Defined Functions

Migrate to Excel 2007

Query Web Data

Build Dialog Boxes

Use Data Visualizations

Automate Word

You are an expert in Excel, but the macro recorder doesn’t work and you can’t make heads or tails out of the recorded code. If this is you, buy this book. Macros that you record today might work today but not tomorrow. Recorded macros might handle a dataset with 14 records but not one with 12 or 16 records. These are all common problems with the macro recorder that unfortunately cause too many Excel gurus to turn away from writing macros. This book shows you why the macro recorder fails and the steps needed to convert recorded code into code that will work every day with every dataset. The book assumes that you know Excel well, but there is no need for prior programming experience. This book describes everything you could conceivably need to know to automate reports and design applications in Excel VBA. Whether you want to automate reports for your office or design full-blown applications for others, this book is for you.

  • Learn VBA syntax as easy-to-understand English

  • Automate Excel’s power tools: Pivot Tables, Charts, Advanced Filters

  • Save hours per week by automating redundant tasks

  • Create applications built on top of Excel with custom dialog boxes

  • Automatically produce hundreds of Excel reports in seconds

  • Understand how changes in Excel 2007 impact your VBA macros

Introduction   1

1 Unleash the Power of Excel with VBA      7

2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar?       29

3 Referring to Ranges 61

4 User-Defined Functions     75

5 Looping and Flow Control   101

6 R1C1-Style Formulas        121

7 What’s New in Excel 2007 and What's Changed  135

8 Create and Manipulate Names in VBA      143

9 Event Programming 155

10 UserForms--An Introduction       177

11 Creating Charts    197

12 Data Mining with Advanced Filter          249

13 Using VBA to Create Pivot Tables         281

14 Excel Power         337

15 Data Visualizations and Conditional Formatting  373

16 Reading from and Writing to the Web              393

17 XML in Excel 2007 413

18 Automating Word  421

19 Arrays       441

20 Text File Processing    449

21 Using Access as a Back End to Enhance Multi-User Access to Data   461

22 Creating Classes, Records, and Collections       477

23 Advanced UserForm Techniques  493

24 Windows Application Programming Interface (API)       517

25 Handling Errors     529

26 Customizing the Ribbon to Run Macros   543

27 Creating Add-Ins  569

Index  577

Table of Contents

  1. Copyright
    1. Dedications
  2. About the Authors
  3. Acknowledgments
  4. We Want to Hear from You!
  5. Introduction
    1. Getting Results with VBA
    2. What Is in This Book
      1. Getting Up the Learning Curve
      2. Excel VBA Power
      3. The Techie Stuff Needed to Produce Applications for Others
      4. Does This Book Teach Excel?
    3. The Future of VBA and Windows Versions of Excel
      1. Versions
    4. Special Elements and Typographical Conventions
    5. Code Files
    6. Next Steps
  6. 1. Unleash the Power of Excel with VBA
    1. The Power of Excel
    2. Barriers to Entry
    3. The Macro Recorder Doesn’t Work!
      1. Visual Basic Is Not Like BASIC
      2. The Good News—It Is Easy to Climb the Learning Curve
      3. The Great News—Excel with VBA Is Worth the Effort
    4. Knowing Your Tools—The Developer Ribbon
    5. Macro Security
      1. Adding a Trusted Location
      2. Using Macro Settings to Enable Macros in Workbooks Outside of Trusted Locations
      3. Using Disable All Macros with Notification
    6. Overview of Recording, Storing, and Running a Macro
      1. Filling Out the Record Macro Dialog
    7. Running a Macro
      1. Creating a Macro Button
      2. Assigning a Macro to a Form Control, a Text Box, or a Shape
    8. Using New File Types in Excel 2007
    9. Understanding the Visual Basic Editor
      1. VB Editor Settings
        1. Customizing VB Editor Options Settings
        2. Enabling Digital Signatures
      2. The Project Explorer
      3. The Properties Window
    10. Understanding Shortcomings of the Macro Recorder
      1. Examining Code in the Programming Window
      2. Running the Same Macro on Another Day Produces Undesired Results
      3. A Possible Solution: Using Relative References When Recording
    11. Next Steps: Learning VBA Is the Solution
  7. 2. This Sounds Like BASIC, So Why Doesn’t It Look Familiar?
    1. I Can’t Understand This Code
    2. Understanding the Parts of VBA “Speech”
    3. Is VBA Really This Hard? No!
      1. VBA Help Files—Using F1 to Find Anything
      2. Using Help Topics
    4. Examining Recorded Macro Code—Using the VB Editor and Help
      1. Optional Parameters
      2. Defined Constants
      3. Properties Can Return Objects
    5. Using Debugging Tools to Figure Out Recorded Code
      1. Stepping Through Code
      2. More Debugging Options—Breakpoints
      3. Backing Up or Moving Forward in Code
      4. Not Stepping Through Each Line of Code
      5. Querying Anything While Stepping Through Code
        1. Using the Immediate Window
        2. Querying by Hovering
        3. Querying by Using a Watch Window
      6. Using a Watch to Set a Breakpoint
      7. Using a Watch on an Object
    6. The Ultimate Reference to All Objects, Methods, Properties
    7. Five Easy Tips for Cleaning Up Recorded Code
      1. Tip 1: Don’t Select Anything
      2. Tip 2: Ride the Range from the Bottom to Find Last Row
      3. Tip 3: Use Variables to Avoid Hard-Coding Rows and Formulas
      4. Tip 4: Learn to Copy and Paste in a Single Statement
      5. Tip 5: Use With...End With If You Are Performing Multiple Actions to the Same Cell or Range of Cells
    8. Putting It All Together—Fixing the Recorded Code
    9. Next Steps
  8. 3. Referring to Ranges
    1. The Range Object
    2. Using the Upper-Left and Lower-Right Corners of a Selection to Specify a Range
    3. Named Ranges
    4. Shortcut for Referencing Ranges
    5. Referencing Ranges in Other Sheets
    6. Referencing a Range Relative to Another Range
    7. Using the Cells Property to Select a Range
      1. Using the Cells Property in the Range Property
    8. Using the Offset Property to Refer to a Range
    9. Using the Resize Property to Change the Size of a Range
    10. Using the Columns and Rows Properties to Specify a Range
    11. Using the Union Method to Join Multiple Ranges
    12. Using the Intersect Method to Create a New Range from Overlapping Ranges
    13. Using the ISEMPTY Function to Check Whether a Cell Is Empty
    14. Using the CurrentRegion Property to Quickly Select a Data Range
    15. Using the Areas Collection to Return a Noncontiguous Range
    16. Referencing Tables
    17. Next Steps
  9. 4. User-Defined Functions
    1. Creating User-Defined Functions
    2. Sharing UDFs
    3. Useful Custom Excel Functions
      1. Set the Current Workbook’s Name in a Cell
      2. Set the Current Workbook’s Name and File Path in a Cell
      3. Check Whether a Workbook Is Open
      4. Check Whether a Sheet in an Open Workbook Exists
      5. Count the Number of Workbooks in a Directory
      6. Retrieve USERID
      7. Retrieve Date and Time of Last Save
      8. Retrieve Permanent Date and Time
      9. Validate an Email Address
      10. Sum Cells Based on the Interior Color
      11. Count Unique Values
      12. Remove Duplicates from a Range
      13. Find the First Non-Zero-Length Cell in a Range
      14. Substitute Multiple Characters
      15. Retrieve Numbers from Mixed Text
      16. Convert Week Number into Date
      17. Separate Delimited String
      18. Sort and Concatenate
      19. Sort Numeric and Alpha Characters
      20. Search for a String within Text
      21. Reverse the Contents of a Cell
      22. Multiple Max
      23. Return Hyperlink Address
      24. Return the Column Letter of a Cell Address
      25. Static Random
      26. Using Select Case on a Worksheet
    4. Next Steps
  10. 5. Looping and Flow Control
    1. For...Next Loops
      1. Using Variables in the For Statement
      2. Variations on the For...Next Loop
      3. Exiting a Loop Early after a Condition Is Met
      4. Nesting One Loop Inside Another Loop
    2. Do Loops
      1. Using the While or Until Clause in Do Loops
      2. While...Wend Loops
    3. The VBA Loop: For Each
      1. Object Variables
    4. Flow Control: Using If...Then...Else and Select Case
      1. Basic Flow Control: If...Then...Else
      2. Conditions
      3. If...Then...End If
      4. Either/Or Decisions: If...Then...Else...End If
      5. Using If...Else If...End If for Multiple Conditions
      6. Using Select Case...End Select for Multiple Conditions
      7. Complex Expressions in Case Statements
      8. Nesting If Statements
    5. Next Steps
  11. 6. R1C1-Style Formulas
    1. Referring to Cells: A1 Versus R1C1 References
    2. Switching Excel to Display R1C1 Style References
    3. The Miracle of Excel Formulas
      1. Enter a Formula Once and Copy 1,000 Times
      2. The Secret—It Is Not That Amazing
    4. Explanation of R1C1 Reference Style
      1. Using R1C1 with Relative References
      2. Using R1C1 with Absolute References
      3. Using R1C1 with Mixed References
      4. Referring to Entire Columns or Rows with R1C1 Style
      5. Replacing Many A1 Formulas with a Single R1C1 Formula
        1. Building the Table
        2. An Interesting Twist
      6. Remembering Column Numbers Associated with Column Letters
    5. Conditional Formatting—R1C1 Required
      1. Setting Up Conditional Formatting in the User Interface
      2. Setting Up Conditional Formats in VBA
    6. Array Formulas Require R1C1 Formulas
    7. Next Steps
  12. 7. What’s New in Excel 2007 and What’s Changed
    1. If It’s Changed in the Front End, It’s Changed in VBA
      1. The Ribbon
      2. Charts
      3. Pivot Tables
      4. Conditional Formatting
      5. Tables
      6. Sorting
      7. SmartArt
    2. The Macro Recorder Won’t Record Actions That It Did Record in Earlier Excel Versions
    3. Learning the New Objects and Methods
    4. Compatibility Mode
      1. Version
      2. Excel8CompatibilityMode
    5. Next Steps
  13. 8. Create and Manipulate Names in VBA
    1. Excel Names
    2. Global Versus Local Names
    3. Adding Names
    4. Deleting Names
    5. Adding Comments
    6. Types of Names
      1. Formulas
      2. Strings
      3. Numbers
      4. Tables
      5. Using Arrays in Names
      6. Reserved Names
    7. Hiding Names
    8. Checking for the Existence of a Name
    9. Next Steps
  14. 9. Event Programming
    1. Levels of Events
    2. Using Events
      1. Event Parameters
      2. Enabling Events
    3. Workbook Events
      1. Workbook_Activate()
      2. Workbook_Deactivate()
      3. Workbook_Open()
      4. Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      5. Workbook_BeforePrint(Cancel As Boolean)
      6. Workbook_BeforeClose(Cancel As Boolean)
      7. Workbook_NewSheet(ByVal Sh As Object)
      8. Workbook_WindowResize(ByVal Wn As Window)
      9. Workbook_WindowActivate(ByVal Wn As Window)
      10. Workbook_WindowDeactivate(ByVal Wn As Window)
      11. Workbook_AddInInstall()
      12. Workbook_AddInUninstall
      13. Workbook_SheetActivate(ByVal Sh As Object)
      14. Workbook_SheetBeforeDoubleClick (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
      15. Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
      16. Workbook_SheetCalculate(ByVal Sh As Object)
      17. Workbook_SheetChange (ByVal Sh As Object, ByVal Target As Range)
      18. Workbook_Sync(ByVal SyncEventType As Office.MsoSyncEventType)
      19. Workbook_SheetDeactivate (ByVal Sh As Object)
      20. Workbook_SheetFollowHyperlink (ByVal Sh As Object, ByVal Target As Hyperlink)
      21. Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
      22. Workbook_PivotTableCloseConnection(ByVal Target As PivotTable)
      23. Workbook_PivotTableOpenConnection(ByVal Target As PivotTable)
      24. Workbook_RowsetComplete(ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean)
    4. Worksheet Events
      1. Worksheet_Activate()
      2. Worksheet_Deactivate()
      3. Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      4. Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
      5. Worksheet_Calculate()
      6. Worksheet_Change(ByVal Target As Range)
      7. Worksheet_SelectionChange(ByVal Target As Range)
      8. Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    5. Chart Sheet Events
      1. Embedded Charts
      2. Chart_Activate()
      3. Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
      4. Chart_BeforeRightClick(Cancel As Boolean)
      5. Chart_Calculate()
      6. Chart_Deactivate()
      7. Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
      8. Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
      9. Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
      10. Chart_Resize()
      11. Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
      12. Chart_SeriesChange(ByVal SeriesIndex As Long, ByVal PointIndex As Long)
      13. Chart_DragOver()
      14. Chart_DragPlot()
    6. Application-Level Events
      1. AppEvent_AfterCalculate()
      2. AppEvent_NewWorkbook(ByVal Wb As Workbook)
      3. AppEvent_SheetActivate (ByVal Sh As Object)
      4. AppEvent_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
      5. AppEvent_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
      6. AppEvent_SheetCalculate(ByVal Sh As Object)
      7. AppEvent_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      8. AppEvent_SheetDeactivate(ByVal Sh As Object)
      9. AppEvent_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
      10. AppEvent_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
      11. AppEvent_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
      12. AppEvent_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)
      13. AppEvent_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)
      14. AppEvent_WorkbookActivate(ByVal Wb As Workbook)
      15. AppEvent_WorkbookAddinInstall(ByVal Wb As Workbook)
      16. AppEvent_WorkbookAddinUninstall(ByVal Wb As Workbook)
      17. AppEvent_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
      18. AppEvent_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
      19. AppEvent_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
      20. AppEvent_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
      21. AppEvent_WorkbookOpen(ByVal Wb As Workbook)
      22. AppEvent_WorkbookPivotTableCloseConnection(ByVal Wb As Workbook, ByVal Target As PivotTable)
      23. AppEvent_WorkbookPivotTableOpenConnection(ByVal Wb As Workbook, ByVal Target As PivotTable)
      24. AppEvent_WorkbookRowsetComplete(ByVal Wb As Workbook, ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean)
      25. AppEvent_WorkbookSync(ByVal Wb As Workbook, ByVal SyncEventType As Office.MsoSyncEventType)
    7. Next Steps
  15. 10. Userforms—An Introduction
    1. User Interaction Methods
      1. Input Boxes
      2. Message Boxes
    2. Creating a Userform
    3. Calling and Hiding a Userform
    4. Programming the Userform
      1. Userform Events
    5. Programming Controls
    6. Using Basic Form Controls
      1. Using Labels, Text Boxes, and Command Buttons
      2. Deciding Whether to Use List Boxes or Combo Boxes in Forms
        1. Using the MultiSelect Property of a List Box
      3. Adding Option Buttons to a Userform
      4. Adding Graphics to a Userform
      5. Using a Spin Button on a Userform
      6. Using the MultiPage Control to Combine Forms
    7. Verifying Field Entry
    8. Illegal Window Closing
    9. Getting a Filename
    10. Next Steps
  16. 11. Creating Charts
    1. Charting in Excel 2007
    2. Coding for New Charting Features in Excel 2007
    3. Referencing Charts and Chart Objects in VBA Code
    4. Creating a Chart
      1. Specifying the Size and Location of a Chart
      2. Later Referring to a Specific Chart
    5. Recording Commands from the Layout or Design Ribbons
      1. Specifying a Built-in Chart Type
      2. Specifying a Template Chart Type
      3. Changing a Chart’s Layout or Style
    6. Using SetElement to Emulate Changes on the Layout Ribbon
    7. Changing a Chart Title Using VBA
    8. Emulating Changes on the Format Ribbon
      1. Using the Format Method to Access New Formatting Options
        1. Changing an Object’s Fill
        2. Formatting Line Settings
        3. Formatting Glow Settings
        4. Formatting Shadow Settings
        5. Formatting Reflection Settings
        6. Formatting Soft Edges
        7. Formatting 3-D Rotation Settings
        8. Changing the Bevel and 3-D Format
    9. Using the Watch Window to Discover Object Settings
    10. Creating Advanced Charts
      1. Creating True Open-High-Low-Close Stock Charts
      2. Creating Bins for a Frequency Chart
      3. Creating a Stacked Area Chart
    11. Exporting a Chart as a Graphic
      1. Creating a Dynamic Chart in a Userform
    12. Creating Pivot Charts
    13. Next Steps
  17. 12. Data Mining with Advanced Filter
    1. Advanced Filter Is Easier in VBA Than in Excel
    2. Using Advanced Filter to Extract a Unique List of Values
      1. Extracting a Unique List of Values with the User Interface
        1. Change the List Range to a Single Column
        2. Change the List Range to a Single Column
      2. Extracting a Unique List of Values with VBA Code
      3. Getting Unique Combinations of Two or More Fields
    3. Using Advanced Filter with Criteria Ranges
      1. Joining Multiple Criteria with a Logical OR
      2. Joining Two Criteria with a Logical AND
      3. Other Slightly Complex Criteria Ranges
      4. The Most Complex Criteria—Replacing the List of Values with a Condition Created as the Result of a Formula
        1. Setting Up a Condition as the Result of a Formula
        2. Using Formula-Based Conditions with VBA
        3. Using Formula-Based Conditions to Return Above-Average Records
    4. Using Filter in Place in Advanced Filter
      1. Catching No Records When Using Filter in Place
      2. Showing All Records after Filter in Place
      3. Using Filter in Place with Unique Records Only
    5. The Real Workhorse: xlFilterCopy with All Records Rather than Unique Records Only
      1. Copying All Columns
      2. Copying a Subset of Columns and Reordering
    6. Using AutoFilter
      1. Enabling AutoFilter with Code
      2. Turning Off a Few Drop-Downs in the AutoFilter
      3. Filtering a Column Using AutoFilters
      4. Selecting Multiple Values from a Filter
      5. Selecting a Dynamic Date Range Using AutoFilters
      6. Filtering Based on Color or Icon
    7. Next Steps
  18. 13. Using VBA to Create Pivot Tables
    1. Introducing Pivot Tables
    2. Understanding Versions
      1. New in Excel 2007
    3. Creating a Vanilla Pivot Table in the Excel Interface
      1. Understanding New Features in Excel 2007 Pivot Tables
    4. Building a Pivot Table in Excel VBA
      1. Defining the Pivot Cache
      2. Creating and Configuring the Pivot Table
      3. Getting a Sum Rather Than a Count
      4. Learning Why You Cannot Move or Change Part of a Pivot Report
      5. Determining Size of a Finished Pivot Table
    5. Creating a Report Showing Revenue by Product
      1. Eliminating Blank Cells in the Values Area
      2. Ensuring Table Layout Is Utilized
      3. Controlling the Sort Order with AutoSort
      4. Changing Default Number Format
      5. Suppressing Subtotals for Multiple Row Fields
      6. Suppressing Grand Total for Rows
    6. Handling Additional Annoyances When Creating Your Final Report
      1. Creating a New Workbook to Hold the Report
      2. Creating a Summary on a Blank Report Worksheet
      3. Filling the Outline View
      4. Handling Final Formatting
      5. Adding Subtotals
      6. Putting It All Together
    7. Addressing Issues with Two or More Data Fields
      1. Calculated Data Fields
    8. Summarizing Date Fields with Grouping
      1. Understanding the Group Method in VBA
      2. Group by Week
      3. Measuring Order Lead Time by Grouping Two Date Fields
    9. Using Advanced Pivot Table Techniques
      1. Using AutoShow to Produce Executive Overviews
      2. Using ShowDetail to Filter a Recordset
      3. Creating Reports for Each Region or Model
      4. Manually Filtering Two or More Items in a Pivot Field
    10. Controlling the Sort Order Manually
    11. Using Sum, Average, Count, Min, Max, and More
    12. Creating Report Percentages
      1. Percentage of Total
      2. Percentage Growth from Previous Month
      3. Percentage of a Specific Item
      4. Running Total
    13. Using New Pivot Table Features in Excel 2007
      1. Using the New Filters
      2. Applying a Table Style
      3. Changing the Layout From the Design Ribbon
    14. Next Steps
  19. 14. Excel Power
    1. File Operations
      1. List Files in a Directory
      2. Import CSV
      3. Read Entire CSV to Memory and Parse
    2. Combining and Separating Workbooks
      1. Separate Worksheets into Workbooks
      2. Combine Workbooks
      3. Filter and Copy Data to Separate Worksheets
      4. Export Data to Word
    3. Working with Cell Comments
      1. List Comments
      2. Resize Comments
      3. Resize Comments with Centering
      4. Place a Chart in a Comment
    4. Utilities to Wow Your Clients
      1. Using Conditional Formatting to Highlight Selected Cell
      2. Highlight Selected Cell Without Using Conditional Formatting
      3. Custom Transpose Data
      4. Select/Deselect Noncontiguous Cells
    5. Techniques for VBA Pros
      1. Pivot Table Drill-Down
      2. Speedy Page Setup
      3. Calculating Time to Execute Code
      4. Custom Sort Order
      5. Cell Progress Indicator
      6. Protected Password Box
      7. Change Case
      8. Selecting with SpecialCells
      9. ActiveX Right-Click Menu
    6. Cool Applications
      1. Historical Stock/Fund Quotes
      2. Using VBA Extensibility to Add Code to New Workbooks
    7. Next Steps
  20. 15. Data Visualizations and Conditional Formatting
    1. Introduction to Data Visualizations
    2. New VBA Methods and Properties for Data Visualizations
    3. Adding Data Bars to a Range
    4. Adding Color Scales to a Range
    5. Adding Icon Sets to a Range
      1. Specifying an Icon Set
      2. Specifying Ranges for Each Icon
    6. Using Visualization Tricks
      1. Creating an Icon Set for a Subset of a Range
      2. Using Two Colors of Data Bars in a Range
    7. Using Other Conditional Formatting Methods
      1. Formatting Cells That Are Above or Below Average
      2. Formatting Cells in the Top 10 or Bottom 5
      3. Formatting Unique or Duplicate Cells
      4. Formatting Cells Based on Their Value
      5. Formatting Cells That Contain Text
      6. Formatting Cells That Contain Dates
      7. Formatting Cells That Contain Blanks or Errors
      8. Using a Formula to Determine Which Cells to Format
        1. Highlight the First Unique Occurrence of Each Value in a Range
        2. Highlight the Entire Row for the Largest Sales Value
      9. Using the New NumberFormat Property
    8. Next Steps
  21. 16. Reading from and Writing to the Web
    1. Getting Data from the Web
      1. Manually Creating a Web Query and Refreshing with VBA
      2. Using VBA to Update an Existing Web Query
      3. Building a New Web Query with VBA
    2. Using Streaming Data
    3. Using Application.OnTime to Periodically Analyze Data
      1. Scheduled Procedures Require Ready Mode
      2. Specifying a Window of Time for an Update
      3. Canceling a Previously Scheduled Macro
      4. Closing Excel Cancels All Pending Scheduled Macros
      5. Scheduling a Macro to Run x Minutes in the Future
      6. Scheduling a Verbal Reminder
      7. Scheduling a Macro to Run Every Two Minutes
    4. Publishing Data to a Web Page
      1. Using VBA to Create Custom Web Pages
      2. Using Excel as a Content Management System
      3. Bonus: FTP from Excel
    5. Trusting Web Content
    6. Next Steps
  22. 17. XML in Excel 2007
    1. What Is XML?
    2. Simple XML Rules
    3. Universal File Format
    4. XML as the New Universal File Format
    5. The Alphabet Soup of XML
    6. Microsoft’s Use of XML as a File Type
      1. How Excel 2007 Stores Workbooks with XML
    7. Next Steps
  23. 18. Automating Word
    1. Early Binding
      1. Compile Error: Can’t Find Object or Library
    2. Late Binding
    3. Creating and Referencing Objects
      1. Keyword New
      2. CreateObject Function
      3. GetObject Function
    4. Using Constant Values
      1. Using the Watch Window to Retrieve the Real Value of a Constant
      2. Using the Object Browser to Retrieve the Real Value of a Constant
    5. Understanding Word’s Objects
      1. Document Object
        1. Create a New Blank Document
        2. Open an Existing Document
        3. Save Changes to a Document
        4. Close an Open Document
        5. Print a Document
      2. Selection Object
        1. HomeKey/EndKey
        2. TypeText
      3. Range Object
        1. Define a Range
        2. Format a Range
      4. Bookmarks
    6. Controlling Word’s Form Fields
    7. Next Steps
  24. 19. Arrays
    1. Declare an Array
      1. Multidimensional Arrays
    2. Fill an Array
    3. Empty an Array
    4. Arrays Can Make It Easier to Manipulate Data, But Is That All?
    5. Dynamic Arrays
    6. Passing an Array
    7. Next Steps
  25. 20. Text File Processing
    1. Importing from Text Files
      1. Importing Text Files with Fewer Than 1,084,576 Rows
        1. Opening a Fixed-Width File
        2. Opening a Delimited File
      2. Reading Text Files with More Than 1,084,576 Rows
        1. Reading Text Files One Row at a Time
        2. Reading Text Files with More Than 1,084,576 Rows
    2. Writing Text Files
    3. Next Steps
  26. 21. Using Access as a Back End to Enhance Multi-User Access to Data
    1. ADO Versus DAO
    2. The Tools of ADO
    3. Adding a Record to the Database
    4. Retrieving Records from the Database
    5. Updating an Existing Record
    6. Deleting Records via ADO
    7. Summarizing Records via ADO
    8. Other Utilities via ADO
      1. Checking for Existence of Tables
      2. Checking for Existence of a Field
      3. Adding a Table On the Fly
      4. Adding a Field On the Fly
    9. Next Steps
  27. 22. Creating Classes, Records, and Collections
    1. Inserting a Class Module
    2. Trapping Application and Embedded Chart Events
      1. Application Events
      2. Embedded Chart Events
    3. Creating a Custom Object
    4. Using a Custom Object
    5. Using Property Let and Property Get to Control How Users Utilize Custom Objects
    6. Collections
      1. Creating a Collection in a Standard Module
      2. Creating a Collection in a Class Module
    7. User-Defined Types (UDTs)
    8. Next Steps
  28. 23. Advanced Userform Techniques
    1. Using the UserForm Toolbar in the Design of Controls on Userforms
    2. More Userform Controls
      1. Check Boxes
      2. Tab Strips
      3. RefEdit
      4. Toggle Buttons
      5. Using a Scrollbar as a Slider to Select Values
    3. Controls and Collections
    4. Modeless Userforms
    5. Using Hyperlinks in Userforms
    6. Adding Controls at Runtime
      1. Resizing the Userform On-the-Fly
      2. Adding a Control On-the-Fly
      3. Sizing On-the-Fly
      4. Adding Other Controls
      5. Adding an Image On-the-Fly
      6. Putting It All Together
    7. Adding Help to the Userform
      1. Showing Accelerator Keys
      2. Adding Control Tip Text
      3. Creating the Tab Order
      4. Coloring the Active Control
    8. Transparent Forms
    9. Next Steps
  29. 24. Windows Application Programming Interface (API)
    1. What Is the Windows API?
    2. Understanding an API Declaration
    3. Using an API Declaration
    4. API Examples
      1. Retrieve the Computer Name
      2. Check Whether an Excel File Is Open on a Network
      3. Retrieve Display-Resolution Information
      4. Custom About Dialog
      5. Disable the X for Closing a Userform
      6. Running Timer
      7. Playing Sounds
      8. Retrieving a File Path
    5. Finding More API Declarations
    6. Next Steps
  30. 25. Handling Errors
    1. What Happens When an Error Occurs
      1. Debug Error Inside Userform Code Is Misleading
    2. Basic Error Handling with the On Error GoTo Syntax
    3. Generic Error Handlers
      1. Handling Errors by Choosing to Ignore Them
      2. Suppressing Excel Warnings
      3. Encountering Errors on Purpose
    4. Train Your Clients
    5. Errors While Developing Versus Errors Months Later
      1. Runtime Error 9: Subscript Out of Range
      2. RunTime Error 1004: Method Range of Object Global Failed
    6. The Ills of Protecting Code
    7. More Problems with Passwords
    8. Errors Caused by Different Versions
    9. Next Steps
  31. 26. Customizing the Ribbon to Run Macros
    1. Out with the Old, In with the New
    2. Where to Add Your Code: customui Folder and File
    3. Creating the Tab and Group
    4. Adding a Control to Your Ribbon
    5. Accessing the File Structure
    6. Understanding the RELS File
    7. Renaming the Excel File and Opening the Workbook
      1. RibbonCustomizer
    8. Using Images on Buttons
      1. Microsoft Office icons
      2. Custom Icon Images
    9. Troubleshooting Error Messages
      1. The Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/Schema
      2. Illegal Qualified Name Character
      3. Element “customui Tag Name” Is Unexpected According to Content Model of Parent Element “customui Tag Name”
      4. Excel Found Unreadable Content
      5. Wrong Number of Arguments or Invalid Property Assignment
      6. Nothing Happens
    10. Other Ways to Run a Macro
      1. Keyboard Shortcut
      2. Attach a Macro to a Command Button
      3. Attach a Macro to an ActiveX Control
      4. Running a Macro from a Hyperlink
    11. Next Steps
  32. 27. Creating Add-Ins
    1. Characteristics of Standard Add-Ins
    2. Converting an Excel Workbook to an Add-In
      1. Using Save As to Convert a File to an Add-In
      2. Using the VB Editor to Convert a File to an Add-In
    3. Having Your Client Install the Add-In
      1. Standard Add-Ins Are Not Secure
      2. Closing Add-Ins
      3. Removing Add-Ins
    4. Using a Hidden Workbook as an Alternative to an Add-In
    5. Next Steps
18.191.88.249