-1

I am reposting this question, because I haven't gotten an answer and I still can't figure out what I'm doing wrong. My latest efforts to resolve the problem on my own are detailed below the code.

Original post: VBA Function not storing variable

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?

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 '<--------- ACTUAL PROBLEM 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) '<----------- DEBUG SAYS HERE
b = spl(1)
c = spl(2)
y = 0.5

..... Math stuff

End Function

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 (" ").

Eric Cozzone
  • 1
  • 2
  • What is the value of `s` before you do all the `Replace` statements? – aphoria Jul 06 '17 at 15:24
  • -1 posting an exact duplicate because a previous post didn't get attention isn't how this site works. A simple [edit] would bump the question back onto the front page. – Mathieu Guindon Jul 06 '17 at 15:41
  • Also you have received comments on the original post, asking for clarification and pointing you toward a solution - the least you could do is reply to those comments, instead of posting a duplicate. – Mathieu Guindon Jul 06 '17 at 15:47
  • Hey @Mat'sMug , I edited the old post (no new comments) and responded to the comments (also no response). I apologize for breaking etiquette, but I'm new to the site. Also, I pretty clearly mentioned that this is a duplicate. – Eric Cozzone Jul 06 '17 at 16:03
  • @aphoria the problem is that it stays blank at the s = t.DataLabel.Text line. So it is blank during all the replacements too – Eric Cozzone Jul 06 '17 at 16:05
  • [this comment](https://stackoverflow.com/questions/44684553/vba-function-not-storing-variable#comment76882748_44684553) still needs to be addressed. Your question is unanswerable in its current form, that's why you didn't get any answers. – Mathieu Guindon Jul 06 '17 at 16:10
  • I answered the comments, and added the code. – Eric Cozzone Jul 06 '17 at 16:24

1 Answers1

0

Yes, of course you will get an error on the line you pointed out. You are calling spl(0) as though it is its own function, though you did not define spl() as a sub (function) anywhere in this code. Or, alternatively (more likely) you are calling it as an array, which also throws up some flags.

Make sure you are defining spl in your code. You never do this. Add line: 

Dim spl(1 to 3) As String

Then you should find that spl(1), spl(2), and spl(3) are what you desire.

ERT
  • 719
  • 5
  • 16
  • `spl = Split(s, " ")`. Even if undeclared, it's clearly an array, and it would be 0-based. This answer is wrong, the error you're describing *would* be a **compile-time** error **if** `Option Explicit` was specified, which it clearly isn't (OP couldn't even *run* their code if that was the case). Funnily enough, the original/linked question has a deleted answer that's saying pretty much exactly what this answer says. – Mathieu Guindon Jul 06 '17 at 15:44
  • Why wouldn't OP *want* to specify `spl` regardless? Isn't that a generally accepted practice in most languages? – ERT Jul 06 '17 at 15:48
  • Absolutely. I'm saying it has nothing whatsoever to do with the problem at hand, and won't fix the issue either. [deleted answer from the duplicate question](https://i.stack.imgur.com/mwiqg.png) – Mathieu Guindon Jul 06 '17 at 15:49
  • So then, how would one remedy this? Now I am interested. You clearly have experience in VBA, what would you do? – ERT Jul 06 '17 at 15:50
  • I'd use `Option Explicit` and indeed declare all my locals. The OP's problem is, as noted on the other post's comments (to which OP couldn't be bothered to reply), quite likely caused by `Set ch = ActiveSheet.ChartObjects(1).Chart`, which assumes the `ActiveSheet` contains a chart, and the rest of the code assumes that chart has data labels that have non-empty `Text`. OP needs to remove assumptions from their code, especially since it appears to be only failing "when I call the function from another sub" - any money bets that this other sub is `.Activate`ing a worksheet. – Mathieu Guindon Jul 06 '17 at 15:54
  • Understood. I see where you are coming from. I suppose OP's structure is just much different than the way I would approach this problem. I am new to VBA, and am still learning a great deal about how certain individuals' logical progression in the language. Sorry about the incorrect answer, then. – ERT Jul 06 '17 at 15:57
  • Nah, nothing wrong with being wrong - that's how we learn. What I'm irked about is OP's attitude, ignoring comments on their original post and putting up an exact duplicate and whining about not getting attention when their question doesn't even provide all the necessary information, even after comments point it out. – Mathieu Guindon Jul 06 '17 at 15:59
  • Yes. I am fairly new to the site, but reposting questions, especially relatively simple ones with little background information, seems like a taboo. Live and learn, I suppose. – ERT Jul 06 '17 at 16:02
  • I'm not trying to ignore comments. I am doing this project at work, and I had to do something else. Thats the only reason for a delayed response. I added a comment less than a day later. Is there some other way to respond to comments? – Eric Cozzone Jul 06 '17 at 16:08
  • 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 Please read the text below my code in this post. The data label correctly has value, but it won't store to the variable s – Eric Cozzone Jul 06 '17 at 16:12
  • @EricCozzone please edit that information into the original post, since this one is closed as an exact duplicate of the other one. If you add `MsgBox `t.DataLabel.Text` just before that `s =` first instruction, do you get an empty message or the expected equation? VBA "not storing variable" makes no sense, there's no magic. – Mathieu Guindon Jul 06 '17 at 16:20
  • I have edited the original post. – Eric Cozzone Jul 06 '17 at 16:27
  • I added MsgBox t.DataLabel.text before the line s=t.DataLabel.text and it is blank. – Eric Cozzone Jul 06 '17 at 16:28
  • 1
    @E.Trauger I had to upvote your answer as it was correct and someone apparently didn't understand why it was actually correct. – Matt Jul 07 '17 at 03:27