Automate Structural Analysis in Excel: Simply Supported Beam with Point Load

Automate Structural Analysis in Excel: Simply Supported Beam with Point Load

Following a request from a viewer on my YouTube channel, I’m sharing how you can use VBA (Visual Basic for Applications) to automate structural analysis tasks directly in Excel.

In this post, we will build a tool to analyze a Simply Supported Beam subjected to a single Point Load. We will calculate reactions, shear forces, and bending moments, and even plot the diagrams automatically.

The Engineering Formulas

1. Support Reactions

Before writing the code, let’s review the fundamental equations we are automating.

R_A = P.(L-a)/L
R_B = P – R_A

Where:

• P= Point Load

  
  

• L = Total Length

  
  

• a= Distance of load from support A

  
  

2. Bending Moment ($M_x$)

• If x < a:

  
  

  M_x = R_A . x

  
  

• If x >= a:

  M_x = R_A .x – P .(x – a)

  
  

3. Shear Force (V_x)

• If x < a:

  V_x = R_A

  
  

• If x >= a:

  V_x = R_A – P

  
  

---

Step 1: Excel Sheet Setup

To make the code work immediately, set up your Excel sheet with the following Inputs and Outputs.

Type	Cell	Description	Example Value
Input	D15	Point Load ($P$)	50
Input	D16	Distance from Left ($a$)	2
Input	D17	Beam Length ($L$)	5
Input	D19	Diagram Scale (for chart visibility)	1
Output	H18	Reaction A ($R_A$)	(Calculated)
Output	H19	Reaction B ($R_B$)	(Calculated)
Output	I16	Max Moment Value	(Calculated)

Note: The detailed results (X, Moment, Shear) will be printed in columns O, P, and Q starting at row 4.

---

Step 2: The VBA Code

Copy and paste the code below into a standard Module in your VBA Editor.

Part A: The Calculation Functions

These functions handle the core structural math.

Option Explicit

' Calculate Reaction at Support A (Left)
Function Reaction1_PointLoad_Cal(Pload As Double, Dist As Double, Length As Double) As Double
    Reaction1_PointLoad_Cal = Pload * (Length - Dist) / Length
End Function

' Calculate Reaction at Support B (Right)
Function Reaction2_PointLoad_Cal(Pload As Double, Dist As Double, Length As Double) As Double
     Reaction2_PointLoad_Cal = Pload - Reaction1_PointLoad_Cal(Pload, Dist, Length)
End Function

' Calculate Bending Moment at specific point X
Function BendingMoment_PointLoad_Cal(Pload As Double, x As Double, Dist As Double, Reaction1 As Double) As Double
    If x <= Dist Then
         BendingMoment_PointLoad_Cal = Reaction1 * x
    Else
         BendingMoment_PointLoad_Cal = Reaction1 * x - Pload * (x - Dist)
    End If
End Function

' Calculate Shear Force at specific point X
Function ShearForce_PointLoad_Cal(Pload As Double, x As Double, Dist As Double, Reaction1 As Double) As Double
       If x < Dist Then
            ShearForce_PointLoad_Cal = Reaction1
       Else
            ShearForce_PointLoad_Cal = Reaction1 - Pload
       End If
End Function

' Helper: Find Max Moment and its Location
Function GetMax_Moment(Moment() As Variant, Distcoll() As Variant) As Variant
  Dim MaxVal As Double
  Dim MaxLoc As Double
  Dim i As Long
  
  MaxVal = 0
  
  For i = LBound(Moment) To UBound(Moment)
       If Abs(Moment(i)) > Abs(MaxVal) Then
           MaxVal = Moment(i)
           MaxLoc = Distcoll(i)
       End If
  Next i
   GetMax_Moment = Array(MaxVal, MaxLoc)
End Function

Part B: Charting Utilities

These subroutines handle creating and deleting the charts automatically.

' Create a Chart
Sub Chart_Add(name As String)
    Dim chr As ChartObject
    ' Delete old chart if it exists
    Chart_Delete (name)
   
    ' Position the chart based on type
    If name = "Bending Moment" Then
       Set chr = ActiveSheet.ChartObjects.Add(155, 400, 450, 250)
    Else
       Set chr = ActiveSheet.ChartObjects.Add(155, 700, 450, 250)
    End If
   
    chr.name = name
    With chr.Chart
         .HasTitle = True
         .HasLegend = False
         .ChartTitle.Text = name
         .ChartType = xlXYScatterLinesNoMarkers
         .Axes(xlCategory).HasMajorGridlines = True
         .Axes(xlValue).HasMajorGridlines = True
    End With
