If this is your first visit, be sure to check out the FAQ by clicking 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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Retrieve Error Bar Values
I have a chart, created by an Excel macro, with data derived from a
named sheet. I'm trying to modify that chart in VBA to reference a different sheet with a different number of lines. Simple enough, except that the chart has custom error bars, and there appears to be no way to retrieve the formula associated with those error bars. Excel obviously keeps a formula for these error bars - I can see it through "Format Error Bars" in the UI - but I don't see it anywhere in a watch. Anyone know how I can get to - and modify - the formula for error bars? Here's my code thus far: Option Explicit Const SEARCH_DIR = "F:" Public Sub Weekly_Charts() Dim wb As Workbook Dim sh As Variant Dim cht As ChartObject Dim ser As Series Dim strFormula As String Dim i As Integer Dim j As Integer Dim iDayRows As Integer Dim iWeekRows As Integer Dim errb As ErrorBars Set wb = Workbooks.Open(SEARCH_DIR & "base_n_year_by_province_2008.xls") For Each sh In wb.Sheets Select Case sh.Name Case "Data_Sheet" iDayRows = sh.UsedRange.Rows.Count Case "Weekly_Data" iWeekRows = sh.UsedRange.Rows.Count End Select Next sh For Each sh In wb.Sheets Select Case sh.Name Case "Data_Sheet" Case "Weekly_Data" Case Else Set cht = sh.ChartObjects(1) cht.Activate For Each ser In ActiveChart.SeriesCollection ser.Formula = WorksheetFunction.Substitute (ser.Formula, "Data_Sheet", "Weekly_Data") ser.Formula = WorksheetFunction.Substitute (ser.Formula, iDayRows, iWeekRows) If ser.HasErrorBars Then Set errb = ser.ErrorBars ' Now what??? End If Next ser sh.ChartObjects(2).Delete sh.ChartObjects(2).Delete End Select Next sh Workbooks.Close Set wb = Nothing End Sub (..and how come, if I define sh as Woksheet, I get a type mismatch on "For Each sh In wb.Sheets"??) |
#2
|
|||
|
|||
Retrieve Error Bar Values
Hi,
First the type mismatch. Worksheet is a specific type of sheet in an excel workbook. Along with Chart sheet and the older macro and dialog sheets. The Sheets collection will return all of the sheet types, as objects. If you just want the worksheets you can use the following and declare sh as worksheet. For Each sh In wb.worksheets The custom error bar formula is not exposed via the object model. In fact if you record a macro of you change the error bar from custom to Percent value you will get Excel4Macro code. ActiveChart.SeriesCollection(1).ErrorBars.Select ExecuteExcel4Macro "ERRORBAR.Y(1,2,5)" So it maybe possible to execute a command to return the formula but I'm too young to remember the Excel4Macro syntax Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Spiggy Topes" wrote in message ... I have a chart, created by an Excel macro, with data derived from a named sheet. I'm trying to modify that chart in VBA to reference a different sheet with a different number of lines. Simple enough, except that the chart has custom error bars, and there appears to be no way to retrieve the formula associated with those error bars. Excel obviously keeps a formula for these error bars - I can see it through "Format Error Bars" in the UI - but I don't see it anywhere in a watch. Anyone know how I can get to - and modify - the formula for error bars? Here's my code thus far: Option Explicit Const SEARCH_DIR = "F:" Public Sub Weekly_Charts() Dim wb As Workbook Dim sh As Variant Dim cht As ChartObject Dim ser As Series Dim strFormula As String Dim i As Integer Dim j As Integer Dim iDayRows As Integer Dim iWeekRows As Integer Dim errb As ErrorBars Set wb = Workbooks.Open(SEARCH_DIR & "base_n_year_by_province_2008.xls") For Each sh In wb.Sheets Select Case sh.Name Case "Data_Sheet" iDayRows = sh.UsedRange.Rows.Count Case "Weekly_Data" iWeekRows = sh.UsedRange.Rows.Count End Select Next sh For Each sh In wb.Sheets Select Case sh.Name Case "Data_Sheet" Case "Weekly_Data" Case Else Set cht = sh.ChartObjects(1) cht.Activate For Each ser In ActiveChart.SeriesCollection ser.Formula = WorksheetFunction.Substitute (ser.Formula, "Data_Sheet", "Weekly_Data") ser.Formula = WorksheetFunction.Substitute (ser.Formula, iDayRows, iWeekRows) If ser.HasErrorBars Then Set errb = ser.ErrorBars ' Now what??? End If Next ser sh.ChartObjects(2).Delete sh.ChartObjects(2).Delete End Select Next sh Workbooks.Close Set wb = Nothing End Sub (..and how come, if I define sh as Woksheet, I get a type mismatch on "For Each sh In wb.Sheets"??) |
#3
|
|||
|
|||
Retrieve Error Bar Values
As Andy points out, Excel knows where the error bar values come from, but is
unable to let VBA read the information. But your VBA code can tell Excel what range to use for error bars. You need to reference the worksheet and use R1C1 notation on the address. It looks like there is a system to the location of the data, since you're changing the sheet name and counting rows and columns and so forth. Use the same approach to determine what the error bar range should be and apply it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Andy Pope" wrote in message ... Hi, First the type mismatch. Worksheet is a specific type of sheet in an excel workbook. Along with Chart sheet and the older macro and dialog sheets. The Sheets collection will return all of the sheet types, as objects. If you just want the worksheets you can use the following and declare sh as worksheet. For Each sh In wb.worksheets The custom error bar formula is not exposed via the object model. In fact if you record a macro of you change the error bar from custom to Percent value you will get Excel4Macro code. ActiveChart.SeriesCollection(1).ErrorBars.Select ExecuteExcel4Macro "ERRORBAR.Y(1,2,5)" So it maybe possible to execute a command to return the formula but I'm too young to remember the Excel4Macro syntax Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Spiggy Topes" wrote in message ... I have a chart, created by an Excel macro, with data derived from a named sheet. I'm trying to modify that chart in VBA to reference a different sheet with a different number of lines. Simple enough, except that the chart has custom error bars, and there appears to be no way to retrieve the formula associated with those error bars. Excel obviously keeps a formula for these error bars - I can see it through "Format Error Bars" in the UI - but I don't see it anywhere in a watch. Anyone know how I can get to - and modify - the formula for error bars? Here's my code thus far: Option Explicit Const SEARCH_DIR = "F:" Public Sub Weekly_Charts() Dim wb As Workbook Dim sh As Variant Dim cht As ChartObject Dim ser As Series Dim strFormula As String Dim i As Integer Dim j As Integer Dim iDayRows As Integer Dim iWeekRows As Integer Dim errb As ErrorBars Set wb = Workbooks.Open(SEARCH_DIR & "base_n_year_by_province_2008.xls") For Each sh In wb.Sheets Select Case sh.Name Case "Data_Sheet" iDayRows = sh.UsedRange.Rows.Count Case "Weekly_Data" iWeekRows = sh.UsedRange.Rows.Count End Select Next sh For Each sh In wb.Sheets Select Case sh.Name Case "Data_Sheet" Case "Weekly_Data" Case Else Set cht = sh.ChartObjects(1) cht.Activate For Each ser In ActiveChart.SeriesCollection ser.Formula = WorksheetFunction.Substitute (ser.Formula, "Data_Sheet", "Weekly_Data") ser.Formula = WorksheetFunction.Substitute (ser.Formula, iDayRows, iWeekRows) If ser.HasErrorBars Then Set errb = ser.ErrorBars ' Now what??? End If Next ser sh.ChartObjects(2).Delete sh.ChartObjects(2).Delete End Select Next sh Workbooks.Close Set wb = Nothing End Sub (..and how come, if I define sh as Woksheet, I get a type mismatch on "For Each sh In wb.Sheets"??) |
#4
|
|||
|
|||
Retrieve Error Bar Values
Ya, it's looking like I'll have to recreate. Just means more work and
less flexibility. The chart I'm applying this to could come from any of several sources, each with its own column positions and names, and I was looking for a generic approach that would fit all cases. I can derive the column names for the error bars from other column names on the same source sheet, so if I can drop the existing error bars, I can just rebuild from scratch; but that means recreating colour and other style info which is not available in the source data. Maybe I can fish that out of the DOM. In case it helps anyone, I did eventually find what's available through the use of ExecuteExcel4Macro at http://support.microsoft.com/kb/128185. There's quite a long list of available functions, but unfortunately none dealing with retrieval of error bars. Thanks all Robert On Feb 20, 3:04*am, "Andy Pope" wrote: Hi, First the type mismatch. Worksheet is a specific type of sheet in an excel workbook. Along with Chart sheet and the older macro and dialog sheets. The Sheets collection will return all of the sheet types, as objects. If you just want the worksheets you can use the following and declare sh as worksheet. For Each sh In wb.worksheets The custom error bar formula is not exposed via the object model. In fact if you record a macro of you change the error bar from custom to Percent value you will get Excel4Macro code. * * ActiveChart.SeriesCollection(1).ErrorBars.Select * * ExecuteExcel4Macro "ERRORBAR.Y(1,2,5)" So it maybe possible to execute a command to return the formula but I'm too young to remember the Excel4Macro syntax Cheers Andy |
Thread Tools | |
Display Modes | |
|
|