I have a meeting this afternoon and can not figure out why my code just all of a sudden stopped working. I am
plotting a trendline for a scatterplot. The trendline uses a spin button to increase or decrease the values of the
trendline, and then the graphic is supposed to update. I am using the spin button to increase a variable by a
factor, and then re-running the trendline calculation to develop another array to replace the existing trendline
with. I am not sure what happened, but now the chart on the screen does not update after I change the
trendline variable. Here is my code:

Private Sub UpdateUpperALSConstantN()

Dim ALSSeriesArray(30, 0) As Double
Dim TestCell As Range
Dim I As Integer
Dim CalcVal As Double

dUpperALS = Round(dUpperALS, 3)

ScatterplotTrendlineTools.TextBox2 = dUpperALS

For Each TestCell In TLYRange
CalcVal = Round(TestCell.Value + dUpperALS, 3)
ALSSeriesArray(I, 0) = CalcVal
I = I + 1
Debug.Print CalcVal
Next TestCell
Stop
'Updates the current graph upper limit of scatter trendline
UpperALSRange = ALSSeriesArray

End Sub

I populate the array with the new calculation, and then try to replace the existing trendline (UpperALSRange) with the newly calculated array (ALSSeriesArray). What am i doing wrong here?

any assistance would be most appreciated.

Hello,

first of all, you didn't specify what exactly doesn't work or which error do you get.

not sure what you do with the UpperALSRange variant array afterwards.

I would do all this via spreadsheet formulas (much easier to debug) and display the calculated 31 values as a line, why do you use VBA here?



Activesheet.chartobjects("Chart 1").Chart.Seriescollection(1).Values = UpperALSRange

could work at the end of your code. Obviously, adjust "Chart 1" to your name.

But once more, why not calculate the trendline in the spreadsheet and plot it in an easy way?

Martin

The entire program is much larger than is what presented. This is part of an excel database program that manages sewer flow data. The routine above simply provides a means for displaying a scatterplot and trendline for the flow data. UpperALSRange is the upper limit of scatter trendline. There are two other trendlines that plot as well, but I selected only one of them for my question. The UpperALSRange is the series on the graph. I am trying to redefine the trendline data, and update the graphical plot. An example of the graphic is attached. Each time I press the spin button, the program recalculates the trendline values and adds or subtracts an addtional percentage to the trendline value, thus increasing or decreasing the position of the trendline on the graphic. The code above has been working fine. Whithout making any changes to the code, the program just stopped working for me yesterday. I was wondering if I am approaching the update of the graph data range appropriately.

And what error do you get?

The method of updating seems to be OK, in general, it should be like this:

ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Values = y

where y is an array of values.

This problably wouldn't help, but is Calculation set to automatic?

Martin

I have run a Debug.Print on the data that is populating the array, and it is changing. I will have a look at the code you presented and let you know if it works. However, again, it was working just fine the way it was. The current focus is on the ChartObject when that code runs, so I didn't feel it necessary to use the method you have presented. But I will try that. Thanks.

No problem, just once more the question:

Does the code error out (any On Error Resume Next statement in the routines?), or the chart simply won't update?

Martin

The chart won't update. There are no errors thrown.

OK, it's difficult to help without having a grip at your file (I understand the file contains sensitive data).

Try to Debug.Print the array just before you assign it to the Values property of the series object.

If it's OK then there must be something wrong in the

Series.Values = Array

statement. What is exactly the statement you use?

And, once more the question, is there maybe On Error Resume Next statement somewhere in your code that would hide the run-time error if it occurs?

Martin

UpperALSRange = ALSSeriesArray is the statement that I use to equate the new array to the existing array.

I have used the Debug.Print before I assign the values, and the values are updated.

UpperALSRange is the named range on the scatterplot

ALSSeriesArray is the data series that is to populate the UpperALSRange.

I have:

Private
Dim ALSSeriesArray(30, 0) As Double
and
Public
Dim UpperALSRange As Range

Oh yeah, and there is no On Error Resume Next statement in the code to bypass a runtime error

And this did work in the past?

You can't refer to a named formula/range like this; either use

Range("UpperALSRange") = ALSSeriesArray

or, for short:

[UpperALSRange] = ALSSeriesArray

Martin

