Recently Tesla hosted “AI Day”, which showcased Tesla’s toys and gadgets which are currently in development by way of Artificial Intelligence. This is interesting considering in Elon Musk’s own words… “If AI has a goal and humanity just happens to be in the way, it will destroy humanity as a matter of course without even thinking about it”. It seems that every industry is feeling the effects of advancements in AI and thousands of jobs are tipped to be replaced by AI, but what about the humble role of the Structural Engineer?

Structural Engineering lies on the boarder of art and science. While it has a human and design element to it, the Structural Engineer we know today should still remain relevant for decades to come. However with advancements in computational design software as well as the introduction of powerful automation tools, it seems the Structural Engineer may become somewhat of a cyborg… a human with the ability to cope with the irrational, enhanced by the tools of technology.

A first step in this direction has seen a number of large Structural design software producers include API capability into their software packages. Software packages such as CSI’s ETABS have shipped with an API since circa 2014 as well as Autodesk’s Robot from a similar period.

(Enjoy using ETABS? Would you like to learn more? Take a look at THIS article which explains a step-by-step process to modelling cracked shear wall behaviour in your ETABS model)

What is an API

API is an acronym for Application Programming Interface. The interface basically allows two applications to talk to each other.

This unlocks a multitude of possibilities for refining your design and analysis workflow. The passing of information between software packages and spreadsheets is a large part of what structural engineers do. A big example of this is the passing of information from a 3D analysis model to a 3D documentation model. I have had varying degrees of success (and much more failures) exporting IFC models between packages and using built-in plugins to “seamlessly” transfer models from one package to another. There are still a few teething issues with this process even today.

But what about using your inhouse software packages and even internally developed design spreadsheets? This is where the power of APIs can help you take you design process to the next level.

What is ETABS

ETABS is a 3D FEA (Finite Element Analysis) design software package for the application of structural building design.

All structural elements can be modelled and designed within ETABS right from foundations, columns, slabs, beams and shear walls (and everything in between)

Where can I Download ETABS?

Legally of course from THIS link (you may need the assistance of a salesperson).

ETABS API Example using VBA within Microsoft Excel

Now we are going to explore a small example on what may be possible using VBA in Microsoft excel to interact with ETABS. The API functionality within ETABS allows you to lift the bonnet of ETABS and control its inner workings. Its worth noting that VBA isn’t the only coding language which is compatible with ETABS’ API; other languages such as C#, C++ and Python are also usable.

If you wish to, you can control almost all of the ETABS functionality within Microsoft Excel without even touching the ETABS software package. A thought experiment example of a possible code written in VBA could perform the following tasks:

1. Start the ETABS application
2. Start a new fresh Model within the ETABS software
3. Define steel material properties in ETABS (properties could be typed by the user directly into an excel table)
4. Define frame section properties into ETABS (frame properties including geometry etc. can be typed by use directly into a pre-formatted excel table)
5. Model a beam in ETABS (geometry can be inputted directly by user into a pre-formatted location)
6. Apply loading and support conditions to the beam in ETABS
7. Run the Analysis
8. Extract the desired results from ETABS and store them into a desired location and format in Excel.
9. Shut down the ETABS software…

All without touching the ETABS .exe file. Granted, this is a long long way from AI, but its very cool nonetheless.

Have you had issues with your models in ETABS seeming like a “black box”? Do you want more confidence in the results your ETABS model is producing? Take a look at THIS article for an in-depth discussion on how to ensure that your ETABS model is correct.

Lets explore a single element of this workflow to see how it works. This example will give you the necessary basic tools to take this example and scale it up as large as you desire and give you a basic grounding into VBA code.

Worked Example – Defining Frame Sections in ETABS

In this example, we want to use Excel to define a whole bunch (possibly hundreds) of frame sections in ETABS by using a populated table within Excel which the user can update and modify and use from project to project.

We will be defining circular and rectangular/square concrete sections in this example. In order to do this the old fashioned way in ETABS, I am referring to the following process…

Define>>Section Properties>>Frame Sections…

