ExcelTips: The Macros
fifth edition

ExcelTips: The Macros includes 563 valuable macro-oriented tips. The following is the Table of Contents:

Introduction

• A Word about Versions
• How to Use the Macros in This Book
• Need More Tips?
• Want to Learn About VBA Programming in Word?
• Sharing this Document

Creating and Managing Macros

• Understanding Macros
• Recording a Macro
• Relative References when Recording Macros
• Writing a Macro from Scratch
• Editing Macros
• Continuing Macro Lines
• Excel 2007 VBA Differences
• Offering Options in a Macro
• Renaming a Macro
• Automating Copying Macros
• Debugging a Macro
• Friendly and Informative Error Handling
• Stepping Through a Macro with a Worksheet Visible
• Macro Runs Slowly, but Steps Quickly
• Getting Big Macros to Run
• Working while a Macro is Running
• Assigning a Macro to a Keyboard Combination
• One Shortcut for Two Macros
• Removing a Macro from a Shortcut Key
• Relative VBA Selections
• Running Macros in the Background
• Aborting a Macro and Retaining Control
• Develop Macros In Their Own Workbook
• Macros in Template Files
• Forcing a Macro to Run When a Worksheet is Recalculated
• Deleting a Macro
• Generating a List of Macros
• Clean Up Your Macro List
• Understanding Phantom Macros
• Getting Rid of the “Enable Macros” Notice
• Removing All Macros
• Easily Deploying Customizations
• Library Not Registered Error
• Hiding Macros
• Disabled Macros
• Maximum Length Limit for a Macro
• Excluding a Specific Add-In at Startup
• Using Macros in Protected Workbooks
• Ctrl+Break Won't Work to Stop a Macro
• Recovering Macros from Corrupted Workbooks
• Trouble Recording Paste Special—Formula
• Digital Signatures for Macros
• Moving Macros from the Personal Workbook
• Automatically Hiding Personal.xls
• Upgrading a Personal.xls Workbook
• Opening Personal.xlsb in Excel 2007
• Documenting Changes in VBA Code
• Out of Memory Errors when Accessing the VBA Editor
• Item Not Available in Library
• Automatically Changing References to VBA Libraries
• Automatically Opening Macro Workbooks when Using a Shortcut Key

Functions and Subroutines

• Understanding Subroutines
• Understanding Functions
• Making Common Functions Available
• Worksheet Events
• Workbook Events

Structures Used in Macros

• Understanding the For … Next Structure
• Exiting a For ... Next Loop Early
• Understanding the If ... End If Structure
• Understanding the While…Wend Structure
• Understanding the Select Case Structure

Working with Variables

• Understanding Variables in Macros
• Declaring Variables
• Swapping The Values in Two Variables
• Comparing Strings
• Converting Numbers to Strings
• Converting Strings to Numbers
• Quickly Dumping Array Contents

Commands and Statements

• Deriving an Absolute Value in a Macro
• Determining a Random Value
• Determining an ANSI Value in a Macro
• Determining the Hour of the Day
• Determining the Day of the Month
• Determining Differences Between Dates
• Creating a String
• Dissecting a String
• Trimming Spaces from Strings
• Determining the Length of a String
• Determining an Integer Value
• Error Using ATAN2 Function in Macro
• Using BIN2DEC In a Macro
• Using SUM In a Macro
• Large Numbers in the MOD Function
• EOMONTH Function is Flakey
• Making VLOOKUP Trigger a Macro
• Making VLOOKUP Case Sensitive

Controlling Data Entry

• Accepting Only a Single Digit
• Setting a Length Limit on Cells
• Shortcut for Pasting Only Values
• Entering Dates Without Separators
• Automatically Protecting After Input
• Maintaining the Active Cell
• Limiting Entry of Prior Dates
• Modifying Default Year for Dates
• Entering or Importing Times without Colons
• Unique Name Entry, Take Two
• Checking for Time Input
• Entering Large Time Values
• Recording a Data Entry Time
• Inserting the Current Time with Seconds
• Inserting a Radical Symbol
• Positioning a Column on the Screen
• Jumping to the Start of the Next Data Entry Row

Selecting Cells and Ranges

• Magnifying Only the Current Cell
• Displaying the Selected Cell's Address
• Selecting a Cell in the Current Row
• Selecting a Range of Cells Relative to the Current Cell
• Selecting the First Cell In a Row
• Selecting a Specific Cell in a Macro
• Highlighting the Rows of Selected Cells
• Choosing Direction After Enter On a Workbook Basis
• Automatically Moving from Cell to Cell when Entering Data

