Advanced Spreadsheet Aided Engineering - 676

Please contact Janet Mincer with inquiries regarding enrollment fees:

Course Description
Alumni from the SpreadsheetWorld Workshop on Spreadsheet Aided Engineering are invited to participate in this Power Users Workshop. The objective of this workshop is to sustain, extend and put into practice the principles and skills taught in the 5-day course and to challenge attendees to take a leadership position in implementing the principles taught in the workshop at your organization. The course addresses enterprise issues for effective use of Excel/VBA in your department and organization. It will establish guidelines and processes which will lead to coordinated libraries of Excel/VBA functions and toolboxes. The objective is to implement a process whereby custom built and commercially available XlToolboxes can be used throughout your organization. This process will result in numerous enterprise level benefits. It will include active development of best practices, increased efficiency and less duplication of effort. The workshop covers a series of advanced topics designed to expand and enhance the attendees skills in support of meeting the groups overall objectives. It will both enhance individual attendee’s skills, and at the same time improve the overall capability of your team members and organization.

Course Materials
Each participant receives a 3-volume set of workshop notes which include all the PowerPoint slides used during the workshop for concept discussion and setting up of workshop exercises. The CD-ROM contains an electronic version of the course notes; the course examples; engineering case studies; and many SpreadsheetWorld Engineering XLToolboxes including XLQuikPlot, XLNumerical Methods, XLSimulation, XLThermal Fluids, XLHeat Transfer, ,XLGasDynamics, geometric modeling, and data analysis gas. It all includes the new and popular Units Converter PowerBook which brings a comprehensive capability of unit conversion as well as extensive engineering constant reference. Workshop attendees receive free updates on all included software for 5 years. Participants also receive 30-day demo copies of other selected XLToolboxes including XLEigenvalues, XLProPlot, XLinear and Roark for Excel. Participants also receive a copy of the course textbook Computational VBA, which is written by the course instructors.

Engineers from all disciplines find this course immediately useful, practical and eye-opening. Participants should have a some computer background including basic keyboard and Excel skills.

Key Topics

  • Define system physical object structure
  • Define process flows
  • Create engineering information tables
  • Develop re-useable system functional models
  • Monitor the impact of key design/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
  • Do dynamic system modeling and simulation
  • Do system optimization using Solver
  • Monitor system requirements using Solver
  • Setup sensitivity maps about a design point
  • How to use Fortran/C modules from Excel
  • Do dynamic system simulation the optimization loop
  • How to setup Configuration Trade-Study Matrix
  • Use UserForms for man-in-the-loop analysis
  • Develop graphic user interfaces for systems design
  • Setup system modeling for integrated design teams

Review of Foundation Course #576

  • VBA Programming Environment
  • Excel Object Library
  • Properties, Methods and Events
  • Event Programming
  • Userforms and ActiveX Controls
  • Building Engineering Function Libraries
  • Building *.BAS Collections
  • Parameter Naming Guidelines
  • Function Testing & Validation
  • Function Limits and Units
  • Function Documentation
  • Function Input/Output Guidelines
  • Categories of Functions
  • Category Tree Structure
  • Functional Modeling of Systems
  • Using *.BAS files in Excel and VBA
  • Function Configuration Management

Building Toolboxes

  • Creating Add-ins (without DLLs)
  • Add-ins with Multiple Functions
  • Private versus Public Functions
  • Individual Function Documentation
  • Add-in Toolbox Management
  • Using Add-ins from Excel
  • Referencing Add-Ins for use in VBA
  • Transporting Workbooks which Use Add-Ins

Documentation support for Excel/VBA Projects

  • Object Linking and Hyper Linking
  • Developing HTML Help Files
  • Integrating HTML Help files with Excel/VBA
  • Help File Management
  • The SpreadsheetWorld Toolbox Manager

Solving Sets of System Rules & Requirements

  • Defining equalities and inequality rule sets
  • Residual equations
  • Linear versus nonlinear rule sets
  • Rules including differential equations
  • Rules including integrals
  • Solving sets of rules
  • Solution automation

Solving Rules with Optimization of One Rule

  • Formulating a system for design optimization
  • System performance objectives and constraints
  • Using Excel Solver for design optimization
  • Objective Functions
  • Constraint Functions
  • Design & Decision Variables
  • Structured Optimization Sheets
  • Linking System Design and Optimization Sheets
  • Solver™ for Local Minimum of Continuous Functions

Configuration Trade Studies

  • Using userforms for configuration selections
  • Optimization for each configuration
  • Multiple objectives
  • Optimization of multiple objectives
  • Utility curves for each objective function
  • Configuration Trade Studies using Utility functions
  • Configuration Optimum Trade Matrix (COTM)
  • Automation of COTM

Using Excel for Team Collaboration

  • System Workbooks and Worksheets
  • Process for capturing requirements
  • Subsystems, disciplines and parameters
  • Setting up the discipline Ports
  • Establishing Parameter Ports
  • Subscribing Parameter Ownership
  • Using a Server for Central Communication
  • Workbook Sharing
  • Software: ICEMaker

Interfacing VBA and FORTRAN DLLs

  • Using your favorite FORTRAN program from the Excel platform.
  • Computationally Intensive Analysis
  • VBA Versus Compiled Runtime Libraries
  • Converting Existing Code to DLLs
  • Interfacing VBA and DLLs
  • Creating FORTRAN DLLs
  • Using DLLs with an Add-in

Applied Numerical Methods for System Modeling

  • Overview of XLNumerical Toolbox
  • Roots of Algebraic Systems
  • Interpolation & Extrapolation
  • Numerical Integration
  • Numerical Differentiation
  • System Sensitivity Maps
  • Finite Difference Methods

Charting and Chart Automation

  • Charting VBA object structure
  • XLQwikplot & XLProplot
  • Using VBA for chart automation

Data I/O, Analysis and Visualization

  • Importing Data into Excel Worksheets
  • Importing Data into VBA
  • Enumeration and Lookup Functions
  • Filtering and Smoothing Data
  • Interpolation and Extrapolation
  • Linear and Nonlinear Regression
  • Linear Surface Regression

System State Space Simulation

  • Explicit General State Space Formulation
  • Initial Value Problems
  • Numerical State Forecasting Methods
  • Self Starting Methods
  • Non-Self Starting Methods
  • Structured Simulation Sheets
  • Spreadsheet Simulation Toolbox
  • Single State Variable Problems
  • Multiple State Variable Problems
  • Simulation in the Optimization Loop
  • Boundary Value Problems

VBA Control of Other VBA Applications

Office Applications

Class Programming

Class Modules
Building Custom Object Libraries

VBA Active Data Objects(ADO)

Basic ADO Object Set

Connection Object and Properties
Open and Close Methods
Recordset Object
Controlling Access Tables
Passing Data to Tables
Retrieving Data from Tables

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