Mastering VBA for Microsoft Office 2016

Mastering VBA for Microsoft Office 2016 PDF

Author: Richard Mansfield

Publisher: Sybex


Publish Date: February 23, 2016

ISBN-10: 1119225388

Pages: 984

File Type: Epub, Mobi, PDF, Azw3

Language: English

read download


Book Preface


What Can I Do with VBA?

You can use VBA to automate almost any action that you can perform interactively (manually) with an Office 2016 application. For example, in Word, VBA can create a document, add text to it, format it, edit it, and save it. All without human intervention.

Here are some more examples. In Excel, you could automatically integrate data from multiple workbooks into a single workbook. PowerPoint’s VBA can create a custom presentation, including the latest data drawn from a variety of sources with no human intervention. And in Access you can create new tables, populate them with data, and send the table up to the cloud.

VBA is faster, more accurate, more reliable, and far less expensive than any human worker. You can even specify conditions for making a decision and then let VBA make that decision for you in the future. By adding decision-making structures and loops (repetitions) to your code, you can go far beyond the range of actions that any human user can perform. What’s more, VBA can finish most jobs in less than a second.

Beyond automating actions you would otherwise perform manually, VBA also gives you the tools to create user interfaces for your code—message boxes, input boxes, and user forms (windows containing graphical objects that you can use to create forms and custom dialog boxes to display to the user).

Using VBA, you can create custom applications that run within the host application, too. For example, you could build within PowerPoint a custom application that automatically creates presentations for you.

And VBA can communicate between applications. Let one application assist another. Word can’t do much in the way of mathematical calculations on sets of data; that’s Excel’s specialty. So, you could make Word start Excel running, perform some calculations, and then put the results into a Word document. Similarly, you could send graphs from Excel to PowerPoint or Outlook. You get the picture.

You only have to learn VBA once. Because VBA provides a standard set of tools that differ only in the specializations of the host applications, once you’ve learned to use VBA in one application, you’ll be able to apply that knowledge quickly to using VBA in another application. For example, you might start by learning VBA in order to manipulate Excel and then move on to using your VBA skills with Outlook. You’ll need to learn the components particular to Outlook, because they’re different from Excel’s features, but you’ll be up to speed rapidly. It’s like shopping. Once you understand the basics, going to a hardware store differs from going to a bookstore only in the particulars.

As with any programming language, getting started with VBA involves a learning curve—but you’ll be surprised how many tools VBA provides to help you quickly learn the fundamentals.

The VBA Editor is among the best programming environments available. It includes help features that list programming options while you’re typing, that instantly point out problems (and suggest solutions), that prevent you from making some kinds of mistakes, that offer context-sensitive help (with example programming), and that even automatically complete your lines (sentences) of programming code.


What’s in This Book?

This book teaches you how to use VBA to automate your work in Office 2016 applications. For its general examples, the book focuses on Word, Excel, Outlook, and PowerPoint, because those are the Microsoft Office applications that you’re most likely to have, and because they have less eccentric programming tools and strategies than Access. The last part of the book continues the discussion of how to program these four applications, but also increases coverage of Access.

Part 1 of the book, “Recording Macros and Getting Started with VBA,” contains the following chapters:

  • Chapter 1 shows you how to record a macro using the Macro Recorder in Word and Excel. You also learn several ways to run macros and how to delete them.
  • Chapter 2 introduces you to the powerful VBA Editor, the application in which you create VBA code (either by editing recorded code or by writing code from scratch) and user forms. The second half of this chapter discusses how you can customize the Visual Basic Editor so that you can work in it more efficiently.
  • Chapter 3 shows you how to edit recorded macros, using the macros you recorded in Chapter 1. You learn how to step through (execute in slow motion) and test a macro in the Visual Basic Editor.
  • Chapter 4 teaches you how to start writing code from scratch in the Visual Basic Editor. You create a procedure (another word for macro) for Word, one for Excel, and a third for PowerPoint.

Part 2, “Learning How to Work with VBA,” contains the following chapters:

  • Chapter 5 explains the essentials of VBA syntax, giving you a brief overview of the concepts you need to know. You also practice creating statements in the Visual Basic Editor.
  • Chapter 6 shows you how to work with variables and constants, which are used to store information for your procedures to work on.
  • Chapter 7 discusses how to use arrays. Arrays are like super-variables that can store multiple pieces of information at the same time.
  • Chapter 8 teaches you how to find the objects you need to create your macros. You learn how to correctly write code involving objects by employing the Macro Recorder, the Object Browser, and the Help system. And you see how to use object variables to represent objects. Finally, you explore the uses of object models.

