We help IT Professionals succeed at work.
Get Started

Excel Chart Disappears when updating formula via VSTO

Last Modified: 2013-11-10
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
                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
                    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
                        If xWorkbook.Length > 0 Then
                            xStartRowLookup = App.Workbooks(xWorkbook).Worksheets(xSheet).range("A:A")
                            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
                        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")
                        xEndRowLookup = App.Worksheets(xSheet).Range("A:A")
                    End If
                    Dim xEndRowNumber As Integer
                        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

Open in new window

Watch Question
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE