[PDF] Excel® 2019 with VBA - contente-bookshelfde

View - Download Excel® 2019 with VBA - contente-bookshelfde

Previous PDF

Next PDF

Excel® 2019

Power Programming

with VBA

Excel® 2019

Power Programming

with VBA

Michael Alexander

Dick Kusleika

Previously by John Walkenbach

Excel® 2019 Power Programming with VBA

Published by

John Wiley & Sons, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

Copyright

2019 by John Wiley & Sons, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-1-119-51492-3

ISBN: 978-1-119-51494-7 (ebk)

ISBN: 978-1-119-51491-6 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by

any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under

Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of

the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance

Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher

for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street,

Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at

Limit of Liability/Disclaimer of Warranty:

The publisher and the author make no representations or warranties

with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties,

including 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. Neither the publisher nor the author shall be liable for damages arising

herefrom. The fact that an organization or Web site 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 provide 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.

For general information on our other products and services please contact our Customer Care Department within

the United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with

standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to

media such as a CD or DVD that is not included in the version you purchased, you may download this material at

. For more information about Wiley products, visit .

Library of Congress Control Number:

2019936928

Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/

or its affiliates, in the United States and other countries, and may not be used without written permission.

Microsoft and Excel are registered trademarks of Microsoft Corporation. All other trademarks are the property of

their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this

book. v

About the Authors

Michael Alexander is a Microsoft Certified Application Developer (MCAD) and author of sev- eral books on advanced business analysis with Microsoft Access and Microsoft Excel. He has more than 20 years of experience consulting and developing Microsoft Office solutions. Mike has been named a Microsoft MVP for his ongoing contributions to the Excel community. You can find Mike at

Dick Kusleika

is a 12-time Microsoft Excel MVP and has been working with Microsoft Office for more than 20 years. Dick develops Access- and Excel-based solutions for his clients and has conducted training seminars on Office products in the United States and Australia. Dick also writes a popular Excel-related blog at vii

About the Technical Editors

Doug Holland

is an Architect Evangelist at Microsoft Corporation, working with partners to drive digital transformation through technologies such as the Microsoft Cloud, Office 365, and HoloLens. He holds a master's degree in software engineering from Oxford University and lives in Northern California with his wife and five children. Guy Hart-Davis is the author of an improbable number of computer books on a bizarre range of topics. If you had been wondering who was responsible for the

Word 2000 Developer's Hand

book, AppleScript: A Beginner's Guide, iMac Portable Genius, or Samsung Galaxy S8 Maniac's

Guide, you need wonder no more.

Credits

Associate Publisher

Jim Minatel

Senior Editorial Assistant

Devon Lewis

Editorial Manager

Pete Gaughan

Production Manager

Katie Wisor

Project Editor

Gary Schwartz

Production Editor

Barath Kumar Rajasekaran

Technical Editors

Doug Holland

Guy Hart-DavisCopy EditorKim Wimpsett

Proofreader

Nancy Bell

Indexer

Johnna VanHoose Dinse

Cover Designer

Wiley

Cover Image

Rost-9D/Getty Images

Acknowledgments

O ur deepest thanks to the professionals at John Wiley & Sons for all the hours of work put into bringing this book to life. Thanks also to Doug Holland and Guy Hart-Davis for suggesting numerous improvements to the examples and text in this book. A special thank-you goes out to our families for putting up with all the time spent locked away on this project. Finally, we'd like to thank John Walkenbach for his many years of work on the previous editions of this book. His efforts in curating Excel knowledge have been instrumental, not only in helping millions of Excel users to achieve their learning goals but also in inspiring countless Excel MVPs to share their expertise with the Excel community. xiii

Contents at a Glance

Introduction ........................................................................ ..................................xxxiii

Part I: Introduction to Excel VBA . .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .1

Chapter 1: Essentials of Spreadsheet Application Development Chapter 2: Introducing Visual Basic for Applications

