Excel Vba 24–Hour Trainer - Urtis Tom | Libro + Dvd John Wiley & Sons 05/2011 - HOEPLI.it

home libri books ebook dvd e film top ten sconti 0 Carrello

Torna Indietro

urtis tom - excel vba 24–hour trainer

Excel VBA 24–Hour Trainer Video lessons on DVD

Disponibilità: Non disponibile o esaurito presso l'editore

32,00 €

Questo prodotto usufruisce delle SPEDIZIONI GRATIS
selezionando l'opzione Corriere Veloce in fase di ordine.

Facebook Twitter Aggiungi commento

Spese Gratis


Lingua: Inglese
Pubblicazione: 05/2011

Note Editore

Increase your productivity and save time and effort with Excel VBA

This unique book–and–DVD package prepares you to get more out of Excel by using Visual Basic for Applications (VBA) to automate your routine or labor–intensive Excel tasks. Microsoft Excel MVP and author Tom Urtis walks through a series of lessons while the accompanying DVD provides demos to complement each lesson. Urtis takes an in–depth look at how manual tasks in Excel can be programmed with VBA for greater speed, efficiency, and accuracy. You'll learn how to use VBA to manipulate Excel in ways you may never have thought possible.

Excel VBA 24–Hour Trainer:

  • Introduces you to VBA and discusses topics including object orientedprogramming, variable declaration, objects and collections, and arrays

  • Teaches you how to write your own macros for programming loops, events, charts, pivot tables and pivot charts, and user–defined functions

  • Shows you how to customize the look and feel of Excel with UserForms, Input Boxes, Message Boxes, and embedded controls

  • Examines advanced topics including class modules, add–ins, and retrieving external data with ADO and SQL

  • Demonstrates how to interact with other Office Applications from Excel, including Word, Access®, PowerPoint®, and Outlook®

Wrox guides are crafted to make learning programming languages and technologies easier than you think. Written by programmers for programmers, they provide a structured, tutorial format that will guide you through all the techniques involved.

On the DVD

Don't just learn do!

The lessons within the book are accompanied by an instructional demonstration on the DVD.

  • Write your own macros

  • Make decisions with VBA

  • Repeat actions with loops

  • Automate procedures with worksheet and workbook events

  • Work with arrays

  • Create class modules

  • Program charts and pivot tables

  • Create add–ins

  • Interact with Office applications Word, Access, PowerPoint, and Outlook

  • Manage external data

  • Debug your code


Introduction xxvii

Section I: Understanding the BASI Cs

Lesson 1: Introduc ing VBA 3

What Is VBA? 3

A Brief History of VBA 4

What VBA Can Do for You 5

Automating a Recurring Task 5

Automating a Repetitive Task 5

Running a Macro Automatically if Another Action Takes Place 5

Creating Your Own Worksheet Functions 5

Simplifying the Workbook s Look and Feel for Other Users 5

Controlling Other Office Applications from Excel 6

Liabilities of VBA 7

Try It 8

Lesson 2: Getting Started with Macros 9

Composing Your First Macro 9

Accessing the VBA Environment 9

Using the Macro Recorder 12

Running a Macro 16

The Macro Dialog Box 16

Shortcut Key 17

Try It 17

Lesson Requirements 18

Step–by–Step 18

Lesson 3: Introduc ing the Visual Basic Editor 19

What Is the VBE? 19

How To Get Into the VBE 20

Understanding the VBE 20

The Project Explorer Window 21

The Code Window 21

The Properties Window 22

The Immediate Window 22

Understanding Modules 22

Using the Object Browser 23

Exiting the VBE 24

Try It 25

Lesson 4: Working in the VBE 27

Toolbars in the VBE 27

Macros and Modules 28

Locating Your Macros 28

Understanding the Code 29

Editing a Macro with Comments and Improvements to the Code 30

Deleting a Macro 33

Inserting a Module 33

Renaming a Module 34

Deleting a Module 36

Locking and Protecting the VBE 36

Try It 37

Lesson Requirements 37

Step–by–Step 37

Section II : Diving Deeper into VBA

