In this hands-on workshop, students will learn structured techniques for developing and maintaining spreadsheet analysis tools in Microsoft Excel. The VBA automation language that sits behind Excel will be covered in depth, providing students with the ability to streamline analyses and automate repetitive tasks. Only those elements of the spreadsheet application that are pivotal in technical analyses will be covered, and all VBA techniques will be geared towards enabling the student for success in current and future projects as opposed to the common cookbook approach of providing solutions for problems with very limited scope. Topics such as plotting and graphic interface techniques will also be covered with a special emphasis on automation using VBA. Hands-on examples throughout the course help to reinforce the concepts presented while going beyond overly simplistic exercises that are not applicable in a production environment. Students will not only leave the workshop with Excel and VBA skills they can apply immediately to their current project, but also exposure to a wide range of applications of the material.

 

 

Steven E. Squier, MS, Research Engineer, Hamilton Sundstrand Aerospace, initiated, developed and continues to promote a computer-aided systems engineering approach to analysis of aircraft subsystems, primarily using Excel. His 26 years of aerospace industry experience at Hamilton Sundstrand and Rockwell International have been primarily focused on the evaluation of vehicle-level system performance impacts due to subsystem design variations. He holds several patents in diverse areas of systems engineering and has experience in electric power, environmental control, thermal analysis, hydraulics, pneumatics, reliability, statistical and uncertainty analysis, neural networks and life cycle costing. As part of the Advanced Systems group within Hamilton Sundstrand, Mr. Squier has conceived and created several custom engineering applications incorporating intuitive graphical user interfaces all within the Microsoft Excel environment, and often coupling Excel with other applications. He has applied the concepts taught in this course to the evaluation of advanced subsystem integration schemes for many commercial and military aircraft platforms, most recently the Boeing 787 Dreamliner and Joint Strike Fighter. Mr. Squier has developed Excel/VBA applications not only for engineering analysis, but also for technical data management, facility/test build configuration documentation (combined with Microsoft Visio as a graphical front end) and automated status reporting of certification testing. Mr. Squier is also an Assistant Professor in the Physical Science Department at Rock Valley College in Rockford, IL, and a former Dean of Liberal Arts and Sciences.

 

David R McDaniel, MS has been working in the aerospace arena for over 15 years now. He served on active duty in the US Air Force for almost 8 years where his tours included flight test work on the B-1B and B-2A bomber aircraft and teaching aerodynamics and thermodynamics courses at the US Air Force Academy. Mr. McDaniel spent most of his post-military years working as a contract researcher at the USAF Academy in the areas of aerodynamics and computational fluid dynamics. He is currently working as an assistant research professor at the University of Alabama at Birmingham and is on the development team for the fixed-wing virtual aircraft product of the DoD High Performance Computing Modernization Program's CREATE project. He has leveraged the power of Excel/VBA in numerous applications related to his work, and he has taught various spreadsheet engineering short courses for the past ten years.

Matthew E Moran, PE is the owner of Isotherm Technologies LLC and a Senior Engineer at NASA. He has 25 years of experience developing products and systems for aerospace, electronics, military, and power generation applications. He has been a founder or key collaborator in five high-tech startup businesses, including Isotherm Technologies that has been developing pioneering thermal technologies since 1997. Recent projects include: thermal management microsystems, high-powered lasers, fuel cell reactants, super-insulation, solar dynamic cryocoolers, waste heat harvesting, and thermal-powered wireless sensors & devices. Matt is a licensed professional engineer with a Bachelor degree and graduate work in mechanical engineering, and an MBA in systems management. He has published 35 papers and articles; and has two patents, three patents pending, and six invention disclosures.

 

Course Outline (Can be Customized for On-Site Courses)

• Course Introduction and Overview
  • Company philosophy
  • Instructor and student introductions
  • Computer customization and setup
• Review of Excel Basics
  • Mapping cell ranges - Worksheets & Workbooks
  • Relative vs. absolute reference; mixed reference usage
  • Printing options; Page breaks (auto and manual)
  • Data filtering; Autofilter
  • Basic data plotting
