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.

Instructor
Tom R. Mincer, Ph.D., CEO, SpreadsheetWorld Inc. and Professor of Mechanical Engineering, California State University, Northridge (CSUN). Dr. Mincer initiated the use of EXCEL spreadsheets in the under-graduate and graduate curriculum at CSUN by integrating it into the courses on systems design, computational methods and computer-aided-engineering. For the past 10 years he has worked extensively in the areas of systems design, simulation and optimization using the Excel structured spreadsheet environment. He has taught the SpreadsheetWorld 5-day seminar on Spreadsheet Aided Engineering over 80 times to 1,200 engineers including on-sites at Goodyear Tire, NASA Goddard Space Flight Center, NASA Glenn Research Center, NASA Johnson Space Center, Jet Propulsion Lab, TRW, General Motors, Delco, Delphi, Naval Weapons Center, Naval Warfare Center, L3 Ocean Systems, Edwards Air Force Base Rocket Research Lab, Hamilton Sundstrand, L.A. Water District, Lockheed Martin, Pratt & Whitney and Honda R&D.

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

Outline
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
Matlab
SolidWorks

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