Excel Chart Disappears when updating formula via VSTO

Posted on 2008-11-04
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

Question by:crymsan
    1 Comment
    LVL 1

    Accepted Solution

    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)
                                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
                            ErrStr &= "Cannot get the Y Axis Columns"
                        End If

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now