Trusses calculations by using Visual Basic (VBA)
This project aims to perform trusses calculations. It contains calculations based on the Visual Basic (VBA)
calculation code in Excel, of which we will create codes to calculate the stiffness of the given elements, the
displacements and the reactions and the stresses in each element..
The project concerns trusses calculations for which we will create macros in Excel with VBA code to deal with
general problems regardless of the number of nodes and elements in given truss. To define a truss, you need
to know the number of nodes and their coordinates as well as the number of elements and their nodes, you
also need to know the material and section of each element. The goal at the end is to calculate the node
displacements, reactions, and stresses in each element. Firstly, we will analytically treat a given truss with 4
nodes and 6 elements and two different sections (figure 2). A truss contains a number of elements (bars) which
are connected together with joints, each element has two nodes. Solving a truss problem involves calculating the
displacements of the nodes, the reactions, the deformations, and the stresses of the elements. Firstly, it is necessary to
determine the stiffness matrix of each element in a local then global reference frame . It is necessary to group the
elementary matrices of the elements in a global matrix of the truss. Figure 1 shows how we can calculate the stiffness
matrix of each element. with T, k and k̂are: the transformation matrix, the global stiffness matrix and the local
stiffness matrix.
k 0 - k 0 l2 lm − l2 − lm
l m 0
0
− m 0 0 0 0
0 EA lm m2 − lm − m 2
k̂ =
l 0 k = T k̂ T =
T
T=
0 0 l m - k 0 k 0 L − l 2 − lm l2 lm
−m l − lm − m m 2
2
0 0 0 0 0 0 lm
l = cos
m = sin
Figure 1: The transformation matrix, the global stiffness matrix and the local stiffness matrix.
Figure 2 shows the given truss with 4 nodes and 6 elements, with pin at node 1 and roller at node 2, a force F at node 3
and -F at node 4.
Figure 2: the truss with the data necessary for the calculation
We have to use the coordinates of the nodes to calculate the length and orientation of each element, we will then
calculate its stiffness (EA/L). Table 1 contains the needed data to calculate the stiffness matrix of each element.
node node
#Element I J prop L (m) cos sin EA/L
1 1 2 1 1 -1 0 2.00E+06
2 2 3 1 1 0 1 2.00E+06
3 3 4 1 1 -1 0 2.00E+06
4 4 1 1 1 0 1 2.00E+06
5 2 4 2 1,41 0.707 -0.707 2,83E+06
6 1 3 2 1,41 0.707 0.707 2,83E+06
Table 1: information needed to calculate the matrix k of each element
𝐸𝐴
The matrices k i are the stiffness matrices of elements 1, 2, 3, 4, 5 and 6 with EA/L = 2x106 et = 1,4142x106 .
√2 𝐴
1 0 −1 0 1 −1 −1 1
k 1 = k 3 = EA/L [ 0 0 0 0] k 5 = 𝐸𝐴/(√2 𝐴) [−1 1 1 −1 ]
−1 0 1 0 −1 1 1 −1
0 0 0 0 1 −1 −1 1
0 0 0 0 1 1 −1 −1
k 2 = k 4 = EA/L [ 0 1 0 −1] k 6 = 𝐸𝐴/(√2 𝐴) [ 1 1 −1 −1]
0 0 0 0 −1 −1 1 1
0 −1 0 1 −1 −1 1 1
The following matrix represents the global stiffness matrix of the truss:
U1 V1 U2 V2 U3 V3 U4 V4
1,707 0,707 -1 0 -0,707 -0,707 0 0 U1
0,707 1,707 0 0 -0,707 -0,707 0 -1 V1
Kglob. -1+ 0+ 1,707 -0,707 0 0 -0,707+ 0,707+ U2
=EA/L 0+ 0+ -0,707 1,707 0 -1 0,707+ -0,707 V2
-0,707 -0,707 0 0 1,707 0,707 -1 0 U3
-0,707 -0,707 0 -1 O,707 1,707 0 0 V3
0 0 -0,707+ 0,707+ -1 0 1,707 -0,707 U4
0 -1 0,707+ -0,707 0 0 -0,707 1,707 V4
We know that Kglob x {𝑈} = {𝐹 }
With {U} is the vector od displacment = {𝑈1, 𝑉1, 𝑈2, 𝑉2, 𝑈3, 𝑉3, 𝑈4, 𝑉4}𝑇 = {0, 0, 𝑈2, 0, 𝑈3, 𝑉3, 𝑈4, 𝑉4, }𝑇
and {F} is the vector of external forces and reactions = {𝐹1𝑥, 𝐹1𝑦, 𝐹2𝑥, 𝐹2𝑦, 𝐹3𝑥, 𝐹3𝑦, 𝐹4𝑥, 𝐹4𝑦} 𝑇
= {𝑅1𝑥, 𝑅1𝑦, 0, 𝑅2𝑦, 1000 𝑁, 0, −1000 𝑁, 0}𝑇
Because U1 V1 U2 V2 , V3 U4 V4
U1=V1=V2=0 1,707 0,707 -1 0 -0,707 -0,707 0 0 U1
we can 0,707 1,707 0 0 -0,707 -0,707 0 -1 V1
remove row -1 0 1,707 -0,707 0 0 -0,707 0,707 U2
and
0 0 -0,707 1,707 0 -1 0,707 -0,707 V2
columns: 1, 2
-0,707 -0,707 0 0 1,707 0,707 -1 0 U3
and 4.
-0,707 -0,707 0 -1 0,707 1,707 0 0 V3
0 0 -0,707 0,707 -1 0 1,707 -0,707 U4
0 -1 0,707 -0,707 0 0 -0,707 1,707 V4
Kglob.
=EA/L
Now we will reduce the system to 5 equations:
1,707 0 0 -0,707 0,707 U2 0
0 1,707 0,707 -1 0 U3 1000 N
2x106 x 0 0,707 1,707 0 0 X V3 = 0
-0,707 -1 0 1,707 -0,707 U4 -1000 N
0,707 0 0 -0,707 1,707 V4 0
Kred
Kred. is the reduced matrix.
We will now determine the displacements U2, U3, V3, U4 and V4 using the following equation:
−1 { }
{𝑈} = 𝐾𝑟𝑒𝑑 𝐹
−1
with 𝐾𝑟𝑒𝑑 is e inverse matrix of Kred
U2 4.2679E-07 1.7679E-07 -7.3214E-08 0.00000025 -7.3214E-08 0
U3 1.7679E-07 7.8045E-07 -3.2321E-07 6.0366E-07 1.7679E-07 1000
V3 = -7.3214E-08 -3.2321E-07 4.2679E-07 -0.00000025 -7.3214E-08 0
X
U4 -
0.00000025 6.0366E-07 -0.00000025 8.5366E-07 0.00000025 1000
V4 -7.3214E-08 1.7679E-07 -7.3214E-08 0.00000025 4.2679E-07 0
So :
U2 -7.3214E-05 m -0,073 mm
U3 = 0.00017679 m 0,177 mm
V3 -7.3214E-05 m -0,073 mm
U4 -0.00025 m -0,25 mm
=
V4 -7.3214E-05 m -0,073 mm
For the reactions :
0
0
-7.3214E-05 m
R1x =2x10 6 x 1,707 0,707 -1 0 -0,707 -0,707 0 0 0
R1y 0,707 1,707 0 0 -0,707 -0,707 0 -1 X 17.679E-5 m
R2y 0 0 -0,707 1,707 0 -1 0,707 -0,707 -7.3214E-05 m
-25E-5 m
-7.3214E-05 m
R1x = 0, R1y= 146,5 N et R2y=0 N ,
For the stress in each element we use the following equation:
= Eε =
E
L
( )
d̂ 2x − d̂1x = − l
E
L
−m l md
ui
With d = (vi
uj ), ui, vi, uj, vj are the horizontal and vertical displacements of the nodes of each element.
vj
𝜎1 14,6
𝜎2 −14,6
𝜎3 85,4
𝜎4 = 14,6
𝑀𝑃𝑎
𝜎5 −10,4
{𝜎6 } { 10,4 }
To create a program using VBA code that will be able to solve trusses problems we will follow the following procedure:
The procedure to follow to create VBA codes capable of calculating the stresses in the elements and tracing the
deformed trusses while going through the calculations of the displacements of the nodes:
• Firstly, we will create codes to count the numbers of the given nodes, elements and materials which will be
noted in the code ndn, nde and ndm.
• Secondly, we will proceed to calculate the lengths, directions (cosine and sine) and stiffness of each element.
This information is useful for determining the stiffness matrix of the truss.
• Thirdly, and after having obtained the displacements of the nodes we can calculate the stresses of the elements.
• Finally, we will draw the deformed truss.
The first part of the VBA code will be used to count the numbers of the nodes entered in column 4 and the numbers of
the elements entered in column 16 and the numbers of the materials entered in column 26 of the blank Excel file
provided. The number of nodes will be displayed in cell (1, 4), the numbers of elements will be displayed in cell (1, 16)
and the numbers of materials in cell (1, 26).
Figure 2 shows the cells where the number of nodes, the number of elements and the number of materials are displayed
(here it is a truss with 4 nodes and 6 elements).
Figure 2 : number of nodes, elements and materials
We have to use the number of nodes, elements and materials, to calculate by using VBA code the length, cos, sin and
stiffness (EA/L) of each element (figure 3). The calculation is activated with the button (Calculation L, cos, sin and raid.).
in this part we created a loop which will pass through each element to find out its nodes then write their coordinates
from the node table.
Figure 3 : The button to calculate L, cos, sin and stiffness.
The calculation of stresses in the elements is based on the calculation of the displacements of the nodes for each
element. Figure 4 shows the button and the calculated stresses in column 24. The calculation in this part is completed
via the loops and conditions to find the displacements of each node and the sines and cosines of each element.
Figure 4 : The Button to compute the stresses.
To be able to see the truss before and after deformation, we created code which takes into account the coordinates of
the nodes and their movements. We carried out this step using the “addline” function in VBA while also using two
different styles to present the lines of the truss before and after the deformation. To better see the truss, we chose to
put it in another sheet which will be called “Trace” (Figure 5). To check the code which corresponds to the visualization
of the deformation of the truss we tested it with a second truss of 3 nodes and three elements (figure 6)
Figure 5: the truss of 4 nodes and 6 elements before and after deformation
(b)
(a)
Figure 6: (a): Given truss, (b):Truss (original and deformed)
Through this project we learned how we can use VBA code to solve a real structural problem. With a generic code,
capable of taking into account a large number of nodes and elements, we had the opportunity to fully understand the
steps of the calculations necessary to solve a truss problem,
