How do I record all the odds

[MiniBlueDragon]

Ah that's a pain. Wonder why it's so hit and miss? :/

The Worksheet_Calculate isn't the same as Worksheet_Change is it? If they're different I've not used the Worksheet_Calculate before. How do you call it?

[Joe68]

Simple as...
Private Sub Worksheet_Calculate()
<<Stick your code here>>
End Sub

Or - at the top of your VBA editing screen, just under the tool bar, you'll see two pull downs.

Try the left one and select worksheet.

Try the right one, which will now list all of the events associated with the worksheet object. Click on "Calculate".

And that's the thing of it, you're not calling it, you're writing code that runs when an event occurs. There are a number of events that can happen in a worksheet, which you see listed in that pulldown.

As for the difference between the Change and calculate events, they certainly are different. Sounds silly cause a lot of people have never seen Excel not calculate when a cell changes.

The classic is if you have autocalculate turned off (Formulas ribbon, Calculation options in Excel 2010). You can change values till the cows come home but it isn't going to calculate until you tell it to. I used to use that option when I was writing extremely large risk management, portfolio analysis type spreadsheets on some older computers. On those things, if it recalculated every time I changed a cell or formula, it would take 10 times longer in development while I waited for it to recalculate. So, it's sometimes useful that change and calculate are different events.

[MiniBlueDragon]

Ah so basically "calculate" does the same as "change" except that "change" does it when a market is selected (the sheet changes) whereas "calculate" does it when the sheet is recalculated (which in this scenario would be directly after it "changes" is autocalculate is enabled.

Gotcha! Thanks.