Affecting Worksheet Data

• Inserting Values with a Macro
• Clearing Everything Except Formulas
• Converting Text Case
• Converting Cells to Proper Case
• Modifying Proper Capitalization
• Capitalizing Just a Surname
• Converting Text to Numbers
• Converting Imported Information to Numeric Values
• Converting Numeric Values to Times
• Creating a Plus/Minus Button
• Finding the Lowest Numbers
• Converting From Relative to Absolute
• Reversing Cell Contents
• Reversing Names In Place
• Concatenating Ranges of Cells
• Finding the First Non-Digit in a Text Value
• Finding the Nth Occurrence of a Character
• Shortcut to Merge Cells
• Identifying Merged Cells
• Getting Rid of Spaces in Cells
• Removing Duplicate Cells
• Deleting Duplicate Text Values
• Removing Duplicates Based on a Partial Match
• Removing Cells from a Selected Range
• Deleting Old Data from a Worksheet
• Deleting Unwanted Styles
• Conditional Page Breaks
• Summing Only Visible Values
• Moving Subtotals
• Formatting Subtotal Rows
• Determining Combinations to Make a Total
• Putting Addresses on State-Specific Worksheets
• Selectively Importing Records
• Importing Huge Data Files
• Merging Many Workbooks
• Importing Multiple Files to a Single Workbook
• Getting Rid of Extra Quote Marks in Exported Text Files
• Getting Rid of Alphabetic Characters
• Adjusting Values with Formulas
• Returning Zero When a Referenced Cell is Blank
• Counting Commas in a Selection
• Counting Consecutive Negative Numbers
• Summing Digits in a Value
• Summing Absolute Values
• Summing Based on Formatting in Adjacent Cells
• Inserting Dashes between Letters and Numbers
• Removing Spaces
• Generating Random Strings of Characters
• Pulling Apart Characters in a Long String
• Breaking Up Variable-Length Part Numbers
• Splitting Text to Multiple Cells
• Placing Textbox Text Into a Worksheet
• Determining "Highest Since" or "Lowest Since"
• Making Changes in a Group of Workbooks
• Listing Combinations
• Putting an X in a Clicked Cell
• Deleting Everything Except Formulas
• Getting Rid of Everything Except Numbers
• Number of Terms in a Formula

Affecting Cell Formatting

• Adding Drop Shadows to Cells
• Highlighting Values in a Cell
• Creating a Center Across Selection Button
• Making a Cell's Contents Bold or Italic
• Counting Cells with Text Colors
• Official Color Names in VBA
• Creating Superscript and Subscript Buttons
• Automatically Copying Formatting
• Matching Formatting when Concatenating
• Checking All Cell Formatting in VBA
• Changing Fonts in Multiple Workbooks
• Making All Occurrences Bold
• Shortcut Key for Format Painter
• Selecting Cells of a Specific Color
• Counting Colors of Cells
• Flashing Cells
• Using a Custom Format to Add Dashes
• Coloring Cells with Formulas
• Conditionally Highlighting Cells Containing Formulas
• Changing Font Face and Size Conditionally
• Protecting Conditional Formatting
• Detecting Errors in Conditional Formatting Formulas
• Removing Conditional Formats, but Not the Effects

Affecting Rows and Columns

• Removing Duplicate Rows
• Conditionally Deleting Rows
• Deleting Every X Rows
• Hiding Rows Based on a Cell Value
• Hiding Rows Based on Two Values
• Skipping Hidden Rows in a Macro
• Easily Adding Blank Rows
• Inserting and Copying Rows
• Moving and Selecting Rows
• Setting Row Height in a Macro
• Splitting Information into Rows
• Shading Rows for Ease in Reading Output
• Summing Every Fourth Cell in a Row
• Condensing Sequential Values to a Single Row
• Hiding a Huge Number of Rows
• Floating Information in a Frozen Row
• Alphabetic Column Designation
• Unhiding a Single Column
• Setting Column Width in a Macro
• Deleting Blank Columns
• Combining Columns
• Combining Multiple Rows in a Column
• Selecting Columns in VBA when Cells are Merged
• Hiding Columns Not within a Date Range
• Hiding Columns Based on a Cell Value

Filtering Data and Working with Filtered Data

• Toggling AutoFilter
• Quickly Identifying Applied AutoFilters
• Filtering for Comments
• Changing AutoFilter Drop-Down Arrow Colors
• Macro Fails after AutoFilter

Working with Worksheets

• Creating Worksheets with a Macro
• Creating and Naming a Worksheet Using a Macro
• Jumping to a Specific Worksheet
• Shortcut to Move Between Two Worksheets
• Telling which Worksheets are Selected
• Determining a Worksheet's Number
• Deriving the Worksheet Name
• Getting the Name of the Worksheet Into a Cell
• Retrieving Worksheet Names
• Dynamic Worksheet Tab Names
• Ordering Worksheets Based on a Cell Value
• Copying Worksheets in a Macro
• Relative Worksheet References
• Sheets for Days
• Referencing Worksheet Tabs
• Locking Worksheet Names
• Freezing Worksheet Tabs
• Running Macros on Hidden Worksheets
• Spell-Checking in a Protected Worksheet
• Preventing Someone from Recreating a Protected Worksheet
• Fixing Macro Button Behavior in Protected Worksheets
• Visually Showing a Protection Status
• Unhiding Multiple Worksheets
• Detecting Types of Sheets in VBA
• Selecting All Visible Worksheets in a Macro
• Deleting Worksheets in a Macro
• Condensing Multiple Worksheets Into One
• Counting the Times a Worksheet is Used
• Generating Unique Numbers for Worksheets
• Disabling Moving Between Worksheets
• Random Width and Height Changes
• Identifying the Last Cell Changed in a Worksheet
• Viewing Same Cells on Different Worksheets
• Opening a Workbook to a Specific Worksheet
• Testing for an Empty Worksheet

Interacting with the Excel Environment

• Turning Off Screen Updating
• Disabling Shift Key Use when Opening a Workbook
• Opening a Workbook but Disabling Macros
• Forcing Editing to Be Done in a Cell
• Using Go To to Jump to a Chart Sheet
• Displaying a Set Column Range
• Hiding Excel in VBA
• Preserving the Undo List
• Clearing the Undo Stack
• Calculating Only the Active Workbook
• Setting the Calculation Default
• Counting All Characters
• Unhiding or Listing All Objects
• Using Named Ranges in a Macro
• Deleting All Names but a Few
• Default Cell Movement when Deleting
• Forcing Manual Calculation For a Workbook
• Pulling Formulas from a Worksheet
• Saving Non-Existent Changes
• Counting Precedents and Dependents
• Turning Off AutoFill for a Workbook
• Noting the Workbook Creation Date
• Displaying the "Last Modified" Date
• Noting When a Workbook was Changed
• Date Last Edited
• Grabbing a User's Name from Excel
• Grabbing the MRU List
• Seeing All Open Workbook Names
• Getting Rid of "Copy of"
• Pulling Cell Names into VBA
• Simultaneous Scrolling
• Looking Up Names when Key Values are Identical
• Accessing Dependant and Precedent Information
• Determining if Caps Lock is On
• Disabling the F1 Key
• Disabling a Function Key
• Disabling Excel's Help System
• Retrieving Drive Statistics
• Full Path Names in Excel
• Automatically Closing a Workbook
• Finding Unknown Links
• Getting Rid of Workbook Links
• Task Pane Doesn't Appear Properly
• Finding the Last-Used Cell in a Macro
• Jumping to the Real Last Cell
• Determining if Calculation is Necessary
• Iterating Circular References
• Determining How Many Windows are Open
• Saving Common Formulas
• Turning Off Track Changes without Unsharing
• Checking if a Workbook is Already Open
• Saving All Open Workbooks
• Saving a Workbook in a Macro
• Saving Changes when Closing
• Closing a Read-Only Workbook
• Using a Single Password for Multiple Workbooks

Menus, Toolbars, and the Ribbon

• Adding a Macro to a Toolbar
• Controlling Display of Toolbar Buttons
• Changing the Shortcut Menu
• Adding Items to a Context Menu
• Removing Items from a Context Menu
• Problem with Missing Context Menu Option
• Tools on Developer Tab are Unavailable

Working with Comments

• Copying Comments to Cells
• Inserting Workbook Comments Into a Cell
• Setting the Default Font Size for Comment Balloons
• Copying Comments when Filtering
• Adding a Comment to Multiple Cells
• Linking Comments to Multiple Cells
• Editing a Comment Close to Its Cell
• Finding and Replacing Text in Comments
• Placing Formula Answers in a Comment
• Counting Comments in a Worksheet

