Efficient Chart Macro

I am using the below procedure (copy from Record Macro) to generate 1
graphs, each graph has 4 rows X 50 columns, by supplying ranges o
data. 

In my opinion, the code is not very efficient  because it take quite 
wait to plot and during the process of plotting a chart, it redraws th
chart many times.

Is there any codes that I can be omitted or rearranged to make it plo
faster? 

Appreciate your help.


Sub PlotProgressCurve(pSect As String, pRangeDate As String, pRangeN
As String)
Dim tRangeDate, tRangeNo As String

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(pRangeNo)
PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!" & pRangeDate
ActiveChart.SeriesCollection(2).XValues = "=Sheet1!" & pRangeDate
ActiveChart.SeriesCollection(3).XValues = "=Sheet1!" & pRangeDate
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=pSect
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Drawing Progress Curve of " 
pSect
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "No. o
Drawings"
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlTop
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = False
ActiveChart.DataTable.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 4
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 5
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.Orientation = xlUpward
End With
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlDot
End With
ActiveChart.PlotArea.Select
Selection.Top = 32
Selection.Height = 405
ActiveChart.Legend.Select
Selection.Left = 50 '74
Selection.Top = 50 '54
With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.Interior.ColorIndex = xlAutomatic
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With

With ActiveChart.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = "&D"
.LeftMargin = Application.InchesToPoints(0.4)
.RightMargin = Application.InchesToPoints(0.4)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.3)
.ChartSize = xlFullPage
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.BlackAndWhite = False
.Zoom = 100
End With
End Sub


---
Message posted from http://www.ExcelForum.com/

0
5/19/2004 5:11:25 AM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
647 Views

Similar Articles

[PageSpeed] 10

I haven't gone through the unformatted code, but one suggestion that 
comes to mind is to include a Application.ScreenUpdating=False 
statement at the top of the code and a complementary =True at the end.

-- 
Regards,

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

In article <tschng.16hm50@excelforum-nospam.com>, tschng 
<<tschng.16hm50@excelforum-nospam.com>> says...
> I am using the below procedure (copy from Record Macro) to generate 12
> graphs, each graph has 4 rows X 50 columns, by supplying ranges of
> data. 
> 
> In my opinion, the code is not very efficient  because it take quite a
> wait to plot and during the process of plotting a chart, it redraws the
> chart many times.
> 
> Is there any codes that I can be omitted or rearranged to make it plot
> faster? 
> 
> Appreciate your help.
> 
> 
> Sub PlotProgressCurve(pSect As String, pRangeDate As String, pRangeNo
> As String)
> Dim tRangeDate, tRangeNo As String
> 
> Charts.Add
> ActiveChart.ChartType = xlLineMarkers
> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(pRangeNo),
> PlotBy _
{snip of unformatted code}
0
5/19/2004 1:01:12 PM
Yup.  This really does speed things up!

-- 
   Toby Erkson
   Oregon, USA
   Excel 2002 in Windows XP

"Tushar Mehta" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
news:MPG.1b1524a7ed0ca9d39897d6@news-server...
> I haven't gone through the unformatted code, but one suggestion that
> comes to mind is to include a Application.ScreenUpdating=False
> statement at the top of the code and a complementary =True at the end.
>
> -- 
> Regards,
>
> Tushar Mehta
....


0
5/19/2004 3:49:05 PM
Toby -

You also have a number of constructions like this:

   ActiveChart.Legend.Select
   Selection.Position = xlTop

You generally don't need to waste time selecting an object before 
changing it, so the above can be shortened to this:

   ActiveChart.Legend.Position = xlTop

Lines like this merely reset the already default property to the 
default, so you could ditch them:

   .ColorIndex = xlAutomatic
   .Background = xlAutomatic

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


Toby Erkson wrote:

> Yup.  This really does speed things up!
> 

0
DOjonNOT (619)
5/20/2004 2:35:21 AM
I know (from reading your web pages) but I think the OP tschng didn't ;-)

Much of the 'stuff' in the WITH...END WITH statements can be removed because
they are default values:
With Selection.Font
    Name = "Arial"
    FontStyle = "Regular"
    Size = 8
    Strikethrough = False
    Superscript = False
    Subscript = False
    OutlineFont = False
    Shadow = False
    Underline = xlUnderlineStyleNone
    ColorIndex = xlAutomatic
    Background = xlAutomatic
End With

....would become...
With Selection.Font
    Name = "Arial"
    FontStyle = "Regular"
    Size = 8
