Excel macros for dummies
Book - 2022
Saved in:
- Subjects
- Published
-
Hoboken, NJ :
For Dummies
2022.
- Language
- English
- Main Author
- Other Authors
- Edition
- Thrd edition
- Item Description
- Previous edition: by Michael Alexander. 2017.
- Physical Description
- 1 volume : illustrations
- ISBN
- 9781119844433
- Introduction
- About This Book
- Foolish Assumptions
- Icons Used in This Book
- Beyond the Book
- Where to Go from Here
- Part 1. Holy Macro Batman!
- Chapter 1. Macro Fundamentals
- Choosing to Use a Macro
- Macro Recording Basics
- Examining the macro
- Editing the macro
- Testing the macro
- Comparing Absolute and Relative Macro Recording
- Recording macros with absolute references
- Recording macros with relative references
- Understanding Macro Security
- Macro-enabled file extensions
- Trusted documents
- Trusted locations
- Storing and Running Macros
- Storing macros in your Personal Macro Workbook
- Assigning a macro to a button and other form controls
- Placing a macro on the Quick Access Toolbar
- Exploring Macro Examples
- Building navigation buttons
- Dynamically rearranging PivotTable data
- Offering one-touch reporting options
- Chapter 2. Getting Cozy with the Visual Basic Editor
- Working in the Visual Basic Editor
- The VBE menu bar
- The VBE toolbars
- The Project Explorer
- The Code pane
- The Immediate window
- Working with the Project Explorer
- Adding a new VBA module
- Removing a VBA module
- Working with a Code Pane
- Minimizing and maximizing windows
- Getting VBA code into a module
- Customizing the VBE
- The Editor tab
- The Editor Format tab
- The General tab
- The Docking tab
- Chapter 3. The Anatomy of Macros
- A Brief Overview of the Excel Object Model
- Understanding objects
- Understanding collections
- Understanding properties
- Understanding methods
- A Brief Look at Variables
- The common variable types
- Understanding Event Procedures
- Worksheet events
- Workbook events
- Error Handling in a Nutshell
- On Error GoTo SomeLabel
- On Error Resume Next
- On Error GoTo 0
- Part 2. Making Short Work of Workbook Tasks
- Chapter 4. Working with Workbooks
- Installing Macros
- Event macros
- Personal Macro Workbook
- Standard macros
- Creating a New Workbook from Scratch
- Saving a Workbook when a Particular Cell Is Changed
- Saving a Workbook before Closing
- Protecting a Worksheet on Workbook Close
- Unprotecting a Worksheet
- Opening a Workbook to a Specific Tab
- Opening a Specific Workbook Defined by the User
- Determining Whether a Workbook Is Already Open
- Determining Whether a Workbook Exists in a Directory
- Closing All Workbooks at Once
- Printing All Workbooks in a Directory
- Preventing the Workbook from Closing Until a Cell is Populated
- Creating a Backup of the Current Workbook with Today's Date
- Chapter 5. Working with Worksheets
- Installing Macros
- Event macros
- Personal Macro Workbook
- Standard macros
- Adding and Naming a New Worksheet
- Deleting All but the Active Worksheet
- Hiding All but the Active Worksheet
- Unhiding All Worksheets in a Workbook
- Moving Worksheets Around
- Sorting Worksheets by Name
- Grouping Worksheets by Color
- Copying a Worksheet to a New Workbook
- Creating a New Workbook for Each Worksheet
- Printing Specified Worksheets
- Protecting All Worksheets
- Unprotecting All Worksheets
- Creating a Table of Contents for Your Worksheets
- Zooming In and Out of a Worksheet with Double-Click
- Highlighting the Active Row and Column
- Part 3. One-Touch Data Manipulation
- Chapter 6. Feeling at Home on the Range
- Installing Macros
- Event macros
- Personal Macro Workbook
- Standard macros
- Selecting and Formatting a Range
- Creating and Selecting Named Ranges
- Looping Through a Range of Cells
- Inserting Blank Rows in a Range
- Unhiding All Rows and Columns
- Deleting Blank Rows
- Deleting Blank Columns
- Limiting Range Movement to a Particular Area
- Selecting and Formatting All Formulas in a Workbook
- Finding and Selecting the First Blank Row or Column
- Chapter 7. Manipulating Data with Macros
- Installing Macros
- Event macros
- Personal Macro Workbook
- Standard macros
- Copying and Pasting a Range
- Converting All Formulas in a Range to Values
- Text to Columns on All Columns
- Converting Trailing Minus Signs
- Trimming Spaces from All Cells in a Range
- Truncating Zip Codes to the Left Five
- Padding Cells with Zeros
- Replacing Blanks Cells with a Value
- Appending Text to the Left or Right of Your Cells
- Cleaning Up Non-Printing Characters
- Highlighting Duplicates in a Range of Data
- Hiding All but Rows Containing Duplicate Data
- Selectively Hiding AutoFilter Drop-down Arrows
- Copying Filtered Rows to a New Workbook
- Showing Filtered Columns in the Status Bar
- Part 4. Macro-Charging Reports and Emails
- Chapter 8. Automating Common Reporting Tasks
- Installing Macros
- Event macros
- Personal Macro Workbook
- Standard macros
- Refreshing All PivotTables in a Workbook
- Creating a List of PivotTables
- Adjusting All Pivot Data Field Titles
- Setting All Data Items to Sum
- Applying Number Formatting for All Data Items
- Sorting All Fields in Alphabetical Order
- Applying a Custom Sort to Data Items
- Applying PivotTable Restrictions
- Applying Pivot Field Restrictions
- Automatically Deleting PivotTable Drill-Down Sheets
- Printing a PivotTable for Each Report Filter item
- Creating a New Workbook for Each Report Filter item
- Resizing All Charts on a Worksheet
- Aligning a Chart to a Specific Range
- Creating a Set of Disconnected Charts
- Printing All Charts on a Worksheet
- Chapter 9. Sending Emails from Excel
- Installing Macros
- Event macros
- Personal Macro Workbook
- Standard macros
- Mailing the Active Workbook as an Attachment
- Mailing a Specific Range as an Attachment
- Mailing a Single Sheet as an Attachment
- Sending Mail with a Link to Your Workbook
- Mailing All Email Addresses in Your Contact List
- Saving All Attachments to a Folder
- Saving Certain Attachments to a Folder
- Chapter 10. Wrangling External Data with Macros
- Working with External Data Connections
- Manually creating a connection
- Manually editing data connections
- Using Macros to Create Dynamic Connections
- Iterating through All Connections in a Workbook
- Using ADO and VBA to Pull External Data
- Understanding ADO syntax
- Using ADO in a macro
- Working with Text Files
- Opening a text file
- Reading the opened text file
- A practical example: Logging workbook usage in a text file
- A practical example: Importing a text file to a range
- Part 5. The Part of Tens
- Chapter 11. Ten Handy Visual Basic Editor Tips
- Applying Block Comments
- Copying Multiple Lines of Code at Once
- Jumping between Modules and Procedures
- Teleporting to Your Functions
- Staying in the Right Procedure
- Stepping through Your Code
- Stepping to a Specific Line in Your Code
- Stopping Your Code at a Predefined Point
- Seeing the Beginning and End of Variable Values
- Turning Off Auto Syntax Check
- Chapter 12. Ten Places to Turn for Macro Help
- Let Excel Write the Macro for You
- Use the VBA Help Files
- Pilfer Code from the Internet
- Leverage User Forums
- Visit Expert Blogs
- Mine YouTube for Video Training
- Attend Live and Online Training Classes
- Learn from the Microsoft Office Dev Center
- Dissect the Other Excel Files in your Organization
- Ask Your Local Excel Genius
- Chapter 13. Ten Ways to Speed Up Your Macros
- Halt Calculations
- Disable Screen Updating
- Turn Off Status Bar Updates
- Tell Excel to Ignore Events
- Hide Page Breaks
- Suspend PivotTable Updates
- Steer Clear of Copy and Paste
- Use the With Statement
- Don't Explicitly Select Objects
- Avoid Excessive Trips to the Worksheet
- Index