troubleshooting Question

VBA - Excel 2010 - Becoming a code more efficient & objective.

Avatar of ocaccy
ocaccyFlag for Japan asked on
Visual Basic ClassicMicrosoft ExcelVisual Basic.NET
6 Comments1 Solution1311 ViewsLast Modified:
Hello everyone.

How to becoming this code PURE VBA?

Sub Macro_A31x()

    Sheets("april").Select
    Range("A31:G32").Select
    ActiveSheet.Shapes.AddChar<wbr ></wbr>t.Select
    ActiveChart.SetSourceData Source:=Range("april!$A$3<wbr ></wbr>1:$G$32")
    
     With ActiveChart
        .ChartType = xlLineMarkers
        .HasTitle = True
        .ChartTitle.Text = "=april!A29"
      With .Parent
              .Top = 350 'Range("A34").Top
              .Left = 2 ' Range("A34").Left
              .Width = 263
              .Height = 170
              .Name = "Grafico 2A31x"
     End With
       
    .Axes(xlCategory, xlPrimary).Select
    .Axes(xlCategory, xlPrimary).TickLabels.Font<wbr ></wbr>.Size = 9.6
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Chara<wbr ></wbr>cters.Text<wbr ></wbr> = "Case"
    .Axes(xlCategory, xlPrimary).AxisTitle.Font.<wbr ></wbr>Size = 8
    ActiveChart.Axes(xlCategor<wbr ></wbr>y).Select
    Selection.TickLabelPositio<wbr ></wbr>n = xlLow
    
    .Axes(xlValue, xlPrimary).Select
    .Axes(xlValue, xlPrimary).TickLabels.Font<wbr ></wbr>.Size = 8
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Chara<wbr ></wbr>cters.Text<wbr ></wbr> = "april"
    .Axes(xlValue, xlPrimary).AxisTitle.Font.<wbr ></wbr>Size = 8
    .Legend.IncludeInLayout = False
    .Legend.Left = 0
    .Legend.Select
    
    Selection.Position = xlCorner
        
    ActiveSheet.ChartObjects("<wbr ></wbr>Grafico 2A31x").Activate
    ActiveChart.Axes(xlValue).<wbr ></wbr>Select
    Selection.MinorTickMark = xlInside

            
    ActiveChart.ChartTitle.Sel<wbr ></wbr>ect
    Selection.Left = -15
    Selection.Top = -15
    Selection.Format.TextFrame<wbr ></wbr>2.TextRang<wbr ></wbr>e.Font.Siz<wbr ></wbr>e = 9.6
    ActiveChart.Legend.Select
    Selection.Left = 230
    Selection.Top = -2
   
    ActiveChart.Legend.LegendE<wbr ></wbr>ntries(1).<wbr ></wbr>Select
    Selection.Format.TextFrame<wbr ></wbr>2.TextRang<wbr ></wbr>e.Font.Siz<wbr ></wbr>e = 8
    ActiveChart.Legend.LegendE<wbr ></wbr>ntries(2).<wbr ></wbr>Select
    Selection.Format.TextFrame<wbr ></wbr>2.TextRang<wbr ></wbr>e.Font.Siz<wbr ></wbr>e = 8

    ActiveChart.SeriesCollecti<wbr ></wbr>on(1).Sele<wbr ></wbr>ct
    
    End With
     
    Dim n As Integer
        With ActiveSheet.ChartObjects("<wbr ></wbr>Grafico 2A31x").Chart
        For n = 1 To 2
            With .SeriesCollection(n)
                .MarkerStyle = Choose(n, 1, 3, 8)

                .MarkerSize = 8
                With .Format.Fill
                    .Visible = msoTrue
                    .ForeColor.ObjectThemeColo<wbr ></wbr>r = msoThemeColorBackground1
                    .ForeColor.RGB = RGB(255, 255, 255)
                    .ForeColor.TintAndShade = 0
                End With
                .Format.Line.Visible = msoTrue
                .Format.Line.Weight = 1
            End With
            Next
        End With
        
    ActiveChart.SeriesCollecti<wbr ></wbr>on(1).Sele<wbr ></wbr>ct
    With Selection
        .MarkerStyle = 1
        End With
    Selection.MarkerStyle = 1
    
    ActiveChart.Legend.Select
    ActiveChart.Legend.LegendE<wbr ></wbr>ntries(1).<wbr ></wbr>Select
    ActiveChart.SeriesCollecti<wbr ></wbr>on(1).Sele<wbr ></wbr>ct
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(112, 48, 160)
        .Transparency = 0
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(112, 48, 160)
        .Transparency = 0
    End With

    ActiveChart.Legend.LegendE<wbr ></wbr>ntries(1).<wbr ></wbr>Select
    ActiveChart.SeriesCollecti<wbr ></wbr>on(2).Sele<wbr ></wbr>ct
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
    End With
    
    ActiveChart.Legend.Select
    Selection.Position = xlTop
    Selection.Left = 55
    Selection.Top = 9
    ActiveChart.ChartTitle.Sel<wbr ></wbr>ect
    ActiveChart.Axes(xlValue).<wbr ></wbr>AxisTitle.<wbr ></wbr>Select
    Selection.Left = -10
    Selection.Top = 38
    ActiveChart.Axes(xlCategor<wbr ></wbr>y).AxisTit<wbr ></wbr>le.Select
    Selection.Left = 120
    Selection.Top = 160
    ActiveChart.PlotArea.Selec<wbr ></wbr>t
    Selection.Height = 160
    Selection.Width = 240
    Selection.Top = 25
    Selection.Left = 10
        
            
End Sub

Thank you in advance for your attention to this matter.
ocaccy
ASKER CERTIFIED SOLUTION
GrahamSkan
Retired
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros