Overview

AUTOMATE REPORTS BUILD FUNCTIONS VISUALIZE DATA WRITE FAST, RELIABLE SCRIPTS     Microsoft Excel 2010 VBA AND MACROS: SAVE TIME AND SUPERCHARGE EXCEL 2010 WITH VBA AND MACROS!   Use Excel 2010 VBA and macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then learn how to make Excel do things you thought were simply impossible! This book reveals scripting techniques you won’t find anywhere else and shows you how to create automated reports that are amazingly powerful and useful. It helps you instantly visualize information so you can understand and act on it. It also shows you how to capture data from anywhere and use it anywhere, and helps you automate Excel 2010’s most powerful new features. Learning advanced Excel scripting has never been easier. You’ll find simple, step-by-step instructions, real-world examples and case studies, and 50 workbooks packed with bonus examples, macros, and solutions, straight from MrExcel.     • Work efficiently with ranges, cells, and R1C1-style formulas • Build super-fast applications with arrays • Customize the Excel 2010 Ribbon to run your macros • Write Excel 2010 VBA code that works on older versions of Excel • Create custom dialog boxes to collect information from your users • Use error handling to make your VBA scripts more resilient • Use Web queries to import data from virtually any online source • Master advanced techniques such as classes and collections • Use Excel VBA to control other Office programs…even control Windows itself, via the Windows API • Create add-ins to share or sell your programs   About MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will   • Dramatically increase your productivity—saving you 50 hours a year or more • Present proven, creative strategies for solving real-world problems • Show you how to get great results, no matter how much data you have • Help you avoid critical mistakes that even experienced users make    

Full Product Details

Author:   Bill Jelen ,  Tracy Syrstad
Publisher:   Pearson Education (US)
Imprint:   Que Corporation,U.S.
Dimensions:   Width: 18.00cm , Height: 3.30cm , Length: 23.20cm
Weight:   1.021kg
ISBN:  

9780789743145

ISBN 10:   0789743140
Pages:   656
Publication Date:   01 July 2010
Audience:   Professional and scholarly ,  Professional & Vocational
Replaced By:   9780789748614
Format:   Paperback
Publisher's Status:   Out of Print
Availability:   Awaiting stock  

Table of Contents

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  

Reviews

Author Information

Bill Jelen, Excel MVP and the host of MrExcel.com, has been using spreadsheets since 1985, and he launched the MrExcel.com website in 1998. Bill was a regular guest on Call for Help with Leo Laporte and has produced more than 1,200 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 30 books about Microsoft Excel and writes the monthly Excel column for Strategic Financemagazine. You will most frequently find Bill taking his show on the road, doing half-day Power Excel seminars wherever he can find a room full of accountants or Excellers. Before founding MrExcel.com, Jelen spent 12 years in the trenches—working as a financial analyst for finance, marketing, accounting, and operations departments of a $500 million public company. He lives near Akron, Ohio, with his wife, Mary Ellen, and his sons, Josh and Zeke.   Tracy Syrstadis the project manager for the MrExcel consulting team. She was introduced to Excel VBA by a co-worker who encouraged her to learn VBA by recording steps and then modifying the code as needed. Her first macro was a simple lookup and highlight for a parts index, although it hardly seemed simple then. But she was encouraged by this success and others to follow. She’ll never forget the day when it all clicked. She hopes this book will bring that click to its readers sooner and with less frustration. She lives near Sioux Falls, South Dakota, with her husband, John.  

Tab Content 6

Author Website:  

Customer Reviews

Recent Reviews

No review item found!

Add your own review!

Countries Available

All regions