How to Analyze 2D Frame with Excel VBA Programming

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:

1. Structural model definition
2. Definition of different Classes with properties and methods
3. Implementation
4. 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.