Part 3, “Making Decisions and Using Loops and Functions,” consists of the following chapters:

  • Chapter 9 describes how to use VBA’s built-in functions—everything from string-conversion functions through mathematical and date functions to file-management functions.
  • Chapter 10 shows you how to create functions of your own to supplement the built-in libraries of functions. You create functions that work in any VBA-enabled application, together with application-specific functions for Word, Excel, and PowerPoint.
  • Chapter 11 shows you how to use conditional statements (such as If statements) to make decisions in your code. Conditional statements are key to making your code flexible and intelligent.
  • Chapter 12 covers how you can use loops to repeat actions in your procedures: fixed-iteration loops for fixed numbers of repetitions, and indefinite loops that repeat until they satisfy a condition you specify. You also learn how to avoid creating infinite loops, which can cause your code to run either forever or until the computer crashes.

Part 4, “Using Message Boxes, Input Boxes, and Dialog Boxes,” has the following chapters:

  • Chapter 13 shows you how to use message boxes to communicate with the users of your procedures and let users make simple decisions about how the procedures run. You also explore input boxes, which are dialog boxes that give the users a way to supply information the macros need.
  • Chapter 14 discusses how to employ VBA’s user forms to create custom dialog boxes that enable the users to supply information, make choices, and otherwise interact with your macros.
  • Chapter 15 discusses how to build more-complex dialog boxes. These include dynamic dialog boxes that update themselves when the user clicks a button, dialog boxes with hidden zones that the user can reveal to access infrequently used options, dialog boxes with multiple pages of information, and dialog boxes with controls that respond to actions the user takes.

Part 5, “Creating Effective Code,” contains the following chapters:

  • Chapter 16 illustrates the benefits of reusable modular code and shows you how to create it.
  • Chapter 17 explains the principles of debugging VBA code, examines the different kinds of errors that occur, and discusses how to deal with them.
  • Chapter 18 explores how to build well-behaved code that’s stable enough to withstand being run under the wrong circumstances and civilized enough to leave the user in the best possible state to continue their work after it finishes running.
  • Chapter 19 discusses the security mechanisms that Windows and VBA provide for safeguarding VBA code and ensuring that you or your users do not run malevolent code (viruses, trojans, worms, and so on). The chapter discusses digital certificates and digital signatures, how to choose an appropriate security setting for the application you’re using, and how to manage passwords.

Part 6, “Programming the Office Applications,” consists of these 12 chapters:

  • Chapter 20 explains the Word object model and shows you how to work with key objects in Word, including the Document object, the Selection object, and Range objects. You also learn how to set options in Word and manage cloud storage via such systems as Dropbox or Microsoft’s OneDrive.
  • Chapter 21 discusses how to work with widely used objects in Word, including the objects for Find and Replace; headers, footers, and page numbers; sections, page setup, windows, and views; and tables.
  • Chapter 22 introduces you to the Excel object model and shows you how to work with key objects in Excel, including the Workbook object, the Worksheet object, the ActiveCell object, and Range objects. You also learn how to set options in Excel.
  • Chapter 23 shows you how to work with charts, windows, and the Find and Replace feature in Excel via VBA.
  • Chapter 24 gets you started working with the PowerPoint object model and the key objects that it contains. You work with Presentation objects, Window objects, Slide objects, and Master objects.
  • Chapter 25 teaches you how to go further with VBA in PowerPoint by working with shapes, headers and footers, and the VBA objects that enable you to set up and run a slide show automatically.
  • Chapter 26 introduces you to Outlook’s object model and the key objects that it contains. You meet Outlook’s creatable objects and main interface items; learn general methods for working with Outlook objects; and work with messages, calendar items, tasks and task requests, and searches.
  • Chapter 27 shows you how to work with events in Outlook. There are two types of events, application-level events and item-level events, which you can program to respond to both Outlook actions (such as new mail arriving) and user actions (such as creating a new contact).
  • Chapter 28 familiarizes you with the Access object model and demonstrates how to perform key tasks with some of its main objects.
  • Chapter 29 shows you how to manipulate the data in an Access database via VBA.
  • Chapter 30 shows you how to communicate between applications via VBA. You learn which tools are available, how to use Automation, how to work with the Shell function, and how to use data objects, DDE, and SendKeys.
  • Chapter 31 explores the various ways you can customize the Ribbon programmatically. It’s not possible to customize it by VBA code alone. Instead, you must write XML code to modify what the user sees on the Ribbon and write callbacks (event-handler procedures in VBA) to respond when the user clicks one of the buttons or other controls you’ve added to the Ribbon. You see how to modify tabs, groups, and individual controls—in Word, PowerPoint, Excel, and, using different techniques, in Access.

Download Ebook Read Now File Type Upload Date
Download Now here Read Now


Epub, Mobi, PDF, Azw3 September 25, 2016

Do you like this book? Please share with your friends, let's read it !! :)

How to Read and Open File Type for PC ?