XY Chart - Category Labels through VBA

[Frank & Pam Hayes]

I have created a short VBA macro that reads x and y data from a spreadsheet,
where every row represents a new series. Besides the x and y data colums, I
also have columns that specify the size of the marker, the markerstyle, and
the foreground and background colors. I have adapted some of John
Walkenbach's code to label each of the datapoints using .name.

The x axis is basically an integer 1 to 5 and the y axis is a value like
price or variable margin. There usually are multiple datapoints on any given
integer on the x axis. For example, I may be plotting the price that a
customers get in three different countries (US, Canada, and Mexico). I
arrange all my data items with the x value equal to 1 (for the US), 2(for
Canada) and 3(for Mexico) along with the corresponding y value equal to the
price that customer receives.

Ploting this results in the graph I want to see, except with 1, 2 and 3 as
the category labels for the x axis. I would like to be able to change the x
axis label to US, Canada, and Mexico but still keep my x values at 1, 2, and
3. I also want to do this within the VBA routine where the other
seriescollection information is determined.

Please note ... each datapoint is its own series. Any ideas?

Thanks

Frank

 

[Jon Peltier]

Frank -

Start by plotting a dummy series, with X values {US, Canada, Mexico} and Y values
{0, 0, 0}. Make it a column chart. Then add your one-point series to the chart,
making each an XY Scatter series. Excel may automatically add secondary axes for the
XY series, but you can remove them and the chart will use the primary axes, plotting
1,2,3 over the X categories.

You could also create a dummy axis with a dummy series:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html
http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html

But I think a dummy column chart series is easier.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Frank & Pam Hayes]

Jon,

Thank you for the advice, but I can not seem to get this to work with
multiple Y values in each x series. Excel wants to treat each Y value in a
separate X value. To continue my example, there may be three customers in
the US, four customers in Canada, and two customers in Mexico. I want to
show the variable margin for each customer, and I only want to have three x
values.

Frank

 

[Jon Peltier]

Frank -

If you add your additional points as XY Scatter series, all you need are the X
values 1, 2, or 3. These will line up above the country names.

I made a very simplistic example to show you the steps:

http://PeltierTech.com/Excel/Zips/ScatterCategoryAxisForFrank.zip

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Frank & Pam Hayes]

Thank you Jon ... I get it now! I appreciate your going to the trouble of
crafting the example.

Frank