- Copyright
- About the Authors
- Acknowledgments
- We Want to Hear from You!
- Introduction
- 1. Unleash the Power of Excel with VBA
- The Power of Excel
- Barriers to Entry
- The Macro Recorder Doesn’t Work!
- Knowing Your Tools—The Developer Ribbon
- Macro Security
- Overview of Recording, Storing, and Running a Macro
- Running a Macro
- Using New File Types in Excel 2007
- Understanding the Visual Basic Editor
- Understanding Shortcomings of the Macro Recorder
- Next Steps: Learning VBA Is the Solution
- 2. This Sounds Like BASIC, So Why Doesn’t It Look Familiar?
- I Can’t Understand This Code
- Understanding the Parts of VBA “Speech”
- Is VBA Really This Hard? No!
- Examining Recorded Macro Code—Using the VB Editor and Help
- Using Debugging Tools to Figure Out Recorded Code
- The Ultimate Reference to All Objects, Methods, Properties
- Five Easy Tips for Cleaning Up Recorded Code
- Putting It All Together—Fixing the Recorded Code
- Next Steps
- 3. Referring to Ranges
- The Range Object
- Using the Upper-Left and Lower-Right Corners of a Selection to Specify a Range
- Named Ranges
- Shortcut for Referencing Ranges
- Referencing Ranges in Other Sheets
- Referencing a Range Relative to Another Range
- Using the Cells Property to Select a Range
- Using the Offset Property to Refer to a Range
- Using the Resize Property to Change the Size of a Range
- Using the Columns and Rows Properties to Specify a Range
- Using the Union Method to Join Multiple Ranges
- Using the Intersect Method to Create a New Range from Overlapping Ranges
- Using the ISEMPTY Function to Check Whether a Cell Is Empty
- Using the CurrentRegion Property to Quickly Select a Data Range
- Using the Areas Collection to Return a Noncontiguous Range
- Referencing Tables
- Next Steps
- 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 Email Address
- Sum Cells Based on the Interior Color
- Count Unique Values
- Remove Duplicates from a Range
- Find the First Non-Zero-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
- 5. Looping and Flow Control
- 6. R1C1-Style Formulas
- Referring to Cells: A1 Versus R1C1 References
- Switching Excel to Display R1C1 Style References
- The Miracle of Excel Formulas
- Explanation of R1C1 Reference Style
- Conditional Formatting—R1C1 Required
- Array Formulas Require R1C1 Formulas
- Next Steps
- 7. What’s New in Excel 2007 and What’s Changed
- 8. Create and Manipulate Names in VBA
- 9. Event Programming
- Levels of Events
- Using 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_SheetActivate(ByVal Sh As Object)
- Workbook_SheetBeforeDoubleClick (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
- Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
- Workbook_SheetCalculate(ByVal Sh As Object)
- Workbook_SheetChange (ByVal Sh As Object, ByVal Target As Range)
- Workbook_Sync(ByVal SyncEventType As Office.MsoSyncEventType)
- Workbook_SheetDeactivate (ByVal Sh As Object)
- Workbook_SheetFollowHyperlink (ByVal Sh As Object, ByVal Target As Hyperlink)
- Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
- 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)
- 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)
- 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_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_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)
- Next Steps
- 10. Userforms—An Introduction
- 11. Creating Charts
- Charting in Excel 2007
- Coding for New Charting Features in Excel 2007
- Referencing Charts and Chart Objects in VBA Code
- Creating a Chart
- Recording Commands from the Layout or Design Ribbons
- Using SetElement to Emulate Changes on the Layout Ribbon
- Changing a Chart Title Using VBA
- Emulating Changes on the Format Ribbon
- Using the Watch Window to Discover Object Settings
- Creating Advanced Charts
- Exporting a Chart as a Graphic
- Creating Pivot Charts
- Next Steps
- 12. Data Mining with Advanced Filter
- Advanced Filter Is Easier in VBA Than in Excel
- Using Advanced Filter to Extract a Unique List of Values
- Using Advanced Filter with Criteria Ranges
- Using Filter in Place in Advanced Filter
- The Real Workhorse: xlFilterCopy with All Records Rather than Unique Records Only
- Using AutoFilter
- Next Steps
- 13. Using VBA to Create Pivot Tables
- Introducing Pivot Tables
- Understanding Versions
- Creating a Vanilla Pivot Table in the Excel Interface
- Building a Pivot Table in Excel VBA
- Creating a Report Showing Revenue by Product
- Handling Additional Annoyances When Creating Your Final Report
- Addressing Issues with Two or More Data Fields
- Summarizing Date Fields with Grouping
- Using Advanced Pivot Table Techniques
- Controlling the Sort Order Manually
- Using Sum, Average, Count, Min, Max, and More
- Creating Report Percentages
- Using New Pivot Table Features in Excel 2007
- Next Steps
- 14. Excel Power
- 15. Data Visualizations and Conditional Formatting
- Introduction to Data Visualizations
- New 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
- Using Visualization Tricks
- 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
- 16. Reading from and Writing to the Web
- 17. XML in Excel 2007
- 18. Automating Word
- 19. Arrays
- 20. Text File Processing
- 21. Using Access as a Back End to Enhance Multi-User Access to Data
- 22. Creating Classes, Records, and Collections
- 23. Advanced Userform Techniques
- 24. Windows Application Programming Interface (API)
- 25. Handling Errors
- 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
- Using Images on Buttons
- 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
- Next Steps
- 27. Creating Add-Ins
VBA and Macros for Microsoft® Office Excel® 2007
Copyright © 2008 by Que Publishing©
All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.
ISBN-13: 978-0-7897-3682-6
Jelen, Bill. VBA and macros for Microsoft Office Excel 2007 / Bill Jelen. p. cm. ISBN 0-7897-3682-9 1. Microsoft Excel (Computer file) 2. Microsoft Visual Basic forapplications. 3. Business—Computer programs. 4. Electronicspreadsheets. I. Title. II. Title: Visual Basic for applications andmacros for Microsoft Office Excel 2007. HF5548.4.M523J457 2008 005.54—dc22 2007024410
Printed in the United States of America
Eighth Printing: May 2012
Trademarks
All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalized. Que Publishing cannot attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of any trademark or service mark.
Warning and Disclaimer
Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.
Bulk Sales
Que Publishing offers excellent discounts on this book when ordered in quantity for bulk purchases or special sales. For more information, please contact
U.S. Corporate and Government Sales 1-800-382-3419 [email protected]
For sales outside of the U.S., please contact
International Sales [email protected]
Associate Publisher Greg WiegandAcquisitions Editor Loretta YatesDevelopment Editor Laura NormanManaging Editor Gina KanouseProject Editor Betsy HarrisCopy Editor Keith ClineSenior Indexer Cheryl LenserProofreader Paula LowellTechnical Editor Christian KenyeresPublishing Coordinator Cindy TeetersBook Designer Anne JonesCompositor Nonie Ratcliff
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.