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" wrote in message
oups.com...
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 :o) 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