Trendline Properties/Deletions in Excel VBA

[Bennett]

I have another minor problem.

Part of the software I'm working on performs a correlational analysis
between different parameters, and includes the option of including
various "best fit" lines - i.e. Trendlines.

For some datasets I can happily add trendlines, delete them, and change
the type of trendline on the fly. Perfection.

For other datasets this isn't possible. When I look at the chart
manually it is clear that certain types of trendline aren't available
for that dataset. Fair enough.

The problem however is that VBA/Excel allows me to allocate/create a
trendline IN THE FORBIDDEN STYLE using my macro.

Charts(3).SeriesCollection(i) _
.Trendlines.Add Type:=trendtype


It does NOT throw up an error. When I then try to delete/amend the
trendline it acts as if it no longer exists, even though it does!

If Charts(3).SeriesCollection(1).Trendlines.Count = 1 Then
Charts(3).SeriesCollection(i).Trendlines(1).Delete <---- cannot change
properties
End If

For example, with one dataset the logarithmic trendline is not
available, but I can use my macro to create a trendline with type
"xlLogarithmic". This trendline is invisible on screen but does have a
legend key. When I then try to delete the trendline the macro crashes.

Interestingly enough if I change the dataset then the logarithmic
trendline reappears ) I can then delete it manually.

I have tried trapping the error, but the error occurs AFTER the
trendline is mistakenly added, so I can do nothing about it. It would
help if there was a way to either detect which trendlines were valid
for a particular dataset or if Excel actually created an error during
the allocation of the incorrect trendline :-/

I guess one workaround is to load up a generic dataset which works for
all trendlines, so activating any hidden ones, and then delete them.
Anyone got a more elegant solution?

Bennett

 

[Michael R Middleton]

Bennett -

In my opinion, an "elegant solution" would require your code to first check
the data (looking for negative or zero values) and then allow adding only
the appropriate types of trendlines.

- Mike
www.mikemiddleton.com

 

[Bennett]

Hmm...I guess that might work. Thinking about it, the dataset that
causes the problem _does_ contain zeros... A search for anything <= 0
would work.

The trick is not slowing the software down with an interative search of
the datapoints, but I think I could just add a line to some existing
code and then set a flag somewhere.

Thanks! It seems that every time I come up with something new to try I
find a whole new way to crash the thing ;-)

Bennett

 

[Peter T]

Hi Bennet,

Do you have a typo, 1 vs. i, either in your post or in your code:
If Charts(3).SeriesCollection(1).Trendlines.Count = 1 Then
Charts(3).SeriesCollection(i).Trendlines(1).Delete <---- cannot change
properties
End If
you are checking in:
SeriesCollection(1).
but deleting in:
SeriesCollection(i).

As posted seems inconsistent and likely to fail. If you are looping
seriescollections change the "1" to "i". Or the "i" to "1" if only the first
series.

As for trying automatically trying to apply the best fit trend line to any
given dataset, I don't know if anyone has ever devised a foolproof method.
Not easy!

Regards,
Peter T

 

[Bennett]

You're right that I'm looping with the "i" variable, and there is a
typo, but so far I've only been testing with i=1 so that problem hasn't
arisen

I shall correct that anyways. Well spotted!

Cheers

Bennett