vba excel: do something every time a certain variable is changed

Question

im doing a bunch of stuff to the variable St

For i = 1 To 30000

 Randomize
 e1 = Rnd
 e2 = Rnd
 z1 = Sqr(-2 * Log(e1)) * Cos(2 * 3.14 * e2)
 z2 = Sqr(-2 * Log(e1)) * Sin(2 * 3.14 * e2)

 St = So * Exp((r - (sigma ^ 2) / 2) * T + sigma * Sqr(T) * z1)
 C = C + Application.WorksheetFunction.Max(St - K, 0)
 St = So * Exp((r - (sigma ^ 2) / 2) * T - sigma * Sqr(T) * z1)
 C = C + Application.WorksheetFunction.Max(St - K, 0)
 St = So * Exp((r - (sigma ^ 2) / 2) * T + sigma * Sqr(T) * z2)
 C = C + Application.WorksheetFunction.Max(St - K, 0)
 St = So * Exp((r - (sigma ^ 2) / 2) * T - sigma * Sqr(T) * z2)
 C = C + Application.WorksheetFunction.Max(St - K, 0)



Next i

how do i get notified every time the variable changes?

Answer 1

you can use breakpoints or dump St's value after every evaluation to a column on your excel file.

a very annoying solution would be to add message boxes all over the place

yet another clever solution would be to add logic to your code by declarin a variable that would store the previous value of St and then compare against it after the evaluation of the new St

Answer 2

maybe this a very naive answer but if you make a function that calls the sub then everytime a variable is changed then the function will be reevaluated.