top header
top gradation HOME top vertical line top vertical line top vertical line top vertical line top vertical line top vertical line top vertical line menu gray
black line 2
menu gray tab More About ATI
menu blue ATI — Who We Are
white line
menu blue Contact ATI Courses
white line
menu blue List Of ATI Courses
white line
menu blue Attendees Testimonials
white line
menu blue The ATI FAQ Sheet
white line
menu blue Suggestions/Wait List
white line
menu blue New Courses
white line
menu blue Become an ATI Instructor
menu gray tab site resources
menu blue Acoustics & Sonar
white line
menu blue Rockets & Space
white line
menu blue GPS Technology
white line
menu blue ATI Blog
white line
menu blue ATI Space News
white line
menu blue ATI Site Map
white line
menu blue ATI Staff Tutorials
white line
menu blue ATI Sampler Page
white line
menu gray tab bar
menu gray tab courses
white line
menu blue Current Schedule
white line
menu blue Onsite Courses
white line
menu blue Register Online
white line
menu blue Request Brochure
white line
menu blue Free On-Site Price Quote
white line
menu blue Download Catalog
white line
menu blue Distance Learning
black line  

ATI's Engineering Analysis and Automation using Excel & VBA course

Share |


    Technical Training Short On Site Course Quote

      Engineering Analysis and Automation using Excel and VBA is the foundational component in a comprehensive sequence of hands-on courses that instruct participants in the use of spreadsheets to perform common and repetitive engineering tasks. Many engineers are knowledgeable in the application of spreadsheets to a myriad of engineering analysis tasks. However, frequently these individually-written spreadsheet models display deficiencies in well-structured development practices and lack consistent documentation and maintainability approaches. Additionally, very few engineers are conversant in the Visual Basic for Applications (VBA) language extension provided with all Microsoft Office products, which can dramatically enhance the capability, usability, and reliability of these tools. Engineers who take this hands-on course will see immediate benefits in their daily work, saving them and their companies countless man-hours through increasing productivity and establishing a consistent methodology for engineering analysis.

      In this hands-on, five day 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 student success in current and future projects. This approach is distinct from the typical non-engineering Excel/VBA course that provides common cookbook 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, M.S. or David R. McDaniel M.S.

      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.

      Contact these instructors (please mention course name in the subject line)

    Who Should Attend:

      Engineers, Technicians, Mathematicians, Scientists and Designers, Program/Project Managers, Cost Analysts
      Each student receives course notes for future reference
    Course Format:
      Workshop format with hands on exercises
    Equipment required from on-site facility:
      LCD projector and screen, white board and markers or flipchart and markers

    Course Outline:

    1. Course Introduction and Overview -- Day 1 Morning
      • Company philosophy
      • Instructor and student introductions
      • Course outline review
      • Logistics and schedule
      • Computer customization and setup
    2. Review of Excel Basics -- Day 1 Morning
      • Formula point and click
      • Mapping cell ranges - Worksheets & Workbooks
      • Relative vs. absolute reference; mixed reference usage
      • Copy / paste / paste special / autofill
      • Freeze/Unfreeze rows & columns
      • Printing options; Page breaks (auto and manual)
      • Data filtering; Autofilter
      • Basic data plotting
    3. Excel for Scientists and Engineers -- Day 1 Morning
      • Excel specifications & limitations
      • Keyboard shortcuts
      • Use and Benefit of Defined Names
      • Range array definition and naming; 1D and 2D arrays
      • Built-in Function library; worksheet programming
      • Analysis Toolpak
      • Available/native mathematical routines
      • Regression analysis
      • Circular references and Worksheet iteration
      • GoalSeek and sorting
    4. Worksheet features -- Day 1 Morning
      • Moving and copying worksheets
      • Internal and external worksheet/workbook links
      • Spreadsheet auditing
      • Cell comments
      • Data validation
      • Worksheet protection
      • Conditional formatting
      • Hide/Unhide worksheets
      • Merge cells vs. center across selection
    5. Tool Development Elements and Project Design Considerations -- Day 1 Afternoon
      • Motivation and purpose of project
      • Elements of an Excel/VBA application
      • Excel hierarchy; application to subsystems modeling
      • Structured spreadsheets; layout and design
      • Structured spreadsheet guidelines
      • Worksheet-based project control
      • Graphical interface based project control
      • Using Forms / ActiveX controls on the worksheet
      • Menus and toolbar buttons
    6. Visual Basic for Applications Fundamentals -- Day 1 Afternoon
      • Excel versus VBA; integrated but not
      • VB Editor; Project Explorer and Properties windows
      • Code Windows (Worksheet / Module sheet)
      • Types of VBA procedures
        • Different methods for Calling VBA procedures
        • Procedure module layout overview
        • Inserting/removing modules
        • Importing/exporting module code
      • VBA Coding standards
        • Security issues
    7. VBA Procedure Specifics -- Day 2 Morning
      • Procedure scope; Private and Public
      • Procedure variables
      • Variable persistence and scope
      • Case recognition in VBA
      • Returning values from Functions
      • Returning values from Procedures
      • Arguments vs. No Arguments
      • Calling procedures from procedures
      • Cross project procedure use
      • Using arrays in functions
      • Exiting procedures early
    8. Visual Basic for Applications Language and Syntax -- Day 2 Afternoon
      • Variable names and data types
      • Declaring variables and constants
      • Variable naming conventions and assignment
      • Procedure argument data typing
      • Passing arrays between VBA and the worksheet: Option Base 1
      • Defining array variables; array dimensioning
      • Referring to array elements
      • Dynamic arrays
      • Passing arrays to/from procedures
      • Numeric operators and logical
      • Decision control IF and CASE
      • Looping - Counted loops and conditional loops
      • VBA string functions
      • Formatting output in VBA
    9. Debugging VBA procedures -- Day 2 Afternoon
      • 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 object browser
    10. VBA Application Libraries -- Day 3 Morning
      • 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
    11. Excel Automation Using VBA -- Day 3 Morning
      • Introduction to Object Oriented applications
      • Definitions (object, method, property)
      • Common Excel objects
      • Excel object hierarchy and containers
      • Object collections
      • Declaring objects; using the Set statement
      • Data typing object variables
      • Looping through collections (For..Each; With..End With)
      • Active objects
      • Common Excel object properties
      • Reading and writing object properties
      • Selected Excel object methods
      • Finding objects and properties; VBA object browser & Macro recorder
      • Calling and returning results from methods
    12. Using the Range object -- Day 3 Afternoon
      • Referencing a range
      • Using the Cells property
      • Using the End property
      • Using the Offset property
      • Using the UsedRange property
      • Using the CurrentRegion property
    13. Graphical User Interface (GUI) Development -- Day 3 Afternoon
      • Excel Menu/Toolbar Manual Customization
      • Customizing menu look (icons, section breaks)
      • Assigning procedures to menus
      • VBA-side Menu/Toolbar Development
      • Userform design considerations
      • Creating and using userforms
      • Displaying and dismissing a userform
      • ActiveX controls for userforms
      • Event programming in VBA
      • Event driven sub procedures (and arguments)
    14. System Modeling in Excel -- Day 4 Morning
      • Functional decomposition in Excel
      • Functional modeling with UDFs
      • Functional modeling libraries
      • Defining and monitoring system requirements
      • Use of Excel for trade studies
    15. Optimization -- Day 4 Morning
      • Defining the optimization problem; visualization
      • Design region; local and global minimums
      • Design variables and design parameters
      • Objective functions; single and multiple
      • Optimization design constraints
      • Mapping to the optimization worksheet
      • Introduction to solver
      • Optimization Worksheet design strategy for solver
      • Activating and running Solver
      • Interpreting Solver results
      • Solver automation from VBA
      • Casting a system of nonlinear equations as an optimization problem
      • Using Solver to solver a system of nonlinear equations
      • Casting a nonlinear regression problem as an optimization problem
      • Using Solver to identify parameters in nonlinear models
    16. Specialized Topics (Time Permitting) Class Participant Driven
      • Chart Automation w/ VBA -- Day 4 Afternoon
        • Creating XY scatter plots from VB A
        • Formatting/customizing chart elements from VBA
        • Animating data in an XY scatter plot
        • Saving a chart to a file
      • File Input/Output w/ VBA -- Day 5 Morning
        • Current directory, changing drives/directories
        • Testing for file existence, deleting files
        • Reading data from ASCII files
        • Writing ASCII files without formatted output
        • Writing ASCII files with formatted output
      • Automating Other Applications w/ VBA -- Day 5 Afternoon
        • Windows COM automation framework
        • Creating new instances of external applications
        • Attaching to existing instances of external applications
        • Accessing the object model of external applications
        • Working with command-line utilities that are not COM-enabled
        • Synchronized and asynchronous shell execution /ul


      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 or Please call us at 410-956-8805 for pricing for this two-day course, or send an email to