VBA and Charting Named Ranges?

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.
0
1/20/2004 4:12:24 AM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
544 Views

Similar Articles

[PageSpeed] 49

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
0
anonymous (74722)
1/20/2004 8:36:08 AM
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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Bill Hertzing wrote:

> 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.

0
1/20/2004 1:54:06 PM
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

In article <8157eae4.0401192012.ba29ef4@posting.google.com>, 
Bill.hertzing@mindspring.com says...
> 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.
> 
0
1/20/2004 1:58:37 PM
Reply:

Similar Artilces:

population pyramids using bar charts with a secondary axis
I have a problem that I had given up as unsolvable, but after recently learning about secondary axes, I've made encouraging progress. However, I'm stuck on the last step(s), and I'm hoping that someone will have some suggestions. My organization has been producing reports that contain population pyramids. In the past, all of their reports have been printed, so it didn't matter that in order to get the desired look, they had to use two charts slightly overlapping each other. However, we are getting to the point where we would like our charts to be available online for download....

First row in Selection range (first index of a cell)
Hello, I have a problem with selecting first cel in selection Range or return an index of the first cell in Selection Cell. I have something like this: .................... Range1.Select "and here I want to Select the first range in selection Range1" ............. I there any special function of finding first cell in selection range or returning an index of the first cell?? Thanks for answet Marcin Maybe range1(1).select or range1.cells(1).select or range1.cells(1,1).select mar_male@wp.pl wrote: > > Hello, > I have a problem with selecting first cel in selection...

Closing another database with VBA
Hi, I’m having a problem with the code to do the following… 1- A user opens a file called Core. 2- Upon opening the splash screen (on the Form_Load event), it compares a version number it finds within the database to one that is in another database called Data. 3- If the versions are different, the Core file opens the Updater file. 4- The Updater file closes the Core file. 5- The Updater file copies the latest version of the Core file (located somewhere else) and replaces the first one with the latest version. 6- The Updater opens the newly copied Core file. 7- The Updater closes itself. ...

VBA Autocomplete
I am having trouble with autocomplete that has stop working in the IDE while coding. Then it will suddenly start again for a while, before stopping... I have tried restarting excel and the PC but no joy Any ideals what causes this? Win 2000 Excel 2000 VBA IDE 6.0.8714 ...

Export a range to a text file
Hello need some advise on how to procede I need to be able to create a text file containg some text as well as data that is within a named range in excel and then some more text. I can handle printing to the text files using cell values etc but am unsure of the best way to print the ranges data. Is there a way or procedure to just print the range as is in csv format? As well my range will contain about 6 columns, each containg a number field (formatting of decimal places is important, some have 2 dec some 3 etc) Also the range has a max of 50 rows however will always contain lower rows of...

