In Excel VBA, the volatile Offset function is very slow. What is a alternative?

Question

Hi,

I'm having a performance issue in my Excel macro. One particular piece of code runs really slow. I think the reason is the Offset function. I've found online that it is a volatile function which recalculates a lot.
The strange thing is that I use it as a VBA-function, so I can't figure out how it would affect performance as it never needs to be recalculated.

This is how I use the offset function:

For i=0 To 10
 For P=0 To 3
  For T=0 To 3
    Range("C9").Offset(6 * i + T, P).Value = effect(i,P,T)
  Next
 Next
Next

Is there a faster (nonvolatile?) alternative?

Answer 1

Have you tried Cells(row,column).Value = ...

Obviously you will need to adjust row and column to account for your starting position of C9 (9,3)

Also are you sure that the function effect() isn't the bottleneck?

UPDATE:

Also have you tried

Application.Calculation = xlCalculationManual
    ....Your Code....
Application.Calculation = xlCalculationAutomatic