Course Description This course covers the VBA language in detail to include data types, functions and sub-procedures, variable scope, arrays, control structures, variable naming convention, debugging, error trapping, file I/O, etc. It also covers the basics of application object models, as well as object properties, methods and events. Participants learn how to develop their own UserForms with supporting ActiveX controls to provide forms driven analysis and project control.
Course Materials Each participant receives a set of course notes containing vital concepts not found in other books and a self-study CD-ROM containing the course slides, examples; and case studies. It includes the SpreadsheetWorld Toolbox Manager as well as several Excel Add-in Toolboxes.
Instructor Tom R. Mincer, Ph.D., Director, PEI and Professor of Mechanical Engineering, CSUN. Dr. Mincer initiated the use of EXCEL spreadsheets in the under-graduate and graduate curriculum at CSUN by integrating it into the courses on systems design, computational methods and computer-aided-engineering. For the past 10 years he has worked extensively in the areas of systems design, simulation and optimization using Excel Structured Spreadsheet environment. He has taught this workshop 90 times to over 1,500 engineers including on-sites at Goodyear Tire, NASA Goddard Space Flight Center, NASA Glenn Research Center, NASA Johnson Space Center, Jet Propulsion Lab, TRW, General Motors, Delco, Delphi, Naval Weapons Center, Naval Warfare Center, L3 Ocean Systems, Edwards Air Force Base Rocket Research Lab, Hamilton Sundstrand, L.A. Water District, Lockheed Martin, Pratt & Whitney and Honda R&D. Audience This course is valuable for management, data analysts, economists, financial analysts, programmers, engineers, scientists and power users of Microsoft Office applications. A basic familiarity with Windows and some programming experience is helpful. Participants should have a basic background in Excel with good keyboard skills.
Key Topics Start automating tasks right away Use events to automate and process tasks Develop custom menus and toolbars Initialize actions in a workbook when it is opened Automate GoalSeeker and Solver Automate Charting Develop VBA Function Libraries Work with named worksheet Ranges Link Range values between workbooks Link Range values to Website Tables Link PowerPoint Slides to Excel Range Parameters Link Word Documents to Excel Range Parameters Create User Forms and use ActiveX Controls Solve more complex work projects Outline Microsoft Office and Office Applications Overview of Office Applications Office Application Object Models The Excel Application Object Model Setting Excel Environment Preferences Excel Objects and Object Collections Menus and Toolbar Objects Overview of Visual Basic (VBA) What is VBA and what is it’s role What applications host VBA The Visual Basic Editor (VBE) Using the VBA Project Explorer Window The Object Properties Window VBA Projects Wookbooks, Userforms, Modules and Class Modules VBE Menus and Toolbars Using the VBA Object Browser Creating VBA User Defined Functions (UDFs) Creating VBA Sub Procedures & Recorded Macros Using Userforms and ActiveX Controls References to Object Libraries and Projects |Debugging VBA Code VBA Debugger Breakpoints & Launching the Debugger Navigation through code and call trees Watch variables Locals and Intermediate Windows Object Oriented Concepts What are Objects? Object Hierarchy & Object Containers Properties of Objects Methods associated with an object Objects with Events What action does an event trigger? Key Excel Objects and VBA Key Object Collections Workbooks, Worksheets, ChartObjects, Add-ins Using the Add Method to add elements to a collection Range Object: Properties and Methods Input-Output Range Fields VBA Language Syntax Variables and Constants Data Types Taking Advantage of IntelliSense Optional and Named Arguments Control Structures: Branching/Looping VBA's Built-In Functions Using the Object Browser to Find VBA Functions [if !mso][endif] Testing Functions in the Immediate Window Building VBA Function Procedures Excel/VBA User Defined Functions (UDF’s) VBA Function Procedures VBA Function Argument Lists Passing Range Objects into the VBA Function List Passing VBA data typed variables through the VBA function list Returning multiple values using VBA Array Functions Excel Output UDF Fields Inverting embedded functions using Goal Seeker Worksheet Iteration And Circular References Building Excel Function Add-Ins Understanding Events Events and Event Procedures Which Objects Have Events? User Form Events ActiveX Control Events Calculation Events Changing Function Input Range Values Using User Forms for Project Control Creating User Forms Adding ActiveX Controls to User Forms Setting Properties of ActiveX Controls Using User Form and ActiveX Control Events How to design Userforms as GUIs Designing Userforms with graphics Object Linking Linked Range Values Hyperlinks to Named Ranges, Worksheets, Files and Websites Links between Office Applications Error Handling in VBA Recognizing a Run-Time Error The Error-Handling Standard Exiting the Error Handler Inline Error Handling Combining Types of Error Handling Graphic User Interfaces (GUIs) The role of Drawing in Excel Userforms and Active X Controls Elements of design Project Control Linking and drawing forms Working with ChartObjects and Data Ranges Understanding the ChartWizard Method Working with the Chart Object Set Automating Charts with VBA PivotTables Reports & PivotGraphs Role of pivot tables for complex data sets Setting up for PivotTable Reports Setting up for PivotGraphs PivotTable Report Wizard Enroll in this course at Professional Education International
