Select X axis with macro charting

bkelly

Active Member
Joined
Jan 28, 2005
Messages
465
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)
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Bryan

What exactly is Charting_Range?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
I am famished and must go eat lunch. When I get back I will try these suggestions.
Thank you very much for your responses.
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top