Excel VBA 24-Hour Trainer
4.2 (35 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
644 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel VBA 24-Hour Trainer to your Wishlist.

Add to Wishlist

Excel VBA 24-Hour Trainer

Increase your productivity and save time and effort with Excel VBA
4.2 (35 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
644 students enrolled
Last updated 2/2014
English
Current price: $10 Original price: $45 Discount: 78% off
1 day left at this price!
30-Day Money-Back Guarantee
Includes:
  • 6 hours on-demand video
  • 5 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Introduces you to programming with Visual Basic for Applications (VBA), macro composition, and the programming environment
  • Explains events programming, embedded controls, user forms, message boxes, input boxes, looping, and more
  • Shows you how to control other Office applications from Excel, such as Word, Outlook, Access, and PowerPoint
  • Includes enhanced coverage of each lesson on the DVD, which also offers detailed examples
  • Provides ideas for applying VBA to everyday tasks in a way that’s both practical and fun
View Curriculum
Requirements
  • A PC running Windows, Mac OS, or Linux with at least 1 GB RAM
Description

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:

  • Introduces you to VBA and discusses topics including object oriented programming, variable declaration, objects and collections, and arrays
  • Teaches you how to write your own macros for programming loops, events, charts, pivot tables and pivot charts, and user-defined functions
  • Shows you how to customize the look and feel of Excel with User Forms, Input Boxes, Message Boxes, and embedded controls
  • Examines advanced topics including class modules, add-ins, and retrieving external data with ADO and SQL
  • Demonstrates how to interact with other Office Applications from Excel, including Word, Access®, PowerPoint®, and Outlook®

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

Who is the target audience?
  • Beginner to Intermediate
Students Who Viewed This Course Also Viewed
Curriculum For This Course
31 Lectures
13:45:31
+
Understanding the BASICs
4 Lectures 12:21

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

Preview 8 pages

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.

Getting Started with Macros
07:54

Introducing the Visual Basic Editor
8 pages

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.

Working with VBE
04:27
+
Working with VBE
4 Lectures 25:17
Object-oriented Programming — An Overview
8 pages

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.

Preview 09:11

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.

Understanding Objects and Collections
05:43

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.

Making Decisions with VBA
10:23
+
The Macro Recorder: Writing Your Own Code
9 Lectures 02:01:38

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.

Preview 06:58

Working with Arrays
11:41

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.

Automating Procedures with Worksheet Events
13:22

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.

Automating Procedures with Workbook Events
10:06

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.

Using Embedded Controls
18:31

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.

Programming Charts
21:27

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.

Programming PivotTables and PivotCharts
11:55

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.

User Defined Functions
09:25

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.

Debugging Your Code
18:13
+
Advanced Programming Techniques
8 Lectures 02:02:56

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.

Preview 18:56

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.

Userform Controls and Their Functions
32:43

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.

Advanced Userforms
15:55

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.

Class Modules
25:08

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.

Add-ins
11:40

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.

Managing External Data
10:12

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.

Data Access with ActiveX Data Objects
8 pages

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.

Not Gone, Not Forgotten
08:22
+
Interacting with Other Office Applications
6 Lectures 01:19:19

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.

Overview of Office Automation From Excel
10:31

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.

Working with Word from Excel
16:56

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.

Working with Outlook from Excel
15:59

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.

Working with Access from Excel
25:42

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.

Working with PowerPoint from Excel
10:11

Excel VBA 24-Hour Trainer eBook
432 pages
About the Instructor
John Wiley & Sons, Inc.
4.2 Average rating
1,083 Reviews
28,810 Students
35 Courses
Video Training

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.