Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trendline Properties/Deletions in Excel VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trendline Properties/Deletions in Excel VBA
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trendline Properties/Deletions in Excel VBA
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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trendline Properties/Deletions in Excel VBA
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 :-P I shall correct that anyways. Well spotted! Cheers Bennett |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct range changing with deletions | Excel Worksheet Functions | |||
Automatic deletions | Excel Discussion (Misc queries) | |||
Because of row/column deletions, how do you reset the last cell? | Excel Discussion (Misc queries) | |||
Prevent Worksheet Deletions | Excel Worksheet Functions | |||
Data Form Deletions | Excel Discussion (Misc queries) |