About the Book
Everyone is looking for ways to save money
these days. That can be hard to do for businesses that have complex needs,
such as custom software applications. However, VBA and Macros for Microsoft
Excel can teach you ways to customize pre-existing software to meet your
specific needs. A variety of topics are covered that are sure to give you
a solid knowledge of the VBA language. Event programming, user forms, carts,
pivot tables, multi-dimensional arrays and Web queries are just a few of
the areas you will learn about in this book. Written by the principal behind
the leading Excel Web site, www.mrexcel.com, this book is sure to save
you time and money!
Related
Books
Business Solutions Books (Business
Solutions Books)
Table ofContents
Introduction.
Getting Results with VBA.
What Is in This Book.
A Brief History of Spreadsheets and
Macros.
The Future of VBA and Excel.
Special Elements and Typographical
Conventions.
Next Steps.
I. FIRST STEPS UP THE VBA LEARNING
CURVE.
1. Unleash the Power of Excel with
VBA!
The Power of Excel.
Barriers to Entry.
Knowing Your Tools-The Visual Basic
Toolbar.
Macro Security.
Overview of Recording, Storing, and
Running a Macro.
Running a Macro.
Understanding the Visual Basic Editor
(VBE).
Examining Code in the Programming
Window.
Running the Macro on Another Day Produces
Undesired Results.
Frustration.
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 Top-Left and Bottom-Right
Corners of a Selection to Specify a Range.
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 Non-contiguous Range.
Next Steps.
4. User-Defined Functions.
Creating User-Defined Functions.
Useful Custom Excel Functions.
Next Steps.
5. Looping.
For...Next Loops.
Do Loops.
The VBA Loop: For Each.
Next Steps.
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 Conditional
Formatting.
Next Steps.
7. Names.
Global Versus Local Names.
Adding Names.
Deleting Names.
Types of Names.
Hiding Names.
Checking for the Existence of a Name.
Next Steps.
8. Event Programming.
Levels of Events.
Using Events.
Workbook Events.
Worksheet Events.
Chart Sheet
Events.
Application-Level Events.
Next Steps.
9. UserForms-An Introduction.
User Interaction Methods.
Creating a Userform.
Calling and Hiding a Userform.
Using Basic Form Controls.
Using the Multipage Control to Combine
Forms.
Next Steps.
II. AUTOMATING EXCEL POWER IN VBA.
10. Charts.
Overview.
Embedded Charts Versus ChartSheets.
Creating a Chart with VBA.
Using Object Variables to Streamline
Code.
The Anatomy of a Chart.
Table of Chart Types.
Details of Various Chart Types.
Interactive Charts.
Exporting Charts as Images.
Drawing with X-Y Charts.
Custom Charts with VBA.
Next Steps.
11. 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 Instead of Unique Records Only.
AutoFilters.
Next Steps.
12. Pivot Tables.
Versions.
Creating a Vanilla Pivot Table in
Excel Interface.
Building a Pivot Table in Excel VBA.
Revenue by Customer for a Product
Line Manager.
Handling Additional Annoyances.
Product Profitability-Issues with
Two or More Data Fields.
Summarizing Date Fields with Grouping.
Advanced Pivot Table Techniques.
Sum, Average, Count, Min, Max, and
More.
Reporting Percentages.
Next Steps.
13. Excel Power.
Using VBA to Extend Excel.
File Operations.
Combining and Separating Workbooks.
Working with Cell Comments.
Utilities to Wow Your Clients.
Techniques for VBA Pros.
Cool Application.
Next Steps.
14. Reading from and Writing to
the Web.
Getting Data from the Web.
Using Streaming Data.
Using Application.OnTime to Periodically
Analyze Data.
Publishing Data to a Web Page.
Next Steps.
15. XML in Excel 2003 Professional.
What Is XML?
Simple XML Rules.
Universal File Format.
XML as the New Universal File Format.
The Alphabet Soup of XML.
Using XML to Round-Trip a Workbook
from Excel to HTML and Back.
Next Steps.
16. Automating Word.
Early Binding.
Late Binding.
Creating and Referencing Objects.
Word's Objects.
Next Steps.
III. TECHIE STUFF YOU WILL NEED
TO PRODUCE APPLICATIONS FOR THE ADMINISTRATOR TO RUN.
17. Arrays.
Declare an Array.
Fill an Array.
Empty an Array.
Arrays Can Make It Easier to Manipulate
Data, But Is That All?
Dynamic Arrays.
Passing an Array.
Next Steps.
18. Text File Processing.
Importing from Text Files.
Writing Text Files.
Next Steps.
19. Using Access as a Back End to
Enhance Multi-User 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.
Next Steps.
20. Creating Classes, Records, and
Collections.
Inserting a Class Module.
Trapping Application and 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.
User-Defined Types (UDTs).
Next Steps.
21. Advanced UserForm Techniques.
Using the UserForm Toolbar in the
Design of Controls on UserForms.
Controls and Collections.
More UserForm Controls.
Tabstrip.
RefEdit.
Modeless Userforms.
Hyperlinks in Userforms.
Adding Controls at Runtime.
Using a Scrollbar as a Slider to Select
Values.
Adding Help Tips to Controls.
Tab Order.
Coloring the Active Control.
Transparent Forms.
Next Steps.
22. Windows Application Programming
Interface (API).
What Is the Windows API?
Understanding an API Declaration.
Using an API Declaration.
API Examples.
Finding More API Declarations.
Next Steps.
23. Handling Errors.
What Happens When an Error Occurs.
Basic Error Handling with the On Error
GoTo Syntax.
Generic Error Handlers.
Train Your Clients.
Errors While Developing Versus Errors
Months Later.
The Ills of Protecting Code.
More Problems with Passwords.
Errors Caused by Different Versions.
Next Steps.
24. Using Custom Menus to Run Macros.
Creating a Custom Menu.
Creating a Custom Toolbar.
Other Ways to Run a Macro.
Next Steps.
25. Add-Ins.
Characteristics of Standard Add-Ins.
Converting an Excel Workbook to an
Add-In.
Having Your Client Install the Add-In.
Using a Hidden Workbook as an Alternative
to an Add-In.
Next Steps.
26. Case Study: Designing an Excel
Application.
About Tushar Mehta.
Using Excel for More Than Number Crunching.
The Solution.
Implementing the Solution in Excel
and VBA.
Summary.
Next Steps.
Index |
