none
Excel 2007-2010 VBA raises Error 91 which worked in Excel 2003 RRS feed

  • Question

  • The following is an Excel VBA sub procedure called from within a loop that slices up data in order to produce a line waveform display of the data.  As shown, after the chart is created, it is embedded on a display sheet and modifed with final display adjustments to borders and such.

    For Excel 2010, the waveform charts appear to have their basis constructed but the final placement of the chart on the display sheet, with moderate adjustments of the display sizes, fails completely.  The first chart created appears to be properly embedded as expected, but all subsequent charts appear to be piled ontop of one another on the display page with final display adjustments not being applied to them.

    When executed through the VB Editor I see an error catch occurring for an Error 91 being raised.

    Under Excel 2002-2003 it works flawlessly with all charts being embedded on the display sheet as expected.

    Under Excel 2007 it appears to work to produce the expected result on the display sheet.  But when stepping through the code in the VB editor I receive the same Error 91 being raised in the same manner as Excel 2010.

    In both Excel 2007 & 2010 the error 91 appears to be raised at the first line encountered after the ".Location Where:=xlLocationAsObject, Name:="Sun Spot Level(s)"" chart method is executed to embed chart on the display sheet.  As explained in code snippet, some of the lines where failure is raised appear to work fine when they are placed prior to the embedded of the chart using the Location method.

    sub PlotSunSpots (SeriesRange As Range, XValuesRange As Range,  sChartName As String, dmin1 As Double, dmax1 As Double)
    
        Static iChartRow As Integer
        Static iChartIndex As Integer
        Dim TempStringArray() As String
        Dim LoopCounter As Integer
    
        iChartIndex = iChartIndex + 1
    
        Charts.Add
    
        With ActiveChart .Axes(xlValue, xlPrimary)
    
            .HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle
            .Characters.Text = "Spot Count"
            .ChartType = xlLine
            .SetSourceData Source:=SeriesRange , PlotBy:=xlColumns
    
             ' if the annotation line is more than 255 characters, it will cause
             ' an error it needs to be truncated to prevent this.
             If Len(sChartName) > 255 Then
    
                 TempStringArray = Split(sChartName, " ", -1)
                 sChartName = ""
    
                 For LoopCounter = 0 To UBound(TempStringArray, 1)
    
                     If TempStringArray(LoopCounter) <> " " And _ TempStringArray(LoopCounter) <> "" Then
    
                         If (Len(sChartName & TempStringArray(LoopCounter)) < 255) Then
    
                             ' less than so add the next bit
                             sChartName = sChartName & " " & TempStringArray(LoopCounter)
    
                         Else
                             ' Limit reached, don't add the next bit that will
                             ' make it go over 255
                            Exit For
    
                        End If
                    End If
                Next
            End If
    
            ' this is the name string minus the annotation part
            .SeriesCollection(1).Name = sChartName
            .SeriesCollection(1).XValues = XValuesRange
    
            .Location Where:=xlLocationAsObject, Name:="Sun Spot Level(s)"
    
        End With
        
        'It is not clear why the previous block was terminated and the following
        ' is established. IF entire block which follows is moved into previous
        ' block it works just fine provided they are moved prior to the .Locations
        ' statement.
        With ActiveChart 
    
            ' The following is the point where the Error 91 is raised.
            ' If this entire block is moved up, then point of failure moves to ' first line of code in following With block
            .HasTitle = True
            .HasAxis(xlCategory, xlPrimary) = True
            .HasAxis(xlValue, xlPrimary) = True
            .HasLegend = False
            .HasDataTable = False
            .Axes(xlCategory, xlPrimary)
            .CategoryType = xlCategoryScale
    
        End With
    
        With ActiveChart.Axes(xlValue)
    
            .HasMajorGridlines = True
            .HasMinorGridlines = True
            .MajorGridlines.Select
            .MinimumScale = dmin1
            .MaximumScale = dmax1
            .MajorUnit = (Abs(dmin1) + Abs(dmax1)) / 8
            .MinorUnit = (Abs(dmin1) + Abs(dmax1)) / 40
            .MinorUnitIsAuto = False
            .MajorUnitIsAuto = False
            .Crosses = xlCustom
            .CrossesAt = dmin1
            .ReversePlotOrder = False
            .ScaleType = xlLinear
            .DisplayUnit = xlNone
            .MinorGridlines.Select
    
        End With
    
        With Selection.Border
    
            .ColorIndex = 57
            .Weight = xlHairline
            .LineStyle = xlDot
    
        End With
    
        With ActiveChart.Axes(xlCategory)
    
            .HasMajorGridlines = True
    
            If SeriesRange.Rows.Count < 1200 Then
                .HasMinorGridlines = True
            Else
                .HasMinorGridlines = False
            End If
    
            .CrossesAt = 1
            .TickLabelSpacing = 120
            .TickMarkSpacing = 24
            .AxisBetweenCategories = True
            .ReversePlotOrder = False
            .TickLabels.Alignment = xlCenter
            .TickLabels.Offset = 100
            .TickLabels.ReadingOrder = xlContext
            .TickLabels.Orientation = xlUpward
    
        End With
    
        ActiveChart.ChartArea.Select
    
        With Selection.Border
    
            .Weight = 2
            .LineStyle = -1
    
        End With
    
        With Selection.Interior
    
            .ColorIndex = 2
            .PatternColorIndex = 1
            .Pattern = 1
    
        End With
    
        Selection.AutoScaleFont = False
    
        With Selection.Font
    
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 5.75
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
            .Background = xlAutomatic
    
        End With
    
        ActiveChart.PlotArea.Select
    
        With Selection.Border
    
            .ColorIndex = 16
            .Weight = xlThin
            .LineStyle = xlContinuous
    
        End With
    
        With Selection.Interior
    
            .ColorIndex = 2
            .PatternColorIndex = 1
            .Pattern = xlSolid
    
        End With
    
        With Worksheets("Sun Spot Level(s)")
    
            .Shapes(ActiveSheet.Shapes(iChartIndex).Name).Left = .Columns(1).Left
            .Shapes(ActiveSheet.Shapes(iChartIndex).Name).Top = .Rows(iChartRow + 1).Top
    
            ' 4 cm + 1.9 for the margin around the waveform box
            .Shapes(ActiveSheet.Shapes(iChartIndex).Name).Height = Application.CentimetersToPoints(4 + 2.9)
    
            ' 25 cm = 25 mm/sec for 10 seconds + the 1.25 margins for the box around the actual waveform area.
           .Shapes(ActiveSheet.Shapes(iChartIndex).Name).Width = Application.CentimetersToPoints(25 + 1.7)
    
        End With
    
        iChartRow = iChartRow + 16
    
    End Sub

     

    It is unclear if scope of visibility is changing as a result of embedding the chart on an existing sheet where a chart already exists or if there is some other reason for the error to be raised.  From the behaviour difference between the various versions of Excel VBA, it seems like some aspect of the processing which was supported in Excel 2002 & 2003 has changed in Excel 2007 & 2010, with a depri'cation in 2007 that has become obsolete in 2010.

    Any insights into what in the following code is causing this would be greatly appreciated:


    Del




    • Edited by grandoak01 Friday, September 30, 2011 1:30 AM
    Tuesday, September 27, 2011 5:10 PM