End With

....and that's assuming Arial isn't the default font otherwise the whole
WITH...END WITH can be removed; I'm making the assumption that the Font info
is not needed but being the newb that I am I could be wrong as some processes
seem to require this stuff.  I'm sure an expert here can expound on this.
Naturally, I comment stuff out and test it before I actually delete it because
I never know...

-- 
   Toby Erkson
   Oregon, USA
   Excel 2002 in Windows XP

"Jon Peltier" <DOjonNOT@SPAMpeltiertechME.com> wrote in message
news:%23T3EbMhPEHA.3300@TK2MSFTNGP09.phx.gbl...
> Toby -
>
> You also have a number of constructions like this:
>
>    ActiveChart.Legend.Select
>    Selection.Position = xlTop
>
> You generally don't need to waste time selecting an object before
> changing it, so the above can be shortened to this:
>
>    ActiveChart.Legend.Position = xlTop
>
> Lines like this merely reset the already default property to the
> default, so you could ditch them:
>
>    .ColorIndex = xlAutomatic
>    .Background = xlAutomatic
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> Toby Erkson wrote:
>
> > Yup.  This really does speed things up!
> >
>


0
5/20/2004 3:04:21 PM
Reply:

Similar Artilces:

Can not get Excel macros to save chart format -lost in space!
Help??? I've been searching this forum for 5 days trying to find a problem similar to what I am having without any luck. I have been trying to use Excel macro to record the creation, formating and saving of a simple bar chart against my Excel data range but the macros do not run for the chart. I am able to creat the charts okay but the macro craps out when I run it. I keep getting " Set ActiveChart = ActiveSheet.ChartObjects(Chart1)" type errors as if it's not recognizing the chart that I just made. I am an advanced Excel person BUT not so with VB coding. ...

Help with Charts
:confused -------------------------------------------------------------------------------- I work in a Doctors surgery and I'm creating a Spread Sheet for select number of patients to monitor their blood results. I need about 30 charts on each patients sheet, To save me doing eac one seperately I have tryed to copy and paste them in to the nex patients details.... But all the charts refer to the previous patient details. Is there anyway of copying them so that they relate to th sheet they are copied in to? Thanx Alexandra Huckerby (fustrated and tired!! -- ~lexy -----------------...

Overlapping bars within chart
Sample scenario: Let's say in each year, I have this many cats: 2010: 6 2011: 8 2012: 20 Out of those cats, let's say that the following number have been neutered: 2010: 1 2011: 4 2012: 18 Is there a way for me to create a form of bar chart that shows the total number of cats per year as a "background" bar, with the total number of neutered cats in each year as a skinnier "foreground" bar that is a different color and overlays the background bar? I just figured this out on my own. As an FYI to anyone else, here was my solution: 1. Plot the ...

Chart Title or Text Box Linked to cell & sheet
I need to create charts from data collected via a com port. As i stands, the data is written to sheet 1. Chart 1 is ready to plot dat as it is written. Once the data is complete, I copy sheet 1 and char 1. This results in sheet 1(2) and Chart 1(2). All references to shee 1 on the newly created chart 1(2) automatically changes to refer t sheet 1(2). This applies to data ranges, Chart Title, Axis names. When creating text boxes refering to sheet1!$A$1, the reference doe not automacially change to sheet 1(2)!$A$1. Can this be done? If not, can I create multiple chart titles? Any help o...

Timeline bar chart
Hello, I am trying to create a bar chart based on key dates in a project. There are 20 different part numbers, all with individual timelines. Some parts of the process take many days, some one, etc. I want a bar chart that signifys these key dates, as well as shows a bar running throught the course of each individual aspect (ie, 12/2 - 12/8). I am having trouble doing this with dates. Any help would be greatly appreciated. -- shane476 ------------------------------------------------------------------------ shane476's Profile: http://www.excelforum.com/member.php?action=getinfo&u...

Extracting macros from a worksheet #3
Gord, Thanks for the info. I have Excel setup to ask me about enabling and disabling macros when opening a worksheet. I've tried both with no luck. I get the same error when attempting to open this file. The workbook that I'm opening has always worked before. That workbook and macros were designed and written by me and they have been working for more that 5 months now. There are only two worksheets in the workbook and I've never had any reason to group them together, however, when the workbook opens with the error, I can see that the two sheets are, in fact, selected. In add...

