faster way to do this?

[Matt S]

I have this code right now:

For ii = 1 to 100
arrayA(ii) = arrayB(ii) / 10^4
Next ii

Is there a way to do this calculation faster? Maybe without cycling thru
all elements of arrayA?

Thanks,
Matt

 

[Otto Moehrbach]

Matt
Obviously if you cycle through less items, it will run quicker. If
ArrayB is a table on a sheet, you can divide each element of the table by a
simple copy - PasteSpecial - Divide. HTH Otto

 

[Rick Rothstein]

The answer is "no" since you are changing the value of the elements.
However, if this is some intermediate step where the values have come from
or are going to be placed on a worksheet, you can eliminate the loop in
favor of a simple PasteSpecial operation; but you will need to tell us more
about what you are doing before we can offer code to you for one of those
situations.

 

[Rick Rothstein]

The answer is "no" since you are changing the value of the elements.
However, if this is some intermediate step where the values have come from
or are going to be placed on a worksheet, you can eliminate the loop in
favor of a simple PasteSpecial operation; but you will need to tell us more
about what you are doing before we can offer code to you for one of those
situations.

 

[Rick Rothstein]

Sorry about posting this to your message... I thought I was pointing at the
OP's message.

 

[Matt S]

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))
Else
arrLambda(ii) = 1 - arrayE(ii) * 2 / (1 + 0.25 *
1.9 - 0.5 * 0.02) / (200 + 3.79 * arrayE(ii))
End If

If arrLambda(ii) > 1.524 Then
arrLambda(ii) = 1.524
Else: End If

Next ii

With Application.WorksheetFunction
arrCorrel(Count) = .Correl(.Index(arrLambda, 0),
..Index(Lambda_engine, 0))
End With

If arrCorrel(Count) > MaxValue Then
index_i = i
index_j = j
index_k = k
index_l = l
MaxValue = arrCorrel(Count)
Else: End If

Count = Count + 1

Next l
Next k
Next j
Next i

If index_i > 0 Then
For i = 1 To index_i
Range("G13").Delete Shift:=xlUp
Next i0
End If
Range("G7").Value = index_i

If index_j > 0 Then
For i = 1 To index_j
Range("F13").Delete Shift:=xlUp
Next i
End If
Range("F7").Value = index_j

If index_k > 0 Then
For i = 1 To index_k
Range("I13:J13").Delete Shift:=xlUp
Next i
End If
Range("I7").Value = index_k

If index_l > 0 Then
For i = 1 To index_l
Range("K13").Delete Shift:=xlUp
Next i
Range("K7").Value = index_l
End If

Range("J1").Value = MaxValue
MaxIndex = Application.WorksheetFunction.Max(index_i, index_j, index_k,
index_l)
LastRow = LastRow - MaxIndex


End Function

 

[Martin Brown]

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

 

[Dana DeLouis]

I have this code right now:

For ii = 1 to 100
arrayA(ii) = arrayB(ii) / 10^4
Next ii

Is there a way to do this calculation faster? Maybe without cycling thru
all elements of arrayA?

Thanks,
Matt

Maybe without cycling thru all elements of arrayA?

Here's one of a few ways to not "Loop," but it's probably not efficient.
I find it only helps when equations get too complicated.


Sub Demo()
Dim v
v = Array(30000, 50000, 70000, 110000, 130000)
v = Fx(v)
End Sub


Private Function Fx(v)
ActiveWorkbook.Names.Add "M", v
Fx = [M/10^4]
ActiveWorkbook.Names("M").Delete
End Function


= = = = = = =
HTH :>)
Dana DeLouis

 

[Matt S]

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
.

Martin, can you explain further what you mean when you say it should be in
the Fourier domain? Can you give me some references?

Thanks,
Matt

 

[Martin Brown]

Martin, can you explain further what you mean when you say it should be in
the Fourier domain? Can you give me some references?

A convolution with a shift in real space is a (complex number)
multiplication in the Fourier domain. The best way depends critically on
the lengths of the data and pattern to be matched. For large datasets
the FFT method will usually win by a huge margin.

There should be plenty of references online and practical but slightly
dodgy code to do it on the Numerical Recipes webpage (old edition is
free access).

http://www.nrbook.com/a/bookcpdf/c13-2.pdf

And you get every correlation result in a single complicated operation.

You probably need to read the descriptions of FFTs and their version is
restricted to power of two length arrays (and you will need to think
about the boundary conditions you wish to impose).

Worth asking in the numerical analysis group too.

They will be rude about NR methods but their algorithms are (mostly)
usable just not always the best.

Regards,
Martin Brown