After I posted this application here and on YouTube, I got many requests about how to program such application in Excel. I have decided to write a post about the processes involved.
In this post, I will share with you in detail my approach and the resources I have used to build this application. You will notice a difference between the codes on this post and the codes in the previous application. I opted for object oriented programming (OOP) approach in this tutorial to improve the readability, reusability of the codes and to make troubleshooting easy.
I will be implementing the direct stiffness methods of analysis of plane frame using Excel VBA programing language. It is assumed in this post that you are familiar with the direct stiffness method of analysis and VBA programming. I will therefore not discuss the theory of the two concepts in this post.
This post will be structured as below:
- Structural model definition
- Definition of different Classes with properties and methods
- Implementation
- Run the application
1-Structural model definition
We need to tell our application how the structural model is made up. For instance ,the application needs to know what is node, element, support and the load that form our frame. The below diagram summarizes how our frame will be modeled in the application.
The green boxes represent the main classes which we will be creating in the next section to form our model.
2-Definition of different Classes with properties and methods
In this section, we will start by creating the classes from the lowest level to top level and on the nodes Classes. If you are not familiar with VBA Classes ,please check this link to get an idea on it.
-
Point Class
This class helps us to set the node location (X and Y coordinates)
Option Explicit Private my As Double Private mx As Double Public Property Get x() As Double x = mx End Property Public Property Let x(value As Double) mx = value End Property Public Property Get y() As Double y = my End Property Public Property Let y(value As Double) my = value End Property 'Custom initializer to allow creation of new instance of this class with 'parameters Sub Initialize(x As Double, y As Double) Me.x = x Me.y = y End Sub Public Function Distance(p1 As Point) As Double Dim dx As Double, dy As Double dx = Me.x - p1.x dy = Me.y - p1.y Distance = (dx ^ 2 + dy ^ 2) ^ 0.5 End Function
-
DofConstraint Class
Here we are creating an Enum to set the constraint type. A degree of freedom can be fixed or released.
Option Explicit Public Enum Constraint Released = 0 Fixed = 1 End Enum
-
NodeConstraint Class
In this class, we are using the DofConstraint class to define the types of node constraints.
Option Explicit Private Xtransl As Constraint Private Ytransl As Constraint Private ZRotation As Constraint Public Function Fixed() As NodeConstraint Xtransl = Constraint.Fixed Ytransl = Constraint.Fixed ZRotation = Constraint.Fixed Set Fixed = Me End Function Public Function Released() As NodeConstraint Xtransl = Constraint.Released Ytransl = Constraint.Released ZRotation = Constraint.Released Set Released = Me End Function Public Function FixedX() As NodeConstraint Xtransl = Constraint.Fixed Ytransl = Constraint.Released ZRotation = Constraint.Released Set FixedX = Me End Function Public Function FixedY() As NodeConstraint Xtransl = Constraint.Released Ytransl = Constraint.Fixed ZRotation = Constraint.Released Set FixedY = Me End Function Public Function FixedYX() As NodeConstraint Xtransl = Constraint.Fixed Ytransl = Constraint.Fixed ZRotation = Constraint.Released Set FixedYX = Me End Function Public Property Get dx() As Constraint dx = Xtransl End Property Public Property Let dx(value As Constraint) Xtransl = value End Property Public Property Get dy() As Constraint dy = Ytransl End Property Public Property Let dy(value As Constraint) Ytransl = value End Property Public Property Get Rotation() As Constraint Rotation = ZRotation End Property Public Property Let Rotation(value As Constraint) ZRotation = value End Property Private Sub Class_Initialize() Xtransl = Constraint.Released Ytransl = Constraint.Released ZRotation = Constraint.Released End Sub
-
JointLoads Class
This class allow us to set the Joint loads at each node.
Option Explicit Private nxForce As Double Private nyforce As Double Private nzmoment As Double 'Custom class initializer to allow creation of new instance of this class with 'parameters Sub Initialize(Optional Xforce As Double, Optional Yforce As Double, Optional moment As Double) If Not Xforce = 0 Then nxForce = Xforce End If If Not Yforce = 0 Then nyforce = Yforce End If If Not moment = 0 Then nzmoment = moment End If End Sub Public Property Get Xforce() As Double Xforce = nxForce End Property Public Property Let Xforce(value As Double) nxForce = value End Property Public Property Get Yforce() As Double Yforce = nyforce End Property Public Property Let Yforce(value As Double) nyforce = value End Property Public Property Get moment() As Double moment = nzmoment End Property Public Property Let moment(value As Double) nzmoment = value End Property
-
Node Class
This class will implement all the above classes.
Option Explicit Private nindex As Long Private nlocation As Point Private nConstr As NodeConstraint Private jload As JointLoads 'Custom class initializer to allow creation of new instance of this class with 'parameters Sub Initialize(x As Double, y As Double, index As Long) nlocation.x = x nlocation.y = y nindex = index End Sub Public Property Get Location() As Point Set Location = nlocation End Property Public Property Get index() As Long index = nindex End Property Public Property Let index(value As Long) nindex = value End Property Public Property Get Constraint() As NodeConstraint Set Constraint = nConstr End Property Public Property Let Constraint(value As NodeConstraint) nConstr = value End Property Public Property Get JointLoad() As JointLoads Set JointLoad = jload End Property Public Property Let JointLoad(value As JointLoads) Set jload = value End Property Private Sub Class_Initialize() Set nConstr = New NodeConstraint Set jload = New JointLoads Set nlocation = New Point End Sub Private Sub Class_Terminate() Set jload = Nothing Set nConstr = Nothing Set nlocation = Nothing End Sub
-
Nodes Class
This is a collection of nodes class. All the nodes are grouped in this class.
Option Explicit Private Coll As Collection Private Sub Class_Initialize() Set Coll = New Collection End Sub Public Sub AddNodes(nde As node) Coll.Add nde, "N" & nde.index End Sub Public Sub RemoveNodes(IndexOrNode As Variant) If TypeOf IndexOrNode Is node Then Coll.Remove IndexOrNode ElseIf IsNumeric(IndexOrNode) Then Coll.Remove "N" & IndexOrNode End If End Sub Public Function NodesCount() As Long NodesCount = Coll.Count End Function Public Property Get node(index As Long) As node Set node = Coll.Item("N" & index) End Property Private Sub Class_Terminate() Set Coll = Nothing End Sub
-
ConcentratedLoads Class
A concentrated load class applicable to each element.
Option Explicit Private lmagnitude As Double Private posit As Double Private langle As Double Sub Initialize(magnitude As Double, positionfromStart As Double, Angle As Double) lmagnitude = magnitude posit = positionfromStart langle = Angle End Sub Public Property Get magnitude() As Double magnitude = lmagnitude End Property Public Property Let magnitude(value As Double) lmagnitude = value End Property Public Property Get positionfromStart() As Double positionfromStart = posit End Property Public Property Let positionfromStart(value As Double) posit = value End Property Public Property Get Angle() As Double Angle = langle End Property Public Property Let Angle(value As Double) langle = value End Property
-
ConcentratedList Class
This class allow us to have more that one point load for each element of the model.
Option Explicit Private List As Collection Private Sub Class_Initialize() Set List = New Collection End Sub Public Sub Add(pointload As ConcentratedLoad) Dim i As Long i = List.Count + 1 List.Add pointload, "PL" & i End Sub Public Property Get Count() As Long Count = List.Count End Property Public Sub Remove(IndexOrElement As Variant) If TypeOf IndexOrElement Is ConcentratedLoad Then List.Remove IndexOrElement ElseIf IsNumeric(IndexOrElement) Then List.Remove "PL" & IndexOrElement End If End Sub Public Property Get GetPload(index As Long) As ConcentratedLoad Set GetPload = List.Item("PL" & index) End Property
-
UDLoad Class
A class to handle uniformly distributed load.
Option Explicit Private lstartMagnitude As Double Private lendmagnitude As Double Private lstartposit As Double Private lendposit As Double Private langle As Double 'Custom class initializer to allow creation of new instance of this class with 'parameters Sub Initialize(Start_magnitude As Double, Startposition As Double, End_magnitude As Double, EndPosition As Double, Angle As Double) lstartMagnitude = Start_magnitude lstartposit = Startposition lendmagnitude = End_magnitude lendposit = EndPosition langle = Angle End Sub Public Property Get StartMagnitude() As Double StartMagnitude = lstartMagnitude End Property Public Property Let StartMagnitude(value As Double) lstartMagnitude = value End Property Public Property Get EndMagnitude() As Double EndMagnitude = lendmagnitude End Property Public Property Let EndMagnitude(value As Double) lendmagnitude = value End Property Public Property Get Startposition() As Double Startposition = lstartposit End Property Public Property Let Startposition(value As Double) lstartposit = value End Property Public Property Get EndPosition() As Double EndPosition = lendposit End Property Public Property Let EndPosition(value As Double) lendposit = value End Property Public Property Get Angle() As Double Angle = langle End Property Public Property Let Angle(value As Double) langle = value End Property
-
UniformloadList Class
This class allow us to have more that one uniform load for each element of the model.
Option Explicit Private List As Collection Private Sub Class_Initialize() Set List = New Collection End Sub Public Sub Add(Udload As Udload) Dim i As Long i = List.Count + 1 List.Add Udload, "UDL" & i End Sub Public Property Get Count() As Long Count = List.Count End Property Public Sub Remove(IndexOrElement As Variant) If TypeOf IndexOrElement Is Udload Then List.Remove IndexOrElement ElseIf IsNumeric(IndexOrElement) Then List.Remove "UDL" & IndexOrElement End If End Sub Public Property Get GetUdload(index As Long) As Udload Set GetUdload = List.Item("UDL" & index) End Property
-
ElementSection Class
This class allow us the define the section size of the element. For this example, we are only considering a rectangular section.
Option Explicit Private elHeight As Double Private elWidth As Double 'Custom class initializer to allow creation of new instance of this class with 'parameters Sub Initialize(Width As Double, Height As Double) elHeight = Height elWidth = Width End Sub Public Property Get Height() As Double Height = elHeight End Property Public Property Let Height(value As Double) elHeight = value End Property Public Property Get Width() As Double Width = elWidth End Property Public Property Let Width(value As Double) elWidth = value End Property Public Property Get Area() As Double Area = elWidth * elHeight End Property Public Property Get I() As Double I = elWidth * elHeight ^ 3 / 12 End Property
-
MaterialProperties Class
In this class, the define the material properties to each element.
Option Explicit Private eldensity As Double Private elYoungmodulus As Double 'Custom class initializer to allow creation of new instance of this class with 'parameters Sub Initialize(density As Double, E As Double) eldensity = density elYoungmodulus = E End Sub Public Property Get density() As Double density = eldensity End Property Public Property Let density(value As Double) eldensity = value End Property Public Property Get YoungModulus() As Double YoungModulus = elYoungmodulus End Property Public Property Let YoungModulus(value As Double) elYoungmodulus = value End Property
-
Element Class
This class defines the data of an element.
Option Explicit Private elStartNode As node Private elendNode As node Private elmIndex As Long Private elmsection As ElementSection Private elemMaterialPro As MaterialProperties Private elmuldload As UniformloadList Private elmconcentratedload As ConcentratedloadList 'Custom class initializer to allow creation of new instance of this class with 'parameters Sub Initialize(startNode As node, endNode As node, index As Long) Set elStartNode = startNode Set elendNode = endNode elmIndex = index End Sub Public Property Get index() As Long index = elmIndex End Property Public Property Let index(value As Long) elmIndex = value End Property Public Property Get startNode() As node Set startNode = elStartNode End Property Public Property Let startNode(value As node) Set elStartNode = value End Property Public Property Get endNode() As node Set endNode = elendNode End Property Public Property Let endNode(value As node) Set elendNode = value End Property Public Property Get UniformLoad() As UniformloadList Set UniformLoad = elmuldload End Property Public Property Let UniformLoad(value As UniformloadList) Set elmuldload = value End Property Public Property Get ConcentratedLoad() As ConcentratedloadList Set ConcentratedLoad = elmconcentratedload End Property Public Property Let ConcentratedLoad(value As ConcentratedloadList) Set elmconcentratedload = value End Property Public Property Get Section() As ElementSection Set Section = elmsection End Property Public Property Let Section(value As ElementSection) Set elmsection = value End Property Public Property Get MaterialProperties() As MaterialProperties Set MaterialProperties = elemMaterialPro End Property Public Property Let MaterialProperties(value As MaterialProperties) Set elemMaterialPro = value End Property Public Property Get Length() As Double Dim dx As Double, dy As Double dx = elendNode.Location.x - elStartNode.Location.x dy = elendNode.Location.y - elStartNode.Location.y Length = (dx ^ 2 + dy ^ 2) ^ 0.5 End Property Public Property Get Cos() As Double Dim dx As Double, dy As Double dx = elendNode.Location.x - elStartNode.Location.x dy = elendNode.Location.y - elStartNode.Location.y Cos = dx / Length End Property Public Property Get Sin() As Double Dim dx As Double, dy As Double dx = elendNode.Location.x - elStartNode.Location.x dy = elendNode.Location.y - elStartNode.Location.y Sin = dy / Length End Property Private Sub Class_Initialize() Set elmuldload = New UniformloadList Set elmconcentratedload = New ConcentratedloadList End Sub Private Sub Class_Terminate() Set elmconcentratedload = Nothing Set elmsection = Nothing Set elemMaterialPro = Nothing Set elmuldload = Nothing End Sub Public Property Get DofIndexing() As Variant Dim Matutil As MatrixUtilities Set Matutil = New MatrixUtilities DofIndexing = Matutil.DofIndexing(Me) End Property Public Property Get TransformationMatrix() As Variant Dim Matutil As MatrixUtilities Set Matutil = New MatrixUtilities TransformationMatrix = Matutil.ElemTransformation(Me) End Property Public Property Get LocalMatrix() As Variant Dim Matutil As MatrixUtilities Set Matutil = New MatrixUtilities LocalMatrix = Matutil.ElemMatrixLocalCoord(Me) End Property Public Property Get GlobalMatrix() As Variant Dim Matutil As MatrixUtilities Set Matutil = New MatrixUtilities GlobalMatrix = Matutil.ElemMatrixGlobalCoord(Me) End Property
-
Elements Class
This class allow us to group and manage the elements of the model.
Option Explicit Private Coll As Collection Private Sub Class_Initialize() Set Coll = New Collection End Sub Public Sub AddElement(elmt As Element) Coll.Add elmt, "E" & elmt.index End Sub Public Sub RemoveElement(IndexOrElement As Variant) If TypeOf IndexOrElement Is Element Then Coll.Remove IndexOrElement ElseIf IsNumeric(IndexOrElement) Then Coll.Remove "E" & IndexOrElement End If End Sub Public Function Count() As Long Count = Coll.Count End Function Public Property Get Element(index As Long) As Element Set Element = Coll.Item("E" & index) End Property Private Sub Class_Terminate() Set Coll = Nothing End Sub
-
StructuralModel Class
This is the main class of our application. As indicated by the diagram (Fig1), it implements all the classes defined above and all the instructions go from here.
Option Explicit Private elms As Elements Private mnodes As Nodes Public Property Get Elements() As Elements Set Elements = elms End Property Public Property Let Elements(value As Elements) Set elms = value End Property Public Property Get Nodes() As Nodes Set Nodes = mnodes End Property Public Property Let Nodes(value As Nodes) Set mnodes = value End Property Private Sub Class_Initialize() Set elms = New Elements Set mnodes = New Nodes End Sub Public Property Get GAssemblyMatrix() As Variant Dim Matutil As MatrixUtilities Set Matutil = New MatrixUtilities GAssemblyMatrix = Matutil.AssemblyMatrixBuilder(Me) End Property Public Property Get Freedof() As Long Dim i As Long, dof As Long dof = 0 For i = 1 To Me.Nodes.NodesCount If Me.Nodes.node(i).Constraint.dx = Released Then dof = dof + 1 End If If Me.Nodes.node(i).Constraint.dy = Released Then dof = dof + 1 End If If Me.Nodes.node(i).Constraint.Rotation = Released Then dof = dof + 1 End If Next i Freedof = dof End Property Public Property Get Restraindof() As Long Dim i As Long, rsdof As Long rsdof = 0 For i = 1 To Me.Nodes.NodesCount If Me.Nodes.node(i).Constraint.dx = Fixed Then rsdof = rsdof + 1 End If If Me.Nodes.node(i).Constraint.dy = Fixed Then rsdof = rsdof + 1 End If If Me.Nodes.node(i).Constraint.Rotation = Fixed Then rsdof = rsdof + 1 End If Next i Restraindof = rsdof End Property Public Property Get JointLoads() As Variant Dim Matutil As MatrixUtilities Set Matutil = New MatrixUtilities JointLoads = Matutil.JointLoads(Me) End Property Public Function Reactions() As Variant Dim Matutil As MatrixUtilities Set Matutil = New MatrixUtilities Reactions = Matutil.Reactions(Me) End Function
-
MatrixUtilities Class
This contents the Functions used by various classes of our application. I will not show it here due to its length, but you will find it in the sample file attached. You will find the below functions in this class.
- ElemTransformation : to calculate the Element transformation matrix
- ElemMatrixGlobalCoord: to calculate the element stiffness matrix at global coordinate
- ElemMatrixLocalCoord: to calculate the element stiffness matrix at local coordinate
- DofIndexing : To count and assign an index to each degree of freedom at each node of the element.
- FreeDOfIndexing: It stores the index number of the free degree of freedoms of the model.
- RestrainDOfIndexing: It stores the index number of the restrained degree of freedoms of the model.
- AssemblyMatrixBuilder: this function is used to build the assembly matrix of the model.
- Rections : to calculate the reactions at the supports
- Etc...
-
Factory Module
This module contents function helpers to create new instance of our defined classes. You will find it in the sample file attached.
3-Implementation
In this section, we will used the defined classes to analysis the frame shown on Fig2.
Note: Element's loads are applied at local coordinate system.
Option Explicit Public Const PI As Double = 3.14159265358979 Sub Program() Dim structure As New StructuralModel Dim Length As Double, Reactions() As Variant ''Add nodes structure.Nodes.AddNodes Factory.NewNode(2, 2, 1) structure.Nodes.AddNodes Factory.NewNode(2, 7, 2) structure.Nodes.AddNodes Factory.NewNode(7, 7, 3) structure.Nodes.AddNodes Factory.NewNode(7, 2, 4) '' Add elements structure.Elements.AddElement Factory.NewElement(structure.Nodes.node(1), structure.Nodes.node(2), 1) structure.Elements.AddElement Factory.NewElement(structure.Nodes.node(2), structure.Nodes.node(3), 2) structure.Elements.AddElement Factory.NewElement(structure.Nodes.node(3), structure.Nodes.node(4), 3) ''Set section dimension structure.Elements.Element(1).Section = Factory.NewElementSection(0.3, 0.6) structure.Elements.Element(2).Section = Factory.NewElementSection(0.3, 0.6) structure.Elements.Element(3).Section = Factory.NewElementSection(0.3, 0.6) ''Set material properties structure.Elements.Element(1).MaterialProperties = Factory.NewMaterialPro(2300, 200000000) structure.Elements.Element(2).MaterialProperties = Factory.NewMaterialPro(2300, 200000000) structure.Elements.Element(3).MaterialProperties = Factory.NewMaterialPro(2300, 200000000) '' define supports structure.Nodes.node(1).Constraint.Fixed structure.Nodes.node(4).Constraint.FixedY ''Add joint loads structure.Nodes.node(3).JointLoad = Factory.NewJointLoads(-15, -30, 0) ''Add Element load structure.Elements.Element(2).ConcentratedLoad.Add Factory.NewConcentratedLoad(-10, 2, 90) structure.Elements.Element(2).ConcentratedLoad.Add Factory.NewConcentratedLoad(-20, 4, 90) structure.Elements.Element(1).UniformLoad.Add Factory.NewUniformLoad(-5, 0, -10, 2, 90) structure.Elements.Element(1).UniformLoad.Add Factory.NewUniformLoad(-5, 2, -5, 5, 90) Reactions = structure.Reactions() End Sub
4-Run the application
After running the application with the applied loading, you will get the below reactions on the activesheet:
Rx1=-15 , Ry1=13.34, M1=10.87 ,Ry4=46.67
I hope you will find this post useful and will be able to implement it on your own to solve your structural problems.
In upcoming posts, i will extend this application to draw the different diagrams and provide more detailed results.
- Version 1.0.0
- Download 37587
- File Size 105 KB
- File Count 1
- Create Date March 20, 2021
- Last Updated March 21, 2021
File | Action |
---|---|
STIFFNESS MATRIX ANALYSIS OF FRAME.zip | Download |
If you believe my tools are helping you and would like to support me, please use the below button to donate.
I would appreciate it very much if you can e-mail me an instruction on how to prepare input file and run your program on how to analyze 2-D frame . Thanks and best wishes for a happy new year.
my e-mail is fanous@iastate.edu