Bridge-Optimizing Genetic Algorithm in Excel VBA

In the fall of 2003, my classmates and I were given an assignment in our Bridge Design course, to model the Salginatobel Bridge as a truss and analyse it using the Stiffness Method in Excel (ignoring buckling effects). As an additional challenge, we were to be awarded bonus marks if we could improve upon the efficiency of this, our professor's favourite bridge, by moving the members around.

Many hours of work and 10 versions later (as inidicated by the filename of the download below!), my teammate and I had a functional model of this bridge in Excel, and I decided to take up the professor's challenge by applying a Genetic Algorithm to it. I chose the member positions as the 'genome' and the displacement as the 'unfitness' function. I implemented the GA logic in a few VBA macros that copy-paste genome values into the model's input cells and copy-paste the resulting output back into the genome list. The results were surprising: the GA invariably bunched up all of the columns to the left and right of the bridge! Upon seeing this result, our professor got a little red in the face as he realized that this is indeed the most efficient solution to the model he has asked us to use: truss members by definition only carry axial loads, so a single flat member across the span will show no displacement as it won't be modelled to bend!

Download


© Nicolas Kruchten 2010-2017