VBA and Macros for Microsoft Excel

by Jelen, Bill; Syrstad, Tracy

• ISBN13: 9780789731296
• ISBN10: 0789731290
• Edition: 1st
• Format: Paperback
• Copyright: 2004-05-10
• Publisher: Que Pub

Summary

& bull; Developers can take advantage of VBA's capabilities with instruction for customizing Excel database applications to meet business needs. & lt;br/ & gt; & bull; Developers can save money by using this book instead of building custom applications from the ground up. & lt;br/ & gt; & bull; Written by an Excel VBA expert with over 18 years experience.

Author Biography

Bill Jelen "Mr. Excel" is an accomplished Excel author and the principal behind the leading Excel website, MrExcel.com. As an Excel consultant, he has written Excel VBA solutions for hundreds of clients around the English-speaking world. His website hosts over 10 million page views annually. Prior to founding MrExcel.com, Jelen spent twelve years in the trenches, working as a financial analyst for finance, marketing, accounting and operations departments of a $500 million public company.

Tracy Syrstad works as an Excel and Access Consultant. As part of the Mr.Excel.com Consulting team, she has helped develop VBA solutions for clients around the world. She remembers the painful trek up the VBA learning curve while developing applications for co-workers at a former job. She is co-editor of Holy Macro!, it¿s ֿ,600 Excel VBA Examples CD” and editor of Dreamboat On Word.

Table of Contents

Introduction

Getting Results with VBA

What Is in This Book

A Brief History of Spreadsheets and Macros

The Future of VBA and Excel

Special Elements and Typographical Conventions

Next Steps

First Steps Up The Vba Learning Curve

Unleash the Power of Excel with VBA!

The Power of Excel

Barriers to Entry

Knowing Your Tools-The Visual Basic Toolbar

Macro Security

Overview of Recording, Storing, and Running a Macro

Running a Macro

Understanding the Visual Basic Editor (VBE)

Examining Code in the Programming Window

Running the Macro on Another Day Produces Undesired Results

Frustration

Next Steps: Learning VBA Is the Solution

This Sounds Like BASIC, So Why Doesn't It Look Familiar?

I Can't Understand This Code

Understanding the Parts of VBA "Speech"

Is VBA Really This Hard?

No! Examining Recorded Macro Code-Using the VB Editor and Help

Using Debugging Tools to Figure Out Recorded Code

The Ultimate Reference to All Objects, Methods, Properties

Five Easy Tips for Cleaning Up Recorded Code

Putting It All Together-Fixing the Recorded Code

Next Steps

Referring to Ranges

The Range Object

Using the Top-Left and Bottom-Right Corners of a Selection to Specify a Range

Referencing Ranges in Other Sheets

Referencing a Range Relative to Another Range

Using the Cells Property to Select a Range

Using the Offset Property to Refer to a Range

Using the Resize Property to Change the Size of a Range

Using the Columns and Rows Properties to Specify a Range

Using the Union Method to Join Multiple Ranges

Using the Intersect Method to Create a New Range from Overlapping Ranges

Using the IsEmpty Function to Check Whether a Cell Is Empty

Using the CurrentRegion Property to Quickly Select a Data Range

Using the Areas Collection to Return a Non-contiguous Range

Next Steps

User-Defined Functions

Creating User-Defined Functions

Useful Custom Excel Functions

Next Steps

Looping

For...Next Loops

Do Loops

The VBA Loop: For Each

Next Steps

R1C1 Style Formulas

Referring to Cells: A1 Versus R1C1 References

Switching Excel to Display R1C1 Style References

The Miracle of Excel Formulas

Explanation of R1C1 Reference Style

Conditional Formatting-R1C1 Required

Array Formulas Require ConditionalFormatting

Next Steps

Names

Global Versus Local Names

Adding Names

Deleting Names

Types of Names

Hiding Names

Checking for the Existence of a Name

Next Steps

Event Programming

Levels of Events

Using Events

Workbook Events

Worksheet Events

Chart SheetEvents

Application-Level Events

Next Steps

UserForms-An Introduction

User Interaction Methods

Creating a Userform

Calling and Hiding a Userform

Using Basic Form Controls

Using the Multipage Control to Combine Forms

Next Steps

Automating Excel Power In Vba

Charts

Overview

Embedded Charts Versus ChartSheets

Creating a Chart with VBA

Using Object Variables to Streamline Code

The Anatomy of a Chart

Table of Chart Types

Details of Various Chart Types

Interactive Charts

Exporting Charts as Images

Drawing with X-Y Charts

Custom Charts with VBA

Next Steps

Data Mining with Advanced Filter

Advanced Filter Is Easier in VBA Than in Excel

Using Advanced Filter to Extract a Unique List of Values

Using Advanced

Table of Contents provided by Publisher. All Rights Reserved.