[PDF] [PDF] Excel® VBA Programming Excel® VBA Programming For Dummies®

View - Download [PDF] Excel® VBA Programming

Previous PDF

Next PDF

Excel VBA

Programming

by John Walkenbach

4th Edition

Excel

VBA Programming For Dummies

, 4th Edition

Published by:

John Wiley & Sons, Inc.,

111 River Street, Hoboken, NJ 07030-5774,

Copyright © 2015 by John Wiley & Sons, Inc., Hoboken, New Jersey Media and software compilation copyright © 2015 by John Wiley & Sons,

Inc. All rights reserved.

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval sys tem or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permit ted under Sections 107 or 108 of the 1976 United States Copyright Act, w ithout the prior written permission of the Publisher. Requests to the Publisher for permission should be add ressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030 , (201) 748-6011, fax (201)

748-6008, or online at

Trademarks:

Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everythin g Easier, and related trade dress are trademarks or registered trademarks of John Wile y & Sons, Inc. and may not be used without written permission. Excel is a registered trademark of Micr osoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Son s, Inc. is not associated with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY

: THE

PUBLISHER AND THE AUTHOR MAKE NO

REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENE SS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCL UDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WA

RRANTY

MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERI

NG LEGAL,

ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHE R THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. T

HE FACT

THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATIO

N AND/OR A

POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVI DE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHE

N THIS

WORK WAS WRITTEN AND WHEN IT IS READ. FULFILLMENT OF EACH COUPON OFFER I

S THE SOLE

RESPONSIBILITY OF THE OFFEROR.

For general information on our other products and services, please conta ct our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-

3993, or fax 317-572-4002. For technical support,

please visit Wiley publishes in a variety of print and electronic formats and by prin t-on-demand. Some material included with standard print versions of this book may not be included in e-bo oks or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version y ou purchased, you may download this material at . For more information about Wiley products, visit

Library of Congress Control Number: 2015950121

ISBN 978-1-119-07739-8 (pbk); ISBN 978-1-119-0774

1-1 (ebk); ISBN 978-1-119-07744-2 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

Contents at a Glance

Introduction

Part I: Getting Started with Excel VBA Programming ......7

Chapter 1: What Is VBA?

...........................9

Chapter 2: Jumping Right In

...................19

Part II: How VBA Works with Excel

.............................29

Chapter 3: Working in the Visual Basic Editor

Chapter 4: Introducing the Excel Object Model

Chapter 5: VBA Sub and Function Procedures

Chapter 6: Using the Excel Macro Recorder

Part III: Programming Concepts

..................................91

Chapter 7: Essential VBA Language Elements

Chapter 8: Working with Range Objects

Chapter 9: Using VBA and Worksheet Functions.......................................................131

Chapter 10: Controlling Program Flow and Making Decisions.................................145

Chapter 11: Automatic Procedures and Events

Chapter 12: Error-Handling Techniques

Chapter 13: Bug Extermination Techniques

Chapter 14: VBA Programming Examples...................................................................215

Part IV: Communicating with Your Users

...................237

Chapter 15: Simple Dialog Boxes

.........239

Chapter 16: UserForm Basics

...............257

Chapter 17: Using UserForm Controls

275

Chapter 18: UserForm Techniques and Tricks

Chapter 19: Accessing Your Macros through the User Interface ............................321

Part V: Putting It All Together

..................................333 Chapter 20: Creating Worksheet Functions - and Living to Tell about I t .............335

Chapter 21: Creating Excel Add-Ins

.....353 Part VI: The Part of Tens ..........................................365

Chapter 22: Ten VBA Questions (and Answers)

Chapter 23: (Almost) Ten Excel Resources

Chapter 24: Ten VBA Do's and Don'ts

.375 Index

Table of Contents

Introduction

About this Book

.......................1

Obligatory Typographical Conventions Section

Check Your Security Settings

.3

Foolish Assumptions

...............4

Icons Used in This Book

.........5

Beyond the Book

.....................5

Where to Go from Here

...........6 Part I: Getting Started with Excel VBA Programming ......7

Chapter 1: What Is VBA?. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .9

Okay, So What Is VBA?

............9

What Can You Do with VBA?

10

Inserting a bunch of text

Automating a task you perform frequently

......................................11

Automating repetitive operations

Creating a custom command..............................................................11

Creating a custom button

Developing new worksheet functions

Creating custom add-ins for Excel

Creating complete, macro-driven

applications ................................12

Advantages and Disadvantages of VBA

VBA advantages

...........13

VBA disadvantages

......13

VBA in a Nutshell

...................14

An Excursion into Versions

..16

Chapter 2: Jumping Right In. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .19

First Things First

....................19

What You'll Be Doing

............20

Taking the First Steps

...........20

Recording the Macro

.............21

Testing the Macro

..................22

Examining the Macro

............23

Modifying the Macro

.............25

Saving Workbooks That Contain Macros

Understanding Macro Security

Revealing More about the NameAndTime Macro

.....................................28 vi

Excel VBA Programming For Dummies, 4th Edition

Part II: How VBA Works with Excel

..............................29

Chapter 3: Working in the Visual Basic Editor. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .31

What Is the Visual Basic Editor?

Activating the VBE

.......31

Understanding VBE components

Working with the Project Window

Adding a new VBA module

Removing a VBA module

Exporting and importing objects

Working with a Code Window

Minimizing and maximizing windows

Creating a module

........38

Getting VBA code into a module

Entering code directly

.40

Using the macro recorder

Copying VBA code

.......45

Customizing the VBA Environment

Using the Editor tab

.....45

Using the Editor Format tab

Using the General tab

..49

Using the Docking tab

.50

Chapter 4: Introducing the Excel Object Model. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .51

Excel Is an Object?

.................52

Climbing Down the Object Hierarchy

Wrapping Your Mind around Collections

Referring to Objects

..............54

Navigating through the hierarchy

Simplifying object references

Diving into Object Properties and Methods

Object properties

.........58

Object methods

............59 Object events........................................................................ ................60

Finding Out More

...................61

Using VBA's Help system

Using the Object Browser

Automatically listing properties and methods

................................63

Chapter 5: VBA Sub and Function Procedures

. .65

Understanding Subs versus Functions

Looking at Sub procedures

Looking at Function procedures

vii

Table of Contents

Naming Subs and Functions

.67

Executing Sub procedures

Executing the Sub procedure directly

Executing the procedure from the Macro dialog box

.....................70 Executing a macro by using a shortcut key......................................71

Executing the procedure from a button or shape

...........................72

Executing the procedure from another procedure

.........................74

Executing Function procedures

Calling the function from a Sub procedure

......................................75

Calling a function from a worksheet

formula ...................................76

Chapter 6: Using the Excel Macro Recorder. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .79

Is It Live, or Is It VBA?

...........79

Recording Basics

...................80

Preparing to Record

..............82

Relative or Absolute?

............82 Recording in absolute mode...............................................................82

Recording in relative mode

What Gets Recorded?

............85

Recording Options

.................86

Macro name

..................86

Shortcut key

.................87 Store Macro In option........................................................................ ..87

Description

...................88

Is This Thing Ef?cient?

..........88

Part III: Programming Concepts

...................................91

Chapter 7: Essential VBA Language Elements

. .93

Using Comments in Your VBA Code

Using Variables, Constants, and Data Types

Understanding variables

What are VBA's data types?

Declaring and scoping variables

Working with constants

Premade constants

....106

Working with strings

.107

Working with dates

....107

Using Assignment Statements

Assignment statement examples

About that equal sign

109

Smooth operators

......110 viii

Excel VBA Programming For Dummies, 4th Edition

Working with Arrays

...........111

Declaring arrays

.........112

Multidimensional arrays

Dynamic arrays

..........113

Using Labels

.........................114

Chapter 8: Working with Range Objects

. .115

A Quick Review

....................115

Other Ways to Refer to a Range

The Cells property

.....117

The Offset property

...118

Some Useful Range Object Properties

The Value property........................................................................ ....120 The Text property........................................................................ ......121

The Count property

...121

The Column and Row properties

The Address property

The HasFormula property

The Font property........................................................................ ......123

The Interior property

124

The Formula property

The NumberFormat property

Some Useful Range Object Methods

The Select method

.....127

The Copy and Paste methods

The Clear method

......128

The Delete method

....129 Chapter 9: Using VBA and Worksheet Functions. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .131

What Is a Function?

.............131

Using Built-In VBA Functions

VBA function examples

VBA functions that do more than return a value

...........................135

Discovering VBA functions

Using Worksheet Functions in VBA

Worksheet function examples

Entering worksheet functions

More about using worksheet functions

Using Custom Functions

.....142 Chapter 10: Controlling Program Flow and Making Decisions. .. .. .. .. .145

Going with the Flow, Dude

.145

The GoTo Statement

...........146

Decisions, Decisions

............147

The If-Then structure

148

The Select Case structure

ix

Table of Contents

Knocking Your Code for a Loop

For-Next loops

............155

Do-While loop

.............161

Do-Until loop

...............162

Using For Each-Next Loops with Collections

Chapter 11: Automatic Procedures and Events. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .165

Preparing for the Big Event

165

Are events useful?

......167

Programming event-handler procedures

Where Does the VBA Code Go?

Writing an Event-Handler Procedure

Introductory Examples

.......171

The Open event for a workbook

The BeforeClose event for a workbook

The BeforeSave event for a workbook

Examples of Activation Events

Activate and deactivate events in a sheet

Activate and deactivate events in a workbook

..............................176 Workbook activation events.............................................................178

Other Worksheet-Related Events

The BeforeDoubleClick event

The BeforeRightClick event

The Change event

......180

Events Not Associated with Objects

The OnTime event

.....182

Keypress events

.........184

Chapter 12: Error-Handling Techniques. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .187

Types of Errors

....................187

An Erroneous Example

.......188

The macro's not quite perfect

The macro is still not perfect

Is the macro perfect yet?

Giving up on perfection

Handling Errors Another Way

Revisiting the EnterSquareRoot procedure....................................192

About the On Error statement

Handling Errors: The Details

Resuming after an error

Error handling in a nutshell

Knowing when to ignore errors

Identifying speci?c errors

An Intentional Error

............199 x

Excel VBA Programming For Dummies, 4th Edition

Chapter 13: Bug Extermination Techniques

. .201

Species of Bugs

....................201

Identifying Bugs

...................202

Debugging Techniques

.......203

Examining your code

.203

Using the MsgBox function

Inserting Debug.Print statements

Using the VBA debugger

About the Debugger

............207

Setting breakpoints in your code

Using the Watch window

Using the Locals window

Bug Reduction Tips

.............213

Chapter 14: VBA Programming Examples

. .215

Working with Ranges

..........215

Copying a range

quotesdbs_dbs17.pdfusesText_23

[PDF] excel vba programming free course

[PDF] excel vba training courses free

[PDF] exception handling in java by durga sir pdf

[PDF] exceptions in english grammar rules

[PDF] excerpt 12 years a slave

[PDF] excessive force examples

[PDF] exchange driving licence netherlands 30% ruling

[PDF] exchange email on iphone not working

[PDF] exchange kohls gift card for cash

[PDF] exchange place fireworks 2020

[PDF] exchange rate and australian economy

[PDF] exchange rate april 2019

[PDF] exchange rate buying

[PDF] exchange rate csv

[PDF] exchange rate effect