Then the Frame Properties Dialogue box opens and we click on “Add New Property”…

The Frame Property Shape Type dialogue box then opens and we either select, under concrete, the rectangular shape or the circular shape (whichever the case may be)…

Then we are free to input all the parameters we feel necessary including size/geometry, material (concrete grade) and reinforcement.

While you can define all parameters using the API, we will just focus on the following elements to keep this example simple and easy to digest

• In the case of a circular column, its diameter
• In the case of a square/rectangular column its length and width
• In the case of all column shapes, its concrete grade.

Step 1: Pre-Format a Sheet for User Inputs

We are going to prepare a table in Excel which is pre-formatted and allows the user to input all the necessary properties to define either a circular column or a square/rectangle column and assign it a column name as well as appropriate concrete grade (material property). Here is what the pre-formatted spreadsheet looks like…

There is nothing special going on whatsoever in this sheet, its simple formatting and text to prompt the user where each parameter needs to go. The location of each parameter is important as we need to specify within Excel which parameters need to be assigned to what location within ETABS.

Once this is set-up you should save the excel workbook session, you will need to save it as a macros enabled workbook for this to all work. To do this, you will need to go to save, then name your file then use the drop-down to select “Excel Macro-Enabled Workbook (*.xlsm)”

Step 2: Reference the ETABS Code Library (*.tlb File)…

Now we need to get to the coding area to start our VBA masterpiece, we do this by preforming the following:

Developer>>Visual Basic

This is where “the magic happens”, we are now in the VBA editor environment. Before we do anything, we need to reference the ETABS API coding library. The coding library comes with your ETABS installation and it comprises the code which ETABS understands (and VBA does not!), by referencing this coding library, VBA can start to talk the same language as ETABS.

To do this, you need to go to Tools then References….

Then the VBAProject References dialogue box comes up. You then need to click the “browse” button then browse to the location of the latest *.tlb file you can find within your ETABS installation folder.

The default installation location for this file is generally…

C:/Program Files/Computers and Structures/ETABS ##

