CODEWITHSUNDEEP
vbaexcel
dig_123
dig_123

Reputation: 2378

Excel VBA script to include Axis names in Graphs

I've the below table in "CPU_STAT" worksheet:

enter image description here

I've a "CPU_STAT_GRAPH" worksheet in same workbook where in I pulled in the columns that I'll use for generating graphs.

The code used:

Sub test()
     Dim sourceColumn1 As Range, targetColumn1 As Range
     Dim sourceColumn2 As Range, targetColumn2 As Range
     Dim sourceColumn3 As Range, targetColumn3 As Range

    Set sourceColumn1 = Workbooks("KPI_stats_v1.xls").Worksheets("CPU_STAT").Columns("D")
    Set targetColumn1 = Workbooks("KPI_stats_v1.xls").Worksheets("CPU_STAT_GRAPH").Columns("A")
    Set sourceColumn2 = Workbooks("KPI_stats_v1.xls").Worksheets("CPU_STAT").Columns("G")
    Set targetColumn2 = Workbooks("KPI_stats_v1.xls").Worksheets("CPU_STAT_GRAPH").Columns("B")
    Set sourceColumn3 = Workbooks("KPI_stats_v1.xls").Worksheets("CPU_STAT").Columns("I")
    Set targetColumn3 = Workbooks("KPI_stats_v1.xls").Worksheets("CPU_STAT_GRAPH").Columns("C")

    sourceColumn1.Copy Destination:=targetColumn1
    sourceColumn2.Copy Destination:=targetColumn2
    sourceColumn3.Copy Destination:=targetColumn3

    Range("A1:C5").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmooth).Select
    ActiveChart.SetSourceData Source:=Range("CPU_STAT_GRAPH!$A$1:$C$5")
    ActiveChart.SetElement (msoElementPrimaryValueGridLinesNone)
    ActiveChart.SetElement (msoElementPrimaryCategoryGridLinesNone)
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = "CPU Utilization"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "CPU Utilization"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 15).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With


End Sub

The output in "CPU_STAT_GRAPH" is:

enter image description here

Everything is as expected. All I need is to rename the Axes of the Graph properly. The vertical Axis as Percent and the Horizontal as Hour.

How to incorporate that in the code ? Also as you can see my code is a very basic one and it doesn't explicitly define the X-axis and the Y-axis to be used from the data on the left. How to optimize my code with minimum code lines and to make it more intelligent :)

[[ Newbie to VBA coding :) ]]

Upvotes: 0

Views: 230

Answers (1)

CodinglyClueless
CodinglyClueless

Reputation: 82

The syntax to add axis titles is very similar to what you have used to add in the title of your chart. In the future you can learn a lot and see what the code looks like from recording a macro and then manually doing the steps - but to add axis, use the following syntax:

ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)

And then to add the titles you can use:

ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Hour"
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percent"

But I'd really recommend recording so that you can see all of the formatting and associated parameters that you can pas through and also its a great way to learn. Hope this helps!

One more thing, since all of these commands all use ActiveChart, you can also use a with loop. So you would group all of the ActiveChart elements together and could say something like:

With ActiveChart
   .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Hour"
   .Axes(xlValue, xlPrimary).AxisTitle.Text = "Percent"
End With

Upvotes: 1

Related Questions