Spreadsheet Aided Engineering - 576

Please contact Janet Mincer with inquiries regarding enrollment fees:


Spreadsheets have become a valuable and powerful engineering tool. They now provide capabilities to create graphical user interfaces (GUI’s), command other languages such as FORTRAN, C and Visual Basic and dynamically link to an increasing array of other computer-aided engineering tools. The GUI capability of Excel allows engineers to create windows like applications (icon driven) but with minimum effort. Excel allows engineers to redefine "back of the envelope" calculation and push it to new limits. This is accomplished by interface capabilities with other languages, third party Excel Add-ins, VBA and a design optimization tool called Solver. Excel provides an environment for command, control and communication of engineering projects of all disciplines to more effectively create and build applications devoted to their specific needs.

Within system design, it is important to have the design team working in a linked, interactive mode. Real-time trade-offs and optimization can be continuously performed to make sure that all the system constraints are maintained. In this way, design review is a continuous process rather that a critical future event. This concurrent design process is accomplished by having continuous interaction between team members from various disciplines. A key feature of this process is that each discipline has ownership of the spreadsheets, which model their part of the system. This process puts a heavy requirement on the design team to be able to efficiently model the system performance and constraints. Excel spreadsheet, operating in a networking environment, has all the elements needed for effective command, control, and communication needed for the system design process. In addition, its extensive computational capability provides the tools needed for effective system modeling.

Course Description
The modular nature of the Excel Spreadsheet working environment is used to establish a platform for performing engineering system design, analysis, and optimization. General engineering system applicability is emphasized by making use of the basic elements of numerical analysis. This approach leads to methodologies, which are applicable to any engineering system. Participants are led through a structured approach to systems design, with the basic principle of object-oriented programming introduced along the way. The elements of Visual Basic Programming are introduced while learning to extend the capability of working on the worksheet. This involves learning how to recognize objects, object properties and methods when working on the worksheet. Graphical User Interfaces are first introduced to modularize and control inputs and input range limits using Active X controls. After maximizing the capability of the worksheet, the course moves on to develop Visual Basic Application procedures. Techniques are introduced for spreadsheet architecture and communication for large-scale systems engineering. Please visit www.spreadsheetworld.org for a complete outline.

Course Materials
Each participant receives a set of course notes containing vital systems analysis, VBA, Excel and FORTRAN DLL concepts not found in other books. It is accompanied by a CD-ROM containing an electronic version of the course notes; the course examples; engineering case studies; and many SpreadsheetWorld Engineering Add-Ins including numerical analysis, thermal materials properties, heat transfer, geometric modeling, data analysis and gas dynamics toolboxes. Participants also receive 30-day demo copies of other selected XLToolboxes.

Engineers from all disciplines find these courses immediately useful, practical and eye-opening. Participants should have a computer background including basic keyboard and Excel skills. Public course participants should bring their laptop . Contact us for computer requirements.

Key Topics
The participants in this workshop will learn how to use Excel and VBA to:

  • Define the mission
  • Define system physical object structure
  • Define process flows
  • Create engineering information tables
  • Develop re-useable system functional models
  • Monitor the impact of key design and process variables
  • Monitor performance and constraint functions
  • Deal with implicit relationships using Goal Seeker
  • Develop and use VBA Add-Ins for rapid modeling
  • Use XLToolboxes to support modeling
  • Solve systems of equality and inequality rules
  • Dynamic system simulation
  • Do system optimization using Solver
  • Monitor System Requirements using Solver
  • Setup system sensitivity maps about a design point
  • Do dynamic system simulation the optimization loop
  • How to setup Configuration Trade-Study Matrix
  • Use Userforms for man-in-the-loop design and analysis
  • Develop graphic user interfaces for systems design
  • Setup system modeling for integrated design teams
  • How to use Fortran and C modules from Excel

Overview of ExcelTM Environment

  • The MS Office Excel Application Object
  • Setting Environment Preferences
  • Menus and Toolbar Objects
  • Excel Internal Function Library
  • Excel Add-Ins - Solver
  • The Analysis Toolpak
  • Iteration and Circular References
  • Linked Worksheets And Workbooks
  • Drawing and Plotting in Excel
  • Forms for Simple Worksheet

