Training

Great Engineers Program – VBA tutorial for Excel

Great Engineers Program – VBA tutorial for Excel 600 374 Eric Roulo

Introduction

Programming is one of the few skills that separates great engineers from merely good ones. Programming is also the best tool I’m aware of for teaching one of the three most important traits of great engineers, curiosity (the other two are being hungry and having enough talent). The value of programming is indisputable however, the barrier to entry is fairly high. Which language should you learn? What exactly will you do with it? Will your manager accept that you’re not, ‘doing your job’ while there is a text editor up with strange syntax up on your screen for hours a day? (Are you programming an iPhone app on company time?!) I have programmed and been exposed to these obstacles for 14 of my 16 years as a mechanical engineer. I have some recommendations:

  • There is no one best software tool, so don’t spend too much time trying to find the holy grail of technical computing.
  • Using open source software will avoid having to ask permission to purchase a software language and makes it easier for others to adopt your software.
  • Accessing the data directly removes the tie to specific pre/post processors that may not be available at your next job.
  • Break the rules in the interest of speed.
  • Good open source languages (Perl, Python); Good Engineering Languages (Matlab); Good development tools (MathCAD).

I recommend starting with VBA (Visual Basic for Applications) for Excel. If you are new to programming it offers many attractive features. You already use Excel, it comes installed with your computer and everyone’s computer you interact with. It expands Excel to do unique things that every engineer in your organization could benefit from. You will immediately be able to program one to ten line programs that have real-world utility. The user base is huge, “googling it” will likely find you someone who has already solved your problem. It’s object-oriented, so if you’re inclined you can add these skills to your repertoire.

Because I’m such a believer in this approach, there will be regular tutorials on using Excel to enhance your engineering productivity. I expect this to evolve over time into more complicated programming languages and techniques, but also provide that *introduction* level exposure to get more people in the field of engineering programming.

Adding UDF’s to Excel

Let’s start, here are three functions that highlight what can be done in just a few lines of VBA.

Download the excel workbook with demo’s here.

The easiest thing to program in VBA is a User Defined Function (UDF). Let’s start with one that computes the root sum of squares of a range of values.

To add user defined code into an excel spreadsheet, we first to need to add a VBAProject ‘module’ into the excel workbook. Enter the VBA editor by typing Alt-F11 from Excel. On the left side of the window, right-click This Workbook->Inset->Module. This will create a window where you can enter VBA code that can be accessed from excel worksheets.

Now we can start typing code. To add a function, type the function (or cut and paste).

1. RSS (Root sum of the squares of a range)

Public Function RSS(Table_array As Range)
' Written by Eric J. Roulo
' Copyright (c) 2017, permission to use is granted if this header is maintained
' Example of using internal VBA command (Sqr) and Excel function (SumSq) to accomplish
' root-sum-of-squares function in one simple call
RSS = Sqr(Application.WorksheetFunction.SumSq(Table_array))
End Function

2. Grms from a range of frequency amplitudes (Dave Steinberg Method)

Public Function grms(Table_array As Range, Optional column As Integer = "2")
' Copyright (c) Roulo Consulting, Inc. 2017
' This function calculates the area under a random vibration input spectrum per
' the method described in Dave Steinberg's "Vibration Analysis for Electronic Equipment"
' 3rd edition, page 195
'
' Instructions for use:
' Input a range where the first column is frequency and the second column is amplitude
' Such as the table below from GEVS-SE Table 2.4-5 "Component Minimum Workmanship
' Random Vibration Test Levels 45.4-kg (100lb) or less"
' [REF  http://arioch.gsfc.nasa.gov/302/gevs-se/toc.htm]
'
'     Freq |  Amplitude  |
'     --------------------
'     20   |     .01     |
'     80   |     .04     |
'     500  |     .04     |
'     2000 |     .01     |
'
'  overall Grms = 6.789
'
' Where Amplitude is in [g^2/Hz]
'
' The grms function will return values that are consistent with the units of the amplitude.
' If your amplitude values are in [(m/s^2)^2/Hz], then your returned rms value will be [m/s^2]

' Written by Eric J. Roulo
' Copyright (c) 2004, permission to use is granted if this header is maintained
' Roulo Consulting, Inc. / www.rouloconsulting.com
' eric.roulo@rouloconsulting.com
' (650) 580-1745

Dim Number As Double
Dim dB As Double
Dim octaves As Double
Dim S As Double
Dim P2 As Double, F2 As Double, F1 As Double

