This workshop is designed to have an immediate impact on the way that Excel Spreadsheets are used in the engineering process. The underlying key to this change is for the user to gain experience and knowledge on the use of Visual Basic for Applications (VBA) in Excel. Extensive use of VBA is the key to unlocking all the features of Excel, and moving away from attempting to program on worksheets. Those who attend this intensive hands-on workshop experience a dramatic change in their use of Excel and VBA. Benefits include increased productivity, automation of tedious tasks, increased use of XLToolboxes and Add-ins, development of re-usable functions, forms and templates, improved documentation and configuration management, and improved team interaction and parameter sharing.
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.
Overview of ExcelTM Environment: The central role of VBA in Excel & Office, The Excel VBA application object library, Excel menus and toolbar objects, Setting application level preferences, Excel internal function library, Inverting functions- Goal Seeker, Iteration and circular references, Linked worksheets and workbooks, Excel Add-Ins - Solver & Analysis Toolpaks, Forms for simple worksheet user interface, Using the VBA Recorder, XLManager and XLToolboxes
Structured Spreadsheets & Documentation: Structured worksheet layout and design, nput-output field structures, Input design parameter field structure, Defined names for input arrays, Output field structures, VBA user defined functions (UDF), Time varying output fields
Visual Basic for Applications (VBA): The computational side of VBA, Visual Basic Editor, VBA projects and modules, VBA user defined function procedures, VBA sub procedures, Declaration statements, Debugging VBA code, Data types and naming conventions, Function design for worksheet topology, Naming Excel objects & VBA variables ,Vectors, matrices and arrays, VBA logic and control structures
Building VBA Function Libraries: Exporting VBA modules to BAS library, Building Excel Add-Ins, Using Add-Ins in Excel, Referencing Add-Ins in VBA, Documentation support .
Object Oriented Programming in Excel: VBA Object Browser, Object libraries & Excel's object library, Excel object properties & methods, Object collections, Properties that return objects, Range object properties and methods, Object events & VBA event code, Drawing objects with VBA, Animation using VBA
Userforms & ActiveX for Project Control: User interface: an overview, Userform driven design, Creating user interfaces, How to design Userforms as GUIs , Designing Userforms with graphics
Solving System Rules & Optimization: Formulating system rules, Objective functions, Constraint functions, Design & decision variables, Structured optimization sheets, Using solver for solving rules, Configuration trade-off studies
Charting and Chart Automation: Charting VBA object structure, XLQwikplot & XLProplot, Using VBA for chart automation,
Data Storage, Retrieval & Analysis: Importing data into Excel worksheets, Importing data into VBA, Enumeration and lookup functions, Filtering and smoothing data, Interpolation and extrapolation, Linear regression, Linear surface regression, non-linear regression.
Numerical Methods for System Modeling: Application of XLNumerical Toolbox ,Roots of nonlinear functions, Linear & nonlinear algebraic systems, Numerical integration & differentiation, System sensitivity maps
Dynamic System Simulation: Application of XLSimulation Toolbox, State variable system modeling, State forecasting methods
Interfacing VBA, FORTRAN & C: Dynamic Link Libraries (DLLs), Computationally intensive analysis, Converting existing code to DLLs, Interfacing VBA and DLLs, Creating FORTRAN DLLs
Automation of Applications Using VBA: Key automation properties & methods, Controlling MATLAB, Controlling Word, PowerPoint, etc.
Developing On-Line Documentation: HTML help system, Elements of documentation, Linking help to VBA functions
Free Membership: Attendees receive 5-year free SpreadsheetWorld Membership which includes all SpreadsheetWorld developed engineering XLToolboxes for 5 years including xlQwikplot, xlNumerical Methods, xlSimulation, xlThermal Fluids, xlHeat Transfer and many more.
Tuition:
Tuition for this five-day course is $2495 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.