User Interface Structured Spreadsheets & Documentation

  • Decomposition of a System in Excel
  • Structured Worksheet Layout and Design
  • User Defined Functions (UDF)
  • Performance and Constraint Modeling
  • Input Design Parameter Field Structure
  • VBA User Defined Functions
  • Output Field Structure
  • Defined Name Ranges
  • Defined Names For Input Arrays
  • Design Parameter Name Conventions
  • Output Range Analysis
  • Time Varying Output Fields
  • Inverting Implicit Performance Functions
  • Goal Seeker
  • Dual Engineering Unit 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

Control Structures

  • Functional Modeling and Engineering UDF Libraries
  • Functional Modeling of Systems
  • Function Modeling Trees
  • Modeling Trees Using VBA UDFs
  • Building Excel Engineering Add-Ins
  • Using Add-Ins in Excel
  • Referencing Add-Ins in VBA
  • XLNumerical Analysis Toolbox
  • Passing Nonlinear Function Strings
  • Blackbox UDFs
  • Documentation support for UDFs

Object Oriented Programming in Excel

  • Properties of Objects
  • Methods of Changing Object Properties
  • How to Use VBA Sub Procedures
  • How to Edit and Streamline VBA Code
  • Range Objects
  • Frequently Used VBA Commands
  • How to Build Object Containers
  • Drawing Objects with VBA
  • Animation using VBA

Solving Rules & System Optimization

  • Formulating a Design Optimization
  • Performance Objectives and Constraints
  • Using Solver for Design Optimization
  • Objective Functions
  • Constraint Functions
  • Design & Decision Variables
  • Structured Optimization Sheets
  • Configuration Trade-Off Studies
  • Evolver™ for Global Minimum of General Functions
  • Configuration Optimum Trade Matrix

Numerical Methods for System Modeling

  • Overview of XLNumerical Toolbox
  • Real Roots of Nonlinear Functions
  • Linear Algebraic Systems
  • Nonlinear Algebraic Systems
  • Interpolation & Extrapolation
  • Linear and Nonlinear Regression
  • Numerical Integration
  • Numerical Differentiation
  • System Sensitivity Maps
  • State Space Forecasting
  • Finite Difference Methods

Data & Data Analysis & Graphing

  • 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

Interfacing VBA & Dynamic Link Libraries – DLLs

  • Computationally Intensive Analysis
  • VBA Versus Compiled Runtime Libraries
  • Converting Existing Code to DLL’s
  • Interfacing VBA and DLL’s
  • Creating FORTRAN DLL’s
  • Speed Benchmark
  • Creating C++ DLLs

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
Short Course Seminars

Spreadsheet Aided Engineering (576)

Advanced Spreadsheet Aided Engineering (676)

Refresher to Microsoft Excel (701)

Intro to Computational VBA for Excel (702)

Intro to VBA Object Sructure for Excel (703)

Visual Basic for Applications (704)

Advanced Excel/VBA Techniques (705)

Introduction to Programming Microsoft Windows with VBA (706)

Online Documentation Techniques (707)

Database-Aided Engineering with Microsoft Access and Microsoft Excel (708)

World Wide Web-Aided Engineering with Excel/Access/Front Page (709)

FORTRAN, DLLs and Integration with Excel/VBA (710)

Practical Risk Modeling in Spreadsheets (711)

Spreadsheet Aided Simulation and Optimization (712)

Spreadsheet Aided Thermal-fluid System Modeling and Optimization (713)

Spreadsheet Aided Propusion System Design (714)

Spreadsheet Aided Signal Processing (715)

Integrated Excel/Access Aided Engineering (716)

Spreadsheet Aided Management (719)

Spreadsheet Aided Vehicle Dynamics (723)

Spreadsheet Aided Data Plotting & Analysis (724)

Systems Modeling and Analysis (726)

Access Aided Engineering (727)

Spreadsheet Aided RF & Microwave Electronics Design (728)

Using Excel and Mathematica (731)
SWI © Copyright 2011. All rights reserved. home page   |    about us   |    services   |    training   |    contacts