Step Line Charts
Does anyone know how I can create a step line chart without having to have 2 data points per x-axis point. What I want is a horizontal line until the next data point and then I want the line to go vertical. Hi Carrie, You Will need some form of extra data to get excel to create a step chart. Take a look at these methods. (http://www.andypope.info/charts/stepchart.htm) Carrie wrote: > Does anyone know how I can create a step line chart without having to have 2 data points per x-axis point. What I want is a horizontal line until the next data point and then I want the line to go vert...

VBA Command to Deselect a Shape
I've written a VBA program that drops shapes (from a stencil I created) on a page, but at the end of the exercise the last shape is selected (it has a dotted line surrounding it). Is there a VBA command that will deselet this shape (or select the page on which it sits? Mike On Fri, 22 Sep 2006 12:16:02 -0700, MikeM <MikeM@discussions.microsoft.com> wrote: >I've written a VBA program that drops shapes (from a stencil I created) on a >page, but at the end of the exercise the last shape is selected (it has a >dotted line surrounding it). Is there a VBA command that ...

Calling VBA subroutine from a query?
Can I call a VBA subroutine from within an Access query? I wrote some English to Metric conversion routines in the Access VBA code and would like to run a query on the data that will return coverted values. I need to be able to execute this from outside the database (run the query from another program). Yes. The function must be a Public Function in a standard module. You use a calculated control to return the results of the query. ConvertedValue: MyFunction([SomeField]) -- Dave Hargis, Microsoft Access MVP "DavidY" wrote: > Can I call a VBA subroutine from within an ...

name appearing in the outgoing mail
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop I would like to change the way the name in each of my accounts shows up in sent emails. I have changed the account settings but the old name still shows up. Any suggestions? <br> Thanks. On 2010-05-04 16:45:26 -0400, Bila@officeformac.com said: > I would like to change the way the name in each of my accounts shows up > in sent emails. I have changed the account settings but the old name > still shows up. Any suggestions? Well if you have indeed changed the name prop...

how can I show repeating values in a chart?
I would like to show modes in the form of a pie chart but am not sure how. For example the number 73 comes up 3 times in a column on my spreadsheet, how can I show that compared to the number 50 which come up 2 times in the sheet? Thanks for the help Hi, You will need to compute those values using formula or a pivot table and then chart the results. Cheers Andy Cindy wrote: > I would like to show modes in the form of a pie chart but am not sure how. > For example the number 73 comes up 3 times in a column on my spreadsheet, how > can I show that compared to the number 50 whi...

more symbols in chart
I need to chart an experiment data for 28 monkeys. I wanted to use a separate symbol for each monkey. I am limited to black and white only, so I can't use multiple colors. I am experimenting on changing white, black for back ground color, fore color, to make a distinct symbol for each monkey. Does any body know how do we get more symbols on the chart? Thanks in advance for any help, sheela Jon Peltier has instructions for Custom series markers: http://www.peltiertech.com/Excel/ChartsHowTo/CustomSeriesFormatting.html sheela wrote: > I need to chart an experiment data for 28 ...

Drop Down List for Chart
I have grouped my series into several groups. How do I display a drop down list including the groups which will dynamically identify which series to plot on the chart? For example: Group A = Series 1, Series 2, Series 3 Group B = Series 4, Series 5 Group C = Series 6 Drop down list includes values: "Group A", "Group B", "Group C" When Group A is selected within the drop down, Series 1, 2 and 3 are plotted on the chart. Do you mean you have to plot data, and have the chart move week to week? if so, easiest way is to use a hlookup/vlookup "savior1&quo...

Truncated "y" axis label in chart
:confused: Hi guys I have a problem with Excel, the label on the �Y� axis appears truncated even though I have resized the chart. Then, I looked the file in another computer with the same version of excel than mine as well as in another with a different version and in both look fine. Thus, I erased and re-installed excel in my computer to no avail. I have also re-sized the chart and used it both as object embedded and in separate sheet, it doesn't fix the problem. Any suggestions to fix this problem? -- flakkortin -----------------------------------------------------------------------...

How do I assign labels to scatter charts
Hi. I'm making an x-y scatter chart but can't assign labels to the individual points. How do I do this? Many thanks in advance for your help. Tom Hi, Here is an explanation of how to link a chart text element to a cell. http://www.andypope.info/tips/tip001.htm But if you have more than a couple of data labels to do you really need to use code. This free addin will do it for you. http://www.appspro.com/Utilities/ChartLabeler.htm Cheers Andy ThomasStudd wrote: > Hi. > > I'm making an x-y scatter chart but can't assign labels to the individual > points. H...

How to reference workbook name in hyperlink formula?
In order to simplify the maintenance of workbook formulas I need to reference workbook name in hyperlink formula =[myWorkbok.xls]mySheet!$E4 in this way =[A1]mySheet!$E4 given the file name stored in cell A1 Cell A1 : myWorkbook How do I write the formula to make it work? Will it work even if the referenced workbook is closed? Regards Frank Krogh The function you'd want to use is =indirect(). But =indirect() won't work if the sending file is closed. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm ...

How can I compare the column names from 2 tables and output them?
I have a requirement to place an alert if the field does not exist in my table, tblStaging. Can any one guide me to reframe this query with error handling messages? I know it is not good practice to use select * but I need to do this as my columns\field names change each time. INSERT INTO tblStaging SELECT * FROM tbl_XL,tblDetails; Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1 Access is a relational database. If your "table design" has the fields in your table(s) changing frequently,...

Changing the names of fields in tables after creating other object
I just leaned about the naming conventions after I have created my tables, reports and several queries. Can I change the name of fields in my tables (to remove the spaces and give them unique names ie not just last name but childlastname) without destroying the work I have done in queries, reports, and forms. Mary -- Positive Direction for Youth & Families, Inc. (www.pdfyinc.com) Possibly. If you are using a newer version of Access, say 2003 or 2007 AND you have Name Autocorrect, and all it's options, enabled, it MIGHT work. I found it somewhat buggy. Before you...

VBA to import 344 objects from 97 to 2003
I am looking to import a select list of tables, forms, queries, reports and macros from Access97 into Access2003. Yes this can be done by selecting each individual object, but I need to do this 5 or 6 more times before I do it for the final production copy. Pieter, Ken, and John have all been great resources in previous VBA questions, I would greatly appreciate any/all assistance. Thank you!!! The import dialog lets you select all items on the form. Since you're only talking 5 or 6 times, it's probably faster to do that, rather than bothering to write code to do it. -- Dou...

User login failed on the customized VBA project after GP 10 upgrad
After GP 10 upgrade, I am getting the login failed error for all useers who tried on working on my customized Dynamics VBA project. Error # -2147217843: Login failed for user 'sa'. Procedure: frmDateEntry - cmdProcess_Click Source: Microsoft OLE DB Provider for SQL Server Thanks for help. Kevin Kevin, You might need to re look at your connection in VBA -- Thanks Janakiram M.P. MCP-GP http://janakirammp.blogspot.com "Kevin Zhou" wrote: > After GP 10 upgrade, I am getting the login failed error for all useers who > tried on working on my customized Dynamic...

3D Pie Chart
Hello all-I have created a 3-D pie chart in Excel and would like some of the pies to be thicker than others...raised higher, thicker, like have the same foundation but the ones I am discussing during the presentation to raise above the others not seperating from the pie. Does that make sense? I am using Excell 2007 but also have access to 2003 and the newest version. If anyone knows how to do this any help woould be so greatly apprecaited. THANKS! Hi, Excel is not designed to do that, maybe a simple alternative would be to explode the segment of the pie you want to emphasize. To e...

Scatter Charts #3
Hi, I am plotting a XY chart. I want that every point should be of different colour. Possibly I can define a range of 2 columns where column 1 contains the name for which the point is plotted and column 2 contains the colour for that point. I have seen this being done in one chart but that person is no longer available for help Please tell me how is it done Thanks Shilps Hi Shilps - Double click on the chart, and on the Options tab, check the Vary Colors by Point box. Now that you know where it's hidden, you'll become the resident expert! - Jon ------- Jon Peltier, Microsoft Excel...

Pivot Table VBA Reference Book & Blank Cells
Hi Everyone, I have a Pivot Table in Access 2003 and need the blank/null cells on the data axis to display "0". This is easy to do in Excel but I have searched through all of the options in Access and cannot find it. What is the VBA code required to set this option? Can anyone suggest a good reference book or website for coding Pivot Tables with VBA? Many thanks, David ...

Hold a cell range for an "average formula"
I add a column every week to my work sheet and want the formula to always calculate the current 5 column range. Wendy Akers wrote: > I add a column every week to my work sheet and want the formula to always > calculate the current 5 column range. > > Add a column WHERE? What "formula"? Where is the "current 5 column range"? Hi, Let's say your data is in range C3:H3. In cell K3, enter the following array formula (Ctrl+Shift+Enter) to get the average =AVERAGE(OFFSET(INDIRECT(ADDRESS(3,MAX(ISNUMBER(C3:J3)*COLUMN(C3:J3))+1)),0,...

Reprsent negative values in excel charts
Hi, Can someone help in how to reprsent the negative values in the chart and which is the best chart to represent it? What type of chart are you making? In a line, column, bar, XY, or area chart, the negative values are merely plotted on the other side of an axis. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "uha" <uha@discussions.microsoft.com> wrote in message news:166604B5-A331-4EE6-8010-D921C1EEA017@microsoft.com... > Hi, > Can someone help in how to reprsent t...

report with VBA not working after upgrade
we did an upgrade from v7.5 to v8, and one of the report can't show vba modified fields. any idea how to fix it? Thanks If you are accessing SQL Data, you probably need new Retrieve Globals. Go to partner source, and download RetrieveGlobals_80.dll, and register this file using RegSvr.exe. The instructions are included in the zip file. Let us know if this doesn't fix your problem. Thx, VJ ...