VBA Guru anyone!? VBA Chart. (1 Viewer)

[Access_guy49]

Hey Everyone, I am in a REAL jam
(Hey.. what's the difference between jam and jelly... nvm i'll save it for the joke board.)

Ok I have a Simple Form with two charts. The record source for these charts is set using vba on a previous form.

I have it so that i can have the user change the chart type with a button on the form. but i'm trying to add a trendline and it's just not happening for me.

This works just fine:

Private Sub cmd_changeGraph_Click()
    'change graph to bar
    Me.Graph1.Object.Application.Chart.ChartType = 51
    Me.Graph3.Object.Application.Chart.ChartType = 51
End Sub

This however, is a friggin disaster.... and here is the kicker.. The first graph it runs through, it actually DOES create a trendline.. but it errors out, so i was trying to catch it and force it to continue but on the second chart it doesn't continue. It has a problem getting the series collection

Private Sub Command10_Click()
    'Add 10 year trend line
    Dim i As Integer
    i = 0
        
    If ThYrTrends = True Then
        'Remove thirty year trends first
        Me.Graph1.SeriesCollection(1).Trendlines(1).Select
        Selection.Delete
        Me.Graph3.SeriesCollection(1).Trendlines(1).Select
        Selection.Delete
    End If
    
    On Error GoTo Ohwell

    Me.Graph1.SeriesCollection(1).Trendlines.Add(Type:=xlMovingAvg, Period:= _
        10, Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False, Name:="10yr Mov. Avg").Select


NextGraph:
    Me.Graph3.SeriesCollection(1).Trendlines.Add(Type:=xlMovingAvg, Period:= _
        10, Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False, Name:="10yr Mov. Avg").Select

Ohwell:
    'Do nothing
    Me.Form.Refresh
    If i = 0 Then
        i = 1
        GoTo NextGraph
    End If
    TenYrTrends = True
End Sub

Ideas? anyone?

 

[vbaInet]

ChrisO is your man for charts and I'm sure if he sees this he would probably reply

But one thing to note is that with collections, when you delete an item, the ordering changes (i.e. their indexes). So if you had a collection with index 0, 1, 2, 3, 4 and you delete 2, this is what happens, 3 now becomes 2 and 4 becomes 3.

 

[Access_guy49]

Thanks vba, i guess i should clearify the charts then. They are just a basic time series chart. I.E
Date -- Value
Date -- Value
Date -- Value

