0

I have included the code of my function. I mostly scrapped this together from things I found online, because I am very much an amateur coder. I am trying to take the trendline of a graph and use it for a mathematical calculation. When I step through this code, it works great. However, when I call the function from another sub, it gives me an error. Error 9: Subscript out of range. When I debug, it shows me the line a = spl(0). The real problem is that the variable "s" remains empty. Why?

I have tried adding the creation of the chart to the function to avoid an error with "Active Sheet". I also tried pasting this code into my sub instead of calling a separate function. Still nothing. When I debug and highlight the t.DataLabel.Text, it shows me the correct value, but for some reason s is not saving that value. In the Locals window, t has value, but s is blank (" ").

Function TrendLineLog() As Double
Dim ch As Chart
    Dim t As Trendline
    Dim s As String
    Dim Value As Double

    ' Get the trend line object
    Set ch = ActiveSheet.ChartObjects(1).Chart
    Set t = ch.SeriesCollection(1).Trendlines(1)

    ' make sure equation is displayed
    t.DisplayRSquared = False
    t.DisplayEquation = True

    ' set number format to ensure accuracy
    t.DataLabel.NumberFormat = "0.000000E+00"

    ' get the equation
    s = t.DataLabel.Text '<--------- HERE


    ' massage the equation string into form that will evaluate

    s = Replace(s, "y = ", "")
    s = Replace(s, "ln", " *LOG")
    s = Replace(s, " +", "")
    s = Replace(s, " - ", " -")
    spl = Split(s, " ")
    a = spl(0) '<----------- HERE
    b = spl(1)
    c = spl(2)
    y = 0.5

..... Math stuff

End Function

Here is the code section calling the function:

For rowx = 41 To 46 Step 1
Cells(rowx, 4).Select
va = Cells(rowx, 4).Value
vb = Cells(rowx, 5).Value
vc = Cells(rowx, 6).Value
vd = Cells(rowx, 7).Value
locb = ActiveCell.Address
Cells(rowx, 7).Select
loce = ActiveCell.Address
rang = locb & ":" & loce
If va < 0.8 Then    
If va < vb And vb < vc And vc < vd Then
    Range(rang).Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    ActiveChart.SetSourceData Source:=Range(rang)
    ActiveChart.FullSeriesCollection(1).Trendlines.Add
    ActiveChart.FullSeriesCollection(1).Trendlines(1).Select
    Selection.DisplayEquation = True
    Selection.Type = xlLinear
    ans = TrendLineLin()
    Sheets("Results").Activate
    Cells(rowx - 39, 3).Value = ans
    Sheets(nam).Activate


ElseIf va < vb And vb < vc And vc > vd Then
    Range(rang).Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    ActiveChart.SetSourceData Source:=Range(rang)
    ActiveChart.FullSeriesCollection(1).Trendlines.Add
    ActiveChart.FullSeriesCollection(1).Trendlines(1).Select
    Selection.DisplayEquation = True
    Selection.Type = xlLogarithmic
    ans = TrendLineLog()
    Sheets("Results").Activate
    Cells(rowx - 39, 3).Value = ans
    Sheets(nam).Activate
ElseIf va < vb And vb < vc And vc > vd Then
    Range(rang).Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    ActiveChart.SetSourceData Source:=Range(rang)
    ActiveChart.FullSeriesCollection(1).Trendlines.Add
    ActiveChart.FullSeriesCollection(1).Trendlines(1).Select
    Selection.DisplayEquation = True
    Selection.Type = xlLogarithmic
    Windows(nam1).Activate
    ans = TrendLineLog(rang)
    Windows(nam1).Activate
    Sheets("Results").Activate
    Cells(rowx - 39, 3).Value = ans
    Sheets(nam).Activate

I have a function for exponential, logarithmic, and linear trendlines. My test case is a log, which is the function that is posted. The code is nearly identical.

Eric Cozzone
  • 1
  • 2
  • What calls this function? – Trevor Jun 21 '17 at 19:21
  • 3
    I suspect your problem is with this `Set ch = ActiveSheet.ChartObjects(1).Chart`. If the active sheet is not what you expect it to be when calling from a separate sub and the 1st chart on that sheet has no trendline, t will fail. One solution is pass the chart object into the function itself to ensure that it works on the chart you expect it to. – Scott Holtzman Jun 21 '17 at 19:21
  • 2
    If it has worked sometimes but not when you call from another sub then the ActiveSheet is probably the issue. You need to specify the sheet rather than just saying "whatever sheet is active at the minute" because if the sub is called from another sheet then your activesheet is not the one with the chart on it?! Try `Set ch = thisworkbook.worksheets("NAME OF SHEET WITH CHART ON IN HERE").ChartObjects(1).Chart` – jamheadart Jun 21 '17 at 19:23
  • What isthe value of `t.DataLabel.Text`? – David Zemens Jun 21 '17 at 19:28
  • 1
    This error will only be expected if `t.DataLabel.Text` is an empty string. Any other value should return a `String()` array, which will always have at least 1 element, so index `0` should not fail. – David Zemens Jun 21 '17 at 19:31
  • I tried adding the creation of the chart and trendline to the function, but I'm still having the same problem. When I click debug and highlight t.DataLabel.Text, it gives me the value, but for some reason, s is not saving the string. In the Locals window, t has a value, but s remains blank – Eric Cozzone Jun 22 '17 at 17:16
  • *However, when I call the function from another sub, it gives me an error* - please include the code for that `Sub`, or at least tell us if that `Sub` is changing the `ActiveSheet`. – Mathieu Guindon Jul 06 '17 at 15:57
  • I made sure that the original sub did not change the active sheet before calling the function. I tried activating the correct sheet exactly one line before calling the function, and I tried activating the correct sheet in the function itself. Still nothing – Eric Cozzone Jul 06 '17 at 16:10
  • I added the sub code around calling the function. – Eric Cozzone Jul 06 '17 at 16:18
  • The value of t.DataLabel.text in my test case (which produces error) is "y = 6.38ln(x) + 3.03" but for whatever reason, s = " " – Eric Cozzone Jul 06 '17 at 16:23
  • @Mat'sMug I added MsgBox t.DataLabel.text before the line s=t.DataLabel.text and it is blank – Eric Cozzone Jul 06 '17 at 16:29
  • Good. So the value of `t.DataLabel.Text` is `""`, not `"y = 6.38ln(x) + 3.03"`. Your code relies heavily on `Select` and `Activate`, which makes it very frail / bug-prone. [Start with fixing that](https://stackoverflow.com/q/10714251/1188513). – Mathieu Guindon Jul 06 '17 at 16:32
  • Thank you. I'll work on that and edit this post if I still can't figure it out – Eric Cozzone Jul 06 '17 at 16:34

0 Answers0