Help to simplify excel vba code

**vbanewb14** · Apr 23 '10, 08:48 PM

Hi, I am new to programming so please bear with me. I have pieced together a code which accomplished what I want it to do (creates bar graphs with a target line). Now I am just trying to simplify the code.

For example, I don't want to activate and select windows...inste ad I would like to just call worksheets directly. Also, I think the graph definitions can be cleaned, I just don't know how.

Would appreciate any guidance!

Code:

Sub LoudnessChart(Row As String)
Dim LChart As ChartObject
Dim Lrange As Range
Dim Trange As Range
Dim Lstr As String
Dim Lname As String


 Windows("Sorted OSQ HC 031910.xls").Activate
    Sheets("ARLSummary").Select

Set Lrange = Range(Cells(Row, 19), Cells(Row, 23))
Set Trange = Range(Cells(4, 7), Cells(Row, 7))
    Lname = "=ARLSummary!R" & Row & "C3"

 Windows("Sorted OSQ HC 031910.xls").Activate
    Sheets("Temp").Select

Set LChart = ActiveSheet.ChartObjects.Add _
        (Left:=100, Width:=375, Top:=75, Height:=225)
 
'Define Seriescollection(1)
    LChart.Chart.SetSourceData Source:=Lrange
    LChart.Chart.ChartType = xlColumnClustered
    LChart.Chart.SeriesCollection(1).XValues = "=ARLSummary!R3C19:R4C23"

     LChart.Chart.SeriesCollection(1).Name = Lname
       With LChart.Chart
        .HasLegend = False
        .HasTitle = True
        .ChartTitle.Characters.Text = Lname
        .Axes(xlCategory).HasTitle = False
         .Axes(xlCategory).TickLabelSpacing = 1
      '  .Axes(xlCategory).TickLabels.Orientation = xlUpward
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Characters.Text = "Sones"
        End With

'Define Seriescollection(2)
     LChart.Chart.SeriesCollection.Add Source:=Trange
        With LChart.Chart.SeriesCollection(2)
            .ChartType = xlXYScatter
            .XValues = "=(ARLSummary!R4C6,ARLSummary!R172C6)"
            ' .values = cells(4, 7)
            .Values = "=(ARLSummary!R4C7,ARLSummary!R172C7)"
            .Name = "Target"
            .ErrorBar Direction:=xlX, Include:=xlMinusValues, Type:=xlFixedValue, Amount:=1
        End With

    With LChart.Chart.SeriesCollection(2)
        .Shadow = False
        .MarkerStyle = xlNone
    End With

    With LChart.Chart.SeriesCollection(2).ErrorBars
        .Border.LineStyle = xlContinuous
        .Border.ColorIndex = 3
        .Border.Weight = xlMedium
        .EndStyle = xlNoCap
    End With

    With LChart.Chart
        .HasAxis(xlCategory, xlPrimary) = True
        .HasAxis(xlCategory, xlSecondary) = True
        .HasAxis(xlValue, xlPrimary) = True
        .HasAxis(xlValue, xlSecondary) = False
        .ApplyDataLabels AutoText:=True
        .PlotArea.Interior.ColorIndex = xlNone
    End With
        
    With LChart.Chart.Axes(xlCategory, xlSecondary)
        .MinimumScale = 0
        .MaximumScale = 1
        .MajorTickMark = xlNone
        .MinorTickMark = xlNone
        .TickLabelPosition = xlNone
    End With
    
    With LChart.Chart.SeriesCollection(1).DataLabels.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .ColorIndex = 32
    End With

    With LChart.Chart.SeriesCollection(2).DataLabels.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .ColorIndex = 3
    End With
 End Sub