VBA and Charting Named Ranges?

[Bill Hertzing]

Our developers write out a .csv file, with row 1 being the strings for
the column titles. I can read these .csv files into a worksheet, so
that column 1, for example is titled "LogTime", and Column 10 is
TimeOnDisk. I can create a named range for each that covers just the
cells that are populated e.g.($A$2:$A$2280). I can use the Names
drop-down to verify the named ranges are correct. I have to do this,
because in the next release, the developers may add or delete columns
of data, but have promised to keep the column names. And the number of
rows varies by the number of days worth of .csv files that get read
in.

I want to create a macro to chart these two columns (using the named
ranges). My current 'best guess' is:
ActiveChart.SetSourceData Source:=Sheets(DSPage).Range(Names
_("LogTime").RefersToRange, Names("TimeOnDisk").RefersToRange) _
, PlotBy:=xlColumns

But this, nor a whole days worth of trying other combinations, has
turned up the successful incantation. I'd sure appreciate any ideas.

 

[Jan]

Bill

I don't know if you realy need the named range but maybe you can try the following to define the range of sourcedata for your char

Dim rng As Rang
Set rng = Cells(1, 1
Set rng = Range(Cells(1, 1), Cells(rng.End(xlDown).Row, 1)

You can always redifine your named range with the range you create
Grtz

 

[Jon Peltier]

Bill -

Don't do the entire source data. Do the series.

Dim srs As Series
With ActiveChart
' use this if the series doesn't exist yet
Set srs = .SeriesCollection.NewSeries
' use this if the series does exist
' with appropriate index in paren
Set srs = SeriesCollection(1)
End With
With srs
.Values = Worksheets(DSPage).Range("TimeOnDisk")
.XValues = Worksheets(DSPage).Range("LogTime")
.Name = ' whatever
End With

For some hints for charting with VBA:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html
http://peltiertech.com/Excel/Charts/chartvba.html

- Jon

 

[Tushar Mehta]

On the Excel | Tutorials | Dynamic Charts page of my web site is a link
to 'using these named formulas in charts.' That explains how. Turn on
the macro recorder (Tools | Macro > Record new macro...) before you do
do by hand and XL will give you the necessary syntax.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions