Optimization of SAP2000 models

in Mechanics

Introduction 

The purpose of this example is to demonstrate the use of the well-known CSi SAP2000 structural analysis program within Microsoft Excel and the optimization of a structure using xlOptimizer. This process is possible since SAP2000 exposes a full API (Application Programming Interface) which can be used by VBA. Optimization with xlOptimizer allows for the selection of the optimum frame sections for each member group in the model.

It is assumed that the structural model is complete and checked for errors (nodes, members, connectivity, supports, loads, design options, etc). Also, the frame members are grouped into meaningful Groups e.g., corner columns, exterior columns, interior columns, beams, x-braces, etc. xlOptimizer will pick the optimum section for each one of these groups. 

Step 0 : Setting-up the spreadsheet

We have already created the spreadsheet for you. It can be used for any structural model created by SAP2000. The following however need to be noted:

  • A maximum number of 10 groups is set up within the worksheet, which is consistent with the restrictions of the demo version of xlOptimizer (up to 10 variables). This can be easily increased in the worksheet, and more groups can be taken into account with the professional edition of xlOptimizer.
  • The test model provided herein is a toy model of a steel structure and the design is meant to be carried out according to EuroCode 3. This is not a restriction, but minor changes in the worksheet and SAP2000 model need to be done for other configurations.
  • The worksheet is set up to work with version 19 of SAP2000. Minor changes are needed to work with newer versions.
  • The objective to be maximized is the sum of capacity ratios, with appropriate penalty functions which are applied when the capacity ratio is more than 95%, or when errors or warnings are returned from SAP2000. This may not be suitable for all cases, in which case proper modifications must be made in the VBA code.
  • This spreadsheet is not meant to be an optimization tool that works out-of-the-box, but rather a demonstration of how to implement a link with applications that offer API.

Spreadsheet for linking Microsoft Excel and SAP2000

 

Step 1 : Open SAP2000 model

Click Open SAP model. A standard dialog box appears for the selection of the SAP2000 model file (*.sdb). If SAP2000 is not currently running, it will be launched and the model will be loaded. If not, the model will be loaded in the active instance of SAP2000. 

 

Step 3 : Load model data

Press Load model data to load the model data, including the Groups and the available Sections. If Initialize table with eligible sections is checked, the table on the right will be filled with X's (and drawn in a green background), marking that the specific section is eligible for the specific group. You should delete improper combinations as this will improve the performance of optimization (i.e. the size of the design space will be reduced). For instance, you should erase the selection for HEM steel sections for x-braces, as this is not a meaningful choice.

Delete the contents of the cells, or use a single "X" to mark eligibility. When you are ready, press Set optimization and all variables in xlOptimizer will be set automatically. You can check the available sections per group in the Optimization sheet, although you should not alter anything there.

 

Step 4 : Setting up the scenaria & performing optimization

By default, a single scenario with Differential Evolution is set-up to run. You can modify the scenaria using xlOptimizer's menu. You should not, however, change anything in the Objectives, Variables, Constraints and Execution. 

When you are ready, you can then proceed with the single-objective optimization in xlOptimizer.

 

Step 5 : Closing SAP

When you have finished, you can close SAP2000 by using the Close SAP button. Make sure to save the model first.

 

Step 6 : Other options

The Other options include:

  • Assign selection, analyze and get results: This applies the selection in the yellow cells of the Optimization sheet in the structural model (integers from 1 to N, where N is the number of eligible sections for the specific group), runs the design in SAP2000 and gets the results. In essence, it is the same routine that is called by xlOptimizer when evaluating a candidate solution.
  • Erase all fields: Clears the spreadsheet.

The file for this example can be downloaded here. Note that it is saved a macro enabled book with the extension .xlsm. You need to enable macros to run it.