Quick VBA Routine: XY Chart with Axis Titles

Someone asked in the newsgroup how to create an XY chart in Excel that uses the label at the top of the columns of X and Y data for the axis titles. That’s not built in, but it’s not hard to do if you know a little VBA. I’ve taken an example from my web site, Interactive Chart Creation, and embellished it a bit.

The VBA procedure (code below) actually works a bit like my first recollection of Excel charting, which had two dialogs, one asking for the data range, the other asking for the range of cells to cover witth the chart. Since I always align my charts with the cell boundaries, this is nicer than the Chart Wizard in Excel versions up to 2003 and the Insert Chart command in Excel 2007.

The code uses a couple of input boxes to get the ranges from the user. I’ve added some smarts so it uses the selection as the default for the source data range in the first input box. If the selection is a single cell, the macro uses the cell’s current region (the contiguous range including the cell).

Copy the code below (use the plain text view of the code for copying) into a regular module in the VB Editor, then from Excel’s Tools menu, choose Macro, then Macros, or use the Alt+F8 shortcut key combination, then select and run the macro ChartWithAxisTitles.

Macro dialog

The first input box asks you to select the data range. Note that the active cell C9 has been expanded to its current region B2:D21 for the dialog’s default data range.

Select Chart Data Dialog

The second input box asks you to select a region for the chart to cover.

Insert Chart Position Dialog

The result is an XY chart with default chart and series formatting. I’ve cleaned up the formatting slightly here, after the procedure inserted the chart, but obviously there’s room for improvement, particularly with the spacing of the chart elements.

Automatic Chart with Axis Titles

My data range had three columns. The first is used for X values and the X axis title, the others are used for Y values for two series. The cell above the first Y data column is used for the Y axis title, and the cell above each Y data column is used for the corresponding series name.

Here is the minimally documented code:

Sub ChartWithAxisTitles()
   Dim objChart As ChartObject
   Dim myChtRange As Range
   Dim myDataRange As Range
   Dim myInitialRange As Range
   Dim sInitialRange As String
 

  ' bail out if we're not on a worksheet
   If Not ActiveSheet Is Nothing Then
     If TypeName(ActiveSheet) = "Worksheet" Then
      
       ' propose an initial data range
       If TypeName(Selection) = "Range" Then
         Set myInitialRange = Selection
         If myInitialRange.Cells.Count = 1 Then
           Set myInitialRange = myInitialRange.CurrentRegion
         End If
         sInitialRange = myInitialRange.Address(True, True)
       End If

      With ActiveSheet

        ' ask user for range that contains data for chart
         On Error Resume Next
         Set myDataRange = Application.InputBox( _
             prompt:="Select a range containing the chart data.", _
             Title:="Select Chart Data", Default:=sInitialRange, Type:=8)
         On Error GoTo 0
         If myDataRange Is Nothing Then Exit Sub

        ' ask user for range chart should cover
         On Error Resume Next
         Set myChtRange = Application.InputBox( _
             prompt:="Select a range where the chart should appear.", _
             Title:="Select Chart Position", Type:=8)
         On Error GoTo 0
         If myChtRange Is Nothing Then Exit Sub

        ' cover chart range with chart
         Set objChart = .ChartObjects.Add( _
             Left:=myChtRange.Left, Top:=myChtRange.Top, _
             Width:=myChtRange.Width, Height:=myChtRange.Height)

        ' Put all the right stuff in the chart
         With objChart.Chart
           .ChartArea.AutoScaleFont = False
           .ChartType = xlXYScatterLines
           .SetSourceData Source:=myDataRange, PlotBy:=xlColumns
           .HasTitle = True
           .ChartTitle.Characters.Text = "My Title"
           .ChartTitle.Font.Bold = True
           .ChartTitle.Font.Size = 10
           With .Axes(xlCategory, xlPrimary)
             .HasTitle = True
             With .AxisTitle
               .Font.Size = 8
               .Font.Bold = True
               .Characters.Text = myDataRange.Cells(1, 1)
             End With
           End With
           With .Axes(xlValue, xlPrimary)
             .HasTitle = True
             With .AxisTitle
               .Font.Size = 8
               .Font.Bold = True
               .Characters.Text = myDataRange.Cells(1, 2)
             End With
           End With
         End With

      End With
     End If
   End If
 End Sub
 

Peltier Tech Chart Utility