Chapter 3: VBA Programming Fundamentals

Chapter 4: Working with VBA Sub Procedures

Chapter 5: Creating Function Procedures

Chapter 6: Understanding Excel's Events

Chapter 7: VBA Programming Examples and Techniques

Part II: Advanced VBA Techniques . .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .287

Chapter 8: Working with Pivot Tables

...289

Chapter 9: Working with Charts

...........305

Chapter 10: Interacting with Other Applications

Chapter 11: Working with External Data and Files

Part III: Working with UserForms . .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .417

Chapter 12: Leveraging Custom Dialog Boxes

Chapter 13: Introducing UserForms ........................................................................

......441

Chapter 14: Looking at UserForm Examples

Chapter 15: Implementing Advanced UserForm Techniques ............................................519

Part IV: Developing Excel Applications . .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .567

Chapter 16: Creating and Using Add-Ins

Chapter 17: Working with the Ribbon

...599

Chapter 18: Working with Shortcut

Menus ....................................................................637

Chapter 19: Providing Help for Your Applications

Chapter 20: Leveraging Class Modules

..679

Chapter 21: Understanding Compatibility Issues

Part V: Appendix

. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .713

Appendix: VBA Statements and Functions Reference .....................................................715

Index ........................................................................ xv

Contents

Part I:

Introduction to Excel VBA 1

Chapter 1: Essentials of Spreadsheet Application Development˜.˜.˜.˜.˜.˜.˜.˜.˜.˜.˜.˜.˜.˜.˜

.˜.˜.˜.˜.˜.˜3

What Is a Spreadsheet Application?

3

Steps for Application Development

.4

Determining User Needs ........................................................................................5

Planning an Application That Meets User Needs

Determining the Most Appropriate User Interface

Customizing the Ribbon

........8 Customizing shortcut menus ........................................................................ .8

Creating shortcut keys

Creating custom dialog boxes

9

Using ActiveX controls on a worksheet

Executing the development effort ................................................................12

Concerning Yourself with the End User

Testing the application

Making the application bulletproof ...............................................................13 Making the application aesthetically appealing and intuitive .........................15

Creating a user Help system

Documenting the development effort ............................................................16

Distributing the application to the user

Updating the application when necessary

Other Development Issues.....................................................................................17

The user's installed version of Excel

Language issues

System speed ..............................................................................................18

Video modes ...............................................................................................18

Chapter 2: Introducing Visual Basic for Applications 19

Getting a Head Start with the Macro Recorder

Creating your first macro

.....20 Examining your macro ........................................................................ 21

Contents

xvi

Testing your macro

.....22

Editing your macro

.....22

Comparing absolute and relative macro recording

Recording macros with absolute references

Recording macros with relative references

Other macro recording concepts

Macro security in Excel

Trusted locations ................................................................................28

Storing macros in your Personal Macro Workbook

..................................29 Assigning a macro to a button and other form controls..........................30

Placing a macro on the Quick Access toolbar

Working with the Visual Basic Editor

Understanding VBE components ...................................................................32 Menu bar ........................................................................ ....................32 Toolbar ........................................................................ ......................33 Project window ........................................................................ ...........33

Code window

..............33 Immediate window .............................................................................34 Working with the Project window .................................................................34

Adding a new VBA module

Removing a VBA module

Working with a Code window........................................................................36 Minimizing and maximizing windows ..................................................36

Getting VBA code into a module

Customizing the VBA environment

The Editor tab

............39

The Editor Format tab

The General tab

...................41

The Docking tab

VBA Fundamentals ........................................................................ .......................43 Understanding objects ........................................................................ .........43 Understanding collections ........................................................................ ..44

Understanding properties ...........................................................................44

Specifying properties for the active object

Understanding methods ......................................................................46

Deep Dive: Working with Range Objects

Finding the properties of the Range object...................................................48 The Range property ........................................................................ ............48

The Cells property

...............49

The Offset property

.............51

