Excel 2007 VBA Code line not completed before next line executes - PLEASE HELP!!!

[basskitcase]

I have about 10 excel programs that all use the same macro to pull the trendline equation from a graph on one sheet and paste it to another cell on that same sheet. I created these programs in Excel 2003 but now that i have updated to 2007, something strange is happening (i am running the 2003 files in compatability mode as recreating the workbook is not feasable). When i call the macro (Macro3) it pulls the trendline equation and pastes it into a cell. I then have formulas in the adjacent cell that parse the equation into its coefficients. The next line of code after calling Macro3 pulls the coefficient and pastes it to another cell. So the flow should be:

Macro3 is called and pastes trendline to cell=>cell formulas parse and update values => next line of code excutes to copy coeffienct and paste elsewhere.

What is happening is that the next line of code after Macro3 seems to execute before Macro3 has finished pasting the trendline (or before the worksheet has properly updated, its happening to fast to tell), so the coefficients that get copied are the previous loop's coefficients. I have a workaround by calling Macro3, using an application.wait function, the calling macro3 again, but this is poor practice in my opinion and should not be required.

What is happening with 2007? why would it not ensure all code is executed and the cells have properly updated before executing the next line of code? The Macro3 code is posted below. I got this code from this site, although i don't remember who the original author was but much thanks to whomever posted it originally!!!

Sub Macro3()
Dim objTL As Trendline
Dim strText As String

With Sheet4.ChartObjects(1).Chart

Set objTL = .SeriesCollection(1).Trendlines(1)

With objTL
strText = .DataLabel.Text
End With

End With
Sheet4.Cells(7, 7) = strText

End Sub

 

[Johnny C]

I think you need to use the DoEvents keyword between lines of code.

Excel2007 doesn't necessarily wait for things to have completed, putting DoEvents on a line between the sections of code forces Excel to wait until it's done

There may be more to it (and wiser folk than I will tell you) but that works for me.

 

[basskitcase]

Thanks, thats a better method than the application.wait, but it still did not fix the problem without having to recall the macro again. An odd twist is that i just checked a different program i use that uses the same macro and it worked fine without the DoEvents or Applicaiton.wait, but i don't understand why because that portion of the code is identical between the two programs.

 

[shg]

Why not use LINEST to calculate the coefficients directly on the sheet, rather than parsing the trendline equation?

 

[basskitcase]

i cannot use linest because i have a polynomial equation.

As an update, the programt that was working now is not working, but i think i figured out why:

As part of the program code, right before it calls Macro3 to get trendline equation, it edits the data that the graph is based off of, necissitating that excel calculate a new trendline equation. I think the code is calling the macro and getting the trendline before the graph is completely done recalculating, thus pulling the old formula. To slow the program down, i activate back and forth between visable sheets twice to give it time to think, which fixes the issue without any use of application.wait or doevents, which do not seem to always fix the problem without the screen switching.

Is there a way to simulate the screen switching without actually switching the screens? That is what i hoped the application.wait would do, but it seems to halt everything, including the new trendline formula calcuation.

As a side note, why does excel 2007 not consider the trendline recalculation as part of the normal cell formula updating that must occur before the next line of code calculates? Is there a setting i am missing somewhere?

 

[shg]

You can use LINEST to do polynomial (and many other types of) regression:

       --A-- ---B--- ---C--- -D-- ---------------------E---------------------
   1    x^3    x^2      x     b                                              
   2   -2.01   15.13    3.54 0.75 A2:D2: {=LINEST(B5:B14, A5:A14^{1,2,3})}   
   3                                                                         
   4     x      y      Fit                                                   
   5       1   17.30   17.41      C5: =SERIESSUM(A5, 3, -1, $A$2:$C$2) + $D$2
   6       2   52.46   52.28                                                 
   7       3   93.29   93.29                                                 
   8       4  128.31  128.41                                                 
   9       5  145.98  145.57                                                 
  10       6  132.00  132.72                                                 
  11       7   78.00   77.81                                                 
  12       8  -31.02  -31.21                                                 
  13       9 -206.34 -206.40                                                 
  14      10 -459.92 -459.82

 

[basskitcase]

Thanks, i didn't realize that. For academic purposes, can anyone think why excel 2007 wouldn't complete the trendline calculation before allowing the next line of code to be processed? Do you think it has anything to do with the fact the speadsheet was origionally written in 2003, so it is running in compatability mode?

 

[Johnny C]

it might just be multithreading. You can turn the number of processors to use down to 1 in Excel options, advanced (if you have a dual core machine) but that's a bit harsh.

Have you tried putting a calculate in? This compares the version of Excel to the version of the workbook and does a full recalc if they are different (if you think that's the problem). Try an application.calculatefull on it's own too. DoEvents is worth leaving after just in case.

 if Application.CalculationVersion <> activeworkbook.CalculationVersion Then Application.CalculateFull
doevents