I cannot figure out why my code will not plot my graph!

[sibie5]

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

[Paul_Hossler]

You have Option Explicit specified which is good, but that requires that you Dimension your variables. Look for the <<<<<<<<<<<<<<

Option Explicit
Sub BeamMoment()
    Dim L As Double, w As Double, p As Double, a As Double  '<<<<<<<<<<<<<<<<<<<<<<<
    
    ' 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
    L = Sheets("Input").Range("C3").Value
    w = Sheets("Input").Range("C4").Value
    p = Sheets("Input").Range("C5").Value
    a = Sheets("Input").Range("C7").Value
    
    Call MomentCal(L, w, p, a)
    
    Call Momentplot
    
End Sub


Sub MomentCal(L As Double, w As Double, p As Double, a As Double)       '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

' 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

Paul

PS. If this assignment is due tomorrow, isn't this cutting it a little close???

PPS. We don't do homework here, but since your question was specifically VBA related, I figured it was OK

[sibie5]

Hey Paul thanks for the reply. Its a introductory course and my prof just throws a bunch of assignments to us to learn by ourselves. I've been using the record macro to try to self teach but it's such a slow process

Btw, what does the "As Double" code mean? I have not got there yet so I want to know what it means before I use it. Thanks

[Paul_Hossler]

When you Dim (look in online help) a variable you should give it a type. If you don't then it's a variant (=look in online help)

Dim myNumber as Long means that the variable myNumber is a Long integer (=on line help again)

Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647.

Double (double-precision floating-point) variables are stored as IEEE 64-bit (8-byte) floating-point numbers ranging in value from -1.79769313486231E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive values.

Other types (no pun intended [OK, maybe a little one]) are

Optional. Data type of the variable; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported), Date, String (for variable-length strings), String * length (for fixed-length strings), Object, Variant, a , or an object type. Use a separate As type clause for each variable you declare

You could also have object variables (on line help again)

Dim wsInput as Worksheet, rTop as Range

BUT assigning to a object uses Set, as in

Set wsInput = Thisworkbook.Worksheets("Sheet1")
Set rTop = wsInput.Range("A1")


Understanding and correctly using the various Excel objects is a real challenge

Clear as mud??

Paul