Keep Pivot Table custom chart type
I'm creating a pivot table custom chart type "Line - Column on 2 Axes". The chart looks great when I create it. The problem is when the user does anything to the chart (filters the data, adds a new flield of data, etc.), the chart changes to where both data series are displayed as bar charts and the second axis is gone. I can't find an event that fires when they modify the chart, so I don't know that I can programatically change it back. Is there a way to keep it from changing in the first place? I'm using Excel 2003. Thanks! Loss of formatting is a known prob...

Make (save) a file by using macro recorder.
Hi all, I am new to macro and using macro recoder to create macro. On one file which I recieve every week from material department. I need to reformat and create pivot table. I use macro recorder and everything is working good. However I need that when my recorded macro end it create a file with the same name with the date and time it was create. I need every week a new file and I dont want to replace the one. Please let me know how I can create a new file with the date and time everyweek when I run my macro. Thanks -- Farhan Hi Farhan This should do it, just insert you...

Rotating a line chart?
Is it possible to rotate a line chart so it looks like a vertical chart rather than a horizontal chart? And if so, once it is rotated, can one still interact with the chart and it's data? Thank you. Visit Excel Tips and Cases http://www.tushar-mehta.com/excel/tips/index.html and scroll down to 'Invert and rotate charts' The direct link http://www.tushar-mehta.com/excel/tips/0201-chart%20invert%20and% 20rotate.pdf is almost certain to change in the near future. The above applies to a XY Scatter chart. Hopefully, you can use that instead of a line chart. -- Regards, Tusha...

Simple charting question
I don't believe I'm having so much trouble with this. I'm trying to make a graph with Temperature (Celesius) as the X-axis and Baseball Speed (Km/hr) as the Y-axis. When I highlight the columns below and try to make a bar graph - 0 44.1 1 47 3 44.7 4 43.5 5 50.2 13 59.9 I get two series plotted or shown seperately. I would like to get one of the following: ...

Combining Stack bar and Line Charts
I'm trying to display 2 sets of data within the same chart; I want to display yr 1 data in a stacked bar format and yr 2 data in a line format. Both data sets would use the same axis; month and volume. I need to distinctively display each mo/yr together to show any increases or decreases in volume. Any suggestions you have are appreciated! -- tibor ------------------------------------------------------------------------ tibor's Profile: http://www.exceltip.com/forum/member.php?action=getinfo&userid=156 View this thread: http://www.exceltip.com/forum/showthread.php?threadid=1...

Add names to scatter plot chart
I have three columns of info in my excel spreadsheet. column 1 has names, column 2 has numbers and column 3 has numbers. I can create the scatter plot chart just fine, but what I am having a problem with is that I cannot get the names from column one to show next to the points on the scatter plot. How is this done? -- oberon.black ------------------------------------------------------------------------ oberon.black's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26732 View this thread: http://www.excelforum.com/showthread.php?threadid=471758 I'm gu...

Chart
Chart - Title Widths Does anyone know how to find the width of a title/axis title? ...

Change Display Order in Charts
I have an X-Y chart with a cloud of points and a line. When the chart plots, the line is always hidden behind the cloud of points. Can I bring the line to the top so it becomes visible? I tried making the line first and last, but it always ends up behind the cloud. Thank you. Lines for a given chart group (group of data series) are plotted behind points of that same chart group in an XY chart. However, you can change the chart group by changing the axis on which the chart group is plotted. Make the plot with one series formatted as markers only and another as lines only, double cli...

Copy data from Word macro to excel
I am working on a combined Word and Excel sheet (Both 2000 version) What I am trying to do is capture information in Word in an Userform The information that is entered in the form is then used to fill out word document that can be send to a customer. However this information also needs to be entered into an excel sheet currently that is done manually. But I would like to automate that wit a macro. The data that is being captured is in the following form: -If bolOKButtonPressed Then WordBasic.SetDocumentProperty "ProjectTitle", 0 UserForm1.ProjectTitle.Value, 2 WordBasic.SetD...

