View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Michael R Middleton Michael R Middleton is offline
external usenet poster
 
Posts: 18
Default Trendline Properties/Deletions in Excel VBA

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