Yes it did, for quite some time, then it stopped one day.

Thanks. I will try your suggestion this afternoon when I have an opportunity. I will keep you posted. I
appreciate your sticking with me.

No problem, I didn't mean to be offending and maybe even I may have misunderstood.

If UpperALSRange is a object variable set to a range object, then your code seems to be OK.

This would mean that there must be something similar to this

Set UpperALSRange = Sheets("Sheet1").Range("A1:A31")

in your code.

But if it's a named formula, you can't refer to it as you did...

Maybe you could post the entire code?

Martin

No offense. I am not an expert in programming and am always willing to listen. You have been helpful.

I will try to copy the code into a text file and send it. However, there are a lot of references and it could be complicated to do so. Give me some time here and I will try.

OK, you don't have to copy all the parts... important is the part where you declare the UpperALSRange variable and the part where you pass the array to the values property of the series object.

From your last posts, I had a feeling that you pass the array into a range in the sheet that serves as value source.

Seeing the code would help

M.

My last post had a zip file. That code is behind my spin button where I update the trendline information.

This post has another zip file attached. The code posted here is run when I first launch the program. This file reads my excel database table information, and creates the scatterplot. The routine called "InitializeGraphics" sets the values to the appropriate source.

I hope this provides some insight. This is complicated to explain without sitting down together. Thanks again for your patience.

Hello,

Where do you initialize UpperALSRange in your text file? I.e., Set UpperALSRange = Range(Whatever)?

I see your Variable declaration, etc... But it doesn't appear to be initialized to anything? Also, what is Stop doing in there?

Also, I would probably explicitly reference the Value property of your Range, e.g.,

UpperALSRange.Value = ALSSeriesArray

I see your point. This program is an existing program that I inherited. I am no expert, which is obvious. When the program launches, the code I posted last time sets all of the chart variables, and defines the ranges of the stored information for each trendline. The first set of code I sent runs each time the spin button is pressed. I don't see where it does initialize UpperALSRange. I will need to do some further searching for how all of this operates. You have been helpful as well. I will spend some time tearing this down even further. Thanks.

By the way, I inserted the Stop to get into the code during the operation to see what was going on.

Thanks a lot for jumping in Nate

coolscheid, based on your last attachment, the critical part of the code is

Private Sub UpdateChartRanges()

routine.

I don't see that you call it from anywhere... or is it an entry point of the entire process? If so, there's no other attempt to change the Y values.

Use F5 to step through the code and see... it's difficult to track the logics of this, sorry to say that, 'spagetti code'.

Martin

Yes, I agree, spaghetti code. I am having difficulty tracking it myself. I have been stepping through each line.

Both you and Nate have been very helpful and I appreciate your efforts. I wil continue to figure this out.

The funny thing is that I have another copy of the file that I restored from the day before I had this problem.
That file uses the same exact code and works just fine. I have made no changes to either file. This is strange and upsetting as the other file could stop working any time.

Of course I meant F8, not F5

Sorry that we couldn't be of more help so far, the code is really difficult to track.

I personally would do much more within the spreadsheet itself, I don't have a feeling that VBA is necessary here in general...

It is very strange that the original file works and this one not. Perhaps use the restored file then? OK, I know, bad suggestion because you never know when the file 'stops working' again.

The only think that strikes me is the calculation setting that tends to be bound to the workbook. But you said the calc is set on automatic, right?

Martin

The more I dig into this program, the more my opinion changes of how it works. Yes, it appears that it is bound
to the work book. Private Sub UpdateqQ() re-calculates the center trendline. qQRange is defined by a range in
the workbook, and is then the new values for the array are set to qQArray. It appears that the new array defined by qQArray is then populated into the workbook in the column next to qQRange. however, the workbook values do not appear to be updating for some reason.

I need to dig further. Is there a way to directly interact with the chart from the code, rather than to reference
the workbook? Essentially, I want to store the final trendline array in the workbook so when I set the
appropriate trendline, I can go back to it in the same state. How do I know what the chart series names are so that I can change their values?

These may be difficult questions, but if you give me a few pointers, I may be able to rewrite this and make it work
correctly. I am just really learning the object model for excel, and am quickly picking up on it. I just need a few
pointers. Thanks.