Lesson 5: Ob ject–oriented Programm ing A n Ov erview 43

What Object–Oriented Programming Means 43

The Object Model 44

Properties 45

Methods 46

Collections 46

Try It 47

Lesson 6: Variabl es, Data Types, and Constants 49

What Is a Variable? 49

Assigning Values to Variables 50

Why You Need Variables 50

Data Types 51

Understanding the Different Data Types 51

Declaring a Variable for Dates and Times 53

Declaring a Variable with the Proper Data Type 53

Forcing Variable Declaration 54

Understanding a Variable s Scope 56

Local Macro Level Only 56

Module Level 56

Application Level 57

Constants 57

Choosing the Scope and Lifetime of Your Constants 58

Try It 58

Lesson Requirements 58

Step–by–Step 58

Understanding Lesson 7: Ob jects and Coll ections 61

Workbooks 61

Worksheets 62

Cells and Ranges 63

SpecialCells 64

Try It 65

Lesson Requirements 65

Step–by–Step 65

Lesson 8: Making Decisions with VBA 69

Understanding Logical Operators 69

AND 70

OR 70

NOT 71

Choosing Between This or That 72

If Then 72

If Then Else 73

If Then ElseIf 74

Select Case 74

Getting Users to Make Decisions 76

Message Boxes 76

Input Boxes 77

Try It 78

Lesson Requirements 78

Step–by–Step 78

Section III : the macro recorder:

writing your Own Code

Lesson 9: Repeating Ac tions with Loops 85

What Is a Loop? 85

Types of Loops 86

For Next 87

For Each Next 88

Exiting a For Loop 89

Looping In Reverse with Step 90

Do While 91

Do Until 91

Do Loop While 93

Do Loop Until 94

While Wend 94

Nesting Loops 94

Try It 95

Lesson Requirements 96

Step–by–Step 96

Lesson 10: Working with Arrays 99

What Is an Array? 99

What Arrays Can Do for You 101

Declaring Arrays 102

The Option Base Statement 103

Boundaries in Arrays 104

Declaring Arrays with Fixed Elements 104

Declaring Dynamic Arrays with ReDim and Preserve 105

Try It 107

Lesson Requirements 107

Step–by–Step 107

Lesson 11: Au tomating Procedures with Worksheet Ev ents 111

What Is an Event ? 111

Worksheet Events an Overview 112

Where Does the Worksheet Event Code Go? 112

Enabling and Disabling Events 114

Examples of Common Worksheet Events 115

Worksheet—Change Event 115

Worksheet—SelectionChange Event 116

Worksheet—BeforeDoubleClick Event 116

Worksheet—BeforeRightClick Event 117

Worksheet—FollowHyperlink Event 117

Worksheet—Activate Event 117

Worksheet—Deactivate Event 118

Worksheet—Calculate Event 118

Worksheet—PivotTableUpdate Event 119

Try It 119

Lesson Requirements 119

Step–by–Step 119

Au tomating Procedures w Lesson 12: ith Workbook Ev ents 123

Workbook Events An Overview 123

Where Does the Workbook Event Code Go? 123

Entering Workbook Event Code 125

Examples of Common Workbook Events 126

Workbook—Open Event 126

Workbook—BeforeClose Event 127

Workbook—Activate Event 127

Workbook—Deactivate Event 128

Workbook—SheetChange Event 128

Workbook—SheetSelectionChange Event 128

Workbook—SheetBeforeDoubleClick Event 129

Workbook—SheetBeforeRightClick Event 129

Workbook—SheetPivotTableUpdate Event 130

Workbook—NewSheet Event 130

Workbook—BeforePrint Event 130

Workbook—SheetActivate Event 131

Workbook—SheetDeactivate Event 131

Workbook—BeforeSave Event 131

Try It 132

Lesson Requirements 132

Step–by–Step 132

Lesson 13: Emb edded Controls 135

Working with Forms Controls and ActiveX Controls 135

The Forms Toolbar 136

The Control Toolbox 140

Try It 144

Lesson Requirements 144

Step–by–Step 144

