How to analyze a simply supported beam with distributed load using Excel VBA?

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.