Matt S wrote:
This is the full code below... it was my solution to my previous post that
nobody was able to help me with. In a nutshell, I define data arrays from
ranges on my excel sheet. Then I go about shifting the data 1 row down,
calculate this lambda value, and 1compare to the engine lambda by the
correlation function. I look for the shift that maximizes the correlation
function. The problem is, if I allow 10 or more shifts in data, it's
(10+1)^3 calculations that need to happen to fill my arrCorrel array. By far
the longest process is calculating the lambda value. Would pasting the
calculated lambda somewhere to do the calculations be faster?

Thanks,
Matt

Function DataAdj(LastRow As Double)

LR = LastRow - 12
Application.DisplayAlerts = True
NumShifts = 10

ReDim arrCorrel(1 To (NumShifts + 1) ^ 4)
ReDim arrCO(1 To LR)
ReDim arrNOx(1 To LR)
ReDim arrHC(1 To LR)
ReDim arrO2(1 To LR)
ReDim Lambda_calc(1 To LR)
ReDim Lambda_engine(1 To LR)
ReDim arrayA(1 To LR)
ReDim arrayB(1 To LR)
ReDim arrayC(1 To LR)
ReDim arrayD(1 To LR)
ReDim arrayE(1 To LR)
ReDim arrayF(1 To LR)
ReDim arrLambda(1 To LR)

arrCO = Application.Transpose(Range("G13:G" & LastRow))
arrHC = Application.Transpose(Range("F13:F" & LastRow))
arrNOx = Application.Transpose(Range("J13:J" & LastRow))
arrO2 = Application.Transpose(Range("K13:K" & LastRow))
Lambda_engine = Application.Transpose(Range("O13:O" & LastRow))

Count = 1
MaxValue = 0

For i = 0 To NumShifts
For j = 0 To NumShifts
For k = 0 To NumShifts
For l = 0 To NumShifts
For ii = 1 To LR - NumShifts

arrayA(ii + i) = arrCO(ii + i) / 10 ^ 4
arrayB(ii + j) = arrHC(ii + j) / 10 ^ 4
arrayC(ii + k) = arrNOx(ii + k) / 10 ^ 4
arrayD(ii + l) = arrO2(ii + l) / 10 ^ 4
arrayE(ii) = 4 / 3 * arrayA(ii + i) + 18 * arrayB(ii
+ j) - (2 * arrayD(ii + l) + arrayC(ii + k))
arrayF(ii) = 0.5 * arrayC(ii + k) + arrayD(ii + l) -
(2 / 3 * arrayA(ii + i) + 9 * arrayB(ii + j))

If arrayF(ii) = 0 Then
arrLambda(ii) = 1 + arrayF(ii) * 2 / (1 + 0.25 *
1.9 - 0.5 * 0.02) / (100 - 4.79 * arrayF(ii))

VBA tends to be a rather simple minded compiler and in an inner loop
like that it probably will not do strength reduction.

You could well find that x = x/10^4
has been compiled as x = x / exp(4*log(10))
in which case x = x/10000 will be faster and x*0.0001 faster still but
rescaling the problem so you don't need to multiply at all would be
fastest of all.

Same applies to common sub expressions like 2/(1+0.25*1.9 - 0.5*0.02)
worth working it out once outside all the loops!

This looks like the sort of correlation problem that is best done in the
Fourier domain - particularly so if the reference spectra are contant.
Then you get the entire correlation at every shift with a cost 2NlogN
transform and some book keeping where N is the length of your array.

Regards,
Martin Brown