ISBN 9788126555475,Excel Vba : 24 - Hour Trainer

Excel Vba : 24 - Hour Trainer


Tom Urtis



Wiley India Pvt Ltd

Publication Year 2015

ISBN 9788126555475

ISBN-10 8126555475


Edition 2nd
Number of Pages 496 Pages
Language (English)

Computer programming

Master VBA automation quickly and easily to get more out of Excel
Excel VBA 24-Hour Trainer, 2nd Edition is the quick-start guide to getting more out of Excel, using Visual Basic for Applications. This unique book / video package has been updated with fifteen new advanced video lessons, providing a total of eleven hours of video training and 45 total lessons to teach you the basics and beyond. This self-paced tutorial explains Excel VBA from the ground up, demonstrating with each advancing lesson how you can increase your productivity. Clear, concise, step-by-step instructions are combined with illustrations, code examples and downloadable workbooks to give you a practical, in-depth learning experience and results that apply to real-world scenarios.
This is your comprehensive guide to becoming a true Excel power user, with multimedia instruction and plenty of hands-on practice.
Program Excel's newest chart and pivot table object models
Manipulate the user interface to customize the look and feel of a project.
Utilize message boxes, input boxes, and loops to yield customized logical results.
Interact with and manipulate Word, Access, PowerPoint, and Outlook from Excel.
If you're ready to get more out of this incredibly functional program, Excel VBA 24-Hour Trainer, 2nd Edition provides the expert instruction and fast, hands-on learning you need.

About the Author

Tom Urtis is an Excel Microsoft Excel MVP, developer and programmer with 30 years of experience in business management and developing spreadsheet and database applications for companies of all sizes. He is an Excel instructor and frequent contributor to newsgroups in the Excel community.
Table of contents :-

Part I : Understanding the Basics

Lesson 1 : Introducing Vba

What is Vba?
A Brief History of Vba
What Vba Can do for You
Liabilities of Vba
Try it

Lesson 2 : Getting Started with Macros

Composing Your First Macro
Running a Macro
Try It

Lesson 3 : Introducing the Visual Basic Editor

What is the Vbe?
Try It

Lesson 4 : Working in the Vbe

Toolbars in the Vbe
Macros and Modules
Understanding the Code
Editing a Macro with Comments and Improvements to the Code
Try It

Part II : Diving Deeper into Vba

Lesson 5 : Object - Oriented Programming: an Overview

What "Object - Oriented Programming" Means
The Object Model
Try It

Lesson 6 : Variables, Data Types and Constants

What is a Variable?
Assigning Values to Variables
Why You Need Variables
Data Types
Forcing Variable Declaration
Understanding a Variable's Scope
Try It

Lesson 7 : Understanding Objects and Collections

Cells And
Try It

Lesson 8 : Working with Ranges

Working with Noncontiguously Populated Ranges
Try It

Lesson 9 : Making Decisions with Vba

Understanding Logical Operators
Choosing Between This or That
Getting Users to Make Decisions
Try It

Part III : Beyond the Macro Recorder: Writing Your Own Code

Lesson 10 : Repeating Actions with Loops

What is a Loop?
Nesting Loops
Try It

Lesson 11 : Programming Formulas

Understanding A1 and R1c1 References
Programming Your Formula Solutions with Vba
Try It

Lesson 12 : Working with Arrays

What is an Array?
The Option Base Statement
Boundaries in Arrays
Declaring Arrays with Fixed Elements
Declaring Dynamic Arrays with Redim and Preserve
Try It

Lesson 13 : Automating Procedures with Worksheet Events

What is an Event?
Worksheet Events: an Overview
Examples of Common Worksheet Events
Try It

Lesson 14 : Automating Procedures with Workbook Events

Workbook Events: an Overview
Examples of Common Workbook Events
Try It

Lesson 15 : Handling Duplicate Items and Records

Deleting Rows Containing Duplicate Entries
Working with Duplicate Data
Try It

Lesson 16 : Using Embedded Controls

Working with Form Controls And
Activex Controls
Try It

Lesson 17 : Programming Charts

Try It

Lesson 18 : Programming Pivottables and Pivotcharts

Creating a Pivottable Report
Understanding Pivotcaches
Manipulating Pivotfields in Vba
Manipulating Pivotitems with Vba
Creating a Pivottables Collection
Try It

Lesson 19 : User - Defined Functions
What is a User - Defi Ned Function?
Udf Examples that Solve Common Tasks
Volatile Functions
Try It

Lesson 20 : Debugging Your Code
What is Debugging?
What Causes Errors?
Weapons of Mass Debugging
Trapping Errors
Try It

Part IV : Advanced Programming Techniques

Lesson 21 : Creating Userforms

What is a Userform?
Creating a Userform
Designing a Userform
Adding Controls to a Userform
Showing a Userform
Where Does the Userform's Code Go?
Closing a Userform
Try It

Lesson 22 : Userform Controls and Their Functions

Understanding the Frequently Used Userform Controls
Try It

Lesson 23 : Advanced Userforms

The Userform Toolbar
Modal Versus Modeless
Disabling the Userform's Close Button
Maximizing Your Userform's Size
Selecting and Displaying Photographs on a Userform
Unloading a Userform Automatically
Pre - Sorting the Listbox and Combobox Items
Populating Listboxes and Comboboxes with Unique Items
Displaying a Real - Time Chart in a Userform
Try It

Lesson 24 : Class Modules

What is a Class?
What is a Class Module?
Creating Your Own Objects
An Important Benefi T of Class Modules
Creating Collections
Class Modules for Embedded Objects
Try It

Lesson 25 : Add - Ins

What is an Excel Add - In?
Creating an Add - In
Converting a File to an Add - In
Installing an Add - In
Creating a User Interface for Your Add - In
Closing Add - Ins
Removing an Add - in from the Add - Ins List
Try It

Lesson 26 : Managing External Data

Creating Querytables from Web Queries
Creating a Querytable for Access
Using Text Files to Store External Data
Try It

Lesson 27 : Data Access with Activex Data Objects

Introducing Ado
An Introduction to Structured Query Language (Sql)
Try It

Lesson 28 : Impressing Your Boss (Or at Least Your Friends)
Selecting Cells and Ranges
Filtering Dates
Setting Page Breaks for Specifi Ed Areas
Using a Comment to Log Changes in a Cell
Using the Windows Api with Vba
Scheduling Your Workbook for Suicide
Try It

Part V : Interacting with Other Office Applications

Lesson 29 : Overview of Office Automation from Excel

Why Automate Another Application?
Understanding Offi Ce Automation
Try It

Lesson 30 : Working with Word from Excel

Activating a Word Document
Creating a New Word Document
Copying an Excel Range to a Word Document
Printing a Word Document from Excel
Importing a Word Document to Excel
Try It

Lesson 31 : Working with Outlook from Excel
Opening Outlook
Composing an E - Mail in Outlook from Excel
Putting it All Together
E - Mailing a Single Worksheet
Try It

Lesson 32 : Working with Access from Excel

Adding a Record to an Access Table
Exporting an Access Table to an Excel Spreadsheet
Creating a New Table in Access
Try It

Lesson 33 : Working with Powerpoint from Excel

Creating a New Powerpoint Presentation
Copying a Worksheet Range to a Powerpoint Slide
Copying Chart Sheets to Powerpoint Slides
Running a Powerpoint Presentation from Excel
Try It