Building a Plane Frame Analyzer in Excel VBA using OOP

After publishing my previous structural analysis application here and on YouTube, I received numerous requests asking how to program such a tool from scratch in Excel. This post allows me to pull back the curtain and show you the exact process.

In this tutorial, I will share my approach and the resources used to build this engine. If you saw the previous code, you will notice a significant difference here: I have adopted an Object-Oriented Programming (OOP) approach.

Why OOP? It significantly improves code readability and reusability, and makes troubleshooting complex structural models much easier compared to standard procedural programming.

I will be implementing the Direct Stiffness Method for the analysis of plane frames. Note: This post assumes you are familiar with the theory of the Direct Stiffness Method and basic VBA syntax. We will focus strictly on the implementation logic.

This post is structured as follows:

1. Structural Model Definition

2. Definition of Classes (Properties and Methods)

3. Implementation (The Main Code)

4. Running the Application

---

1. Structural Model Definition

First, we must teach our application what a “structure” actually is. The application needs to understand the concepts of a Node, an Element, a Support, and a Load.

The diagram below summarizes the hierarchy of how our frame is modeled within the application.

Fig1: Structural Model Diagram

For our test case, we will be analyzing the following Plane Frame:

The classes we define in the next section will act as the building blocks (represented by the green boxes in the diagram above) to create this model.

---

2. Definition of Classes

We will build our classes from the “bottom up,” starting with the most basic geometrical components.

(Note: If you are new to VBA Classes, I recommend reviewing the basics of Class Modules before proceeding).

A. Point Class

This fundamental class defines the location (X and Y coordinates) of any object.

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 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

B. Constraint Classes

We need to tell the program which degrees of freedom are fixed (supports) and which are free to move.

DofConstraint (Enum)

Option Explicit

Public Enum Constraint
      Released = 0
      Fixed = 1
End Enum

NodeConstraint Class This class manages the constraints for a specific node (X-translation, Y-translation, and Z-Rotation).

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

' Getters and Setters for dx, dy, and Rotation
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

C. Load Classes

JointLoads Class Handles forces applied directly to nodes.

Option Explicit

Private nxForce As Double
Private nyforce As Double
Private nzmoment As Double

Sub Initialize(Optional Xforce As Double, Optional Yforce As Double, Optional moment As Double)
If Not Xforce = 0 Then nxForce = Xforce
If Not Yforce = 0 Then nyforce = Yforce
If Not moment = 0 Then nzmoment = moment
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

ConcentratedLoad Class Handles point loads applied along an 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

' Getters and Setters for magnitude, positionFromStart, Angle (omitted for brevity)

UDLoad Class Handles Uniformly Distributed Loads.

Option Explicit

Private lstartMagnitude As Double
Private lendmagnitude As Double
Private lstartposit As Double
Private lendposit As Double
Private langle As Double

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

' Getters and Setters (omitted for brevity)

(Note: The ConcentratedLoadList and UniformLoadList classes are simple Collection wrappers used to store multiple loads per element. These are included in the downloadable source file.)

D. Structural Component Classes

Node Class This acts as the “Manager” for a specific point, aggregating location, constraints, and joint loads.

Option Explicit

Private nindex As Long
Private nlocation As Point
Private nConstr As NodeConstraint
Private jload As JointLoads

Sub Initialize(x As Double, y As Double, index As Long)
      nlocation.x = x
      nlocation.y = y
      nindex = index
End Sub

' Properties for Location, Index, Constraint, and JointLoad
' ... (Standard Get/Set implementation)

Private Sub Class_Initialize()
      Set nConstr = New NodeConstraint
      Set jload = New JointLoads
      Set nlocation = New Point
End Sub

Element Class This defines a beam or column, connecting two nodes and holding material/section properties.

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

Sub Initialize(startNode As node, endNode As node, index As Long)
      Set elStartNode = startNode
      Set elendNode = endNode
      elmIndex = index
End Sub

' ... (Standard Properties for Nodes, Section, Material, and Load Lists)

' Calculated Properties
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
      Cos = (elendNode.Location.x - elStartNode.Location.x) / Length
End Property

Public Property Get Sin() As Double
      Sin = (elendNode.Location.y - elStartNode.Location.y) / Length
End Property

' Stiffness Matrix Calculations (Calls Helper Class)
Public Property Get GlobalMatrix() As Variant
      Dim Matutil As MatrixUtilities
      Set Matutil = New MatrixUtilities
      GlobalMatrix = Matutil.ElemMatrixGlobalCoord(Me)
End Property

StructuralModel Class This is the Master Class. It holds the collections of Nodes and Elements and triggers the matrix assembly.

Option Explicit

Private elms As Elements
Private mnodes As Nodes

' Properties for Elements and Nodes collections...

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 Function Reactions() As Variant
      Dim Matutil As MatrixUtilities
      Set Matutil = New MatrixUtilities
      Reactions = Matutil.Reactions(Me)
End Function

3. Implementation

Now, we put it all together. Note the use of a Factory module. This is a helper module I created (included in the download) that simplifies the creation of new objects so our main code remains clean and readable.

Here is how we analyze the frame from Fig 2:

Option Explicit
Public Const PI As Double = 3.14159265358979

Sub Program()
      Dim structure As New StructuralModel
      Dim Length As Double, Reactions() As Variant
     
      ' 1. Define Nodes (X, Y, ID)
      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)
   
      ' 2. Define Elements (StartNode, EndNode, ID)
      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)
    
      ' 3. Set Section Dimensions (Width, Depth)
      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)
      
      ' 4. Set Material Properties (Density, E)
      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)
    
      ' 5. Define Supports
      structure.Nodes.node(1).Constraint.Fixed         ' Fixed Support
      structure.Nodes.node(4).Constraint.FixedY        ' Roller (Fixed Y only)
    
      ' 6. Add Loads
      ' Joint Load at Node 3
      structure.Nodes.node(3).JointLoad = Factory.NewJointLoads(-15, -30, 0)
    
      ' Element Loads (Local Coordinates)
      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)
    
      ' 7. Solve
      Reactions = structure.Reactions()
End Sub

4. Running the Application

After running the Program sub, the application builds the stiffness matrix, inverts it, and solves for the unknowns. You can print the results to the ActiveSheet or the Immediate Window.

Results for this example:

• Rx1: -15.00 kN

• Ry1: 13.34 kN

• M1: 10.87 kNm

• Ry4: 46.67 kN

I hope this walkthrough helps you understand how to structure a complex engineering problem using Object-Oriented VBA. In upcoming posts, I will extend this application to draw the Shear Force and Bending Moment diagrams automatically.

Free – Purchase Checkout Added to cart