MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Chart formatting questions

  Asked By: David    Date: Sep 09    Category: MS Office    Views: 684

I'm a new guy writing to ask for help on a specific formatting issue
for XY scatterplots, and for recommendations for books that will help me.

I use Excel to analyze data for different experiments, one experiment
per column, with a time column to the left. Below the experimental
data in the same column I have a model (a curve fit if you will) that
I generate. When I plot the data I include the model on the plot. So
a plot includes data points vs time and a line for the curve fit/model
vs time.

I had a pretty extensive VBA setup in Excel 2003 to do stuff like plot
the data as points and the model as a line, and to fit the data using
solver. 2007 broke my code. I know the smarter thing to do would be
to reinstall XP Pro and 2003. In fact, I will likely do this anyway,
as I need a stable, functioning O/S and application base and
Vista/2007 ain't it. But I'm stubborn. So I'm rewriting my code.

I can't seem to set my data point series so that it is points, not
points plus lines. Some code snippets below should tell you where
I'm at. I've added question marks on the lines I want to fix. I'm
probably using the wrong object properties. Any help appreciated.

By the way, I don't think I'm alone in my dissapointment with
Microsofts latest main products (Vista and Office 2007). Not only
missing stuff that it used to have (e.g. a functioning macro
recorder!) but it's bloated and confusing. For guidance, I've never
found the VBA help (including the F2 - Object Browser) to be very
useful (to be honest, I've never found ANY of Microsoft's help pages
useful. Ask about a topic, you get a thousand listings that are
tangentially related to your question. I find Google is my best
Microsoft help page). As far as books, John Walkenback's book on
Excel 2007 macros has been pretty useful but doesn't have the detail I
need for my work. Is there a consensus on other useful information?
Jelen's book? Nutshell guides?

Code follows:

Range(modrange).Select ' Only the y model data to start
ActiveSheet.Shapes.AddChart.Select ' Add the chart
idx = GetChtIndex()
nam = GetChtName()
SetChtName (label) ' I just like to name charts what I want to
With ActiveChart
.SetSourceData Source:=Range(modrange)
.ChartType = xlXYScatterSmooth
modtimrangef = "='" & acsht & "'!" & modtimrange
.SeriesCollection(1).XValues = modtimrangef ' Add the time points
Range(datrange).Select ' Add data to this chart
dattimrangef = "='" & acsht & "'!" & dattimrange
.SeriesCollection(2).XValues = dattimrangef 'add model time data
With Selection
.MarkerStyle = xlMarkerStyleNone ' This works - gives a line
.name = label 'VBA bug - "Name" case changed to "name"
End With
With Selection
.name = "=""Model"""
.MarkerStyle = xlMarkerStyleCircle
.MarkerSize = 7
.MarkerLine??? ' I want to set to red, 1 pt
.MarkerFill??? ' I want no fill
.LineStyle???? ' I want no line, only data points
End With



No Answers Found. Be the First, To Post Answer.

Didn't find what you were looking for? Find more on Chart formatting questions Or get search suggestion and latest updates.