(## is your Version Number, I am running ETABS 18 in this example). Scroll down within this folder until you see something that looks like ETABSv##.tlb select this file then click open. Note taht if you are using ETABS 2019 or beyond you are looking for an ETABSv1.tlb file

It should then be found in the Available References window on the previous dialogue box. Scroll to it (its in alphabetical order) then tick the tick box and click OK.

Step 3: Start a New Module

We now need to create a new module. A module is a location where you place your code, you can have multiple modules within the same worksheet. Each module can be regarded as a separate “macro” or code or sub-routine (however you wish to refer to it).

To do this, you need to hover over the VBA Project explorer section on the left of your screen then right click in the white space, scroll to “insert” then click “module”…

A fresh blank module sheet will now open up, we are wanting to then place the following code word for word and character for character into the module sheet (there is a cut and paste friendly version at the end of this article you can use. There are two code examples at the end of this article, one should be used for ETABS 2018, one should be used for EABS 2019 and beyond (for some reason the coding requirements changed slightly between the two versions). I strongly recommend however that you run through the next few sections first as I go through in detail what each line of code does, this way you can make your own code that performs even cooler tasks!).

VBA Coding Basics

Lets start with the basics of the code… the colours. The blue text represents code which is recognised within VBA (the native VBA coding language). The green text are notes to the code reader, when the code is executed, the VBA compiler will simply ignore the green text. The green sections are not mandatory however it is good practice to sprinkle notes throughout your code so down the track when you return to read or update your code you know what was your thought process and how it all works because you will forget later on (believe me, you will forget!). To have the VBA compiler ignore any line of code you wish an turn it green simply start the line with a ” ‘ ” symbol.

When the code is executed, VBA starts at the very first line and reads the code line-by-line, executing the commands as it goes, this is handy to know as sometimes things need to occur in particular orders, its good to know that the execution sequence is simply top to bottom so make sure you have the items which need to be executed first higher than the functions which need to be performed at the end of your code.

Now, just like VBA reading our code, lets go through line-by-line and figure out what each section does…

Setting up the Sub-routine and Attaching to the ETABS software.

The first line Reads…

Sub ColSections()

This is the syntax you need to use in order to start a sub-routine in VBA. The “ColSections” portion is a unique name I have given this sub-routine, there are some limitations to what you can call a sub-routine:

• It can’t have any spaces in its name
• It can’t be the same as a known VBA native language function.

For example, this sub-routine name would give an error immediately in VBA:

Sub Col Sections()

And Also this:

Sub Sub()

The next two lines of code read as follows:

Dim myETABSObject As cOAPI

Set myETABSObject = Nothing

These two lines of code set ETABS as an object within VBA. Objects can be used, manipulated and have variables shared with them (which is what we want to do with ETABS). The next section of code reads:

Dim myHelper As cHelper

Set myHelper = New Helper

These two lines of code activate the syntax/code helper from the ETABS API. The helper is like predictive text when typing the code. For example later on we are going to use an ETABS specific function which will define a circular frame member, when beginning this line of code, the helper will assist us with the function options and the code syntax. We begin to type the function then VBA automatically provides us with a drop down list of options (as we can see below, using the PropFrame function within the API we can do a number of things (most are not visible and you need to scroll to see all the possibilities).

This comes in really handy when you are just learning the ETABS functions which are possible within the API. A full help file with all the functions outlined and explained as well as example code is provided in your ETABS installation folder at the default location C:/Program Files/Computers and Structures/ETABS ##

Rounding out the first portion of code are the following lines…

Set myETABSObject = GetObject(, “CSI.ETABS.API.ETABSObject”)

Dim mySapModel As ETABSv17.cSapModel

Set mySapModel = myETABSObject.SapModel

These lines of code set an instance of ETABS as an object (ETABS itself is an object and the actual ETABS model you are working with is more or less an object within that object, we not only want to interact with the ETABS but also with the ETABS model file).

The three lines above specifically attach to a previously opened instance of ETABS. Therefore for this macro to work, you need to have ETABS open with a model file open within it (it can be a blank model file or a building you have already fully modelled and completed).

You may be thinking, “how come it says Sap in there, I’m using ETABS not SAP2000″ (which is also another CSI design software product). I’m not sure why this is, possibly because the API framework was originally set up for their SAP2000 software then adapted/adopted/modified to be used on their ETABS software.

All the code we have gone through so far can be simply copy and pasted to any new coding tool you wish to create which may perform different functions. In summary we have so far achieved the following up until now:

• Evoked the ETABS API library so VBA understands its language
• Introduce the ETABS software as an “Object” to VBA
• Start the API helper tool for future code writing assistance
• Introduced an open model within ETABS as an “Object” to VBA (which lies within the ETABS software object)
• Attached our code to an opened instance of ETABS (an ETABS model file) ready for future manipulation and control by VBA.

Define Variables to be Used Within our Code

This block of code is all about defining (sometimes called declaring) our variables. In VBA coding (and most other coding languages) you need to define your variables first before using them and storing values within them.

Think of variables like containers, later in the code we will be using these containers to store numbers and/or text.

The Syntax for defining Variables is as follows:

Dim VariableName As Variabletype

The variable name is a unique name you, the code writer, assign to that variable. It follows the same naming rules as the name of your sub-routine (no spaces between characters and no functions that VBA uses in its native coding language).

The variable type can vary, it lets your computer know how it should store the value and how much memory to allocate in preparation for handling that value. Here is a summary of a few very common variables you will need to know and use when starting your VBA coding journey…

Variable	Storage Allowance	Values Used for variable
Integer	2 Bytes	A whole round number between -32,768 to 32,767
Long	4 Bytes	A whole round number between -2,147,483,648 to -2,147,483,648
Double	8 Bytes	A decimal number -1.79769313486232e+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232e+308 for positive values.
String	Length of string + 10Bytes	A text/word based value (not a number) containing 0 to 2 billion characters
Boolean	2 Bytes	Either a “True” or “False” value

The functions that ETABS uses in its API are very particular about the variable type (each function outlines what variable types you need to use which is all outlined in the API help file). If you use the wrong variable type, your code simply won’t work (in some cases you won’t even receive an error message, simply nothing will happen when you execute your code).

Lets take a look at the variables I have defined for this piece of code and discuss what each one will do…

Dim LastRow As Integer

I want this spreadsheet tool to be really flexible and not restrict the user to a discrete number of sections properties which can be defined in ETABS. Before the code runs, I want Excel and VBA to determine how many populated rows of text there are in our pre-formatted table, this will then allow the code to later understand how many times it needs to perform the Define>>Section Properties>>Frame Sections task (similar to how you would do it yourself manually in ETABS).

From the previous table, we can see that an integer will give us storage capacity to have a whole number anywhere up to 32,767 so this should be plenty of space for us. Also it won’t be possible for us to have 5.5 ows in excel (or a fraction of a row), we are expecting to see nice whole round numbers which makes the integer variable type perfect for this application.

Dim IsRectangle As Boolean

There are two different functions that ETABS oAPI uses when defining either a circular frame section or a rectangular frame section, we will need to make our software smart enough to make a decision which one to use based on the user inputs. If the value stored in IsRectangle is “True” then we will use the ETABS oAPI function for defining a rectangular section, if the value of IsRectangle is “False” we will use the ETABS oAPI function for defining a circular shaped section.

This variable I am storing as a Boolean function because as you can see from above, we either want a Yes (True) or No (False) value stored here in this variable at any one time.

Dim ColName As String

This is the first variable which we will actually be passing onto ETABS through the API. This is the name of the frame property we will be inserting, the API requires that this variable is a “String”. A great feature of the code helper is that the predictive text also prompts us with what the expected variable type is for each function within the ETABS API…

In the snapshot above, we can see that when we type the first parenthesis to input the variables into the PropFrame.SetRectangle function, it tells us the variables it needs and also the corresponding variable type this function requires for defining rectangular sections in ETABS such as:

• Name (Name of the frame property) As String
• MatProp (the material property of the frame) As String
• T3 (length dimension) As Double
• T2 (width dimension) As Double

The next three lines of code provide the remaining variables required for this ETABS function and are now straightforward to understand given the above explanation…

Dim Length As Double

Dim Width As Double

Dim ConcreteGrade As String

Determine the Size of the Data Range Provided by the User

To give the automated tool ultimate flexibility, it is best practice to not limit the inputs received from the user of the software. The next line of code checks to see how many rows are in the data set which the user has typed in (the total number of frame elements to define in ETABS). This code is as follows:

LastRow = Range(“A” & Rows.Count).End(xlUp).Row

The LastRow is the variable we already defined previously, we are now assigning a value to place into this variable “container”, by performing this line of code the value for LastRow will be equivalent to the row number corresponding with the last non-blank cell.

Everything to the right hand side of the equals sign is standard coding syntax for VBA to perform a last non-blank row look-up. You can cut and paste this and use for your specific application, you can change the “A” to whichever column you wish to perform this look-up function on.

Set up a Loop Function to Perform a Task Multiple Times

We have finally arrive at the “automation” part of the code. This section is the keys to the Lamborghini to allow our little script to perform a task many times faster than what we could if we were manually using ETABS, this is what the code looks like as a block below, we will look at it first on the macro scale then go into detail on each line of code afterwards…

This is what is called a “For Loop” in VBA (there are other loop types in VBA however this is the one I use the most and it has not let me down so far). I have highlighted the start and end of the loop so you can visualise how it works.

The first green highlighted line of code sets up the loop, “For” is the syntax to start the loop, “i” is an arbitrary name for our counter variable, you can really make it whatever letter you want. Then we set the first value of our counter for the loop, in this case i = 2.

We are starting the loop at i = 2 because our data within the actual spreadsheet we want to read starts on row 2 (the first row being taken up by our table headings).

Then we define when we want the loop to end (when “i” reaches a defined value), in this case we want to use our previously stored value within the variable LastRow so we want the loop to count from 2 to whatever the value of the LastRow is (if the user has inserted 20 rows of data into the user input table, the For Loop loop will keep spinning until i = 20 is reached).

The second code highlight represents the end of the defined loop, the syntax for this is “Next i”. This is where the loop will end and return back to the beginning of the loop. In simple terms this loop will now perform the following:

• Tells VBA that we want to perform a loop (a task multiple times)
• Sets our loop counter character and starting position (in this case the counter is “i” and its first value is 2)
• The For Loop will then perform all the code within the two highlighted green boxes from top to bottom.
• When it reaches the “Next i” it will add 1 to the current i value then repeat the loop (making the second loop starting with i = 3 then i = 4 and so on).
• Once the loop has reached the defined count limit (in this case however many non-blank cells are in our column “A”), the code compiler will exit the loop and move onto the very next line of code for execution after the “Next i” line

Now lest look inside this loop to see what we are performing multiple times…

Storing Values in Variables for Use in ETABS API Functions

Lets now look at that block of code again however this time we will ignore the “For Loop” to keep it simple…

The first 4 lines of code in this block are simply taking values from the spreadsheet (where the user has inserted the necessary data), then storing it in our variable “containers”.

Lets look at one of them as an example…

ColName = Cells(i, 1).value

Here we are saying that we want to place whatever value which is located in the spreadsheet cell with coordinates of Row i and Column 1 into our variable container which we previously defined as ColName (which was variable type “String“). This is the beauty of our powerful “i” counter, the first loop around as you recall this value starts at 2 so it will take the values of coordinate (2,1), the next time it performs the loop it will take the value at coordinate (3,1) and so on until the loop reaches the end of the data range.

The next mini block of code is our “AI” component (ok it may not be exactly “AI” but it has been said that advanced “AI” is simply in infinite number of “IF” statements). In this section, we want to have our code make a choice based on weather the cell in column 3 (“C”) is blank or not. If the cell is blank, we want to define a circular frame in ETABS, if it is not blank, we want to define a rectangular shape in ETABS (with the value in column 3 (“C”) being its width dimension).

This is achieved through what is called an “IF” statement. The syntax is very similar to the IF function in standard Excel however the layout is just slightly different. In simple terms the syntax works in the following manner:

• If Something Happens… (If IsEmpty(Cells(i, 3).value = True Then)
• Perform This Task (ret = mySapModel.PropFrame.SetCircle(ColName, ConcreteGrade, Length)
• If that thing didn’t happen…(Else)
• Then perform this Task (ret = mySapModel.PropFrame.SetRectangle(ColName, ConcreteGrade, Length, Width)
• Move on to the next line of code (End If)

Performing Tasks in ETABS Using the API

Finally, lets focus on two last specific lines of code (they lie within our “IF” statement), these are the only two lines of code which are actually directly “Talking” to ETABS through use of the API…

ret = mySapModel.PropFrame.SetCircle(ColName, ConcretGrade, Length)

ret = mySapModel.PropFrame.SetRectangle(ColName, ConcreteGrade, Length, Width)

The syntax for preforming functions in ETBAS using the API and allowing you to control its functionality through Excel always starts with the following portion…

ret = mySapModel.

Here are some other functions which work, they all live in the API help file for ETABS, the naming convention is quite intuitive, you can probably guess what the following lines of code may do to your ETABS model…

ret = mySapModel.FrameObj.AddByCoord(

ret = mySapModel.AreaObj.AddByCoord(

ret = mySapModel.Analyze.RunAnalysis

ret = mySapModel.AreaObj.Delete(

ret = mySapModel.Diaphragm.SetDiaphragm(

et = mySapModel.Story.SetStories_2(

ret = mySapModel.SpandrelLabel.SetSpandrel(

ret = mySapModel.Pierlabel.SetPier(

Ok I have the Keys to the Lamborghini, Now How do I Start it?

That’s a great question, no point having a fast car unless you can use it! The most elegant way to do this is to create a macro button within the spreadsheet to give the user something to press when they are ready to execute the code .

To insert a control button you simply need to comlpete teh following…

Developer>>Insert>>Button

Then draw a rectangular shape on the sheet to make the button as large or as small as you like, give it a name then assign your newly created macro to this button.

Once you have it all set-up with the button and some data ready to push to ETABS it should look something like this…

You will notice a big red note I have included on this sheet to help guide the user on how it woks (to hopefully perform the duty successfully with no errors being encountered). In row “D”, where we are defining the concrete grade (or the material property), this material needs to be already defined in your opened ETABS model for this to work and on top of that, the text you insert in column “D” for the material name needs to exactly match the text name of that material which you have already defined within ETABS.

There is a function to define material properties through Excel/VBA/API however that may be perhaps for another discussion.

Hit the button and you are off and running!!!

Conclussion

We have gone through all the necessary things you need to know to go forth and code your own automation tools using VBA to talk to ETABS via its API functionality to supercharge your design workflows and take some of the boring heavy lifting tasks from your day to day life (the possibilities are restricted only by your imagination).

Let us know via a comment below what nifty tools you have made to make your life easier in design and what functions they perform.

As promised, here is a cut and paste friendly piece of code to try this particular tool yourself…

ETABS 2018 Friendly Cut and Paste Example

Sub ColSections()
'Boiler Plate Code to setup the ETABS code and Attach to an opened ETABS session
Dim myETABSObject As cOAPI
Set myETABSObject = Nothing
Dim myHelper As cHelper
Set myHelper = New Helper
Set myETABSObject = GetObject(, "CSI.ETABS.API.ETABSObject")
Dim mySapModel As ETABSv17.cSapModel
Set mySapModel = myETABSObject.SapModel
'Define the Variables for this Task
Dim LastRow As Integer
Dim IsRectangle As Boolean
Dim ColName As String
Dim Length As Double
Dim Width As Double
Dim ConcreteGrade As String
'Tells us what the last row count is for Row "A" of the sheet
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'Loops Through all the available column types and defines them in ETABS
For i = 2 To LastRow
    ColName = Cells(i, 1).Value
    Length = Cells(i, 2).Value
    Width = Cells(i, 3).Value
    ConcreteGrade = Cells(i, 4).Value
    If IsEmpty(Cells(i, 3).Value) = True Then
        ret = mySapModel.PropFrame.SetCircle(ColName, ConcreteGrade, Length)
        Else
        ret = mySapModel.PropFrame.SetRectangle(ColName, ConcreteGrade, Length, Width)
    End If
Next i
End Sub

ETABS 2019 Friendly Cut and Paste Example

Sub ColSections()

‘Boiler Plate Code to setup the ETABS code and Attach to an opened ETABS session
Dim myETABSObject As ETABSv1.cOAPI
Set myETABSObject = Nothing

Dim myHelper As ETABSv1.cHelper
Set myHelper = New ETABSv1.Helper

Set myETABSObject = GetObject(, “CSI.ETABS.API.ETABSObject”)
Dim mySapModel As ETABSv1.cSapModel
Set mySapModel = myETABSObject.SapModel

‘Define the Variables for this Task
Dim LastRow As Integer
Dim IsRectangle As Boolean
Dim ColName As String
Dim Length As Double
Dim Width As Double
Dim ConcreteGrade As String
‘Tells us what the last row count is for Row “A” of the sheet
LastRow = Range(“A” & Rows.Count).End(xlUp).Row

‘Loops Through all the available column types and defines them in ETABS
For i = 2 To LastRow
ColName = Cells(i, 1).Value
Length = Cells(i, 2).Value
Width = Cells(i, 3).Value
ConcreteGrade = Cells(i, 4).Value

If IsEmpty(Cells(i, 3).Value) = True Then
    ret = mySapModel.PropFrame.SetCircle(ColName, ConcreteGrade, Length)

    Else

    ret = mySapModel.PropFrame.SetRectangle(ColName, ConcreteGrade, Length, Width)

End If

Next i

End Sub