Hi there,

I am greatly puzzled by VBA running speed for the following code, which is run
inside a loop, with the number of total loops can be specified by the user.

When I use the gross return as the input value for the net return variable,
the macro/function runs fast and reasonable.

However, when I tried to use the calculated net return as the input for the
net return variable, either by referencing the worksheet cell or by
calculating as in below code, the macro/function got stuck when the number of
total loops specified exceeds some number, like 167 for me.


Is there some general reason why this could happen?

It seems the interaction among the rates can cause the problem, but why it
didn't cause problem for lower number of total loops?
Even when I tried to reference the copied/pasted values of calculated net
returns from worksheet cell, which should have removed the interaction among
the rates, it didn't solve the problem.


And the exported output of the calculated net returns in VBA code is
matching what are obtained in the worksheet calculation, for number of total
loops exceeding the stuck point 167, as mentioned above.

Really appreciate any advice or suggestion on this one!!


The code for the returns are as below:

NetReturn(i) = Cells(i, 4)

GrossReturn(i) = Cells(i, 4)
Rate1(i) = GrossReturn(i) * ((1 + f1) ^ (1 / 12) - 1)
Rate2(i) = (GrossReturn(i) - Rate1(i)) * ((1 - (1 - f2) ^ (1 / 12)) _
+ (1 - (1 - 0.0025) ^ (1 / 12)))
TotalRate(i) = Rate1(i) + Rate2(i)

NetReturntest(i) = GrossReturn(i) - TotalRate(i)
' NetReturn(i) = NetReturntest(i)