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)
    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

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

  • 2
    \$\begingroup\$ 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. \$\endgroup\$ – Vogel612 Oct 18 '16 at 6:31
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – 200_success Oct 18 '16 at 8:13
  • \$\begingroup\$ 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. \$\endgroup\$ – Noldor130884 Oct 18 '16 at 8:31
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – mdfst13 Oct 18 '16 at 10:34
  • 1
    \$\begingroup\$ 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 \$\endgroup\$ – Noldor130884 Oct 18 '16 at 10:49

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)")


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).

  • \$\begingroup\$ 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... \$\endgroup\$ – Noldor130884 Oct 19 '16 at 6:35
  • \$\begingroup\$ 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. \$\endgroup\$ – Noldor130884 Oct 19 '16 at 7:17
  • 2
    \$\begingroup\$ You don't need to use Evaluate to get the Mod function. See VBA equivalent to Excel's Mod function \$\endgroup\$ – PeterT Oct 19 '16 at 18:01
  • \$\begingroup\$ Read the comment above please. \$\endgroup\$ – Noldor130884 Oct 20 '16 at 5:46
  • \$\begingroup\$ "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. \$\endgroup\$ – Máté Juhász Oct 20 '16 at 5:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.