Lesson 14: Programm ing Charts 151

Adding a Chart to a Chart Sheet 152

Adding an Embedded Chart to a Worksheet 154

Moving a Chart 155

Looping Through All Embedded Charts 157

Deleting Charts 158

Renaming a Chart 159

Try It 160

Lesson Requirements 160

Step–by–Step 160

Lesson 15: Programm ing PivotTabl es and PivotCharts 163

Creating a PivotTable Report 163

Hiding the PivotTable Field List 167

Using the Report Filter Area 167

Formatting Numbers in the Values Area 168

Why It s Called a PivotTable 170

Creating a PivotChart 171

Understanding PivotCaches 173

Manipulating PivotFields in VBA 176

Manipulating PivotItems with VBA 177

Creating a PivotTables Collection 177

Try It 178

Lesson Requirements 178

Step–by–Step 179

Lesson 16: User Defined Fu nctions 183

What Is a User Defined Function? 183

Characteristics of User Defined Functions 184

Anatomy of a UDF 184

UDF Examples That Solve Common Tasks 185

Volatile Functions 188

The Name of the Active Worksheet and Workbook 189

UDFs with Conditional Formatting 190

Calling Your Function from a Macro 190

Adding a Description to the Insert Function Dialog 191

Try It 193

Lesson Requirements 193

Step–by–Step 193

Lesson 17: Debu gging Your Code 195

What Is Debugging? 195

What Causes Errors? 196

Weapons of Mass Debugging 198

The Debugging Toolbar 198

Trapping Errors 207

Error Handler 207

Bypassing Errors 208

Try It 210

Lesson Requirements 210

Step–by–Step 210

Section IV: Advanced Programm ing Techniques

Lesson 18: Creating UserForms 215

What Is a UserForm? 215

Creating a UserForm 216

Designing a UserForm 218

Showing a UserForm 225

Where Does the UserForm s Code Go? 225

Closing a UserForm 226

Unloading a UserForm 226

Hiding a UserForm 227

Try It 228

Lesson Requirements 228

Step–by–Step 228

Lesson 19: nd Their Fu nctions 231

Understanding the Frequently Used UserForm Controls 231

CommandButtons 232

Labels 232

TextBoxes 234

ListBoxes 236

ComboBoxes 238

CheckBoxes 240

OptionButtons 241

Frames 243

MultiPages 245

Try It 246

Lesson Requirements 246

Step–by–Step 246

Lesson 20: Advanced UserForms 249

The UserForm Toolbar 249

Modal versus Modeless 250

Disabling the UserForm s Close Button 250

Maximizing Your UserForm s Size 252

Selecting and Displaying Photographs on a UserForm 252

Unloading a UserForm Automatically 253

Pre–Sorting the ListBox and ComboBox Items 253

Populating ListBoxes and ComboBoxes with Unique Items 255

Display a Real–Time Chart in a UserForm 258

Try It 259

Lesson Requirements 259

Step–by–Step 259

Lesson 21: Class Modul es 263

What Is a Class? 263

What Is a Class Module? 264

Creating Your Own Objects 265

An Important Benefit of Class Modules 266

Creating Collections 268

Class Modules for Embedded Objects 269

Try It 272

Lesson Requirements 272

Step–by–Step 272

Lesson 22: Add–Ins 279

What Is an Excel Add–In? 279

Creating an Add–In 280

Converting a File to an Add–In 284

Installing an Add–In 286

Creating a User Interface for Your Add–In 288

Changing the Add–In s Code 290

Closing Add–Ins 290

Removing an Add–In from the Add–Ins List 291

Try It 291

Lesson Requirements 291

Step–by–Step 291

Lesson 23: Managing External Data 295

Creating QueryTables from Web Queries 295

Creating a QueryTable for Access 299

Using Text Files to Store External Data 301

Try It 304

Lesson Requirements 304

Step–by–Step 304

Lesson 24: Data Acc ess with Ac tiveX Data Ob jects 307

Introducing ADO 307

The Connection Object 309

The Recordset Object 309

The Command Object 310

