Excel Chart Disappears when updating formula via VSTO

I am trying to update the range of a chart using VSTO. In order to do so, I get the start and end date desired by the user, lookup the range in column a (always the date) and change the chart formula to correspond to the new values.  However, when updating a chart that has a secondary axis, the chart will disappear from the sheet after it is updated. An interesting note is that this appears to happen immediately after the file is opened, and the graph is not on the screen. If I select the chart, the run the code to update the range, everything works fine.

Any ideas on what is causing the charts to disappear?
For c As Integer = 1 To chtSeries.Count
            Dim ser As Excel.Series = chtSeries.Item(c)
            Dim form As String
            Try
                form = ser.Formula
            Catch ex As Exception
                If MsgBox("Could not access the formula for the Series Name: " & ser.Name & " on the chart: " & cht.ChartTitle.Text & _
                          ". Do you want to continue?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                    Continue For
                Else
                    Throw New Exception("Stopped processing Graphs because of a charting error")
                End If
            End Try
 
            'Pull out the formula information
            Dim paramsMatch As RegularExpressions.Match = Regex.Match(form, "\((.*)\)")
            If paramsMatch.Success Then
                Dim params As Collections.Generic.List(Of String) = paramsMatch.Groups(1).Value.Split(",").ToList
                Dim x As String = params(1)
                Dim y As String = params(2)
 
                'Get the x portion of the formula
                'Could add some additional checking on the matches
                Dim xMatch As RegularExpressions.Match = Regex.Match(x, "(.+?)!\$(.+?)\$(.+?):\$(.+?)\$(.+)")
                If (xMatch.Success) Then
                    Dim xSheet As String = xMatch.Groups(1).Value.Replace("'", "")
 
                    'Look for secondary workbook, This gets tricky when people link to other sheets for data,
                    '  We cannot just guess where we are based on this sheet
                    Dim xWorkbook As String = ""
                    Dim xWorkMatch As RegularExpressions.Match = Regex.Match(xSheet, "\[(.+?)\](.+)")
                    If xWorkMatch.Success Then
                        xSheet = xWorkMatch.Groups(2).Value
                        xWorkbook = xWorkMatch.Groups(1).Value
                    End If
 
                    Dim xStartCol As String = xMatch.Groups(2).Value
                    Dim xStartRow As Integer = CType(xMatch.Groups(3).Value, Integer)
                    Dim xEndCol As String = xMatch.Groups(4).Value
                    Dim xEndRow As Integer = CType(xMatch.Groups(5).Value, Integer)
 
                    'Make sure we are dealing with the date column
                    If Not xStartCol = "A" Then
                        Continue For
                    End If
 
                    'Lookup date X
                    Dim xStartRowLookup As Excel.Range
                    Try
                        If xWorkbook.Length > 0 Then
                            xStartRowLookup = App.Workbooks(xWorkbook).Worksheets(xSheet).range("A:A")
                        Else
                            xStartRowLookup = App.Worksheets(xSheet).Range("A:A")
                        End If
                    Catch ex As Exception
                        'If we can't find the sheet, we can't know if we are using date (or row A)
                        ErrStr &= "Graphing Error:The sheetname is quite odd: " & xSheet & " for data in graph: " & cht.ChartTitle.Text & vbNewLine
                        Continue For
                    End Try
 
 
                    Dim xStartRowNumber As Integer
                    Try
                        xStartRowNumber = App.WorksheetFunction.Match(App.WorksheetFunction.EDate(startDate.Date, 0), xStartRowLookup, 0)
                    Catch ex As Exception
                        xStartRowNumber = common.EstimateRangeFromRawData(startDate, WorkBook:=xWorkbook)
                    End Try
 
 
                    'Lookup end date X
                    Dim xEndRowLookup As Excel.Range
                    If xWorkbook.Length > 0 Then
                        xEndRowLookup = App.Workbooks(xWorkbook).Worksheets(xSheet).range("A:A")
                    Else
                        xEndRowLookup = App.Worksheets(xSheet).Range("A:A")
                    End If
 
 
                    Dim xEndRowNumber As Integer
                    Try
                        xEndRowNumber = App.WorksheetFunction.Match(App.WorksheetFunction.EDate(endDate.Date, 0), xEndRowLookup, 0)
                    Catch ex As Exception
                        'Lets guess where it should be
                        xEndRowNumber = common.EstimateRangeFromRawData(endDate, WorkBook:=xWorkbook)
                    End Try
 
                    Dim nFormula As String = ser.Formula
                    nFormula = Regex.Replace(nFormula, "\$" & xStartRow & ":", "$" & xStartRowNumber.ToString & ":")
                    nFormula = Regex.Replace(nFormula, "\$" & xEndRow & ",", "$" & xEndRowNumber.ToString & ",")
                    'nFormula = Regex.Replace(ser.Formula, "(.+?)!\$(.+?)\$(.+?):\$(.+?)\$(.+)", nFormula)
                    ser.Formula = nFormula
 
                    'Set the x series
                    'Dim xRange As Excel.Range
                    'xRange = App.Worksheets(xSheet).Range(xStartCol & xStartRowNumber, xEndCol & xEndRowNumber)
                    'cht.Chart.SetSourceData(xRange, Excel.XlRowCol.xlColumns)
                End If
                'Create new Range
                'Add new series range
 
            End If
        Next

Open in new window

LVL 1
crymsanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crymsanAuthor Commented:
The only workaround I have found is to not edit the formula directly, and update the values in the graph by using the XValues and Values property of the series.
                    Dim yMatch As RegularExpressions.Match = Regex.Match(y, "(.+?)!\$(.+?)\$(.+?):\$(.+?)\$(.+)")
                    If yMatch.Success Then
                        If xWorkbook.Length > 0 Then
                            ser.XValues = App.Workbooks(xWorkbook).Worksheets(xSheet).Range("A" & xStartRowNumber & ":A" & xEndRowNumber)
                            ser.Values = App.Worksheets(xSheet).Range(yMatch.Groups(2).Value & xStartRowNumber & ":" & yMatch.Groups(4).Value & xEndRowNumber)
                        Else
                            ser.XValues = App.Worksheets(xSheet).Range("A" & xStartRowNumber & ":A" & xEndRowNumber)
                            ser.Values = App.Worksheets(xSheet).Range(yMatch.Groups(2).Value & xStartRowNumber & ":" & yMatch.Groups(4).Value & xEndRowNumber)
                        End If
                    Else
                        ErrStr &= "Cannot get the Y Axis Columns"
                    End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.