So my understanding.. (and from what i'v seen on stepping through code) is that 1 should always work.

 

[vbaInet]

I haven't been playing around with charts of recent so I can't say I know all the methods or properties associated with a chart object Just wait for ChrisO or you could search for him and look through the threads his replied to. There are couple of threads regarding charts there.

Yes 1 would work but the item 1 is refering to may be the wrong one.

 

[ChrisO]

It looks like Option Explicit is not at the top of the Module.

You have two variables which are not declared in the procedure, ThYrTrends and TenYrTrends. ThYrTrends is a typo and if there is no Option Explicit it becomes a variant. When tested for True or False it will default to a numerical value of zero and hence be False. Therefore the If ThYrTrends = True Then code block should not execute.

If an error is raised in the following code that error is not cleared because you are doing a GoTo NextGraph rather than a Resume NextGraph. Access will not trap more than one error at a time.

Graphs are difficult to code so it would help if you could post the application.

 

[vbaInet]

What did I tell ya!! You can always count on Chris

 

[ChrisO]

Actually, I would prefer if people didn’t count on me because I may not have time and it might dissuade others from answering.

 

[vbaInet]

True. Will try to keep schtum next time

 

[Access_guy49]

Sorry Chris, I didn't post all the code, only that Sub. My variables are Public because they are used in other subs within the module.

Complete Code for the form:

Option Compare Database
Option Explicit
Public TenYrTrends As Boolean
Public ThYrTrends As Boolean

Private Sub cmd_changeGraph_Click()
    'change graph to bar
    Me.Graph1.Object.Application.Chart.ChartType = 51
    Me.Graph3.Object.Application.Chart.ChartType = 51
End Sub

Private Sub cmd_Closeform_Click()
On Error GoTo Err_cmd_Closeform_Click

    If ThYrTrends = True Then
        'Remove Thirty Year trend lines
        Me.Graph1.SeriesCollection(1).Trendlines(1).Select
        Selection.Delete
        Me.Graph3.SeriesCollection(1).Trendlines(1).Select
        Selection.Delete
    ElseIf TenYrTrends = True Then
        Me.Graph1.SeriesCollection(1).Trendlines(1).Select
        Selection.Delete
        Me.Graph3.SeriesCollection(1).Trendlines(1).Select
        Selection.Delete
        'Remove Ten year trend lines
    End If

    DoCmd.Close

Exit_cmd_Closeform_Click:
    Exit Sub

Err_cmd_Closeform_Click:
    MsgBox Err.Description
    Resume Exit_cmd_Closeform_Click
    
End Sub

Private Sub Command10_Click()
    'Add 10 year trend line
    Dim i As Integer
    i = 0
        
    If ThYrTrends = True Then
        'Remove thirty year trends first
        Me.Graph1.SeriesCollection(1).Trendlines(1).Select
        Selection.Delete
        Me.Graph3.SeriesCollection(1).Trendlines(1).Select
        Selection.Delete
    End If
    
    On Error GoTo Ohwell

    Me.Graph1.SeriesCollection(1).Trendlines.Add(Type:=xlMovingAvg, Period:= _
        10, Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False, Name:="10yr Mov. Avg").Select


NextGraph:
    Me.Graph3.SeriesCollection(1).Trendlines.Add(Type:=xlMovingAvg, Period:= _
        10, Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False, Name:="10yr Mov. Avg").Select

Ohwell:
    'Do nothing
    Me.Form.Refresh
    If i = 0 Then
        i = 1
        GoTo NextGraph
    End If
    TenYrTrends = True
End Sub

Private Sub Command13_Click()
    'Add 30 year trend line
    Dim i As Integer
    i = 0
    
    If TenYrTrends = True Then
        'Remove ten year trends first
        Me.Graph1.SeriesCollection(1).Trendlines(1).Select
        Selection.Delete
        Me.Graph3.SeriesCollection(1).Trendlines(1).Select
        Selection.Delete
    End If
    
    On Error GoTo Ohwell

    Me.Graph1.SeriesCollection(1).Trendlines.Add(Type:=xlMovingAvg, Period:= _
        30, Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False, Name:="10yr Mov. Avg").Select


NextGraph:
    On Error GoTo Ohwell
    Me.Graph3.SeriesCollection(1).Trendlines.Add(Type:=xlMovingAvg, Period:= _
        30, Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False, Name:="10yr Mov. Avg").Select

Ohwell:
    'Do nothing
    Me.Form.Refresh
    If i = 0 Then
        i = 1
        GoTo NextGraph
    End If
    ThYrTrends = True
End Sub

Private Sub Command8_Click()
    'Change the Graph to a Line
    Me.Graph1.Object.Application.Chart.ChartType = xlLineMarkers
    Me.Graph3.Object.Application.Chart.ChartType = xlLineMarkers
End Sub

Private Sub Form_Load()
DoCmd.Maximize
End Sub

 

[Access_guy49]

OK here are My databases. Front end and back end..
I had to remove some data because the back end was too large to post.
The front end i've removed other forms as it was 33 megs with all forms.

 

[ChrisO]

Front end attached.

I tried to remove as much clutter as possible and that includes error handling.
You may wish to add error handling back in again.

 

[Access_guy49]

WOW,
Amazing!! Thank you for the help chris!

 

[ChrisO]

No problems, glad it works okay.

Just a note on removing clutter…

If we choose reasonable names for, say, command buttons we can replace names like Command18_Click with something like cmd10YearTrendLine_Click or cmd30YearTrendLine_Click. Internally those subs also have the words AddTrendline, the Graph Name, the Series Number and intPeriod.

So, for instance, it reads cmd10YearTrendLine_Click, AddTrendline, Graph1, Series:=1 and intPeriod:=10
(I would also rename Graph1 and Graph3 to something more like the GraphSV and GraphSWE.)

To me that becomes so readable that the comment line is hardly necessary and personally I would not include the comment. Even though graphs can be a little obscure at times I think only comments that explain something should be included. So if we can keep the functionality of a procedure concise and choose good names the requirement for comments disappears.

What that means is that comments which are actually needed to imply something stand out more and don’t run the risk of being lost in a sea of ‘green stuff’.

So rather than: -

Private Sub cmd10YearTrendLine_Click()
    
    [color=green]'Add 10 year trend line  [/color]
    AddTrendline Me.Graph1, intSeries:=1, intPeriod:=10
    AddTrendline Me.Graph3, intSeries:=1, intPeriod:=10

End Sub


Private Sub cmd30YearTrendLine_Click()
    
    [color=green]'Add 30 year trend line [/color]
    AddTrendline Me.Graph1, intSeries:=1, intPeriod:=30
    AddTrendline Me.Graph3, intSeries:=1, intPeriod:=30

End Sub

I would sooner live with: -

Private Sub cmd10YearTrendLine_Click()
    
    AddTrendline Me.GraphSV, intSeries:=1, intPeriod:=10
    AddTrendline Me.GraphSWE, intSeries:=1, intPeriod:=10

End Sub


Private Sub cmd30YearTrendLine_Click()
    
    AddTrendline Me.GraphSV, intSeries:=1, intPeriod:=30
    AddTrendline Me.GraphSWE, intSeries:=1, intPeriod:=30

End Sub

Personal opinion only but if they’re necessary fine else remove them because they can become more of a hindrance than a help.