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

Select allOpen in new window