Hi all
Does anybody know if it is possible to capture the data contained in a
trendline legend using VBA. Specifically I am interested in the getting the
base and exponent values of the y function. Currently I have a series of
input boxes that prompt the user to enter the base and exponent values
respectively (as they can see them on the chart). However, it would be cool
if I could get the code to do all this. Can anybody help please?!
Thanks
Andy

Please ignore this question - I've managed to work out a solution based on
previous posts requesting the same for polynomial trendlines.

"Andy" wrote:

Hi all
Does anybody know if it is possible to capture the data contained in a
trendline legend using VBA. Specifically I am interested in the getting the
base and exponent values of the y function. Currently I have a series of
input boxes that prompt the user to enter the base and exponent values
respectively (as they can see them on the chart). However, it would be cool
if I could get the code to do all this. Can anybody help please?!
Thanks
Andy

Even though the original poster found his own way, I would still find i
helpful if someone could still answer this for me. Thanks

--
miker
-----------------------------------------------------------------------
mikerr's Profile: http://www.excelforum.com/member.php...fo&userid=2583
View this thread: http://www.excelforum.com/showthread.php?threadid=39210

Mike,

Excel has a number of built-in statistical worksheet functions that you may
be able to use
(even in code).

I believe the Solver Add-In also has a number of statistical functions.

And than you can build your own functions either on the worksheet or with
code.

I always remember the old formula for a straight line:
y = mx + b

If you can find a similar formula for your chart than you can use the data
range
to get all the stats you want...


--
steveB

Remove "AYN" from email to respond
"mikerr" wrote in
message ...

Even though the original poster found his own way, I would still find it
helpful if someone could still answer this for me. Thanks.


--
mikerr
------------------------------------------------------------------------
mikerr's Profile:
http://www.excelforum.com/member.php...o&userid=25830
View this thread: http://www.excelforum.com/showthread...hreadid=392109

Hi Mike
I hope you're still checking this!
I think the code you will need to use varies depending on what type of
trendline you are using. In my case it was a power line (my code is below
and comes with a health warning!!). In effect it returns an array where the
first element is the base number, the second element is the exponent and the
third element is the R^2 value. The inputs to the function are simply a
chart and a number identifying the index of the series (in case you have more
than one - I use an enum). As the comments state this works by extracting
the data from the trendline data label. If you want to get the information
straight from the source data then have a look he

http://www.j-walk.com/ss/excel/tips/tip101.htm

Also try doing a search on this newsgroup for 'trendline data' as there have
been a few other related queries, which is how I figured this out.

Good luck!

Private Function GetPowerTrendData(myChart As Chart, stSeries As
SERIES_TYPE) As Variant
'This function strips out the trendline data from the label
'and returns them in an array. It only works for a power trend
'curve.
Dim serData As Series
Dim tl As Trendline
Dim intPos As Integer
Dim strText As String
Dim varArr As Variant

'Assign the series and trendline variables
Set serData = myChart.SeriesCollection(stSeries)
Set tl = serData.Trendlines(1)

'Only proceed if the trendline type is a power curve
If tl.Type = xlPower Then

'Get the text of the trendline label
strText = tl.DataLabel.Text

'Find the first instance of an "=" symbol (will not need the "y=" bit
intPos = InStr(1, strText, "=")

'Replace the 'x' with a delimiter for the Split function
strText = Replace(strText, "x", "|")

'Replace the return char and R2 text with a delimiter
strText = Replace(strText, Chr(10) & "R2 = ", "|")

'Replace the text string with the end part (excluding the y=)
strText = Mid(strText, intPos + 1)

'Split the text, assign to an array and return the array
varArr = Split(strText, "|")
GetPowerTrendData = varArr
Else
MsgBox "This function can only be used for a Power trendline.",
vbExclamation, "Incorrect Trendline"
End If


End Function



"mikerr" wrote:


Even though the original poster found his own way, I would still find it
helpful if someone could still answer this for me. Thanks.


--
mikerr
------------------------------------------------------------------------
mikerr's Profile: http://www.excelforum.com/member.php...o&userid=25830
View this thread: http://www.excelforum.com/showthread...hreadid=392109

Mike---
You might be interested in code that I posted some time back; link (allow
for wrap) is
http://groups-beta.google.com/group/...0d 271303e0d6

If you Google under the newsgroups for microsoft.public.excel.*, author =
Braden, words contain "trendline", you can get a sense of community effort
and thought leading to the code. You *will* need to watch out for line
breaks that happened with the posted code. Sorry about that. I also posted
code back in 1999 to extract the numbers, but the later post (2003) works
more as a function. It is intended to get around then-extant problems with
LINEST; I don't yet know if these problems have been fixed with the Office
SP1 update, but given Microsoft's response time to fixing other problems
with Excel, I sorta doubt Linest is working well.

HTH
Dave Braden


"mikerr" wrote in
message ...

That's what I was hoping for. Thanks!


--
mikerr
------------------------------------------------------------------------
mikerr's Profile:
http://www.excelforum.com/member.php...o&userid=25830
View this thread: http://www.excelforum.com/showthread...hreadid=392109