Working with Headers and Footers

• Dynamic Headers and Footers
• Full Path Names in Headers or Footers
• Adding a File Path and Filename
• Multiple Line Headers and Footers
• Putting a Different Date in a Header
• Inserting the Saved Date in a Header or Footer
• Last Saved Date in a Footer
• Specifying Date Formats in Headers
• Selective Headers and Footers
• Switching Headers in a Frozen Row
• Putting Cell Contents in Footers
• Copying Headers and Footers
• Header and Footer Background Color
• Using a Different Footer on Secondary Pages
• Leading Zeros in Page Numbers
• Changing Page Number Format
• Roman Numerals for Page Numbers
• Changing Section Headers
• Find and Replace in Headers

Working with Graphics and Charts

• Assigning Macros to Graphics
• Pasting a Graphic to Multiple Worksheets
• ScreenTip for an Image
• Sizing Text Boxes and Cells the Same
• Resizing a Text Box in a Macro
• Adding AutoShapes
• Shifting Objects Off a Sheet
• Removing Pictures for a Worksheet in VBA
• Snapshots of Excel Worksheets for PowerPoint
• Creating Charts in VBA
• Automatically Creating Charts for Individual Rows in a Data Table
• Unlocking Charts
• Positive and Negative Colors in a Chart
• Labeling X-Y Scatter Plots
• Calculating the Distance Between Points
• Exporting Black and White Charts
• Specifying the Size of Chart Objects
• Specifying Chart Sizes
• Changing Elements in Lots of Charts at One Time
• Hyperlinks to Charts
• Determining Mouse Cursor Coordinates On a Graphic

Working with the Outside World

• Creating a Splash Screen
• Getting User Input in a Dialog Box
• Using InputBox to Get Data
• Hiding Entries in an InputBox
• Forcing Input to Uppercase
• Pausing Macros for User Input
• Using Message Boxes
• Conditionally Displaying a Message Box
• Using a Progress Indicator
• Progression Indicator in a Macro
• Using the Status Bar
• Getting a File Name
• Determining If a File Exists
• Determining the Length of a Text File
• Getting Input from a Text File
• Saving Information in a Text File
• Using Seek In a Macro
• Renaming a File
• Deleting a File
• Faster Text File Conversions
• Aligning Cells when Importing from CSV
• Specifying a Delimiter when Saving a CSV File in a Macro
• Creating a Directory
• Changing Directories
• Determining the Current Directory
• Removing a Directory
• Changing the Default Drive
• Easily Changing the Default Drive and Directory
• Word Documents from Excel Macros
• Using Old Lotus Macros
• Linking to a Specific Page in a PDF File
• Updating Automatically When Opening Under Macro Control
• Displaying Messages when Automatic Data Changes
• Preparing Data for Import into Access
• Is Daylight Savings Time in Effect?

Web and Online

• Opening an HTML Page in Excel
• Converting a Range of URLs to Hyperlinks
• Getting Rid of Many Hyperlinks
• Getting Rid of All Hyperlinks
• Changing Portions of Many Hyperlinks
• Converting to Hyperlinks in a Shared Workbook
• Extracting E-mail Addresses from Hyperlinks
• Extracting URLs from Hyperlinks
• Extracting URLs from Hyperlinked Images
• Get Rid of Web Stuff
• Sending Single Worksheets via E-mail
• Suppressing the Reviewing Toolbar on E-mailed Workbooks
• Retrieving Web Query Data without Interruption

Working with the Printer

• Page Numbers in VBA
• Changing Paper Size for a Complete Workbook
• Displaying the Print Dialog Box
• Specifying Print Quantity in a Cell
• Controlling the Printer in a Macro
• Working With Multiple Printers
• Using Multiple Print Settings
• Printing Selected Worksheets
• Printing Only Non-Blank Worksheets
• Printing Limited Pages from a Range of Worksheets
• Printing an Entire Workbook by Default
• Printing a Single Column in Multiple Columns
• Printing Multiple Worksheets on a Single Page
• Printing a Worksheet List
• Printing Workbook Properties
• Adjusting Comment Printouts
• Disabling the Print Option
• Automatically Printing a Range
• Setting Print Ranges for Multiple Worksheets
• Printing All or Nothing
• Conditional Printing
• Protecting Print Settings
• Locking the Print Area
• Showing Filter Criteria on a Printout
• Repeating Rows on a Printout Except On the Last Page
• Printing Based on Cell Contents