Answers

  • As expected, the problem was found to be in the " .Location Where:=xlLocationAsObject, Name:="Sun Spot Level(s)"" statement. 

    Appears there are many side effects, which I have not found documented anywhere, such as invalidating the ActiveChart pointer for one that made subsequent With block fail with the Error 91 issue due to what was now an undefined reference!!!  Imagine THAT!  You would think that this would have been noteworthy of mentioning in the documentation for the method!!

    When the .Location statement was moved within the sbprocedure body to just prior to the final With block, all statements between the original placement and the new one executed flawlessly, as in versions of Excel preceeding 2010.  However, those contained in the final With block still failed.

    I then learned that not only does the .Location statement clear visibility pointers (not sure if this is correct reference), but it changes the Name attribute for the chart when it embedded the chart.  Consequently, since each chart being embedded on the "Sun Spot Level(s)" sheet was done so independent of the others being embedded via a For loop surrounding the call to the subprocedure for each chart to be relocated.

    Net result was that every chart had the same name given to it ("Sun Spot Level(s) Chart1") and the actions of the subsequent statements after the move were constantly being applied to the same chart (i.e. the first one embedded on over and over for all the other charts on the "Sun Spot Level(s)" sheet!

    The final solution to get it all working properly once again was to then insert the statement to change the Name attribute for the embedded chart, which immediately follows the With clause in the following code block which represents the new final block in the subprocedure body of the code in original post.  The allowed the final statements controlling the placement of the embedded chart on the worksheet where it was moved.  The following is how that final With block of the subprocedure body now appears, which is working:

    With Worksheets("Sun Spot Level(s)")
    
         'Following was the line needed to make rest of the statement work
         ActiveSheet.Shapes(iChartIndex).Name = "Chart"&iChatIndex
    
        .Shapes(ActiveSheet.Shapes(iChartIndex).Name).Left = .Columns(1).Left
        .Shapes(ActiveSheet.Shapes(iChartIndex).Name).Top = .Rows(iChartRow + 1).Top
    
        ' 4 cm + 1.9 for the margin around the waveform box
        .Shapes(ActiveSheet.Shapes(iChartIndex).Name).Height = Application.CentimetersToPoints(4 + 2.9)
    
        ' 25 cm = 25 mm/sec for 10 seconds + the 1.25 margins for the box around the actual waveform area.
        .Shapes(ActiveSheet.Shapes(iChartIndex).Name).Width = Application.CentimetersToPoints(25 + 1.7)
    
    End With
    

    Frustration to say the least when functionality of methods change and documentation for them doesn't!!!   But at least resolution was found.

    • Proposed as answer by GrandOak1 Friday, September 30, 2011 12:50 AM
    • Marked as answer by danishaniModerator Tuesday, January 31, 2012 4:12 AM
    Friday, September 30, 2011 12:48 AM

All replies

  • Excel 2007 changed teh chart object from 2003. So any chart code in Excel VBA 2003 needs re-writing for 2007 or 2010.

    Try recording a macro of you manually creating the chart to get a start.


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Wednesday, September 28, 2011 7:23 AM
  • As expected, the problem was found to be in the " .Location Where:=xlLocationAsObject, Name:="Sun Spot Level(s)"" statement. 

    Appears there are many side effects, which I have not found documented anywhere, such as invalidating the ActiveChart pointer for one that made subsequent With block fail with the Error 91 issue due to what was now an undefined reference!!!  Imagine THAT!  You would think that this would have been noteworthy of mentioning in the documentation for the method!!

    When the .Location statement was moved within the sbprocedure body to just prior to the final With block, all statements between the original placement and the new one executed flawlessly, as in versions of Excel preceeding 2010.  However, those contained in the final With block still failed.

    I then learned that not only does the .Location statement clear visibility pointers (not sure if this is correct reference), but it changes the Name attribute for the chart when it embedded the chart.  Consequently, since each chart being embedded on the "Sun Spot Level(s)" sheet was done so independent of the others being embedded via a For loop surrounding the call to the subprocedure for each chart to be relocated.

    Net result was that every chart had the same name given to it ("Sun Spot Level(s) Chart1") and the actions of the subsequent statements after the move were constantly being applied to the same chart (i.e. the first one embedded on over and over for all the other charts on the "Sun Spot Level(s)" sheet!

    The final solution to get it all working properly once again was to then insert the statement to change the Name attribute for the embedded chart, which immediately follows the With clause in the following code block which represents the new final block in the subprocedure body of the code in original post.  The allowed the final statements controlling the placement of the embedded chart on the worksheet where it was moved.  The following is how that final With block of the subprocedure body now appears, which is working:

    With Worksheets("Sun Spot Level(s)")
    
         'Following was the line needed to make rest of the statement work
         ActiveSheet.Shapes(iChartIndex).Name = "Chart"&iChatIndex
    
        .Shapes(ActiveSheet.Shapes(iChartIndex).Name).Left = .Columns(1).Left
        .Shapes(ActiveSheet.Shapes(iChartIndex).Name).Top = .Rows(iChartRow + 1).Top
    
        ' 4 cm + 1.9 for the margin around the waveform box
        .Shapes(ActiveSheet.Shapes(iChartIndex).Name).Height = Application.CentimetersToPoints(4 + 2.9)
    
        ' 25 cm = 25 mm/sec for 10 seconds + the 1.25 margins for the box around the actual waveform area.
        .Shapes(ActiveSheet.Shapes(iChartIndex).Name).Width = Application.CentimetersToPoints(25 + 1.7)
    
    End With
    

    Frustration to say the least when functionality of methods change and documentation for them doesn't!!!   But at least resolution was found.

    • Proposed as answer by GrandOak1 Friday, September 30, 2011 12:50 AM
    • Marked as answer by danishaniModerator Tuesday, January 31, 2012 4:12 AM
    Friday, September 30, 2011 12:48 AM