Comments

  1. I have just started running into a problem where a selected range is not recognised as a range and so I get spat out on through the isrange…exit sub checks (2003, XP sp2). Has anyone else had this occur and know a solution?

    Thanks
    Matt

  2. When you get this error, go to the VB Editor, open the Immediate Window (Ctrl+G), type in

    ?typename(selection)

    then press Enter and see what the response is. If that doesn’t help, step through the code and see where you are ejected from execution.

  3. Matt responded via email instead of posting here. He said:


    If I select a range of cells then the suggested code in the immediate
    window returns type Range. When I select the same range of cells when
    asked for a chart range via the input box, then run the immediates
    window on ? typename(myDataRange) the type of myDataRange returns type
    Nothing, hence I get spat out at ‘If myDataRange is Nothing Then Exit
    Sub’.

    If I accept the default data range then I get spat out on the ‘If
    myChtRange Is Nothing Then Exit Sub’ as this input box again returns
    type nothing.

    I have just changed PCs (supposedly within the same standard operating
    environment) – are there any VBA references that should be set by
    default?

    Matt -

    After the Set MyDataRange = statement, insert this code:

    MsgBox "MyDataRange is type " & TypeName(MyDataRange" _
        & vbNewLine & "Error " & Err.Number & ": " & Err.Description

     
    Similarly, after the Set MyChtRange = statement, insert this code:

    MsgBox "MyDataRange is type " & TypeName(MyChtRange" _
        & vbNewLine & "Error " & Err.Number & ": " & Err.Description

     
    Then rerun the code, once accepting the default data range, once setting your own, and email me screen shots of the message boxes.

  4. Jon,

    Early in your minimally documented code, I believe the following line contains a typo, as

    If myInitialRange.Cells.Count = 1 Then

    precedes a block of code that seems like it was meant to apply if a useful XY range had been selected, so you probably meant:

    If myInitialRange.Cells.Count > 1 Then

    Nice little subroutine, by the way.

  5. Dale -

    Actually that’s a bit of intelligence built into the code. myInitialRange is defined as the selected range, but if this range has only one cell, the code then defines myInitialRange as the current region of the active cell. This is similar to the way the chart wizard determines the range to use for the source data of a chart. It’s usually so much easier to select one cell in a range than to meticulously select the entire range.

  6. Ok — that is a handy feature IF someone wants to just select one cell of the range to plot AND go with Excel’s guess at what the entire range should be.

    Unfortunately, for the user who has already selected exactly the range to be charted before calling this marco, this bit of intelligence plays dumb and makes one reselect the range.

    I bet you meant to put the following line of code one line later, so it gets executed either way, between instead of before those two END IF statements:

    sInitialRange = myInitialRange.Address(True, True)

  7. Doh! I said the code was smart. I made no such assertions about the coder!

  8. I am trying to generate the similar graph but I want to select for example first 10 coloumns as X and 10 second coloumns as Y.
    please let me know which kind of modification I should do.

    Regards

  9. Mohammed -

    Is the data in rows? Or do you have 10 series, with all ten X ranges first, then all ten Y ranges?

  10. Thanks for your reply.

    I have 10 series ,such that for example in first series X are($A$1:$A$10) and Y are(!$K$1:$K$10) and in second seris X are ($B$1:$B$10) and Y are($L$1:$L$10) and so on.

  11. Mohammad -

    That’s one of the data layouts that my Quick Excel XY Chart Utility can handle. It’s called X-X-Y-Y and it’s 4th from the left in the first row. This will not add the axis titles, but by adding all the series, the ustility will save you more time.

    The utility is free and works well up to 2003. I haven’t tried it in 2007 but I don’t think there’s going to be a problem with it.

    Let me know how it works.

  12. Many thanks

    It works for me in excel 2007.the good thing is that the program can be used for many X:Y data arrangement.

  13. My question is how to keep the x-axis labels as what it is:
    My data:
    Time 16 to 24 25 to 54 55 +
    2007Q1 4.2 4.7 0.5
    2007Q2 3.6 3.3 0.8
    2007Q3 2.1 6 0.8
    2007Q4 6.2 5.3 1.4
    2008Q1 3.1 5.2 3.3
    2008Q2 2.4 4 1.3
    2008Q3 6.1 5.6 1.4
    2008Q4 7.7 4.6 2.2

    How can I lable the ticks in X-axis as 2007Q1 instead of 1, and somethng like that?

    Thanks

  14. Wu -

    You need to make a line chart, not an XY chart.

    Find the line of code that says
    .ChartType = xlXYScatterLines

    and change it to
    .ChartType = xlLineMarkers

  15. Hi Jon,

    It works. You make my day much better; Thank you very much!

    Wu

  16. Bo -

    This approach is complicated by the use of multiple areas in the data range selection. The code has to recognize that it is using two areas, then note the order of columns. Instead of going sequentially, the columns in your example skip from B to F.

    Here is the enhanced code, with the changes highlighted:

    Sub ChartWithAxisTitles()
      Dim objChart As ChartObject
      Dim myChtRange As Range
      Dim myDataRange As Range
      Dim myInitialRange As Range
      Dim sInitialRange As String
      ' in case of multiple ranges
      Dim iArea As Long
      Dim rArea1 As Range
      Dim rArea2 As Range
      Dim myXAxisCell As Range
      Dim myYAxisCell As Range
    
      ' bail out if we're not on a worksheet
      If Not ActiveSheet Is Nothing Then
        If TypeName(ActiveSheet) = "Worksheet" Then
    
          ' propose an initial data range
          If TypeName(Selection) = "Range" Then
            Set myInitialRange = Selection
            If myInitialRange.Cells.Count = 1 Then
              Set myInitialRange = myInitialRange.CurrentRegion
            End If
            sInitialRange = myInitialRange.Address(True, True)
          End If
    
          With ActiveSheet
    
            ' ask user for range that contains data for chart
            On Error Resume Next
            Set myDataRange = Application.InputBox( _
                prompt:="Select a range containing the chart data.", _
                Title:="Select Chart Data", Default:=sInitialRange, Type:=8)
            On Error GoTo 0
            If myDataRange Is Nothing Then Exit Sub
    
            If myDataRange.Areas.Count = 1 Then
              Set myXAxisCell = myDataRange.Cells(1, 1)
              Set myYAxisCell = myDataRange.Cells(1, 2)
            Else
              Set rArea1 = myDataRange.Areas(1)
              For iArea = 2 To myDataRange.Areas.Count
                If myDataRange.Areas(iArea).Column < rArea1.Column Then
                  Set rArea1 = myDataRange.Areas(iArea)
                End If
              Next
              Set myXAxisCell = rArea1.Cells(1, 1)
              If rArea1.Columns.Count > 1 Then
                Set myYAxisCell = rArea1.Cells(1, 2)
              Else
                For iArea = 1 To myDataRange.Areas.Count
                  If myDataRange.Areas(iArea).Column > rArea1.Column Then
                    Set rArea2 = myDataRange.Areas(iArea)
                    Exit For
                  End If
                Next
                For iArea = 1 To myDataRange.Areas.Count
                  If myDataRange.Areas(iArea).Column > rArea1.Column And _
                      myDataRange.Areas(iArea).Column < rArea2.Column Then
                    Set rArea2 = myDataRange.Areas(iArea)
                  End If
                Next
                Set myYAxisCell = rArea2.Cells(1, 1)
              End If
            End If
    
            ' ask user for range chart should cover
            On Error Resume Next
            Set myChtRange = Application.InputBox( _
                prompt:="Select a range where the chart should appear.", _
                Title:="Select Chart Position", Type:=8)
            On Error GoTo 0
            If myChtRange Is Nothing Then Exit Sub
    
            ' cover chart range with chart
            Set objChart = .ChartObjects.Add( _
                Left:=myChtRange.Left, Top:=myChtRange.Top, _
                Width:=myChtRange.Width, Height:=myChtRange.Height)
    
            ' Put all the right stuff in the chart
            With objChart.Chart
              .ChartArea.AutoScaleFont = False
              .ChartType = xlXYScatterLines
              .SetSourceData Source:=myDataRange, PlotBy:=xlColumns
              .HasTitle = True
              .ChartTitle.Characters.Text = "My Title"
              .ChartTitle.Font.Bold = True
              .ChartTitle.Font.Size = 10
              With .Axes(xlCategory, xlPrimary)
                .HasTitle = True
                With .AxisTitle
                  .Font.Size = 8
                  .Font.Bold = True
                  .Characters.Text = myXAxisCell.Value
                End With
              End With
              With .Axes(xlValue, xlPrimary)
                .HasTitle = True
                With .AxisTitle
                  .Font.Size = 8
                  .Font.Bold = True
                  .Characters.Text = myYAxisCell.Value
                End With
              End With
            End With
    
          End With
        End If
      End If
    End Sub

Subscribe without commenting

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites