Building a graph using a macro

**AKL01** · 09-23-2011, 12:43 PM

I have been searching the forum to see if there is an example of building a graph from scratch using a macro. Is there one I can be pointed to? If not, I'll start drafting an example workbook.

I know that you can set up a graph that references a data range that is updated by a macro. But I need to make a column graph (and for the next project it will be a stacked column graph) where the number of entries will vary for each time you run the code.

If I build the graph for the largest data set expected and then just have the macro fill it, I will have a ton of blank space on the graph and the actual data will be jammed together.

As I said, if there is a previous answer for this (and I am just not searching for it well), just point me there. In case there isn't an answer, I will create an example.

Thanks in advance.

**AKL01** · 09-23-2011, 05:05 PM

A workbook with the data format and an example of what the graph should look like. One of my attempts is also in the file-it shows the data stacked on top of a previous plot of the same data (run 2 plotted on top of run 1). The macro I was using is called plot. A few things I've tried are still there, just commented out.

The parts that I've been having a problem with:
being able to vary the data range (sometimes there will be 3 pairs of data in columns G&H, sometimes it could be 20.

I would actually like the graph to be on it's own sheet, but every time I try to move it, instead fo the graph name being "By System" it is "Chart17" (or whichever number run of the macro I am on).

On a similar note (and I'm watching a few other threads on this), the spreadsheet gets pretty large getting the data to this point-I'm pretty sure I can shrink the file size by clearing the clipboard (either a few times or once at the end of the macro). I am still working on that, but if you have a suggestion for that as well, it would be appreciated.

Thanks all.

EDIT
Workbook wouldn't upload (error message attached). I took screen shots of the sheet and the macro.

**tlafferty** · 09-23-2011, 05:21 PM

Welcome to the forum! In order to get your workbook to upload. Try logging out of the forum, closing your browser and all instances of the browser if you have other browsers open, close them. Then log back in. If you still get the error message, make sure you have enabled cookies, and if you're running IE 8 or later, try viewing the site in compatibility mode.

**AKL01** · 09-26-2011, 09:37 AM

@tlafferty

Thanks for the response! I tried all the suggestions; computer was shut down for the weekend, I checked to make sure cookies were enabled for the site, and tried the site in compatability mode. Still got the same message. I spoke to our company IT and they checked the items they could think of as well (mostly the same things you suggested), but he was not sure what security token would be missing. He did say if there were any other suggestions, IT would be willing to help me try it. Not sure what to try now. Is there a site IT person that could help, or do they just do the registration issues?

Thanks again for the help.

**tlafferty** · 09-26-2011, 05:27 PM

I haven't run into this before, so you may want to start a thread about this specific subject.

**Jon_Peltier** · 09-26-2011, 08:24 PM

Really, there's no need to upload a workbook. Your description of what you want isn't too bad. The advanced editing mode here lets you put your code into a code block, so we don't have to open a workbook to see it.

If you post the code, and indicate as clearly as possible what you need, I or anyone else can make suggestions.

For example, if you always know the top left cell of the chart data range, you can use

TopLeftCell.CurrentRegion

to reference the region of data connected to the top left row, that extends out until it reaches blank rows and columns. This will bound the variable data range.

If we could see how you are creating and naming the chart, we could help make the name stick to the chart sheet.

**AKL01** · 09-27-2011, 08:10 AM

I have a question about my error to the forum site, so I'm hoping that will get worked out.

Thanks Jon, I'll do my best to explain.
The data will always start on row 2 of columns G and H. How many rows of data is the variable part. I attached the screen shot of what the base data looks like; the plot data range in this example is G2:H12. Column G is what is used to label the X axis 'bins' and column H is the number being plotted. The values in H could be as high as 30, if that makes a difference. Column G is created from another macro that concatenates the text string from columns D and C (again, I mention it in case it makes a difference-I assumed it didn't matter).

I would like to have the macro create a column plot in a separate sheet (graph on the sheet by itself) for only the amount of data present. The graph title and axis labels won't change.

Below is what I have tried in the macro so far. I had tried a few things that didn't work-I left the attempts in the macro, just commented out so you could see what I had tried. I had also put a few notes in the macro to help as well. The two parts that seem to be tripping me up are getting the range to auto select for the data size and getting the chart on it's own sheet. I was trying to create the graph then move to a new sheet, but the title reference wasn't matching/keeping with the name I picked.

Code:

Sub plot()
'this macro creats the plot

 '  Sheets("GRAPH By System").Select         'delete old chart
 '   ActiveWindow.SelectedSheets.delete
 
  
Sheets("TEST DATA").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnStacked
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=""By System"""
    ActiveChart.SeriesCollection(1).Values = "='TEST DATA'!$H$2:$H$11"          'this is where the range needs to be varied each time.
    ActiveChart.SeriesCollection(1).XValues = "='TEST DATA'!$G$2:$G$11"
    ActiveChart.Legend.Select
    Selection.delete
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    
    
'I 've tried to get the titles automated too, but haven't been able to get the title to translate;
'the 'new' chart is always "By System" but if I use that as the pointer, the macro crashes.  The macro wants to
'use "Chart 17" or whatever number it is creating that time around.


'    ActiveSheet.ChartObjects("By System").Activate
'    ActiveChart.Axes(xlCategory).AxisTitle.Select
 ''   ActiveSheet.ChartObjects("By System""""").Activate
 '   ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = _
 '       "System-Significance Level"
 '   ActiveSheet.ChartObjects("By System""""").Activate
 '   ActiveChart.Axes(xlValue).AxisTitle.Select
  '  ActiveSheet.ChartObjects("By System").Activate
 '   ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Number of Graded Items"
 '   ActiveSheet.ChartObjects("By System").Activate
 '   ActiveChart.ChartArea.Select
 '   ActiveChart.SetElement (msoElementChartTitleAboveChart)
 '   ActiveSheet.ChartObjects("By System").Activate
 '   ActiveChart.ChartArea.Select
    
'     ActiveSheet.ChartObjects("By System").Activate
'    ActiveChart.Location Where:=xlLocation("GRAPH By system")
    
  '     ActiveSheet.ChartObjects("By System").Activate
  '  ActiveChart.Location Where:=xlLocationAsObject, Name:="GRAPH By System"
End Sub

Thanks again all for the help.

**Jon_Peltier** · 09-27-2011, 09:59 PM

For your data range, it's best to use a range variable, start with the top, and use .End to find the end. Add the chart, add the data, add the titles, and finally move the chart. And it's best to do all this without selecting the chart or its components.

Sub MakeAPlot()
    Dim rChartXData As Range
    Dim rChartYData As Range

    ' determine chart data ranges
    With ActiveSheet
        Set rChartXData = .Range(.Range("G2"), .Range("G2").End(xlDown))
        Set rChartYData = rChartXData.Offset(, 1)
    End With

    ' add chart    
    With ActiveSheet.Shapes.AddChart
        ' start from scratch
        .ChartArea.ClearContents

        ' add data series
        With .NewSeries
            .Name = "By System"
            .Values = rChartYData
            .XValues = rChartXData
        End With

        .ChartType = xlColumnStacked
        .Legend.Delete

        ' add titles, assign text to titles
        .Axes(xlCategory).HasTitle = True
        .Axes(xlValue).HasTitle = True
        .Axes(xlCategory).AxisTitle.Characters.Text = "System-Significance Level"
        .Axes(xlValue).AxisTitle.Characters.Text = "Number of Graded Items"

        ' move to its own sheet
        .Location xlLocationAsNewSheet, "GRAPH By System"
    End With

End Sub

**AKL01** · 09-28-2011, 08:16 AM

Jon,

Thank you for the assistance, it's been a great help.

I tried the code you suggested and it made it to

.ChartArea.ClearContents

and had a run-time error (can't rememebr which). I commented it out for one attempt because I was thinking it needed to have something to clear-not sure if that thought is right, but it did make it past that line the second run. I put the line back in, and ran it again, and it still made it past that line. Just FYI.

I am still getting "Run-time error '438' Object doesn't support this property or method" at line

With .NewSeries

I did a little searching and found this link with "The most common cause of error 438 is not maintaining binary compatibility between successive versions of your components." There was an example about different types of drop down boxes as an example as well-but I don't know what to check for compatability. Here is where I am very weak about the inner workings of Excel. Any idea where I should look and what the setting should be?

Also, I really do want to understand what the code is doing and I wanted to see if I am following the Dim and Offset functions correctly; I found this answer for Dim. Summarizing: setting the data ranges as variables using the Dim function allows the ranges to be referenced the same way every run in the plotting section. And for the Offset function, I found this explanation So in the following,

With ActiveSheet
        Set rChartXData = .Range(.Range("G2"), .Range("G2").End(xlDown))
        Set rChartYData = rChartXData.Offset(, 1)
    End With

the End function finds the end of the 'X' data, however long that is, then the offset function selects the same number of rows of data, just one column over for the 'Y' data. Did I understand that correctly? Again, thank you for the help and patience.

**Jon_Peltier** · 09-28-2011, 09:52 AM

My bad. I left off a couple important keywords. I also left off the part that deletes the old chart. Here's the refined code:

Sub MakeAPlot()
    Dim rChartXData As Range
    Dim rChartYData As Range

    ' remove existing chart, but don't error if it's not there
    On Error Resume Next
    Application.DisplayAlerts = False
    ActiveWorkbook.Charts("GRAPH By System").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    ' determine chart data ranges
    With ActiveSheet
        Set rChartXData = .Range(.Range("G2"), .Range("G2").End(xlDown))
        Set rChartYData = rChartXData.Offset(, 1)
    End With

    ' add chart
    With ActiveSheet.Shapes.AddChart.Chart
        ' start from scratch
        .ChartArea.ClearContents

        ' add data series
        With .SeriesCollection.NewSeries
            .Name = "By System"
            .Values = rChartYData
            .XValues = rChartXData
        End With

        .ChartType = xlColumnStacked
        .Legend.Delete

        ' add titles, assign text to titles
        .Axes(xlCategory).HasTitle = True
        .Axes(xlValue).HasTitle = True
        .Axes(xlCategory).AxisTitle.Characters.Text = "System-Significance Level"
        .Axes(xlValue).AxisTitle.Characters.Text = "Number of Graded Items"

        ' move to its own sheet
        .Location xlLocationAsNewSheet, "GRAPH By System"
    End With

End Sub

Next time I'll test more thoroughly.

Edit: I tried color coding parts of the code to highlight changes, but the custom code routine used in this forum does not allow it.

**AKL01** · 09-28-2011, 11:39 AM

That works perfectly. I also tried it in the bigger workbook so I could run the full report-worked like a charm.

I copied the two macros into word to do a side-by-side and I can see the changes (the highlighting was there too). Unless my understanding of Dim or Offset are incorrect, I think I am following all of what is going on in this. Thank you very much for the learning experience.

Very big help, thank you very much.

(I'll work on marking thread as solved.)