Format Error Bars Vba
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings
Vba Custom Error Bars
and policies of this site About Us Learn more about Stack Overflow xlerrorbartypecustom the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags vba errorbar Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up VBA: Creating different formatting for Error Bars in a Scatter Chart up vote 1 down vote favorite I'm trying to create a project timeline using vba. I want to create different color/style formatting for the vertical(Y) and horizontal(X) Error Bars. I used Record Macro first to see how the X and Y bars are selected differently, but both show as the same ErrorBars property. I want the Y Error bars to be dashed, but when I add that line of code both the X and Y Error bars are changed. Any suggestions? Sub Timeline() Dim sheet1 As Worksheet Dim timeline As Chart Set sheet1 = ActiveWorkbook.Worksheets("sheet1") Set timeline = sheet1.Shapes.AddChart.Chart With timeline .ChartType = xlXYScatter .SeriesCollection.NewSeries .SeriesCollection(1).Name ="Project Lengths" .SeriesCollection(1).XValues ="='sheet1'!C7:C25" .SeriesCollection(1).Values ="='sheet1'!D7:D25" .SeriesCollection(1).ErrorBar Direction:=xlX, Include:= _ xlPlusValues, Type:=xlCustom, Amount:="='sheet1'!E7:E25" .SeriesCollection(1).ErrorBars.EndStyle = xlNoCap .SeriesCollection(1).ErrorBars.Format.Line.Visible = msoTrue .SeriesCollection(1).ErrorBars.Format.Line.ForeColor.RGB = RGB(0, 112, 192) .SeriesCollection(1).ErrorBars.Format.Line.Transparency = 0 .SeriesCollection(1).ErrorBars.Format.Line.Weight = 2.5 .SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _ xlMinusValues, Type:=xlPercent, Amount:=100 .SeriesCollection(1).ErrorBars.EndStyle = xlNoCap .SeriesCollection(1).ErrorBars.Format.Line.Visible = msoTrue .SeriesCollection(1).ErrorBars.Format.Line.DashStyle = msoLineSysDash End With End Sub vba excel-vba charts share|improve this question asked May 23 '14 at 18:28 Momo 482311 Interesting question, seems you can set different formats manually but the macro recorder doesn't show how to select each one. It also appears Microsoft has made it available as xlYErrorBars and xlXErrorBars for the interop class... so why didn't they make it more available within excel? hmm... –Portland Runner May 24 '14
Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages Excel Web Pages | Produce Clean Efficient VBA Code Every Time | Build Automated Trading Models In Excel | Excel Web Pages | Excel Video Training Forum New Posts FAQ Calendar Forum Actions Mark Forums Read Quick Links Today's Posts What's New? Advanced Search Forum HELP FORUMS Excel General Charts : Error Bars thru VBA Excel Training / Excel Dashboards Reports If this is your first visit, be sure to check out the FAQ by clicking http://stackoverflow.com/questions/23836138/vba-creating-different-formatting-for-error-bars-in-a-scatter-chart the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above. Click here to view the relaunched Ozgrid newsletter. Results 1 to 5 of 5 http://www.ozgrid.com/forum/showthread.php?t=17600 Thread: Charts : Error Bars thru VBA Thread Tools Show Printable Version Search Thread Advanced Search January 6th, 2004 #1 lambuhere1 View Profile View Forum Posts Visit Homepage Member Join Date 24th January 2003 Location India Posts 97 Charts : Error Bars thru VBA Hi I am having a issue with respect to adding error bars to each data point in a series collection. Error Propogates in Time. And my chart is Time Vs Independent variable in XY Scatter Chart Sub Type One. Error on each data point is bothe positive and negative. Both the values are same. Chart is made from data in columns A and B. Column A has time in seconds, Column B has independent variable, lets say X. Column C has the error corresponding to each data point. Now this error needs to be represented on each data point using VBA. Can anyone be kind enough to help me out in this. Thanks Ram P Ram P Reply With Quote January 6th, 2004 #2 Andy Pope View Profile View Forum Posts Visit Homepage OzMVP (Roobarb) Join Date 7th March 2003 Location Essex, England Pos
Forum Microsoft Office Application Help - Excel Help forum Excel Charting & Pivots [SOLVED] [SOLVED] Error bars in VBA To get http://www.excelforum.com/showthread.php?t=564740 replies by our experts at nominal charges, follow this link to http://www.andypope.info/tips/tip014.htm buy points and post your thread in our Commercial Services forum! Here is the FAQ for this forum. + Reply to Thread Results 1 to 6 of 6 [SOLVED] Error bars in VBA Thread Tools Show Printable Version Subscribe to this Thread… Mark error bars this thread as unsolved… Rate This Thread Current Rating Excellent Good Average Bad Terrible Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 07-25-2006,10:55 AM #1 mhandersen@gmail.com Guest [SOLVED] Error bars in VBA Hi everyone, I am having trouble with some vba code to add error bars to format error bars a new series I am creating. When I run the following code I get a 'ErrorBar method of Series class failed' error on the first .errorbar line. Can anyone help me out? With ActiveChart.SeriesCollection.NewSeries .name = GraphForm.tbxSeriesName.Value .Values = Worksheets("Data").Range("B1:B25") .XValues = Worksheets("Data").Range("A1:A25") .ErrorBar Direction:=xlX, Include:=xlErrorBarIncludeBoth, Type:=xlCustom, _ Amount:=Worksheets("Data").Range("C1:C25") .ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth, Type:=xlCustom, _ Amount:=Worksheets("Data").Range("D1:D25") End With There is probably a better way to do this (add error bars to a new series) but I am not familiar enough with Excel programming to know what it is. Thanks for any help in advance. Register To Reply 07-25-2006,12:00 PM #2 Graham Whitehead Guest Re: Error bars in VBA I have had some problems in the past drawing charts with error bars. After a while I found that it helps to organise the code a more logical fashion. Here is an example of something I has lying around which I have just grabbed for you. Hopefully, you can see where to change bits
ErrorBars Dim rngUseErrors As Range Dim rngMinusX As Range Dim rngPlusX As Range Dim rngMinusY As Range Dim rngPlusY As Range Set chtTemp = ActiveSheet.ChartObjects(1).Chart Set objSeries = chtTemp.SeriesCollection(1) Set rngUseErrors = Range("N1:N4") Set rngMinusX = Range("D2:D10") Set rngPlusX = Range("E2:E10") Set rngMinusY = Range("F2:F10") Set rngPlusY = Range("G2:G10") If rngUseErrors.Cells(1, 1) Then ' -X If rngUseErrors.Cells(2, 1) Then ' and +X objSeries.ErrorBar xlX, xlErrorBarIncludeBoth, xlErrorBarTypeCustom, "='" & rngPlusX.Parent.Name & "'!" & rngPlusX.Address(, , xlR1C1), "'" & rngMinusX.Parent.Name & "'!" & rngMinusX.Address(, , xlR1C1) Else ' only objSeries.ErrorBar xlX, xlErrorBarIncludeMinusValues, xlErrorBarTypeCustom, "", "='" & rngMinusX.Parent.Name & "'!" & rngMinusX.Address(, , xlR1C1) End If ElseIf rngUseErrors.Cells(2, 1) Then ' +X objSeries.ErrorBar xlX, xlErrorBarIncludePlusValues, xlErrorBarTypeCustom, "='" & rngPlusX.Parent.Name & "'!" & rngPlusX.Address(, , xlR1C1), "" Else objSeries.ErrorBar xlX, xlErrorBarIncludeNone, xlErrorBarTypeFixedValue End If If rngUseErrors.Cells(3, 1) Then ' -Y If rngUseErrors.Cells(4, 1) Then ' and +Y objSeries.ErrorBar xlY, xlErrorBarIncludeBoth, xlErrorBarTypeCustom, "='" & rngPlusY.Parent.Name & "'!" & rngPlusY.Address(, , xlR1C1), "='" & rngMinusY.Parent.Name & "'!" & rngMinusY.Address(, , xlR1C1) Else ' only objSeries.ErrorBar xlY, xlErrorBarIncludeMinusValues, xlErrorBarTypeCustom, "", "='" & rngMinusY.Parent.Name & "'!" & rngMinusY.Address(, , xlR1C1) End If ElseIf rngUseErrors.Cells(4, 1) Then ' +Y objSeries.ErrorBar xlY, xlErrorBarIncludePlusValues, xlErrorBarTypeCustom, "='" & rngPlusY.Parent.Name & "'!" & rngPlusY.Address(, , xlR1C1), "" Else objSeries.ErrorBar xlY, xlErrorBarIncludeNone, xlErrorBarTypeFixedValue End If End Sub VBA Custom Error Bar code (18kb) Created 1st May 2010 Last updated 5th August 2014 Microsoft® and Microsoft® Excel are registered trademarks of the Microsoft Corporation. andypope.info is not associated with Microsoft. Copyright ©2007-2014 Andy Pope