Excel: Programming with VBA - two days

Learning Outcomes:

• Work with objects and assign an object to a variable.

• Identify and control Range objects.

• Place controls on worksheets that run VBA procedures.

• Create code that executes in a specific worksheet when you press Enter.

• Place controls on custom dialog boxes that run VBA procedures and link controls on a dialog sheet to a worksheet.

This two-day class will enable students to learn about various programming techniques and tips to create their own VBA code.

Getting Started
Working with Procedures and Functions
Understanding Objects
Using Expressions, Variables, and Intrinsic Functions
Controlling Program Execution
Working with Forms and Controls
Working with the PivotTable Object
Debugging Code
Handling Errors

 

Excel: Programming with VBA - two days

Students will learn the following lesson objectives: Start out by recording, running and editing a Macro. Use Visual Basic Help. Create a Standard Module; understand procedures and create a sub procedure, call procedures using the immediate window, work with the Code Editor. Understand objects, navigate the Excel object hierarchy, work with collections, use the object browser, work with properties, use the With statement, work with Methods and create an event procedure. Understand expressions and statements, declare variables, understand data types, work with variable scope, use intrinsic functions, constants, and input boxes. Understand & learn guidelines for Control-of-Flow structures, work with Boolean expressions, Use the If…End if decision structures, use the select case...end, the do…loop, the for…to…next and the for each…next structures. Use the Toolbox, work with Userform properties, events, and methods, set control properties in the properties window. Work with Label Control, Text Box Control, Command Button Control, Combo Box Control, Frame Control and Option Button Controls. Set the tab order and populate a control, add code to controls and launch a form in Code. Work with Pivot Tables. Understand errors and error handling, use debugging tools, set breakpoints, use Break mode during Run mode. Work with VBA’s Error Trapping options, write an error-handling routine and work with Inline Error Handling.

Class targeted to:Those proficient in Microsoft Excel with a knowledge of PivotTables helpful but not required.

Prerequisites: Excel: Power User/Visual Basic Applications or a programming background and Advanced Excel knowledge.