End Sub

' Add Data Series to Chart
Sub Chart_Add_Data(Xvalue() As Variant, Yvalue() As Variant, name As String)
    Dim m As Integer
    ActiveSheet.ChartObjects(name).Activate
    With ActiveChart
       m = .SeriesCollection.Count
       .SeriesCollection.NewSeries
       .SeriesCollection(m + 1).XValues = Xvalue
       .SeriesCollection(m + 1).Values = Yvalue
       .SeriesCollection(m + 1).name = name
    End With
End Sub

' Delete Chart Helper
Sub Chart_Delete(name As String)
     On Error Resume Next
     ActiveSheet.ChartObjects(name).Delete
     On Error GoTo 0
End Sub

Part C: The Main Routine

This is the macro you will actually run. It loops through the beam length, calculates values, fills the table, and draws the charts.

Sub Main()
   Dim Pload As Double, Dist As Double, Length As Double
   Dim mscale As Double
   Dim Reaction_A As Double, Reaction_B As Double
   
   ' Loop variables
   Dim x As Double
   Dim i As Long, Steps As Long
   Dim StepSize As Double
   
   ' Arrays for data
   Dim Moment() As Variant
   Dim ShearForce() As Variant
   Dim Distcoll() As Variant
   
  
   With ActiveSheet
       Pload = .Range("D15").Value
       Dist = .Range("D16").Value
       Length = .Range("D17").Value
       mscale = .Range("D19").Value
   End With
   
   ' Define precision (Interval of calculation)
   StepSize = 0.1
   Steps = CLng(Length / StepSize)
   
   ReDim Moment(Steps)
   ReDim Distcoll(Steps)
   ReDim ShearForce(Steps)
   
   
   Reaction_A = Reaction1_PointLoad_Cal(Pload, Dist, Length)
   Reaction_B = Reaction2_PointLoad_Cal(Pload, Dist, Length)
  
   ' -- 3. Calculate Forces along the beam --
   For i = 0 To Steps
       x = i * StepSize
       
       ' Handle floating point errors near the end
       If x > Length Then x = Length
       
       Distcoll(i) = x
       ' We multiply by mscale for the chart, but divide later for the table
       Moment(i) = BendingMoment_PointLoad_Cal(Pload, x, Dist, Reaction_A) * mscale
       ShearForce(i) = ShearForce_PointLoad_Cal(Pload, x, Dist, Reaction_A) * mscale
   Next i
  
  
  Call Chart_Add("Bending Moment")
  Call Chart_Add("Shear Force")
  
  Call Chart_Add_Data(Distcoll, Moment, "Bending Moment")
  Call Chart_Add_Data(Distcoll, ShearForce, "Shear Force")
  
  
  ActiveSheet.Range("O4:Q1000").ClearContents
  
  For i = LBound(Distcoll) To UBound(Distcoll)
       ActiveSheet.Range("O" & 4 + i) = Distcoll(i)
       ActiveSheet.Range("P" & 4 + i) = Moment(i) / mscale ' Remove scale for text output
       ActiveSheet.Range("Q" & 4 + i) = ShearForce(i) / mscale
  Next i
  
   ' Output Summaries
   ActiveSheet.Range("H18").Value = Reaction_A
   ActiveSheet.Range("H19").Value = Reaction_B
   
   ' Get Max Moment (Unscaled)
   Dim MaxRes As Variant
   MaxRes = GetMax_Moment(Moment, Distcoll)
   ActiveSheet.Range("I16").Value = MaxRes(0) / mscale
   ActiveSheet.Range("K16").Value = MaxRes(1)

   MsgBox "Analysis Complete!", vbInformation
End Sub

https://www.youtube.com/embed?v=xcMt7AGwSvs

–>

How to Run It

1. Download the sample file (link below).

   
   

2. Open the file and ensure Macros are enabled.

   
   

3. Enter your Load and Length values in cells D15–D17.

   
   

4. Click the “Run Analysis” button (or run the Main macro from the VBA editor).

   
   

5. View the generated Shear and Moment diagrams instantly!

   
   

Download the sample file and follow the video tutorial on YouTube

The next post will be about simply supported beam subjected to uniformly distributed load.

Free – Purchase Checkout Added to cart