An Introduction to Structured Query Language (SQL) 310

The SELECT Statement 311

The INSERT Statement 311

The UPDATE Statement 312

The DELETE Statement 312

Try It 313

Lesson 25: Not Gone, Not Forgotten 315

Using Dialog Sheets 315

What Does a Dialog Sheet Look Like? 316

Option to Show Message Only Once 318

Using XLM Get.Cell Functions 321

Using the SendKeys Method 322

Try It 323

Lesson Requirements 323

Step–by–Step 323

Interacting with Other Section V: Office Applications

Lesson 26: Ov erview of Office Au tomation from Excel 327

Why Automate Another Application? 327

Understanding Office Automation 328

Early Binding 328

Late Binding 329

Which One Is Better? 330

Try It 330

Lesson Requirements 330

Step–by–Step 330

Lesson 27: Working with Word from Excel 333

Activating a Word Document 333

Activating the Word Application 334

Opening and Activating a Word Document 334

Creating a New Word Document 336

Copying an Excel Range to a Word Document 337

Printing a Word Document from Excel 337

Importing a Word Document to Excel 338

Try It 339

Lesson Requirements 339

Step–by–Step 339

Lesson 28: Working with Ou tlook from Excel 343

Opening Outlook 343

Composing an E–mail in Outlook from Excel 344

Creating a MailItem Object 344

Transferring an Excel Range to the Body of Your E–mail 345

Putting It All Together 346

E–mailing a Single Worksheet 348

Try It 348

Lesson Requirements 348

Step–by–Step 348

Lesson 29: ith Acc ess from Excel 353

Adding a Record to an Access Table 353

Exporting an Access Table to an Excel Spreadsheet 356

Creating a New Table in Access 358

Try It 359

Lesson Requirements 359

Step–by–Step 360

Lesson 30: Working with PowerPoint from Excel 363

Creating a New PowerPoint Presentation 363

Copying a Worksheet Range to a PowerPoint Slide 364

Copying Chart Sheets to PowerPoint Slides 365

Running a PowerPoint Presentation from Excel 367

Try It 368

Lesson Requirements 368

Step–by–Step 368

Appendix: What s on the DVD? 371

Index 375


This unique book-and-DVD package helps even non-programmers extend Excel with VBA

Virtually every manual task in Excel can be automated with VBA, which increases your productivity and saves enormous amounts of time. This unique book-and-DVD package prepares you to get more out of Excel by using Visual Basic for Applications (VBA) to automate many routine or labor-intensive Excel tasks. Microsoft Excel MVP and author Tom Urtis walks through a series of lessons and illustrations, while the accompanying DVD provides demos and screencasts to complement each lesson.
* Introduces you to programming with Visual Basic for Applications (VBA), macro composition, and the programming environment
* Explains events programming, embedded controls, user forms, message boxes, input boxes, looping, and more
* Shows you how to control other Office applications from Excel, such as Word, Outlook, Access, and PowerPoint
* Includes enhanced coverage of each lesson on the DVD, which also offers detailed examples
* Provides ideas for applying VBA to everyday tasks in a way that's both practical and fun

Use this book-and-DVD package to get the total learning experience of VBA for Excel!


Tom Urtis is a Microsoft Office developer, programmer, instructor, and Microsoft Excel MVP. Tom is founder of Atlas Programming Management (www.atlaspm.com), a Silicon Valley–based Office business solutions company specializing in Excel, providing consulting, development, training, and support of fully customized Office programs for a diverse international clientele.

Altre Informazioni



Condizione: Nuovo
Collana: Wrox Programmer to Programmer
Dimensioni: 235 x 20.73 x 187 mm Ø 758 gr
Formato: Brossura
Pagine Arabe: 432

Utilizziamo i cookie di profilazione, anche di terze parti, per migliorare la navigazione, per fornire servizi e proporti pubblicità in linea con le tue preferenze. Se vuoi saperne di più o negare il consenso a tutti o ad alcuni cookie clicca qui. Chiudendo questo banner o proseguendo nella navigazione acconsenti all’uso dei cookie.