sibie5
11-17-2013, 03:55 PM
Been working on this for the past 2 days and I cannot get the code to plot my graph with its values onto my output sheet. Will someone take a look and put me on the right path? My prof is teaching for his first time and i feel bad saying this but he cannot teach to save his life :(
I attached my excel file with my vba codes.
Thanks in advance
EDIT: forgot to add: It keeps on saying my L value is not defined
Sub BeamMoment()
' This Sub will read the input data: L,w,P and a from the worksheet "Input", calculate bending moment and plot the result in output sheet
Sheets("Input").Select
Range("C3").Select
L = ActiveCell.Value
Range("C4").Select
w = ActiveCell.Value
Range("C5").Select
p = ActiveCell.Value
Range("C7").Select
a = ActiveCell.Value
Call MomentCal(L, w, p, a)
Call Momentplot
End Sub
Sub MomentCal(L, w, p, a)
' This sub calculates bending moments at intervals of L/50 and output the interval and moment in two columns of the "Output" worksheet
Sheets("output").Select
Dim i As Integer
For i = 1 To 51
Cells(i + 1, 1) = 0 + (L / 50) * (i - 1)
If Cells(i + 1, 1) <= a Then
Cells(i + 1, 2) = w * Cells(i + 1, 1) * (L - Cells(i + 1, 1)) / 2 + p * (L - a) * Cells(i + 1, 1) / L
Else
Cells(i + 1, 2) = w * Cells(i + 1, 1) * (L - Cells(i + 1, 1)) / 2 + p * a * (L - a) / L - p * a * (Cells(i + 1, 1) - a) / L
End If
Next
End Sub
Sub Momentplot()
' This Sub plots the Bending moment diagram in Output sheet
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Output!$A$2:$A$52"
ActiveChart.SeriesCollection(1).Values = "=Output!$B$2:$B$52"
ActiveChart.SeriesCollection(1).Name = "=""Bending Moment Diagram"""
ActiveChart.Legend.Select
Selection.Delete
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
Selection.Format.TextFrame2.TextRange.Characters.Text = "Position (m)"
With Selection.Format.TextFrame2.TextRange.Characters(1, 2).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 2).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Moment (KN·m)"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Moment (KN·m)"
With Selection.Format.TextFrame2.TextRange.Characters(1, 4).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 4).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
With ActiveChart.Parent
.Height = 300
.Width = 500
.Top = 50
.Left = 200
End With
End Sub
I attached my excel file with my vba codes.
Thanks in advance
EDIT: forgot to add: It keeps on saying my L value is not defined
Sub BeamMoment()
' This Sub will read the input data: L,w,P and a from the worksheet "Input", calculate bending moment and plot the result in output sheet
Sheets("Input").Select
Range("C3").Select
L = ActiveCell.Value
Range("C4").Select
w = ActiveCell.Value
Range("C5").Select
p = ActiveCell.Value
Range("C7").Select
a = ActiveCell.Value
Call MomentCal(L, w, p, a)
Call Momentplot
End Sub
Sub MomentCal(L, w, p, a)
' This sub calculates bending moments at intervals of L/50 and output the interval and moment in two columns of the "Output" worksheet
Sheets("output").Select
Dim i As Integer
For i = 1 To 51
Cells(i + 1, 1) = 0 + (L / 50) * (i - 1)
If Cells(i + 1, 1) <= a Then
Cells(i + 1, 2) = w * Cells(i + 1, 1) * (L - Cells(i + 1, 1)) / 2 + p * (L - a) * Cells(i + 1, 1) / L
Else
Cells(i + 1, 2) = w * Cells(i + 1, 1) * (L - Cells(i + 1, 1)) / 2 + p * a * (L - a) / L - p * a * (Cells(i + 1, 1) - a) / L
End If
Next
End Sub
Sub Momentplot()
' This Sub plots the Bending moment diagram in Output sheet
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Output!$A$2:$A$52"
ActiveChart.SeriesCollection(1).Values = "=Output!$B$2:$B$52"
ActiveChart.SeriesCollection(1).Name = "=""Bending Moment Diagram"""
ActiveChart.Legend.Select
Selection.Delete
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
Selection.Format.TextFrame2.TextRange.Characters.Text = "Position (m)"
With Selection.Format.TextFrame2.TextRange.Characters(1, 2).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 2).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Moment (KN·m)"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Moment (KN·m)"
With Selection.Format.TextFrame2.TextRange.Characters(1, 4).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 4).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
With ActiveChart.Parent
.Height = 300
.Width = 500
.Top = 50
.Left = 200
End With
End Sub