book

Excel Macros For Dummies, 3rd Edition

Name: Excel Macros For Dummies, 3rd Edition
Author: Dick Kusleika
ISBN: 9781119844433

by Dick Kusleika

March 2022

Beginner

304 pages

6h 19m

English

For Dummies

Read now

Unlock full access

Cover
Title Page
Copyright
Introduction
About This BookFoolish AssumptionsIcons Used in This BookBeyond the BookWhere to Go from Here
Part 1: Holy Macro Batman!
Chapter 1: Macro Fundamentals
Choosing to Use a MacroMacro Recording BasicsComparing Absolute and Relative Macro RecordingUnderstanding Macro SecurityStoring and Running MacrosExploring Macro Examples
Chapter 2: Getting Cozy with the Visual Basic Editor
Working in the Visual Basic EditorWorking with the Project ExplorerWorking with a Code PaneCustomizing the VBE
Chapter 3: The Anatomy of Macros
A Brief Overview of the Excel Object ModelA Brief Look at VariablesUnderstanding Event ProceduresError Handling in a Nutshell
Part 2: Making Short Work of Workbook Tasks
Chapter 4: Working with Workbooks
Installing MacrosCreating a New Workbook from ScratchSaving a Workbook when a Particular Cell Is ChangedSaving a Workbook before ClosingProtecting a Worksheet on Workbook CloseUnprotecting a WorksheetOpening a Workbook to a Specific TabOpening a Specific Workbook Defined by the UserDetermining Whether a Workbook Is Already OpenDetermining Whether a Workbook Exists in a DirectoryClosing All Workbooks at OncePrinting All Workbooks in a DirectoryPreventing the Workbook from Closing Until a Cell Is PopulatedCreating a Backup of the Current Workbook with Today’s Date
Chapter 5: Working with Worksheets
Installing MacrosAdding and Naming a New WorksheetDeleting All but the Active WorksheetHiding All but the Active WorksheetUnhiding All Worksheets in a WorkbookMoving Worksheets AroundSorting Worksheets by NameGrouping Worksheets by ColorCopying a Worksheet to a New WorkbookCreating a New Workbook for Each WorksheetPrinting Specified WorksheetsProtecting All WorksheetsUnprotecting All WorksheetsCreating a Table of Contents for Your WorksheetsZooming In and Out of a Worksheet with Double-ClickHighlighting the Active Row and Column

Part 3: One-Touch Data Manipulation
Chapter 6: Feeling at Home on the Range
Installing MacrosSelecting and Formatting a RangeCreating and Selecting Named RangesLooping Through a Range of CellsInserting Blank Rows in a RangeUnhiding All Rows and ColumnsDeleting Blank RowsDeleting Blank ColumnsLimiting Range Movement to a Particular AreaSelecting and Formatting All Formulas in a WorkbookFinding and Selecting the First Blank Row or Column
Chapter 7: Manipulating Data with Macros
Installing MacrosCopying and Pasting a RangeConverting All Formulas in a Range to ValuesText to Columns on All ColumnsConverting Trailing Minus SignsTrimming Spaces from All Cells in a RangeTruncating Zip Codes to the Left FivePadding Cells with ZerosReplacing Blanks Cells with a ValueAppending Text to the Left or Right of Your CellsCleaning Up Non-Printing CharactersHighlighting Duplicates in a Range of DataHiding All but Rows Containing Duplicate DataSelectively Hiding AutoFilter Drop-down ArrowsCopying Filtered Rows to a New WorkbookShowing Filtered Columns in the Status Bar
Part 4: Macro-Charging Reports and Emails
Chapter 8: Automating Common Reporting Tasks
Installing MacrosRefreshing All PivotTables in a WorkbookCreating a List of PivotTablesAdjusting All Pivot Data Field TitlesSetting All Data Items to SumApplying Number Formatting for All Data ItemsSorting All Fields in Alphabetical OrderApplying a Custom Sort to Data ItemsApplying PivotTable RestrictionsApplying Pivot Field RestrictionsAutomatically Deleting PivotTable Drill-Down SheetsPrinting a PivotTable for Each Report Filter ItemCreating a New Workbook for Each Report Filter ItemResizing All Charts on a WorksheetAligning a Chart to a Specific RangeCreating a Set of Disconnected ChartsPrinting All Charts on a Worksheet
Chapter 9: Sending Emails from Excel
Installing MacrosMailing the Active Workbook as an AttachmentMailing a Specific Range as an AttachmentMailing a Single Sheet as an AttachmentSending Mail with a Link to Your WorkbookMailing All Email Addresses in Your Contact ListSaving All Attachments to a FolderSaving Certain Attachments to a Folder
Chapter 10: Wrangling External Data with Macros
Working with External Data ConnectionsUsing Macros to Create Dynamic ConnectionsIterating through All Connections in a WorkbookUsing ADO and VBA to Pull External DataWorking with Text Files
Part 5: The Part of Tens
Chapter 11: Ten Handy Visual Basic Editor Tips
Applying Block CommentsCopying Multiple Lines of Code at OnceJumping between Modules and ProceduresTeleporting to Your FunctionsStaying in the Right ProcedureStepping through Your CodeStepping to a Specific Line in Your CodeStopping Your Code at a Predefined PointSeeing the Beginning and End of Variable ValuesTurning Off Auto Syntax Check
Chapter 12: Ten Places to Turn for Macro Help
Let Excel Write the Macro for YouUse the VBA Help FilesPilfer Code from the InternetLeverage User ForumsVisit Expert BlogsMine YouTube for Video TrainingAttend Live and Online Training ClassesLearn from the Microsoft Office Dev CenterDissect the Other Excel Files in your OrganizationAsk Your Local Excel Genius
Chapter 13: Ten Ways to Speed Up Your Macros
Halt CalculationsDisable Screen UpdatingTurn Off Status Bar UpdatesTell Excel to Ignore EventsHide Page BreaksSuspend PivotTable UpdatesSteer Clear of Copy and PasteUse the With StatementDon’t Explicitly Select ObjectsAvoid Excessive Trips to the Worksheet
Index
About the Author
Advertisement Page
Connect with Dummies
End User License Agreement

Content preview from Excel Macros For Dummies, 3rd Edition

Chapter 13

Ten Ways to Speed Up Your Macros

IN THIS CHAPTER

Disabling performance draining Excel features

Avoiding unnecessary macro actions

Optimizing your macro code for better performance

As your macros become increasingly robust and complex, you may find they lose performance. When discussing macros, the word performance is usually synonymous with speed. Speed is how quickly your VBA procedures perform their intended tasks.

You can take steps to improve the performance of your macros. In this chapter, you find ten ways you can help keep your Excel macros running at their optimum performance level.

Halt Calculations

Each time a cell that affects any formula in your spreadsheet is changed or manipulated, Excel recalculates the entire worksheet. In worksheets that have a large amount of formulas, this behavior can drastically slow down your macros.

If your workbook is formula intensive, you may not want Excel to trigger a recalculation every time your macro alters a cell value. You can use the Application.Calculation property to tell Excel to switch to manual calculation mode. When a workbook is in manual calculation mode, the workbook does not recalculate until you explicitly ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781119844433Purchase Link

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Excel Macros For Dummies, 3rd Edition

by Dick Kusleika

Ten Ways to Speed Up Your Macros

Halt Calculations

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.