Excel VBA for Structural Analysis: Simply Supported Beam with UDL
In my previous post, I demonstrated how to analyze a beam with a Point Load. Today, we take it a step further. In this tutorial, we will use Excel VBA to analyze a Simply Supported Beam subjected to a Uniformly Distributed Load (UDL).
This script handles partial UDLs as well (where the load starts at distance $a$ and ends before support $B$).
The Engineering Formulas
Here are the equations we will be automating.
-
W = Load Intensity (e.g., kN/m)
-
L = Total Beam Length
-
a = Distance from left support to start of load
-
b = Distance from right support to end of load
-
d = Length of the load (d = L – a – b)
-
Reactions at supports
RA=W * d*(d/2+b)/Length
RB=W*d-RA
- Bending Moment equation
Mx=RA*X if a>X
Mx=RA * X – W*c^2/2 if a<X<(a+d) with c=x-a
Mx=RA * X – W*d*(d/2+(X-a-b)) if X>(a+d)
- Shear force equation
Shx=RA if a>X
Shx=RA – W*c if a<X<(a+d) with c=x-a
Shx=RA – W*d if X>(a+d)
Excel VBA implementation
Step 1: Excel Sheet Setup
To ensure the code runs immediately, please set up your Excel sheet with these exact cells:
| Type | Cell | Description | Example Value |
| Input | C15 | UDL Intensity ($W$) | 10 |
| Input | C16 | Start Distance ($a$) | 2 |
| Input | C17 | End Distance ($b$) | 2 |
| Input | C18 | Beam Length ($L$) | 8 |
| Input | C19 | Diagram Scale | 1 |
| Output | G18 | Reaction A ($R_A$) | (Calculated) |
| Output | G19 | Reaction B ($R_B$) | (Calculated) |
Note: The script will output the detailed analysis data (Distance, Moment, Shear) in columns O, P, and Q.
Step 2: The VBA Code
Copy and paste the code below into your VBA Editor (Alt + F11 > Insert Module).
Option Explicit
' Reaction at Support A
Function Reaction1_UDL_Cal(Udl As Double, a As Double, b As Double, Length As Double) As Double
Dim d As Double
d = Length - a - b
Reaction1_UDL_Cal = Udl * d * (d / 2 + b) / Length
End Function
' Reaction at Support B
Function Reaction2_UDL_Cal(Udl As Double, a As Double, b As Double, Length As Double) As Double
Dim d As Double
d = Length - a - b
Reaction2_UDL_Cal = (Udl * d) - Reaction1_UDL_Cal(Udl, a, b, Length)
End Function
' Bending Moment Calculation
Function BendingMoment_UDL_Cal(Udl As Double, x As Double, a As Double, b As Double, Length As Double, Reaction1 As Double) As Double
Dim c As Double, d As Double
d = Length - a - b
If x <= a Then
BendingMoment_UDL_Cal = Reaction1 * x
ElseIf x > a And x <= (a + d) Then
c = x - a
BendingMoment_UDL_Cal = Reaction1 * x - (Udl * c ^ 2 / 2)
Else ' x > (a + d)
BendingMoment_UDL_Cal = Reaction1 * x - Udl * d * (d / 2 + x - a - d)
End If
End Function
' Shear Force Calculation
Function ShearForce_UDL_Cal(Udl As Double, x As Double, a As Double, b As Double, Length As Double, Reaction1 As Double) As Double
Dim c As Double, d As Double
d = Length - a - b
If x <= a Then
ShearForce_UDL_Cal = Reaction1
ElseIf x > a And x <= (a + d) Then
c = x - a
ShearForce_UDL_Cal = Reaction1 - (Udl * c)
Else ' x > (a + d)
ShearForce_UDL_Cal = Reaction1 - (Udl * d)
End If
End Function
' Helper to find Max Value in Arrays
Function GetMax_Moment(Moment() As Variant, Distcoll() As Variant) As Variant
Dim MaxVal As Double, 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
Sub Chart_Add(name As String)
Dim chr As ChartObject
Chart_Delete (name) ' Clear old chart
' Position charts nicely on the sheet
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
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
Sub Chart_Delete(name As String)
On Error Resume Next
ActiveSheet.ChartObjects(name).Delete
On Error GoTo 0
End Sub
Sub Main()
' Variables
Dim Udl As Double, a As Double, b As Double, Length As Double
Dim Mx As Double, Shx As Double, x As Double
Dim mscale As Double
Dim Reaction_A As Double, Reaction_B As Double
' Loop Variables
Dim i As Long, Steps As Long
Dim StepSize As Double
' Arrays
Dim Moment() As Variant, ShearForce() As Variant, DistColl() As Variant
With ActiveSheet
Udl = .Range("C15").Value
a = .Range("C16").Value
b = .Range("C17").Value
Length = .Range("C18").Value
mscale = .Range("C19").Value
End With
StepSize = 0.1
Steps = CLng(Length / StepSize)
ReDim Moment(Steps)
ReDim ShearForce(Steps)
ReDim DistColl(Steps)
Reaction_A = Reaction1_UDL_Cal(Udl, a, b, Length)
Reaction_B = Reaction2_UDL_Cal(Udl, a, b, Length)
For i = 0 To Steps
x = i * StepSize
' Cap X at Length to prevent overflow errors
If x > Length Then x = Length
Mx = BendingMoment_UDL_Cal(Udl, x, a, b, Length, Reaction_A)
Shx = ShearForce_UDL_Cal(Udl, x, a, b, Length, Reaction_A)
DistColl(i) = x
Moment(i) = Mx * mscale
ShearForce(i) = Shx * 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")
' Max Values
Dim MaxRes As Variant
MaxRes = GetMax_Moment(Moment, DistColl)
ActiveSheet.Range("H16").Value = MaxRes(0) / mscale
ActiveSheet.Range("J16").Value = MaxRes(1)
' Reactions
ActiveSheet.Range("G18").Value = Reaction_A
ActiveSheet.Range("G19").Value = Reaction_B
' Full Data Table
ActiveSheet.Range("O5:Q1000").ClearContents
For i = LBound(DistColl) To UBound(DistColl)
ActiveSheet.Range("O" & 5 + i).Value = DistColl(i)
ActiveSheet.Range("P" & 5 + i).Value = Moment(i) / mscale
ActiveSheet.Range("Q" & 5 + i).Value = ShearForce(i) / mscale
Next i
MsgBox "Analysis Complete!", vbInformation
End Sub
Download the sample file and follow the video tutorial on YouTube
The next post will be about an indeterminate beam subjected to combine uniformly distributed load and point load.