Essential Concepts to Remember ........................................................................

...52

Don't Panic-You Are Not Alone

......54

Read the rest of the book

.....54

Let Excel help write your macro

Contents

xvii

Use the Help system

............55

Location matters when asking for help

You need to be connected to the Internet

Use the Object Browser

........55

Pilfer code from the Internet

Leverage user forums

..........57

Visit expert blogs

................58

Mine YouTube for video training

Learn from the Microsoft Office Dev Center

Dissect the other Excel files in your organization

Ask your local Excel genius

Chapter 3: VBA Programming Fundamentals

61

VBA Language Elements: An Overview

Comments ...........................................................................................................63

Variables, Data Types, and Constants

Defining data types

.............66 Declaring variables ........................................................................ ..............67

Determining a data type

Forcing yourself to declare all variables

Scoping variables ........................................................................ ................70 Local variables ........................................................................ ...........70 Module-wide variables .........................................................................71 Public variables ........................................................................ ..........72 Static variables ........................................................................ ..........72 Working with constants ........................................................................ .......72 Declaring constants ........................................................................ ....73

Using predefined constants

Working with strings ...................................................................................74

Working with dates

.............74 Assignment Statements ........................................................................ ................76 Arrays ........................................................................ Declaring arrays ........................................................................ ..........................78

Declaring multidimensional arrays

Declaring dynamic arrays ........................................................................ ....79 Object Variables ........................................................................ ...........................80 User-Defined Data Types ........................................................................ ...............81 Built-in Functions ........................................................................ ........................82

Manipulating Objects and Collections

With-End With constructs ........................................................................ ....85

For Each-Next constructs .............................................................................86

Controlling Code Execution ........................................................................ ...........88 GoTo statements ........................................................................ ..................88 If-Then constructs ........................................................................ ...............89

Select Case constructs .................................................................................93

Contents

xviii

Looping blocks of instructions

For-Next loops ........................................................................ ............97

Do While loops

..........100

Do Until loops

..........102

Chapter 4: Working with VBA Sub Procedures

105
About Procedures ........................................................................ .......................105

Declaring a Sub procedure

Scoping a procedure ........................................................................ ..........107

Public procedures

.....107 Private procedures ............................................................................107

Executing Sub Procedures

...........108 Executing a procedure with the Run Sub/UserForm command .......................109

Executing a procedure from the Macro dialog box

Executing a procedure with a Ctrl+shortcut key combination ........................110 Executing a procedure from the Ribbon.......................................................111 Executing a procedure from a customized shortcut menu .............................112

Executing a procedure from another procedure

Calling a procedure in a different module

Calling a procedure in a different workbook

Executing a procedure by clicking an object

Executing a procedure when an event occurs

Executing a procedure from the Immediate window

.....................................118

Passing Arguments to Procedures

119
Error-Handling Techniques ........................................................................ .........123 Trapping errors ........................................................................ .................123 Error-handling examples ........................................................................ ....124

A Realistic Example That Uses Sub Procedures

The goal

...........................127

Project requirements .................................................................................128

What you know

.................128 The approach ........................................................................ ....................129

Some preliminary recording .......................................................................129

Initial setup ........................................................................ .....................130

Code writing

.....................131 Writing the Sort procedure........................................................................ .132quotesdbs_dbs11.pdfusesText_17

[PDF] excel advanced book pdf khmer

[PDF] excel advanced course pdf myanmar

[PDF] excel binary to hex formula

[PDF] excel calendar with week numbers

[PDF] excel chart exercises pdf

[PDF] excel chart tutorial pdf

[PDF] excel data analysis examples

[PDF] excel data analysis for dummies

[PDF] excel exercise 1 student gpa calculator

[PDF] excel final exam answers

[PDF] excel for dummies 2013 pdf

[PDF] excel formula download

[PDF] excel formulas cheat sheet pdf

[PDF] excel formulas for data analysis

[PDF] excel formulas in hindi pdf