Excel® VBA Programming For Dummies®, 4th Edition to media such as a CD or DVD that is not included in the version you purchased, you may download
Previous PDF | Next PDF |
[PDF] 2 Excel VBA Programming For Dummies, 4th Edition - Media Control
Excel® VBA Programming For Dummies®, 4th Edition to media such as a CD or DVD that is not included in the version you purchased, you may download
[PDF] Excel Vba Programming For Dummies
7 jan 2021 · for dummies 3rd edition, download excel vba programming for dummies pdf for dummies 4th edition by john walkenbach the vba language contains a number
[PDF] Microsoft Office Excel VBA Programming For Dummiespdf
Excel® 2007 VBA Programming For Dummies® Published by for his assis- tance with this edition This book has its very own Web site where you can download the example located in the third row and fourth column of the matrix
[PDF] Read PDF ~ Excel VBA Programming for Dummies, 4th Edition
[PDF] Excel VBA Programming for Dummies, 4th Edition (Paperback) Excel VBA Programming for Dummies, 4th Edition (Paperback) Book Review A must buy
[PDF] Excel Vba Programming For Dummies 4th E
Bookmark File PDF Excel Vba Programming For Dummies 4th E This 4 th edition of Excel VBA Programming For Dummies introduces you to a wide array of
[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 31 december 2018 gbp to usd
[PDF] exchange rate and australian economy
[PDF] exchange rate april 2019
[PDF] exchange rate buying
[PDF] exchange rate csv
Excel VBA
Programming
by John Walkenbach4th Edition
ExcelVBA Programming For Dummies
, 4th EditionPublished 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
: THEPUBLISHER 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 WARRANTY
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 RENDERING 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. THE FACT
THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION 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 WHEN THIS
WORK WAS WRITTEN AND WHEN IT IS READ. FULFILLMENT OF EACH COUPON OFFER IS 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, visitLibrary 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 ......7Chapter 1: What Is VBA?
...........................9Chapter 2: Jumping Right In
...................19Part II: How VBA Works with Excel
.............................29Chapter 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
..................................91Chapter 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.................................145Chapter 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
...................237Chapter 15: Simple Dialog Boxes
.........239Chapter 16: UserForm Basics
...............257Chapter 17: Using UserForm Controls
275Chapter 18: UserForm Techniques and Tricks
Chapter 19: Accessing Your Macros through the User Interface ............................321Part V: Putting It All Together
..................................333 Chapter 20: Creating Worksheet Functions - and Living to Tell about I t .............335Chapter 21: Creating Excel Add-Ins
.....353 Part VI: The Part of Tens ..........................................365Chapter 22: Ten VBA Questions (and Answers)
Chapter 23: (Almost) Ten Excel Resources
Chapter 24: Ten VBA Do's and Don'ts
.375 IndexTable of Contents
Introduction
About this Book
.......................1Obligatory Typographical Conventions Section
Check Your Security Settings
.3Foolish Assumptions
...............4Icons Used in This Book
.........5Beyond the Book
.....................5Where to Go from Here
...........6 Part I: Getting Started with Excel VBA Programming ......7Chapter 1: What Is VBA?. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .9
Okay, So What Is VBA?
............9What Can You Do with VBA?
10Inserting a bunch of text
Automating a task you perform frequently
......................................11Automating repetitive operations
Creating a custom command..............................................................11Creating a custom button
Developing new worksheet functions
Creating custom add-ins for Excel
Creating complete, macro-driven
applications ................................12Advantages and Disadvantages of VBA
VBA advantages
...........13VBA disadvantages
......13VBA in a Nutshell
...................14An Excursion into Versions
..16Chapter 2: Jumping Right In. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .19
First Things First
....................19What You'll Be Doing
............20Taking the First Steps
...........20Recording the Macro
.............21Testing the Macro
..................22Examining the Macro
............23Modifying the Macro
.............25Saving Workbooks That Contain Macros
Understanding Macro Security
Revealing More about the NameAndTime Macro
.....................................28 viExcel VBA Programming For Dummies, 4th Edition
Part II: How VBA Works with Excel
..............................29Chapter 3: Working in the Visual Basic Editor. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .31
What Is the Visual Basic Editor?
Activating the VBE
.......31Understanding 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
........38Getting VBA code into a module
Entering code directly
.40Using the macro recorder
Copying VBA code
.......45Customizing the VBA Environment
Using the Editor tab
.....45Using the Editor Format tab
Using the General tab
..49Using the Docking tab
.50Chapter 4: Introducing the Excel Object Model. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .51
Excel Is an Object?
.................52Climbing Down the Object Hierarchy
Wrapping Your Mind around Collections
Referring to Objects
..............54Navigating through the hierarchy
Simplifying object references
Diving into Object Properties and Methods
Object properties
.........58Object methods
............59 Object events........................................................................ ................60Finding Out More
...................61Using VBA's Help system
Using the Object Browser
Automatically listing properties and methods
................................63Chapter 5: VBA Sub and Function Procedures
. .65Understanding Subs versus Functions
Looking at Sub procedures
Looking at Function procedures
viiTable of Contents
Naming Subs and Functions
.67Executing Sub procedures
Executing the Sub procedure directly
Executing the procedure from the Macro dialog box
.....................70 Executing a macro by using a shortcut key......................................71Executing the procedure from a button or shape
...........................72Executing the procedure from another procedure
.........................74Executing Function procedures
Calling the function from a Sub procedure
......................................75Calling a function from a worksheet
formula ...................................76Chapter 6: Using the Excel Macro Recorder. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .79
Is It Live, or Is It VBA?
...........79Recording Basics
...................80Preparing to Record
..............82Relative or Absolute?
............82 Recording in absolute mode...............................................................82Recording in relative mode
What Gets Recorded?
............85Recording Options
.................86Macro name
..................86Shortcut key
.................87 Store Macro In option........................................................................ ..87Description
...................88Is This Thing Ef?cient?
..........88Part III: Programming Concepts
...................................91Chapter 7: Essential VBA Language Elements
. .93Using 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
....106Working with strings
.107Working with dates
....107Using Assignment Statements
Assignment statement examples
About that equal sign
109Smooth operators
......110 viiiExcel VBA Programming For Dummies, 4th Edition
Working with Arrays
...........111Declaring arrays
.........112Multidimensional arrays
Dynamic arrays
..........113Using Labels
.........................114Chapter 8: Working with Range Objects
. .115A Quick Review
....................115Other Ways to Refer to a Range
The Cells property
.....117The Offset property
...118Some Useful Range Object Properties
The Value property........................................................................ ....120 The Text property........................................................................ ......121The Count property
...121The Column and Row properties
The Address property
The HasFormula property
The Font property........................................................................ ......123The Interior property
124The Formula property
The NumberFormat property
Some Useful Range Object Methods
The Select method
.....127The Copy and Paste methods
The Clear method
......128The Delete method
....129 Chapter 9: Using VBA and Worksheet Functions. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .131What Is a Function?
.............131Using Built-In VBA Functions
VBA function examples
VBA functions that do more than return a value
...........................135Discovering 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. .. .. .. .. .145Going with the Flow, Dude
.145The GoTo Statement
...........146Decisions, Decisions
............147The If-Then structure
148The Select Case structure
ixTable of Contents
Knocking Your Code for a Loop
For-Next loops
............155Do-While loop
.............161Do-Until loop
...............162Using For Each-Next Loops with Collections
Chapter 11: Automatic Procedures and Events. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .165
Preparing for the Big Event
165Are events useful?
......167Programming event-handler procedures
Where Does the VBA Code Go?
Writing an Event-Handler Procedure
Introductory Examples
.......171The 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.............................................................178Other Worksheet-Related Events
The BeforeDoubleClick event
The BeforeRightClick event
The Change event
......180Events Not Associated with Objects
The OnTime event
.....182Keypress events
.........184Chapter 12: Error-Handling Techniques. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .187