Select X axis with macro charting

bkelly

Active Member
Joined
Jan 28, 2005
Messages
444
Late edit: After reading this post I realized that it appears that I am blaming Jon's code for my errors. The contex in which I used his code is different from that in which he wrote it. I don't really understand the nuances of most of the Excel extensions of VBA and that lack of understanding is the cause of the problem, not Jon's code.
bkelly

From this page on Jon Peltier’s web site:
http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

I found some example code to help with some macros that I use to create charts. Almost all the time, the macros I wrote (with much help from people in this forum) work just fine. But with some data sets, using the same code, Excel wants to plot the X data on the Y axis and this screws up the entire chart. The core of my charting macros is:

Code:
ActiveChart.SetSourceData source:=Charting_Range, PlotBy:=xlColumns

Charting_Range is a union of ranges, each of which represents an entire column. The left most column always becomes the X axis. I don’t specify that, it just seems to work that way. As I understand Jon’s web pages, these following snipits should work.

These two lines of code, tried one at a time:
Code:
ActiveChart.SeriesCollection(1).XValues = Range("B1:B2829")
ActiveChart.SeriesCollection(1).XValues = Range(Charting_Range)

And these two lines tried together
Code:
'ActiveChart.SeriesCollection.NewSeries
'ActiveChart.SeriesCollection(6).XValues = "=data!R1C3:R2829C4"

Both produce the following error.
Run Time Error: :1004’: Method ‘Range’ of object ‘_Global’ failed

The following lines:
Code:
With ActiveChart.SeriesCollection.NewSeries
   .Name = ActiveSheet.Range("data")
   .Values = ActiveSheet.Range("D1:D2829")
   .XValues = ActiveSheet.Range("C1:C2829")
End With

Produce this error:
Run-time error ‘438’:
Object doesn’t support this property or method

In all of these above snipits, the X axis should be from column C. The variable Charting_Range is set to the entire column. The range specifier C1:C2829 is intended to represent that same range.

Range specifier R1C2:R38294C4 was intended to contain columns C and D in an attempt to chart column C on the X axis and column D on the Y axis. As noted, it does not work for me.

What can I write that says “Take this range and put its values on the X axis. Then take this other range and put it on the Y axis.”

(edited to correct a typo)
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
I'm not into VBA, but, your stated
Code:
ActiveChart.SeriesCollection(1).XValues = Range("B1:B2829") 
ActiveChart.SeriesCollection(1).XValues = Range("Charting_Range")
might be necessary to change into
Code:
ActiveChart.SeriesCollection(1).XValues = Range("B1:B2829") 
ActiveChart.SeriesCollection(1).YValues = Range("C1:C2829")
At least, that seems to make some sense to me. Am I way off base?

I don't know if one can code which column is to be the x-alues and which the y-values, as when using the Chart Whizard, the first column is always charted as the x-values. Perhaps, in VBA, one can specify which is which, as in my proposed code change, above?
 

bkelly

Active Member
Joined
Jan 28, 2005
Messages
444
Hello Ralph,
Thanks for your reply.

When I stated:

These two lines of code, tried one at a time:
Code:
ActiveChart.SeriesCollection(1).XValues = Range("B1:B2829") 
ActiveChart.SeriesCollection(1).XValues = Range("Charting_Range")


I meant that the line:
ActiveChart.SeriesCollection(1).XValues = Range("B1:B2829")

solicited the error and the line

ActiveChart.SeriesCollection(1).XValues = Range("Charting_Range")

solicited the same error at a different time. When reading your response, I found an error. The second line should have read:

ActiveChart.SeriesCollection(1).XValues = Range(Charting_Range)

I really need to use the variable because the column location for the X axis often changes. I used the quoted value to simplify the code in order to get the syntax correct.

...as when using the Chart Whizard, the first column is always charted as the x-values.


The statement is correct,..., almost all the time. The problem is that in a few charts, Excel puts the first column on the X axis, then also puts that data on the Y axis. The entire chart gets screwed up.
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
The problem is that in a few charts, Excel puts the first column on the X axis, then also puts that data on the Y axis. The entire chart gets screwed up.
Is this for your VBA code, or for the Chart Whizard? If the latter case, could you furnish a simple example, with, perhaps, the first four or five x- and y-values to experiment with?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Bryan

