In my previous post, I demonstrated how a simply supported beam with point load can be analyzed using excel VBA. In this post, I will focus on structural analysis with Excel VBA of simply supported beam subjected to uniformly distributed load.
Simply supported beam with uniformly distributed load formulas
- 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
The VBA functions below are the VBA implementation of the simply supported beam formulas.
- 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 at a point X along the beam
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 Dim d As Double d = Length - a - b If a > X Then BendingMoment_UDL_Cal = Reaction1 * X ElseIf a <= X And X < (a + d) Then c = X - a BendingMoment_UDL_Cal = Reaction1 * X - Udl * c ^ 2 / 2 ElseIf X > (a + d) Then BendingMoment_UDL_Cal = Reaction1 * X - Udl * d * (d / 2 + X - a - d) End If End Function
- Shear force at a point X along the beam
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 Dim d As Double d = Length - a - b If a > X Then ShearForce_UDL_Cal = Reaction1 ElseIf a <= X And X < (a + d) Then c = X - a ShearForce_UDL_Cal = Reaction1 - Udl * c ElseIf X > (a + d) Then ShearForce_UDL_Cal = Reaction1 - Udl * d End If End Function
- Get the maximum bending moment and position
Function GetMax_Moment(Moment() As Variant, Distcoll() As Variant) As Variant Dim temp As Double, i As Integer, n As Integer temp = 0 For i = LBound(Moment) To UBound(Moment) If Abs(Moment(i)) > Abs(temp) Then temp = Moment(i) n = i End If Next i GetMax_Moment = Array(temp, Distcoll(n)) End Function
Sub routines to draw the diagrams
- Add chart to worksheet
Sub Chart_Add(name As String) Dim chr As ChartObject Chart_Delete (name) If name = "Bending Moment" Then Set chr = ActiveSheet.ChartObjects.Add(155, 400, 450, 450) Else Set chr = ActiveSheet.ChartObjects.Add(155, 900, 450, 450) End If chr.name = name With chr.Chart .HasTitle = True .HasLegend = False .ChartTitle.Text = name .ChartType = xlXYScatterLinesNoMarkers .Axes(xlCategory).HasMajorGridlines = True .Axes(xlCategory).MajorUnit = 1 .Axes(xlValue).HasMajorGridlines = True .Axes(xlValue).MajorUnit = 1 End With End Sub
- Add data to the 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 existing chart
Sub Chart_Delete(name As String) On Error Resume Next ActiveSheet.ChartObjects(name).Delete On Error GoTo 0 End Sub
- Then Main sub routine where all the functions will be called to analyze the beam
Sub Main() Dim Udl As Double, a As Double, b As Double Dim Mx As Double, Shx As Double, Length As Double Dim X As Double, Moment() As Variant, ShearForce() As Variant, DistColl() As Variant Dim i As Integer, n As Integer Dim Reaction_A As Double, Reaction_B As Double Dim mscale As Double '''Read input Udl = ActiveSheet.Range("C15").Value a = ActiveSheet.Range("C16").Value b = ActiveSheet.Range("C17").Value Length = ActiveSheet.Range("C18").Value mscale = ActiveSheet.Range("C19").Value ReDim Moment(CInt(Length / 0.1)) ReDim ShearForce(CInt(Length / 0.1)) ReDim DistColl(CInt(Length / 0.1)) '' Calculate the reactions Reaction_A = Reaction1_UDL_Cal(Udl, a, b, Length) Reaction_B = Reaction2_UDL_Cal(Udl, a, b, Length) '' Bending and shear Force Do Until Round(X, 1) > Length Mx = BendingMoment_UDL_Cal(Udl, X, a, b, Length, Reaction_A) Shx = ShearForce_UDL_Cal(Udl, X, a, b, Length, Reaction_A) Moment(i) = Mx * mscale ShearForce(i) = Shx * mscale DistColl(i) = X i = i + 1 X = X + 0.1 Loop '''Draw diagrams 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") ' Output the results on the worksheet ActiveSheet.Range("H16").Value = GetMax_Moment(Moment, DistColl)(0) / mscale ActiveSheet.Range("j16").Value = GetMax_Moment(Moment, DistColl)(1) ActiveSheet.Range("G18").Value = Reaction_A ActiveSheet.Range("G19").Value = Reaction_B ActiveSheet.Range("O5:Q1000").ClearContents For n = LBound(DistColl) To UBound(DistColl) ActiveSheet.Range("O" & 5 + n).Value = DistColl(n) ActiveSheet.Range("p" & 5 + n).Value = Moment(n) ActiveSheet.Range("q" & 5 + n).Value = ShearForce(n) Next n 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.
- Version 1.0.0
- Download 6918
- File Size 43 KB
- File Count 1
- Create Date January 29, 2021
- Last Updated March 12, 2022
File | Action |
---|---|
Simply supported beam with udl.zip | Download |
If you believe my tools are helping you and would like to support me, please use the below button to donate.