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
- 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
Product information
- Title: Business Solutions VBA and Macros for Microsoft® Office Excel® 2007
- Author(s):
- Release date: August 2007
- Publisher(s): Que
- ISBN: None
You might also like
book
Business Solutions VBA for the 2007 Microsoft Office System
“VBA for the 2007 Microsoft Office System is jam-packed with code samples that you’ll be able …
book
Microsoft® Offic Excel® 2007 Visual Basic® for Applications Step by Step
Quickly teach yourself how to automate tasks and create custom spreadsheet solutions with Excel 2007 Visual …
book
Alison Balter's Mastering Access 2002 Enterprise Development
Developing applications for the enterprise introduces challenges quite different from those associated with developing applications for …
book
Microsoft® SQL Server™ 2005: Applied Techniques Step by Step
SQL Server 2005 is Microsoft’s next-generation data management and analysis solution that delivers increased security, scalability, …