What exactly is Charting_Range?
 

bkelly

Active Member
Joined
Jan 28, 2005
Messages
444
Norie,

Charting_Range is

Dim Charting_Range As Range

I use it in the following manner:

Code:
Set Charting_Range = Select_Column_By_Name(Column_Name)

Column_Selector = Column_Selector + 1
Do While Column_Selector <= Column_Count
  Column_Name = Extract_Column_Parameter(Column_List, Column_Selector)
  Set Temp_Range = Select_Column_By_Name(Column_Name)
  Set Charting_Range = Union(Charting_Range, Temp_Range)
  Column_Selector = Column_Selector + 1
Loop

Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData source:=Charting_Range, PlotBy:=xlColumns

I have a generic function that accepts the column names of N number of columns, selects those columns, unions them together, builds a chart, then does a bunch of formatting.


Does that answer the question?

I would like to post code and data, but there is a problem. I have created a bunch of Excel macros in VBA that import a text file, format the data, display a list of charts to create, create the charts, and use a worksheet of notes to specify the X and Y ranges, the colors of the line, the width of the lines, the title of the charts, etc. I can start with a couple of meg of text data and in two or three minutes build a dozen charts, all labeled, scaled, and colored consistantly. That is a lot of code and it will take a long time to extract out a workable subset to post here.

Is there something else I can tell you without going to that level of effort.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Bryan

If is declared as a range you don't need Range in the code when referring to it for the chart.

Perhaps this is what you want?
Code:
ActiveChart.SeriesCollection(1).XValues = Charting_Range
 

bkelly

Active Member
Joined
Jan 28, 2005
Messages
444
I am famished and must go eat lunch. When I get back I will try these suggestions.
Thank you very much for your responses.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,922
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
A few comments and observations:

What kind of chart are you creating? What kind of data is in column C, column D, etc.

But with some data sets, using the same code, Excel wants to plot the X data on the Y axis and this screws up the entire chart. The core of my charting macros is:

Code:
ActiveChart.SetSourceData source:=Charting_Range, PlotBy:=xlColumns

With SetSourceData, Excel tries to guess what to do with the data. If you make an XY chart, the first column is always used for the X values. If you make a Line/Area/Column type, Excel compares the first column to the rest. If it looks the same (similar type of values) you might get all columns as Y values for different series, and the array {1, 2, 3, ...} for X values. However, if the first column contains dates or text labels, or if you have left the top left cell blank, Excel will use the first column for X. If the top left cell is blank, Excel will also use the top row for series names.

These two lines of code, tried one at a time:
Code:
ActiveChart.SeriesCollection(1).XValues = Range("B1:B2829")
ActiveChart.SeriesCollection(1).XValues = Range(Charting_Range)

Don't just say "Range". Tell Excel where the range is:

ActiveSheet.Range("B1:B2829")
ActiveWorkbook.Worksheets("My Data").Range("B1:B2829")

If you've created a chart sheet, there is no range, so you need to specify a worksheet.

And these two lines tried together
Code:
'ActiveChart.SeriesCollection.NewSeries
'ActiveChart.SeriesCollection(6).XValues = "=data!R1C3:R2829C4"

Both produce the following error.
Run Time Error: :1004’: Method ‘Range’ of object ‘_Global’ failed
Neither of these lines include the Range keyword, and both are commented out.

The following lines:
Code:
With ActiveChart.SeriesCollection.NewSeries
   .Name = ActiveSheet.Range("data")
   .Values = ActiveSheet.Range("D1:D2829")
   .XValues = ActiveSheet.Range("C1:C2829")
End With

Produce this error:
Run-time error ‘438’:
Object doesn’t support this property or method
See my comment above about the active sheet when creating a chart sheet (no cells, so no range, in a chart sheet).
 

Watch MrExcel Video

Forum statistics

Threads
1,113,919
Messages
5,545,027
Members
410,647
Latest member
bernardazar
Top