This unique video course and eBook 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 along with 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:
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.
Visit www.wrox.com and www.wiley.com
VBA is a programming language created by Microsoft to automate operations in applications that support it, such as Excel. This lesson will introduce you to using VBA to automate recurring and repetitive tasks, running a macro based in another action, creating your own worksheet functions, simplifying the Workbook look and feel, controlling other Office applications from Excel, and warn you of some liabilities of VBA.
This is one of the handful of lessons in this course that is only a reading assignment with no associated video lecture.
Many of the lessons with this course make use of sample files you'll want to download and work with. All of those samples are available for free download at http://www.wrox.com/WileyCDA/WroxTitle/Excel-VBA-24-Hour-Trainer.productCd-047089069X,descCd-DOWNLOAD.html
In this lesson, you learn how to create a simple macro, what its code looks like, and a few options for how you can run the macro. This lesson leads you through the process of composing a macro to sort and format a range of data. But even before the first line of programming code is written, you’ll want to set up shop by giving yourself easy access to the VBA-related tools you’ll be using.
In this lesson, you navigate through the VBE windows to learn how to handle the kinds of maintenance tasks you will often encounter in the VBE.
In this lesson, you navigate through those VBE windows for the purpose of demonstrating how to handle the kinds of maintenance tasks you will often encounter in the VBE. In this lesson you practice creating a macro that includes a declared variable. Once you create a macro, without using the Macro Recorder,you will declare a variable for the String data type, and manipulate the string text with a few lines of practice code.
This lesson goes over some programming techniques to deal with the most common types of object collections you will encounter: workbooks, worksheets, cells, and ranges. In this lesson you practice with the useful Intellisense tool to help you become familiar with the properties and methods of VBA objects. VBA’s “IntelliSense” feature offers you VBA syntax assistance in the VBE. You learn how to use it for the purpose of seeing a list of your objects’ properties and methods in your VBE Code window.
This lesson shows you how to ask the user for information when the situation calls for it, and also how to simply let VBA do the decision-making on the fly, in circumstances when the user does not even need to be involved in the decision process. In the Try It, you'll learn to use a single-line If statement, an If…Then structure, a Select Case structure, a message box to ask the user a Yes or No question, and an InputBox to accept a text entry from the user.
A loop is a method of performing a task more than once. VBA provides several different looping structures, and at least one of them will be suited for any looping requirement you’ll encounter. In the Try It, you will work with the For...Next loop to add 12 worksheets to a workbook and name them by month.
In the Excel object model, an event is something that happens to an object, and is recognized by the computer so an appropriate action can be taken. This lesson teach you common Excel worksheet events and how they are triggered by actions relating to individual worksheets. In the Try It in this lesson you write a Worksheet_Change event that allows you to sum numbers as they are entered into the same cell.
In Lesson 11, you learned about worksheet-level events and how they are triggered by actions relating to individual worksheets. Workbooks themselves can also recognize and respond to a number of events that take place at the workbook level. This lesson describes how you can further customize your workbooks with VBA procedures for the most commonly used workbook events. In the Try It in this lesson you write a Workbook_BeforePrint workbook-level event that instructs Excel not to print a particular range of confidential data that resides on a particular worksheet.
This lesson shows you how to execute VBA code by clicking a button or other object that you can place onto your worksheet to make your macros easier to run. In the Try It in this lesson, you attach a macro to a Forms button that will toggle certain columns as being visible or hidden.
You may be familiar with using the Macro Recorder to do some automation of chart creation. This lesson takes you past the Macro Recorder’s capabilities to show how to create and manipulate embedded charts and chart sheets. In the Try It in this lesson you create an embedded pie chart, position it near the source data, and give each legend key a unique color.
PivotTables are an amazing tool that can summarize more than a million rows of data into concise, meaningful reports in a matter of seconds. You can format the reports in many ways, and include an interactive chart to complement the reports at no extra cost of time. This lesson starts with an overview of PivotTables and PivotCharts, followed by examples of how to create and manipulate them programmatically with VBA. In the Try It in this lesson, you write a macro that adds a PivotChart to accompany an existing PivotTable.
A function performs a calculation or evaluation, and returns a value. Functions used in your VBA expressions act the same way; they do what they are programmed to do, and return a result. With VBA, you can write (“define”) your own custom function that looks, acts, and feels like a built-in function, but with a lot more power and versatility.
This lesson will teach you Excel's tools and techniques for debugging your code, so that when things go wrong, you’ll be familiar with the resources that are at your disposal for finding and fixing errors. You'll test your debugging skills in the Try It by creating a macro that avoids a runtime error while using the Find method to locate a value on your worksheet.
A userform is a versatile approach to asking for and gathering many kinds of information from the users, all within a dedicated interface that’s convenient and easy to use. In this lesson you will create a userform, design it, and add controls. In the Try It in this lesson, you design a simple UserForm with a Label control, a TextBox control, a CheckBox control, and two CommandButton controls.
This lesson leads you through the design of various UserForms, with examples of how to program an assortment of controls that you’ll utilize most frequently including CommandButtons, Labels, TextBoxes, ListBoxes, and ComboBoxes. In the Try It in this lesson, you design a UserForm with several controls, including a ListBox that is populated dynamically with the ability to select multiple items.
This lesson builds on lessons 18 and 19 and takes an expanded look at how you can get more out of UserForms by tapping into their capacity for supporting some interesting and useful operations. In theTry It in this lesson you build a UserForm to browse the Internet.
In this lesson you will learn what classes and class modules are, what class modules can do for you, and you will see examples of class modules applied to UserForm and embedded worksheet controls. In the Try It in this lesson you create a class module to handle the Click event of some of the OptionButtons on a UserForm, purposely not involving all OptionButtons in the class.
Add-ins are a useful feature in Excel, considered by many Excel developers to be an indispensable tool when distributing their custom projects to a wider audience. Anyone can create an add-in — it’s the kind of thing that’s easy to do once you know how. This lesson discusses the concept of add-ins and how to incorporate them into your Excel projects. In the Try It in this lesson, you create and install an add-in that contains a User Defined Function to return the text of another cell’s comment.
In this lesson, you learn how to use VBA to share data between Excel and other external sources, including Access, the Internet, and text files. In the Try It in this lesson you create a Web query to import a display of the current day and time for several time zones.
ADO is an acronym for ActiveX Data Objects, which is the technology Microsoft recommends for accessing data in external databases. Excel’s spreadsheets, being tabular row and column objects, share common features with database tables, providing a natural environment for data to be transferred between Excel and relational databases. In this lesson you will learn to use ADO to connect to external databases, add, deleted, and edit records from the database, and to query databases to return a dataset into your worksheet.
This is one of the handful of lessons in this course that is only a reading assignment with no associated video lecture.
This lesson looks at two almost-forgotten features: 5.0 dialog sheets and XLM Get.Cell functions. You’ll also see examples of the SendKeys method, which is not so much outdated as it is misunderstood. In the Try It in this lesson, you compose a short Worksheet_Selection procedure that uses the SendKeys method to automatically expand the drop-down list of a cell containing Data Validation.
This section introduces how to control other Office applications from Excel, using the same VBA programming language with which you are now familiar, but using a different set of methods and statements with which those other Office applications are familiar. In the Try It in this lesson, you compose a macro that opens a Presentation file in PowerPoint.
In this lesson you'll learn to activate a Word document from Excel, create a new Word document from Excel, automate copying an Excel range to Word, print a Word document from Excel, and import a Word document to Excel. In the Try It in this lesson, you write a macro that asks for the name of a Word document and opens that Word document if it exists in a particular folder.
In this lesson you learn to open Outlook from Excel and check to see if Outlook is already open, create Outlook MailItem objects from Excel, transfer an Excel range to an email body, and email a single worksheet. In the Try It in this lesson, you write a macro in Excel that creates an e‑mail in Microsoft Outlook for multiple recipients and attaches the active Excel workbook to that e‑mail.
I this lesson you learn to add a record to an Access table from Excel, export an Access table to an Excel spreadsheet, and create a new table in Access. In the Try It in this lesson, you write a macro that adds a new field to an existing table in an Access database.
In this lesson you learn to create a new PowerPoint presentation from Excel, copy a worksheet range to a PowerPoint slide, copy chart sheets to PowerPoint slides, and to run a PowerPoint presentation from Excel. In the Try It in this lesson, you copy an embedded chart to an empty slide in an open PowerPoint presentation.
Founded in 1807, John Wiley & Sons, Inc. has been a valued source of information and understanding for more than 200 years, helping people around the world meet their needs and fulfill their aspirations. Wiley and its acquired companies have published the works of more than 450 Nobel laureates in all categories: Literature, Economics, Physiology or Medicine, Physics, Chemistry, and Peace.
Wiley is a global provider of content and content-enabled workflow solutions in areas of scientific, technical, medical, and scholarly research; professional development; and education. Our core businesses produce scientific, technical, medical, and scholarly journals, reference works, books, database services, and advertising; professional books, subscription products, certification and training services and online applications; and education content and services including integrated online teaching and learning resources for undergraduate and graduate students and lifelong learners. Wiley's global headquarters are located in Hoboken, New Jersey, with operations in the U.S., Europe, Asia, Canada, and Australia. The Company is listed on the New York Stock Exchange under the symbols JWa and JWb.