Microsoft Excel 2007/2010 Programming with VBA Course EXL10-VBA - 2 Days - Instructor-led - Hands on

Introduction This course is intended for advanced Microsoft Excel users that need to automate Excel spreadsheet tasks using Visual Basic for Applications (VBA). Learn how manual tasks in Excel can be programmed with VBA for greater speed, efficiency and accuracy.

At Course Completion Upon successful completion of this course, students will be learn the following skills:     

Introduced to VBA and topics including object oriented programming, variable declaration, objects and collections and arrays Learn how to write your own macros for programming loops, events, charts, pivot tables and pivot charts and user-defined functions .How tucustomize the look and feel of Excel with UserForms, Iinput Boxes, Message Boxes and embedded controls Exam advanced topics including class modules, add-ins, retrieve external data with ADO and SQL How to interact with other Office Applications from Excel, including Word, Access, PowerPoint and Outlook

Prerequisites To ensure your success, you should know the basics of Excel, including how to create, edit, format, and print worksheets that include charts and sorted and filtered data. Students are recommended to first take the following courses or have equivalent knowledge:    

Microsoft Microsoft Microsoft Microsoft

Office Office Office Office

Excel Excel Excel Excel

2010: 2010: 2010: 2010:

Level Level Level Level

1, 2, 3, 4,

Course Course Course Course

EXL10--1 EXL10-2 EXL10-3 EXL10-4

Course Outline Module 1: Introducing VBA  

What is VBA? A brief history of VBA

Contact ISInc for more information at 916.920.1700 or by visiting our website at http://www.isinc.com

 

What VBA can do for you Liabilities of VBA

Module 2: Getting Started with Macros  

Composing your first macro Running a macro

Module 3: Introducing the Visual Basic Editor      

What is the VBE? How to get into the VBE Understanding the VBE Understanding Modules Using the Object Browser Exiting the VBE

Module 4: Working in the VBE   

Toolbars in the VBE Macros and modules Locking and protecting the VBE

Module 5: Object-Oriented Programming – An Overview  

What object-oriented programming means The object module

Module 6: Variables, Data Types and Constants       

What is a variable? Assigning values to variables Why you need variables Data types Forcing variable declaration Understanding a variable’s scope Constants

Module 7: Understanding Objects and Collections    

Workbooks Worksheets Cells and ranges SpecialCells

Contact ISInc for more information at 916.920.1700 or by visiting our website at http://www.isinc.com

Module 8: Making Decisions with VBA   

Understanding logical operators Choosing between this or that Getting users to make decisions

Module 9: Repeating Actions with Loops   

What is a Loop? Types of loops Nesting loops

Module 10: Working with Arrays     

What is an array? The option base statement Boundaries in arrays Declaring arrays with fixed elements Declaring dynamic arrays with ReDim and Preserve

Module 11: Automating Procedures with Worksheet Events   

What is an “Event”? Worksheet events—an overview Examples of common worksheet events

Module 12: Automating Procedures with Workbook Events  

Workbook events—an overview Examples of common workbook events

Module 13: Using Embedding Controls 

Working for forms controls and ActiveX controls

Module 14: Programming Charts      

Adding a chart to a chart sheet Adding an embedded chart to w worksheet Moving a chart Looping through all embedded charts Deleting charts Renaming a chart

Module 15: Programming PivotTables and PivotCharts  

Creating a PivotTable report Why it’s called a PivotTable

Contact ISInc for more information at 916.920.1700 or by visiting our website at http://www.isinc.com

    

Creating a PivotChart Understanding PivotCaches Manipulating PivotFields in VBA Manipulating PivotItems with VBA Creating a PivotTables collection

Module 16: User Defined Functions  

What is a user defined function? Volatile functions

Module 17: Debugging Your Code    

What is debugging? What causes errors? Weapons of mass debugging Trapping errors

Module 18: Creating UserForms      

What is a UserForm? Creating a UserForm Designing a UserForm Showing a UserForm Where does the UserForm’s code go? Closing a UserForm

Module 19: UserForm Controls and Their Functions 

Understanding the frequently used UserForm controls

Module 20: Advanced UserForms        

The UserForm toolbar Modal versus modeless Disabling the UserForm’s close button Maximizing your UserForms’s size Selecting and displaying photographs on a UserForm Unloading a UserForm automatically Pre-sorting the ListBox and ComboBox items Populating ListBoxes and ComboBoxes with unique items

Module 21: Class Modules     

What is a class? What is a class module? Creating your own objects An important benefit of class modules Creating collections

Contact ISInc for more information at 916.920.1700 or by visiting our website at http://www.isinc.com



Class Modules for embedded objects

Module 22: Add-ins        

What is an Excel Add-In? Creating an Add-In Converting a file to an Add-In Installing an Add-In Creating a user interface for your Add-In Changing the Add-In’s code Closing Add-Ins Removing an Add-In from the Add-Iins list

Module 23: Managing External Data   

Creating QueryTables from web queries Creating a QueryTable for Access Using text files to store external data

Module 24: Data Access with ActiveX Data Objects  

Introducing ADO An introduction to Structured Query Language (SQL)

Module 25: Not Gone, Not Forgotten   

Using dialog sheets Using XML Get.Cell functions Using the SendKeys method

Module 26: Overview of Office Automation from Excel  

Why automate another application? Understanding office automation

Module 27: Working with Word from Excel     

Activating a Word document Creating a new Word document Coping an Excel range to a Word document Printing a Word document from Excel Importing a Word document to Excel

Module 28: Working with Outlook from Excel   

Opening Outlook Composing an E-mail in Outlook from Excel E-mailing a single worksheet

Contact ISInc for more information at 916.920.1700 or by visiting our website at http://www.isinc.com

Module 29: Working with Access from Excel   

Adding a record to an Access table Exporting an Access table to an Excel spreadsheet Creating a new table in Access

Module 30: Working with PowerPoint with Excel    

Creating a new PowerPoint presentation Copying a worksheet range to a PowerPoint slide Copying chart sheets to PowerPoint slides Running a PowerPoint presentation from Excel

Contact ISInc for more information at 916.920.1700 or by visiting our website at http://www.isinc.com