Focus This 4 day course is available to be taught on-site at your facility. Call PEI today to schedule this course. Course Description One of the most frequent uses of Excel is to work with data and plotting the results. This hands-on workshop is designed to dramatically increase your ability to use Excel and VBA to more effectively support your data plotting and analysis process. Frequently, this process can become limited when working with worksheets, worksheet functions and worksheet wizards. By using VBA, you can get more complete control over the plotting and data analysis features of Excel. This requires that you become familiar with the Excel Charting Object Model and the associated properties, methods and events. The methods taught in this workshop are particularly helpful for those working with large data sets, repetitive data analysis and plotting, plotting results of computational intensive calculations, plotting filtered or smoothed data, and specialized plotting styles which are not part of the standard style set available in the Chart Wizard. In addition, the participants at this workshop will be among the first to learn the features of XLProPlot, the new SpreadsheetWorld toolbox designed especially for engineering use. We encourage [articipants to bring their plotting and data analysis projects to the workshop for class discussion and class projects. Course Materials Each participant receives a free copy of our premier data plotting application, XLProPlot and a set of course materials. It is accompanied by a CD-ROM containing an electronic version of the course notes; both generic and discipline-specific course exercises; in-depth engineering case studies; the Professional Version of the SpreadsheetWorld Toolbox Manager, and several SpreadsheetWorld Toolboxes including Thermo-Fluid Properties, Heat Transfer, Numerical Analysis, Dynamic Systems Simulation, Geometric Design, Data Analysis, and Matrix Methods. Instructor Tom R. Mincer, Ph.D., Founder and President of SpreadsheetWorld, Inc. (www.spreadsheetworld.com) and Professor of Mechanical Engineering, California State University, Northridge (CSUN). Dr. Mincer is widely recognized as an early pioneer in the extensive use of Excel, VBA and FORTRAN DLLs in engineering. In 1985, he initiated extensive use of Excel into the curriculum at CSUN by integrating it into the courses on systems design, computational methods and computer-aided-engineering. For the past 15 years he has worked extensively in the areas of systems design, simulation and optimization using the Excel Structured Spreadsheet environment. In 1995, he extended his efforts to industry by launching his popular hands-on workshop on Spreadsheet Aided Engineering, which has now been taught 130 times to engineers from over 150 companies worldwide. Alumni from these workshops exceed 1,500 engineers. This workshop is offered on a regular basis at over 30 companies. Dr. Mincer founded SpreadsheetWorld to extend the training services to include development of new and commercialization of existing Excel/VBA Toolboxes, and services to enhance the implementation of the best practices and methods taught in his workshops. Audience Engineers from all disciplines find this course immediately useful, practical and eye-opening. Participants should have a computer background including basic keyboard and Excel skills. Key Topics Data Plotting: Get total control over Excel Charting Objects Implement VBA to Automate Data Plotting Master VBA Plotting Objects, Properties and Methods Use VBA to Rapidly Visualize Analysis Results Develop Highly Customized Chart Templates and Overlays Use SpreadsheetWorld XLTechPlot™ Plotting Add-in Data Analysis: Setup Worksheet Layout and Design for Data Analysis Use VBA to Work with Large Data Arrays and Databases Effectively Query External Databases Input Parameter Control for External Data Queries Integrate Data Analysis Process into the Optimization Loop Advanced File I/O Techniques Using VBA Use VBA to Automate the Entire Analysis Process Do Linear/Nonlinear Multi-Variable Regression Analysis Do Data Smoothing and Filtering Wild-Point Editing with VBA Use the SpreadsheetWorld Data Analysis Add-in
Outline Excel Overview and Review Equations and relative references Keyboard shortcuts Structured spreadsheets Intro to Visual Basic for Applications (VBA) Subs and Functions VBA Editor Overview Add-ins VBA Programming Language syntax and constructs Intrinsic functions Passing 1-D and 2-D arrays between Excel and VBA Debugging Object-Based Programming Referencing and manipulating Excel objects Working with the Cells property and Range objects Data Storage and Retrieval Overview of data storage locations and formats Creating links from worksheet data ranges to external data sources Manually importing/exporting text data to/from worksheets Windows folder and file management using VBA Reading and writing sequential and random access files with VBA Using VBA and ActiveX Data Objects (ADO) to query and update external data servers Data Analysis Manual filtering, sorting, and lookup of lists and tables Filtering, sorting, and searching on a worksheet using VBA Overview of the Data Analysis tools and Analysis Toolpak in Excel Numerical analysis techniques - Interpolation/Extrapolation - Linear Regression - Using Excel’s built-in Solver for nonlinear regression - Integration/Differentiation Modal filtering, smoothing, and wildpoint editing Integrating external applications into the Excel/VBA-controlled analysis process Using Fortran Dynamic Link Libraries (DLLs) to enhance security and performance Data Visualization Manually creating, formatting, and modifying charts in Excel In-depth overview of selected chart types (scatter, surface, etc.) Manual charting tips and tricks such as: - Plotting data on multiple dependent and independent axes - Including multiple chart types on the same chart - Plotting a large data series as a set of smaller data series - Locating multiple charts on a single chart sheet - Controlling axes locations in a scatter plot - Automating the creation and control of charts using VBA - Extending Excel’s plotting capabilities using XLPoPlot™ Enroll in this course at Professional Education International