• Excel for Scientists and Engineers
  • Excel specifications & limitations
  • Use and Benefit of Defined Names
  • Range array definition and naming; 1D and 2D arrays
  • Built-in Function library; worksheet programming
  • Regression analysis
  • Circular references and Worksheet iteration
• Worksheet features
  • Internal and external worksheet/workbook links
  • Spreadsheet auditing
  • Data validation
  • Worksheet protection
  • Conditional formatting
• Tool Development Elements and Project Design Considerations
  • Elements of an Excel/VBA application
  • Excel hierarchy; application to subsystems modeling
  • Structured spreadsheets guidelines; layout and design
  • Worksheet-based project control
  • Graphical interface based project control
  • ActiveX controls, menus, and toolbar buttons
• Visual Basic for Applications Fundamentals
  • VB Editor; Project Explorer and Properties windows
  • Code Windows (Worksheet / Module sheet)
  • Types of VBA procedures; calling VBA procedures
  • Procedure module layout overview
  • Importing/exporting module code
  • VBA Coding standards and security issues
• VBA Procedure Specifics
  • Procedure scope; Private and Public
  • Procedure variables: variable persistence and scope
  • Returning values from Functions and Procedures
  • Calling procedures from procedures; Cross project procedure use
  • Using arrays in functions
  • Exiting procedures early
• Visual Basic for Applications Language and Syntax
  • Declaring variables and constants; variable data types
  • Procedure argument data typing
  • Passing arrays between VBA and the worksheet: Option Base 1
  • Defining array variables; array dimensioning
  • Passing arrays to/from procedures
  • Decision control structures and looping
  • VBA string functions
• Debugging VBA procedures
  • Setting up Excel/VBA for annoyance-free programming
  • Classifications of errors (compile, runtime, syntax)
  • Option Explicit
  • Using and navigating online help
  • Error trapping methods
• VBA Application Libraries
  • Importing/exporting/deleting VBA modules
  • VBA project and routine scope
  • Library configuration control in your workgroup
  • Creating Excel Add-ins
  • Accessing Add-ins from within VBA
  • Distributing Add-ins to workgroups
• Excel Automation Using VBA
  • Introduction to Object Oriented applications
  • Common Excel objects, properties, and methods
  • Object containers and object collections
  • Declaring and data typing objects; using the Set statement
  • Looping through collections (For..Each; With..End With)
  • Reading and writing object properties
  • Finding objects and properties; VBA object browser & Macro recorder
• Using the Range object
  • Referencing a range
  • Using the Cells property
  • Using the End and Offset properties
  • Using the UsedRange and CurrentRegion properties
• Graphical User Interface (GUI) Development
  • Excel-side Menu/Toolbar Manual Customization
  • VBA-side Menu/Toolbar Development
  • Userform design considerations
  • Creating, displaying and dismissing a userform
  • ActiveX controls for userforms
  • Event programming in VBA
• System Modeling in Excel
  • Functional decomposition in Excel
  • Functional modeling with UDFs; functional modeling libraries
  • Defining and monitoring system requirements
  • Use of Excel for trade studies
• Optimization
  • Defining the optimization problem; visualization
  • Design region; local and global minimums
  • Design variables, design parameters and objective functions
  • Optimization design constraints
  • Introduction to Solver; optimization design strategy
  • Activating and running Solver
  • Nonlinear equations and regression with Solver
• Specialized Topics (Time Permitting) – Class Participant Driven
  • Data Analysis and Graphing (importing data, filtering/smoothing, automation of charts)
  • File Input/Output and manipulation w/ VBA
  • Automating Other Applications (Word, PowerPoint, Visio) from Excel
  • Applied Numerical Methods (integration, differentiation, finite difference)
  • Interfacing VBA and FORTRAN/C++ Dynamic Link Libraries (DLLs)
  • Project Documentation (creating help files, documentation support)
  • Drawing Objects and Animation / Integration with VBA
  • Integrated Design Teams / Concurrent Engineering by linking Excel Workbooks
  • Dynamic System Simulation / Optimization Loop
  • Review and Further Exploration of Object Oriented Programming
  • Graphical User Interfaces / Active-X Controls on Userforms