Calculating some worst-case values for angles in an interval

Question

Sadly enough I have a bit of code that is really slow and being a newbie in programmation, I don't have the faintest clue on how to optimize. Is there any way to make those calculations faster?

This code calculates the worst case of some parameters cycling through AktBeam and FileCounter (those cycles contain a LOT of other stuff, and I'm sorry to say that I cannot include the whole code) and inside a circumference (have a look at the select case block), and stores them in the BT and EC arrays.

AmplCo, AmplCr, PhaseCo and PhaseCr are arrays containing double variables.

I'm looking for all the possibilities here to make my processor perform the least amount of operations (e.g. if x^2 is worse than x * x, I'd use x * x).

Please notice that I cannot split the arrays in any way.

For PhiDrehung = 0 To 2*Sector-1 Step 3
 A = 0
 B = 0
 C = 0
 D = 0
 EC_Worst = 0
 BT_Worst = 0
 For k = 0 To FensterTheta
 For i = PhiDrehung-FensterPhi/2 To PhiDrehung+FensterPhi/2
 Select Case i
 Case Is < 0
 l = 360+i
 Case Is > 359
 l = i-360
 Case Else
 l = i
 End Select
 A = A + AmplCo(FileCounter-1, AktBeam,l,k) * AmplCo(FileCounter, AktBeam,l,k) * Cos(PhaseCo(FileCounter-1, AktBeam,l,k) - PhaseCo(FileCounter, AktBeam,l,k)) + AmplCr(FileCounter-1, AktBeam,l,k) * AmplCr(FileCounter, AktBeam,l,k) * Cos(PhaseCr(FileCounter-1, AktBeam,l,k) - PhaseCr(FileCounter, AktBeam,l,k))
 B = B + AmplCo(FileCounter-1, AktBeam,l,k) * AmplCo(FileCounter, AktBeam,l,k) * Sin(PhaseCo(FileCounter-1, AktBeam,l,k) - PhaseCo(FileCounter, AktBeam,l,k)) + AmplCr(FileCounter-1, AktBeam,l,k) * AmplCr(FileCounter, AktBeam,l,k) * Sin(PhaseCr(FileCounter-1, AktBeam,l,k) - PhaseCr(FileCounter, AktBeam,l,k))
 C = C + (AmplCo(FileCounter-1, AktBeam,l,k)^2 + AmplCr(FileCounter-1, AktBeam,l,k)^2)
 D = D + (AmplCo(FileCounter, AktBeam,l,k)^2 + AmplCr(FileCounter, AktBeam,l,k)^2)
 BT_Worst = BT_Worst + (AmplCo(FileCounter-1, AktBeam,l,k)^2 + AmplCr(FileCounter-1, AktBeam,l,k)^2) / (AmplCo(FileCounter, AktBeam,l,k)^2 + AmplCr(FileCounter, AktBeam,l,k)^2)
 Next
 Next
 EC_Worst = ((A^2 + B^2) / (C*D))^0.5 * 100
 If EC_Worst > EC((FileCounter-1) / 2, AktBeam) Then EC((FileCounter-1) / 2, AktBeam) = EC_Worst
 BT_Worst = 20 * Log( BT_Worst / (((FensterTheta+1)*(FensterPhi+1)) * ((PRad(FileCounter-1, AktBeam) + PRad(FileCounter, AktBeam))/2)) ) / Log(10)
 If BT_Worst > BT((FileCounter-1) / 2, AktBeam) Then BT((FileCounter-1) / 2, AktBeam) = BT_Worst
Next

FensterTheta and FensterPhi are user-inserted values, let Sector = 180.

• Vogel612: Welcome to Code Review! As we all want to make our code more efficient or improve it in one way or another, try to write a title that summarizes what your code does, not what you want to get out of a review. Please see How to get the best value out of Code Review - Asking Questions for guidance on writing good question titles.
• 200_success: Please read Vogel612's comment, as well as How to Ask. Titles such as "How do I optimize X?" are not acceptable on Code Review as they apply to nearly every single question. The desire for optimization is implied for all questions on the site. The site standard is to state what your code accomplishes, rather than your concern about the code.
• Noldor130884: As you guys wish. My only problem here, though, is that this cycle takes a long time to be executed and, let me stress it again, that I'd like to optimize in a certain way: not the syntax, not the style, but the least amount of operations required.
• mdfst13: I'm still not clear on what a "worst case angle" is. Is this the smallest angle? The largest? Something else? What constrains it? What do you know going into the problem? This is likely to be especially important here, as I suspect many of us won't recognize the meaning of things like Drehung and Fenster.
• Noldor130884: I think you misread the title. It's not a worst case angle but the worst case of two values (EC and BT) in a certain angle (which is normalized to 360° - so that for example -30° = 330°). Phi Drehung, FensterPhi and FensterTheta are manual inputs

Answer 1

Your code already seems to do only the steps necessary, slow performance seems to be the limitation of VBA.

You may do some minor improvements however:

Select Case i

You don't need to check conditions here, it's a simple Mod operation:
l = Application.Evaluate("=MOD(" & i & ",360)")

Formulas

Your calculations contains a lot of duplicates, you can use temporary variables to reduce number of calculations:

t1 = AmplCo(FileCounter-1, AktBeam,l,k) * AmplCo(FileCounter, AktBeam,l,k)
t2 = PhaseCo(FileCounter-1, AktBeam,l,k) - PhaseCo(FileCounter, AktBeam,l,k)
t3 = AmplCr(FileCounter-1, AktBeam,l,k) * AmplCr(FileCounter, AktBeam,l,k)
t4 = PhaseCr(FileCounter-1, AktBeam,l,k) - PhaseCr(FileCounter, AktBeam,l,k)
A = A + t1 * Cos(t2) + t3 * Cos(t4)
B = B + t1 * Sin(t2) + t3 * Sin(t4)
t5 = (AmplCo(FileCounter-1, AktBeam,l,k)^2 + AmplCr(FileCounter-1, AktBeam,l,k)^2)
t6 = (AmplCo(FileCounter, AktBeam,l,k)^2 + AmplCr(FileCounter, AktBeam,l,k)^2)
C = C + t5
D = D + t6
BT_Worst = BT_Worst + t5 / t6

This halves the number of array accesses in your code (so best case you can get double speed).

---

As I've written already, there are limitations in VBA too. Further improvement could be done implementing your code in e.g. Visual Studio and compile as an Excel add-in. (That case you could avoid run time compilation and debugging which are the biggest issue in VBA).

• Noldor130884: These are some great tips, thanks a lot! Before I select your answer as the correct one, may I ask you if you know (I couldn't find anything anywhere) if x * x is better than x^2 ? Also, I have no clue if it is possible to compile che code in Visual Studio, but I'm not using Excel (that begs the question: will the Evaluate command work?)... Instead I'm working on a proprietary program, that allows VBA programmation. This program puts at disposal a number of libraries that I must use to interpret their format of file...
• Noldor130884: Ok, just found out that the Evaluate doesn't work, and also the operation x Mod y is possible but doesn't take in account the negative angles... So I had to insert an If l < 0 Then l = l + 360. I wonder at this point if the Select Case is more efficient.
• PeterT: You don't need to use Evaluate to get the Mod function. See VBA equivalent to Excel's Mod function
• Noldor130884: Read the comment above please.
• Máté Juhász: "if x * x is better than x^2 " - as you've also more complex calculations, even if there's slight difference I don't think that would make any real improvement.