Out of Stack Space ... Recorded Macros
Excel2003 ... I know nothing of writing Code ... I Record Macros only ... That said ... SS working fine until I started getting error (Out of Stack Space) Is there "HELP" for me??? ... Thanks ... Kha SS Calculation set to "Manual" ... Here is my Recorded Code ... I am getting "Out of Stack Space" message on "Calculate" ... However, SS allows me to F9 Calculate??? Sub Sort_Type_Rec_Ascend() ' ' Sort_Type_Rec_Ascend Macro ' ' a = MsgBox("SORT ... Type Record (Ascending)?", vbYesNo, "ATTENTION!&...

Macro Assigned to Tool Bar Button
How can I tell what Macro is assigned to a tool bar button? Thanks Right-Click the Toolbar and select Customize. Then Right-Click the button and select Assign Macro... "C" <C@discussions.microsoft.com> wrote in message news:7ADB30E4-7ADD-43B8-8696-53C97C9D4100@microsoft.com... > How can I tell what Macro is assigned to a tool bar button? > > Thanks Thanks Jim. That was fairly simple. "Jim Spiller" wrote: > Right-Click the Toolbar and select Customize. Then Right-Click the button > and select Assign Macro... > >...

combining charts from two spreadsheets
I hope someone can help me with this: I have two charts. The one thing these charts have in common are the addresses (the charts are from two different sources). On one chart, I will have monthly sales for the addresses. On another chart, I will have how much each location paid in expenses. I would like to combine these charts into one chart. The addresses on one chart will correspond to the addresses on the other chart and from their, I would like the other information to fall into place. Is this possible? Thanks for any help. this is totally out of the blue, but are you related to...

Run macro automatically.
How do I make a macro run automatically when a worksheet it is attached to is loaded? right click on the sheet tab>view code>left window worksheet>right window activate -- Don Guillett SalesAid Software donaldb@281.com "Excel macro" <Excel macro@discussions.microsoft.com> wrote in message news:DD7AF1E3-9263-4523-AC49-A43ABA1AB9D0@microsoft.com... > How do I make a macro run automatically when a worksheet it is attached to is > loaded? I am unable to find "activate " when I right click on worksheet. ( i assume that you are referring to the windo...

Bar chart with XY on secondary axis
I have four quarters of data that I display as a bar chart. I want to add additional data superimposed on each (XY) as a secondary axis. However, the XY coordinates are displayed on a single vertical axis in the center of the bars rather than on each related bar. Data: (BAR) 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr A 7.1 2.3 4.5 8.7 B 10.5 1.5 6.7 4.8 C 14.6 4.0 7.9 11.1 (XY) 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr A 25 55 67 ...

Code efficiency of Sumproduct()
Two formulae generating the same result: {=SUM(array1*array2*...arrayn)} =SUMPRODUCT(array1*array2*...arrayn) Does the non-array version use up fewer PC resources and/or calculate faster than the array version? Or do they both compile the same way? -- Return email address is not as DEEP as it appears Hi use =SUMPRODUCT(array1,array2,...,arrayn) This should be faster than the other two versions -- Regards Frank Kabel Frankfurt, Germany Jack Schitt wrote: > Two formulae generating the same result: > {=SUM(array1*array2*...arrayn)} > =SUMPRODUCT(array1*array2*...arrayn) >...

Creating multiple charts
I am new to creating charts etc in Excel. I am compiling all the accident information for my church and I would like to have charts linked to this data to give a visual presentation of all the statistics collected. Can anyone tell me in plane english how I do this from a single large spreadsheet which contains all the data. select the data you want to use for the chart, click on the chart wizard icon on the toolbar & follow the wizard. -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink ...

In charts, is it possible to change data references to relevant?
Ive created a basic bar chart, but the speadsheet in which the data is held is constantly being updated. This is ruining my graph as the data references are absolute. Is there a way of changing the source data references to relevant? ive tried "f4" when in the graph "Source data", "values" option, but this does not work. Ive tried deleting all of the $ from the data, but they apear again when the dialogue box is closed. Charts use absolute references. But you can also use a reference to a dynamic name: http://peltiertech.com/Excel/Charts/Dynamics.html ...

labelling points in xy chart
I am using an XY chart as a simple map. The data are in three columns of a worksheet - X coordinate, Y coordinate and Point ID. On my chart I want to plot the points and have each labelled with the point ID. At the moment I can only make the labels show either the X coordinate value (if I select Show label in the Data Labels tab) or the Y coordinate value (if I select Show value). How can I automatically link the label text to the column with Point IDs? Grateful for any help. Hi have a look at the following add-ins which will do this for you as Excel does not support this automatica...

Pie Chart Problem
I have a form into which I enter data. The first 2 boxes are names and the third is a date. These are no problem. I then have 5 categories which have 3 check boxes each depending on what is decided, say Red, Amber or Green. Example: Red Amber Green Health & Safety Delivery I want to create a pie chart from the information so that I can see on a particular day, what percentage was Red, what percentage was Amber and Green and so on for each category. I would therefore have 5 charts all together. I am not that skill...