' Where:
' Number  = number of rows in range supplied. This represents the number of freq/amplitude data points
' dB      = the decibel value between two consecutive data points
' S       = the
' octaves = the number of octaves between two consecutive frequencies
' P2      = amplitude (power) at frequency point
' F2      = frequency at high end of two consecutive points
' F1      = frequency at low end of two consecutive points
' column  = the column in the input range with the amplitude. It defaults to 2 without entry. Sometimes the
'           amplitude column is separated by the frequency column and this needs to be adjusted
Number = Table_array.Rows.Count
For i = 2 To Number
P2 = Table_array(i, column)
F2 = Table_array(i, 1)
F1 = Table_array(i - 1, 1)
dB = 10 * Log(Table_array(i, column) / Table_array(i - 1, column)) / Log(10#)
octaves = Log(Table_array(i, 1) / Table_array(i - 1, 1)) / Log(2#)
S = dB / octaves
    If S <> -3 Then
    A = A + (3 * P2) / (3 + S) * (F2 - (F1 / F2) ^ (S / 3) * F1)
    End If
    If S = -3 Then
    A = A - F2 * P2 * Log(F1 / F2)
    End If
Next
grms = Sqr(A)
End Function

3. Linear Interpolation (from the internets)

Public Function LInterpolateVLOOKUP(sLookup_value As Single, rTable_array As Range, iCol_index_num As Integer)
'A User Defined Function that returns values linearly interpolated "between"
'the points in the lookup table. May also return error values, hence definition as implied Variant
'Created by Myrna Larson
'obtained from Mr Excel by Bruce Mutton July 2001
'Substantially edited and documented by Bruce Mutton July 2001
'I have tryed to emulate the behaviour of builtin Vlookup function
'sLookup_value is the value to be found in the first column of the array.
'               sLookup_value can be a value or a reference
'rTable_array  is the table of information in which data is looked up.
'               Use a reference to a range or a range name, such as Database or List.
'               Values in the first column of table_array must be placed in ASCENDING ORDER:
'               ..., -2, -1, 0, 1, 2, ... , otherwise LInterpolateVLOOKUP
'               WILL NOT give the correct value.
'iCol_index_num    is the column number in rTable_array from which the matching value should be
'               returned. A iCol_index_num of 1 returns the value in the first column in table_array;
'               a iCol_index_num of 2 returns the value in the second column in table_array, and so on.
'               If iCcol_index_num is less than 1, LInterpolateVLOOKUP returns the #VALUE! error value;
'               if iCol_index_num is greater than the number of columns in table_array, LInterpolateVLOOKUP
'               returns the #REF! error value.
'
'If sLookup_value is smaller than the smallest value, or larger than the largestvalue, in the first
'   column of rTable_array, LInterpolateVLOOKUP returns the #N/A error value.
                                          
Dim iTableRow As Integer
Dim vTemp As Variant
Dim dbl_x0 As Double, dbl_x1 As Double, dbl_yo As Double, dbl_y1 As Double
                       
'Deal with obvious user errors
If iCol_index_num > rTable_array.Columns.Count Then
LInterpolateVLOOKUP = CVErr(xlErrRef)
Exit Function
End If
                     
If sLookup_value < Application.WorksheetFunction.Min(rTable_array.Columns(1)) Or sLookup_value > Application.WorksheetFunction.Max(rTable_array.Columns(1)) Then
LInterpolateVLOOKUP = CVErr(xlErrNA)
Exit Function
End If
                       
'Now the real thing
On Error Resume Next
vTemp = Application.WorksheetFunction.Match(sLookup_value, rTable_array.Resize(, 1), 1)
On Error GoTo 0
If IsError(vTemp) Then
LInterpolateVLOOKUP = CVErr(vTemp)
                                       
Else
iTableRow = CInt(vTemp)
dbl_x0 = rTable_array(iTableRow, 1)
dbl_yo = rTable_array(iTableRow, iCol_index_num)
If sLookup_value = dbl_x0 Then
LInterpolateVLOOKUP = dbl_yo
Else
dbl_x1 = rTable_array(iTableRow + 1, 1)
dbl_y1 = rTable_array(iTableRow + 1, iCol_index_num)
LInterpolateVLOOKUP = (sLookup_value - dbl_x0) / (dbl_x1 - dbl_x0) * (dbl_y1 - dbl_yo) + dbl_yo
End If  'sLookup_value
End If  'IsError(vTemp)
End Function

Again all these examples are presented in working form in this excel attachment.

Some Closing Thoughts About Software Languages

You should start learning multiple languages. Get good at one or two first. Start thinking about the future, when you need to do something 1 million times, what will you do? How will you think about it?

Watch this video by Perl creator Larry Wall on Which Five Languages Should you Know. It’s only 6 minutes and will whet your appetite for how you should focus your efforts.

Programming as One of Three Critical Traits of Engineers

Programming is the teachable equivalent to curiosity, and curiosity is the hallmark of great engineers. The reason programming is a teachable equivalent for curiosity is that it forces you to break down any problem into fully understood and logically functionalized code. You can’t program a solution until you understand exactly how the problem works. And understanding everything about how a problem works forces a level of curiosity. The thing about programming is there is a positive feedback loop. When you finish the program, the problem is solved. It solves easily (human effort), repeatably, and quickly. You are free to tackle other challenges. And now that you know how to break down problems, understand them, and then solve them forever, you should be motivated to do it more often.

Coming Soon:

1. Newton Iteration in VBA

2. Updating linear interpolation for log-log, log-linear interpolation

3. A better version of vlookup

-ejr

Design of Aerospace Mechanisms

Design of Aerospace Mechanisms 548 416 Eric Roulo

Download 43 Volumes Now

I have recently had the opportunity to work with some clients who are developing space mechanisms. This is a very specialized field with 60 years of tribal knowledge. When working at NASA I was exposed to the Aerospace Mechanisms Symposium which is the semi-annual gathering for all things space mechanism. It is the best place that tribal knowledge is shared. RCI is now sharing the previous 43 volumes with you! You can download the zip file containing all 43 volumes and the contents of the original 2 CD set The NASA Space Mechanism Handbook and Reference Guide*.

If you are involved in aerospace mechanism design, analysis, or oversight, this data set is the best single source of information in the field. Repeating any of the previously documented failures in mechanism design is inexcusable and extremely expensive. Yes, it won’t be an easy task to review the tens of thousands of pages of literature, but they are in searchable pdf format and come with an index.

One of the early articles that I recommend is a 3 page article by an Air Force Major named James C. McSheery. It’s titled, The Design of Aerospace Mechanisms – A Customer’s Opinion. It’s such a humorous and short piece I use it as one of my first reading/writing assignments in my engineering training program. Read it here.

Another article I recommend that came out of the last symposium in 2016 was this article on how Ti bolts are less tough than their equivalent steel fasteners. This is despite the material properties being almost identical. Shocking.

*These files do NOT contain the NASA Handbook due to its ITAR restriction, but you can request a copy here if you are a US Citizen and in the field.

-ejr

Updated 4/28/2019

The latest volume is here (2018): AMS44

Earned Value Management System = Get Paid!

Earned Value Management System = Get Paid! 600 374 Eric Roulo

Earned Value Management System = Get Paid!

Bottom line: you don’t get paid, if you don’t show earned value. Usually earned value is done during key milestone such as: preliminary design review (PDR), critical design review (CDR), test readiness review (TRR), first article inspection. In the real world, you will see management pushing on holding key milestone reviews, since it is associated with invoicing for payment. It is critical for engineers, managers, and supporting functions to understand payment milestone drive the schedule.

An EVMS Earned Value Management Systems is a method used to track project performance and forecast future performance. An EVMS integrates performance, cost, schedule and risk management of a project and provides early warning of risks and problems. Provides accurate response to management; are we behind or ahead of schedule, are we under or over budget, what will be spent on the whole project. It provides an EVMS and reporting system to use as a tool to manage the timely indication of work progress, early identification to problem areas, potential issues, and performance trends which give the project manager the ability to project controlling the baseline.

Table 1 Earned Value Parameters and Performance Variance

The benefits of understanding the EVMS process is that the program effectively integrates performance, time, cost, & risks. All the EVMS variables are defined in Figure 1 Earned Value Management Systems and Table 1 Earned Value Parameters and Performance Variance shows how the EVMS is measured.

History of EVMS:

Cost and Schedule Control Systems Criteria (C/SCSC) used in DoD for more than 35 years, DoD 5000 series contains 35 criteria. In 1996, C/SCSC transitioned to EVMS containing 32 guidelines. In 2003, DoD raised concerns and actioned government/industry to establish EVM working groups. In 2005, Office of Management and Budget (OMB) implemented EVMS on all federal agency capital asset acquisitions and inter-agency work.

EVMS is a requirement of many U.S. Government agencies such as: Department of Defense (DoD), Department of Transportation (DoT), Department of Energy (DoE), Department of Homeland Security (DHS), National Aeronautics and Space Administration (NASA), Federal Aviation Administration (FAA), and others. This is why you have to conform, you want their money and you want to get paid.

The ANSI/EIA-748-C EVMS Standard contains a set of 32 Guidelines that defines the requirements EVMS must meet. There are 5 phases of EVMS process:

Reference: National Defense Industrial Association, Earned Value Management Systems ANSI/EIA-748-C, April 29 2014

-npp

Presenting FEA Results Properly

Presenting FEA Results Properly 600 374 Eric Roulo
Anytime you present data, it should be done with the intent of providing as much information as possible with the goal of preemptively answering questions the viewer might have.

Margins should be reported in tables and then supported with imagery. An expected image is the fringe plot of stress, strain, or perhaps deflection. This image is what most people perceive as the primary output of finite element analysis, but presented alone often provide misleading and partial data.

Fringe plots (contour plots) should be shown to identify where the high stress regions are for a given load case. The color bar should be discretized to aid in the understanding how large an area of a certain stress level is. The color bar should have a reasonable number of levels (i.e. 10-20). Usually the defaults are fine. The background should be white to allow for good printing (no color gradient or black background). This includes pictures for PowerPoint presentations as they usually get printed out too. Feel free to rescale the color bar to a meaningful set of numbers. The top number should usually not be the highest value reported. Make sure the post processor does show you the maximum value, usually identifying the area directly (with a number). It’s always nice to be able to see the date generated, what file the results are from, etc.

Element edges should be turned on so you can see the type of element and mesh density in the region of interest. If the element edges overwhelm the colors trying to describe the stresses, turn them off for the big picture view and then zoom in and turn them on for a detailed view.

Remove elements from the calculation that are high due to modeling technique (singularities). This will include areas where point loads are introduced, elements attached to rigid elements or near spring connections representing fasteners (among others).

A good measure of if your charts and tables are providing the correct information is if no one is grabbing a calculator or doing math on the numbers you present. If everyone is computing a ratio of your results, compute that ratio for them and report it directly.

The following Guidelines are a good start for effectively presenting contour/fringe plots:

  1. Discrete Color Ranges (level colors)
  2. Element Edges On
  3. Legend On
  4. Show Output Data
  5. Text Size 18 pts
  6. Background to White
  7. Show BC’s and Loads
  8. Specify Contour Levels
  9. No Magenta
  10. Use High-Resolution Output Images
  11. Size Image Window Appropriately
  12. Use Colors to Describe Model Features
  13. Remove Elements
  14. Show Overall and Zoomed in View

-ejr

Prepare for Your Technical Future with Dr. Richard Hamming

Prepare for Your Technical Future with Dr. Richard Hamming 549 526 Eric Roulo

hammin_102743951sm“FEA is for insight, not numbers” was the quote on the inside cover of the student guide for I-DEAS. I found out 15 years later that it was a misquote from Dr. Hamming, but that research led me to one of the great finds of my professional development. Learning to Learn is a 31 part lecture series that outlines the lessons learned from a giant in the field of science and engineering. Watch the first episode and be captivated.

The binder of Hamming’s notes contained the following, which eventually was published as a book.

The Art of Doing Science and Engineering and the ebook is located here.

The videos can be viewed on RCI’s youtube channel in a special playlist here.

“The purpose of computing is insight, not numbers.” -Richard Hamming

-ejr

RCI’s YouTube Channel – Direct Training Material

RCI’s YouTube Channel – Direct Training Material 900 900 Eric Roulo

Watch free HD Training provided through our RCI YouTube channel. Subscribe today to get notifications of all new material.

We are proud to announce the launch of our YouTube channel where HD content will be available 24 hr/day.

Subscribe Here

The channel will provide original content across a spectrum of engineering topics and serve as a platform for our training programs.

Topics will include:

  • Femap and NX/Nastran Modeling
  • Stress Analysis Hand Calcs
  • Engineering Programming and Software Development
  • The RCI Engineering Environment (Ubuntu-VM based)
  • Engineering Case Studies
  • Engineering Forensic Photography
  • Product Development/Design

Production Finite Element Webinar Series (Industry Best Practices)

Production Finite Element Webinar Series (Industry Best Practices) 529 406 Eric Roulo

RCI will be conducting a four-part Webinar on Best Practices for FEA modeling starting in February 2014. The series will include separate 45-minute lectures on the following topics critical to producing reliable models in a production environment.

  1. Introduction to engineering modeling
  2. Building the model you want
  3. Model Checks Pre/Post
  4. Presenting Modeling Results

Agendas for the meeting are provided on the signup page here. Attendance is limited to 25 people via our video conferencing WebEx account.

Presenter: Eric J. Roulo

Dates: 2/6/2014, 2/13/2014, 2/21/2014, 2/28/2014

Background: Eric has been in an Aerospace production environment since he started his professional career at Space Systems/Loral in 1999. Having worked at over 15 companies since becoming a contractor and consultant, he has seen a wide variety of methods and approaches to utilizing Finite Element Models (FEMs) to calculate margins and provide verification for performance requirements. This is will be an in-the-trenches, actionable set of presentations that start with the philosophical approach to engineering modeling and then provide detailed guidance for implementing those strategies in NX/Nastran and Femap.

Effective Technical Communication – E-Mail

Effective Technical Communication – E-Mail 800 533 Eric Roulo

The most sanctioned company-sponsored waste of human effort is by far e-mail. The reasons for this are many, but the solution for the individual is relatively simple. By following these guidelines, one can free up hours a week of time to focus on your true priorities and provide an example on how to communicate effectively with email. Following will be some guidelines and then the philosophy behind them.

1) Check your email only three times a day and then close your email client

2) Empty your email