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"??)

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"??)

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"??)

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