Excel Macros For Dummies

Name: Excel Macros For Dummies
Brand: Wiley
Price: 25.99 EUR
Availability: OnlineOnly

Dick Kusleika(Autor*in)

Wiley (Verlag)

3. Auflage

Erschienen am 15. Februar 2022

304 Seiten

E-Book

PDF mit Adobe-DRM

Systemvoraussetzungen

978-1-119-84444-0 (ISBN)

25,99 €inkl. 7% MwSt.

Systemvoraussetzungen

für PDF mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

Weitere Details

Weitere Ausgaben

Inhalt

Intro
Title Page
Copyright Page
Table of Contents
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
EULA

Systemvoraussetzungen

Als PDF speichern Als Link merken