Special-Purpose Macros

• Running a Macro When a Workbook is Opened
• Running a Macro When a Workbook is Closed
• Running a Macro When a Worksheet is Activated
• Running a Macro When a Worksheet is Deactivated
• Running a Macro in a Number of Workbooks
• Mouse Click Event in VBA
• Saving in Two Locations
• Saving in Multiple Locations
• Understanding Add-Ins
• Creating Add-Ins
• Using Custom Add-Ins
• Automatically Loading Add-ins

Searching and Sorting

• Universal Searching
• Searching through Many Workbooks
• Searching a Workbook by Default
• Searching a Workbook by Default, Take Two
• Searching by Columns, by Default
• Searching for Leading Apostrophes
• Searching for Line Breaks
• Searching for a Value Using a Function
• Using Find and Replace to Pre-Pend Characters
• Finding and Replacing Error Values
• Superscripts in Find and Replace
• Wildcards in 'Replace With' Text
• Finding Columns of a Certain Width
• Sorting Worksheets
• Non-standard Sorting
• Sorting Data on Protected Worksheets
• Sorting by Colors
• Sorting by Fill Color
• Sorting Data Containing Merged Cells
• Determining Sorting Criteria

Macro Cookbook

• AutoFilling with the Alphabet
• Numbers Spelled Out
• Converting Phone Numbers
• Adding Leading Zeroes to ZIP Codes
• Shortening ZIP Codes
• Working with Imperial Linear Distances
• Adding Ordinal Notation to Dates
• Selecting Random Names
• Generating Random Testing Data
• Zooming with the Keyboard
• Always Open at 100% Zoom
• Determining If a Number is Odd or Even
• Playing with a Full Deck
• Counting Wins and Losses
• Making Squares
• Counting Words
• Default Worksheet when Opening
• Protecting Individual Worksheets, by User
• Unprotecting Groups of Worksheets
• Creating Individual Workbooks
• Disappearing Toolbar Buttons for Macros
• Sheets for Months
• Macro for Month Name
• Naming Tabs for Weeks
• Jumping to Alphabetic Worksheets
• Flipping Data
• Counting Shaded Cells
• Counting Unique Values
• Counting Cells According to Case
• Finding Differences Between Lists
• List of Macro Shortcuts in All Open Workbooks
• Quickly Changing Windows
• Using an Exact Number of Digits
• Engineering Calculations
• Maintaining Accuracy of Significant Digits
• Counting Groupings Below a Threshold
• Using Excel for Timing
• Parsing Non-Standard Date Formats
• Automatically Advancing by a Month
• Weekdays in a Month
• Automatically Converting to GMT
• Converting an Unsupported Date Format
• Tombstone Date Math
• Calculating TV Time
• Referencing External Cell Colors
• Automatically Editing Formulas
• Developing Reciprocal Conversion Formulas
• Spreading Out a Table
• DOS From Macros
• Deleting Macros from within a Macro
• Self-Deleting Macros
• Deleting Worksheet Code in a Macro
• Using the Camera in VBA
• Conditional Page Breaks
• Calculating the Interval Between Occurrences
• Self-Aware Macros
• Pasting without Updating References
• Expiration Date for Excel Programs
• Jumping To a Specific Page
• Pulling All Fridays
• The Last Business Day
• Updating Multiple PivotTables at Once
• Removing Subtotals from Many PivotTable Fields
• Reducing File Sizes for Workbooks with PivotTables
• Resizing Checkboxes
• Combinations for Members in Meetings
• Extracting Proper Words
• Clearing Large Clipboard Entries
• Pulling Apart Cells
• Reorganizing Data
• Finding Unused Names
• Getting Rid of Non-Printing Characters Intelligently
• Changing Months in a Workbook
• Triggering a Macro for Drop-Down List Changes
• Delimited Text-to-Columns in a Macro
• Replacing and Converting in a Macro
• Protecting an Entire Folder of Workbooks
• Limiting Scroll Area
• Waiting for Update Completion
• Conditionally Playing an Audio File

Description	CD		Download
ExcelTips: The Macros	$36.77		$29.77		(4.4 MB)

You can also order ExcelTips: The Macros by calling our order line: 801-607-2035.