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 participants to bring their plotting and data analysis projects to the workshop for class discussion and class projects. Each participant receives a free copy of the 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.
Instructor:
Matthew E. Moran, PE is a Senior Engineer at NASA, and an
instructor in the graduate school at Walsh University.
He has 27 years experience
developing products and systems for aerospace,
electronics, military, and power generation applications.
He has created Excel/VBA thermal & fluid system models
for the Air Force, Office of Naval Research, Missile
Defense Agency, NASA, and other organizations. Matt is
Professional Engineer (Ohio), with a B.S. & graduate work
in Mechanical Engineering, and an MBA in Systems
Management. He has published 37 papers, and has 2
patents and 3 patents pending, in the areas of thermal
systems, cryogenics, MEMS/microsystems, power
generation systems, and electronics cooling.
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.
Course 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
Tuition:
Tuition for this three-day course is $1795 per person at one of our scheduled public courses. Onsite pricing is available. Please call us at 410-956-8805 or send an email to ati@aticourses.com.
Matthew E. Moran, PE is a Senior Engineer at NASA, and an
instructor in the graduate school at Walsh University.
He has 27 years experience
developing products and systems for aerospace,
electronics, military, and power generation applications.
He has created Excel/VBA thermal & fluid system models
for the Air Force, Office of Naval Research, Missile
Defense Agency, NASA, and other organizations. Matt is
Professional Engineer (Ohio), with a B.S. & graduate work
in Mechanical Engineering, and an MBA in Systems
Management. He has published 37 papers, and has 2
patents and 3 patents pending, in the areas of thermal
systems, cryogenics, MEMS/microsystems, power
generation systems, and electronics cooling.
