Optimization of space trusses (Excel+VBA)

Introduction 

The purpose of this example is the optimization (minimization) of the weight of a space truss using both Microsoft Excel cell functions and VBA (Visual Basic for Applications).

VBA is a versatile tool which extends the capabilities of Microsoft Excel considerably. You can refer to a very simple example, the Sphere function, to see how to set up a spreadsheet with VBA.

In this example, we have created a spreadsheet which solves space trusses. As an application, a truss is optimized which consists of 10 nodes and 25 bars (members). 

 

 

Step 1 : Setting-up the spreadsheet

We have already created the spreadsheet, which can be found here. It looks like this:

Space truss solver with Microsoft Excel and VBA

After modifying certain cells, such as the number of nodes or the number of bars, you can use the "Highlight Cells" button to highlight the cells that need input.

You can hit the "Recalculate" button to analyze the truss based on the present data. 

In order to take into account the restriction on the number of variables imposed by the demo version, we have arranged the variables into five groups of five bars each: bars 1 to 5 are group 1, bars 6 to 10 are group 2, etc. Of course, there is no such restriction in the full version. 

A consistent unit system is used; in this example distances are measured in inches, the forces in kips and the weight in lbs. The supports are indicated per degree-of-freedom (dof); 1 means a constrained dof and nothing or zero stands for a free dof.

The weight of the truss is evaluated in cell AM1. We wish to minimize this value, therefore we set the objective function to take values from this cell. The minimization must take into account certain constraints. In this example, the stress of each bar (cells AJ3 to AJ27) must take values in the range [-10, 10] (ksi). We have included all these constraints in xlOptimizer. Any violation adds the penalty function 1000*X to the weight of a candidate design, where X is the absolute value of the violation. Alternatively, you could easily implement the penalty functions in the spreadsheet; this is actually faster, because it minimizes the data transfer between xlOptimizer and Microsoft Excel.

Step 2 : Minimization

After setting up an algorithm, in this case the Differential Evolution, we can begin the minimization: 

Progress in the minimization of the space truss weight 

That's it!

The files for the example described in this article can be here found here.