VBA processes before needed cells are calculated

I'm using a script from here: http://www.excelforum.com/excel-form...final-and.html

The script basically gets a collection of cells and comma separates them with a final "and" (as the last comma)

My problem is, the VBA script seems to be running before cell values are calculated. If I change student % scores (sheet 1 "Grades") then the "Top Subjects" column is what it should be just prior to the change of data.

How can I make sure the script executes only when the cells have been fully calculated, so that the results appear accurate when data is updated?

Please examine the attachment: autograder_lags.xlsm

Can you put the code in

Please Login or Register  to view this content.

b

Open the VBE. In the left window which lists your objects, click on the Worksheet you want to place the code in. Your window on the right will open blank. Click in the left address window and select worksheet. Now in the right address window, select calculate. Copy everything from your code except the First line and last line and paste in. SAVE

See the attached .VBE.jpg

Define your rRange as a Dimension statement

Please Login or Register  to view this content.

To delete a module, single click on the module in the left window, click on file and remove module.

Not sure why that is happening. An alternative means. Click on Alt +F8 to open the Macro selection window. Highlight your macro and click on delete. Does that work?

ALT+F8 brings up the a message box with the previous error message (above) saying that my modified VB is broken. (Compile Error: Function call on left-hand side of argument must return a Variant or object) Looks like we need to fix the newer procedure you directed me in making before we can delete other parts.

I feel like there are a lot of dead ends being introduced. Could you please look at the file I attached in my original post? It's actually working except that the VB function gets called too early (it's processing immediately, before the cells feeding to it have had their own formulae updated)

What's meant to be happening (in the excel worbook) is that the right column reports the highest subject score for each student (e.g. English). If a student has equal top score, it will report all of them with commas and "and" like this: "English, Maths and Cooking". The actual formula etc are run from the sheet labelled "BestWorst".

Edit: I managed to delete the module by cutting and repasting the main VB code (attempt to flush any errors). Deleting the original module might not have been the best move. Now I'm getting Run-time error 424: Object required. I take it the formula cells are trying to call the module I've just deleted. Am going back to previous backup.

Still don't know how to get the VB modules to not run after (not before) other cells in the workbook are updated. This might be a chicken egg problem, because other cells will use the output.